Here,I will give u Detailed steps for Schema Migration using exp/imp, pipe,gz between two databases

How to migrate the schema from one database to another database

The below steps need to be done on the Source Database

Metadata about schema

SQL> select owner,sum(bytes)/1024/1024 from dba_segments where owner in ('EXAMPLE_SCH') group by owner; 

SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_segments where owner in ('EXAMPLE_SCH') group by tablespace_name; 

SQL> select default_tablespace,username from dba_users where username in ('EXAMPLE_SCH'); 
SQL> select owner,status,object_type,count(*) from dba_objects where owner in ('EXAMPLE_SCH') group by object_type,owner,status;
SQL> select object_type,count(*) from dba_objects where owner in ('EXAMPLE_SCH') group by object_type; 

SQL> select count(*),status from dba_objects where owner in ('EXAMPLE_SCH') group by status; 

SQL> select object_type,count(*),status from dba_objects where owner in ('EXAMPLE_SCH') group by status,object_type;  

Export the schema

vi exp.sh
#!/bin/ksh
export NLS_LANG=AMERICAN_AMERICA.UTF8
mknod /tmp/exp_pipe1 p
gzip -c < /tmp/exp-pipe1 >  SOURCE_schemas.dmp.gz &
exp system/password file=/tmp/exp_pipe1 buffer=2097152 log=exp_SOURCE_schemas.log statistics=none owner=EXAMPLE_SCH
rm -f /tmp/exp_pipe1
unset NLS_LANG

nohup ./exp_SOURCE_schemas.sh > exp_SOURCE_schemas.log 2>&1

On the Target Database

Create metadata in the Target database

1. Create tablespaces as per the output from you got on Source database

2.Create users on Target database
Run the following script on the source database and execute the resultant script on the Target database
set linesize 300
set pagesize 300
spool create_users.sql
select 'create user '||username||' identified by values '''||password||''' default tablespace '||default_tablespace||' temporary tablespace temp profile '||profile||';' from dba_users where username in ('EXAMPLE_SCH');
spool off

3. Grant Quotas on Target database
Run the following script on the source database and execute the resultant script on the Target database

spool create_tablespace_quota.sql
select 'alter user '||username||' quota unlimited on '||tablespace_name||';' from dba_ts_quotas where MAX_BYTES='-1' and username in ('EXAMPLE_SCH');
select 'alter user '||username||' quota '||max_bytes||' on '||tablespace_name||';' from dba_ts_quotas where MAX_BYTES!='-1' and username in ('EXAMPLE_SCH');
spool off

4. Grant Roles on Target database
Run the following script on the source database and execute the resultant script on the Target database

spool create_grant_roles.sql
select 'grant '||GRANTED_ROLE||' to '||GRANTEE||';' from dba_role_privs where ADMIN_OPTION='NO' and grantee in ('EXAMPLE_SCH');
select 'grant '||GRANTED_ROLE||' to '||GRANTEE||' with admin option;' from dba_role_privs where ADMIN_OPTION='YES' and grantee in ('EXAMPLE_SCH');
spool off

5. Grant System privs on Target database
Run the following script on the source database and execute the resultant script on the Target database

spool create_sys_privs.sql
select 'grant '||PRIVILEGE||' to '||GRANTEE||';' from dba_sys_privs where ADMIN_OPTION='NO' and grantee in ('EXAMPLE_SCH');select 'grant '||PRIVILEGE||' to '||GRANTEE||' with admin option;' from dba_sys_privs where ADMIN_OPTION='YES' and grantee in ('EXAMPLE_SCH');
spool off 

Import schema in Target database

vi imp.sh
#!/bin/ksh
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
mknod /tmp/imp_pipe1 p
gunzip -c SOURCE_schemas.dmp.gz >/tmp/imp_pipe1 &
imp system/password file=/tmp/imp_pipe1 buffer=20971520 log=imp_TARGET_schemas.log full=y
rm -f /tmp/imp_pipe1
unset NLS_LANG

Post steps

1. Verify the Import logs for errors
2. Run the metadata which was ran on Source to compare the object counts
3. Grant Table privs on the Target

Run the following script on the source database and execute the resultant script on the Target database

spool create_tab_privs.sql
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' from dba_tab_privs where GRANTABLE='NO' and grantee in ('EXAMPLE_SCH');
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||' with grant option;' from dba_tab_privs where GRANTABLE='YES' and grantee in ('EXAMPLE_SCH');
spool off

4. Compile The imported schema

EXEC DBMS_UTILITY.COMPILE_SCHEMA ('EXAMPLE_SCH');

5. gather the stats on all the Imported schema’s

exec dbms_stats.gather_schema_stats('EXAMPLE_SCH', cascade=>TRUE);

Related Articles

how to import schema in oracle using impdp
Oracle 12c pluggable database commands