How to use impdp/expdp to transfer schema from one database to another

Jephe Wu -

Environment: Oracle 11g 32bit
Objective: copy schemas base and u_user1 from UAT Oracle DB uatdb to schemas base and d_user1 in devdb
Note: from database uatdb to devdb, the base schema is global schema, keep the name as base, but for u_user1 schema, change the schema name to d_user1

1. stop listener - lsnrctl stop
2. drop schema base and u_user1 in uatdb
. oraenv

sqlplus / as sysdba
drop tablespace base including contents and datafiles;
drop tablespace u_user1 including contents and datafiles;
drop user base cascade;
drop user u_user1 cascade;

If you cannot drop user, such as it's saying the user has connection to the database. You can use the following commands to kill all the session relating to this user.

sqlplus / as sysdba @killusersession.sql

the content of killusersession.sql is:
set head off feedback off pagesize 0 echo off term off linesize 32767 trimsp on tab off define off;
spool /u01/scripts/killusersessionfinal.sql;
alter user u_user1 account lock;
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='u_user1';

select 'exit;' from dual;
spool off;

sqlplus / as sysdba @killusersessionfinal.sql

3. create tablespace
create tablespace u_user1 datafile '/u01/app/oracle/oradata/uatdb/base.dbf' size 100m autoextend on next 10m flashback off;

note: use 'select name from v$datafile' to find the existing datafile path

4. export and import files
for base schema, since the source and destination schema name is the same, so use

. oraenv
expdp system dumpfile=base.dmpdp logfile=base.logdp schemas=base exclude=statistics
note: this will use the default directory which is /u01/app/oracle/admin/uatdb/dpdump

. oraenv
impdp system dumpfile=base.dmpdp logfile=base.logdp schema=base
note: this will use the default directory which is /u01/app/oracle/admin/devdb/dpdump

for u_user1 schema, we use the following impdp command:
impdp system dumpfile=u_user1.dmpdp logfile=u_user1.logdp remap_schema=u_user1:d_user1 remap_tablespace=u_user1:d_user1 transform=oid:n

5. preparing the existing public synonyms

# preparing existing public synonyms and permission granting script
cat > /u01/cronjob/existing_public_synonym_generation.sql << owner="'PUBLIC';"> exec dbms_utility.compile_schema('BASE');

7. check invalid objects
column object_type format A10;
column object_name format A30;
select object_type "Invalid BASE",object_name from dba_objects where owner='BASE' and status <> 'VALID' order by object_type,object_name;
select object_type "Invalid D_USER1",object_name from dba_objects where owner='D_USER1' and status <> 'VALID' and object_name not like 'FB_%' order by object_type,object_name;

select count(*) BASE, object_type from dba_objects where owner='BASE' and status <> 'VALID' group by object_type;
select count(*) U_USER1, object_type from dba_objects where owner='U_USER1' and status <> 'VALID' and object_name not like 'FB_%' group by object_type;

8. start listener
after starting listener, you should 'alter system register' to make database to register with listener immediately.

9. consistent=y

expdp '/ as sysdba' dumpfile=jephe.dmp logfile=jephe.log flashback_time=systimestamp

a. 11.2 introduced legacy mode, now can use the old exp and imp parameters, including 'consistent=y'.
b. by default expdp is consistent only for the table it is currently exporting.
c.  flashback_time=systimestamp is same as 'consistent=y'.