Datapump Activities

Datapump Activities

-Take refresh in the same database and from the same schema
-Take refresh in the same database but different schema
-Take refresh in the same database into different schema with different name
-Take backup activity and restoration back the same table.
-Take refresh to the remote database, to the same schema with different name.
-Multiple table backups from multiple schemas.
-Restore specific set of tables from the multiple tables backup

select segment_name, bytes/1024, owner from dba_segments where segment_name='DEPT' and owner='SCOTT';
select * from dba_directories;
mkdir -p /u01/oradata/dpbkps
create or replace directory DPBKPS as '/u01/oradata/dpbkps';

--Take the table dept backup from scott schema and refresh to the same schema when requested later.

expdp dumpfile=exp_scott.dept.dmp logfile=scott.dept.log directory=DPBKPS tables=scott.dept compression=all

Note: compression will include additional load on cpu, and it will take more time compare to normal.

You can drop the existing table or used table_exists_action command.

impdp directory=DPBKPS dumpfile=exp_scott.dept.dmp logfile=impdp_scott.dept.log table_exists_action=replace/truncate/append

--Table refresh in the same database but different schema

impdp directory=DPBKPS dumpfile=exp_scott.dept.dmp logfile=impdp_scott.dept.log remap_schema=SCOTT:SYSTEM

--Take refresh in the same database into different schema with different name

select table_name from dba_tables where table_name='DEPT_TMP';

impdp system/manager directory=DPBKPS dumpfile=exp_scott.dept.dmp logfile=impdp_scott.dept.log remap_schema=SCOTT:SYSTEM remap_table=DEPT.DEPT_TEMP

--Take refresh to the remote database, to the same schema with different name.

scp the export dumpfile to target location:

impdp system/manager directory=DPBKPS dumpfile=exp_scott.dept.dmp logfile=impdp_scott.dept.log table_exists_action=replace

--Multiple table backups from multiple schemas.

check the size of all tables.

select sum(bytes/1024) from dba_segments where segment_name in ('DEPT','DEPT_TEMP','EMP') and owner in ('SCOTT','SYSTEM');

expdp directory=DPBKPS dumpfile=expdp_multi_tables.dmp logfile=expdp_multi_tables.log tables=scott.dept,scott.emp,system.dept,system.dept_temp

impdp directory=DPBKPS dumpfile=
expdp_multi_tables.dmp logfile=expdp_multi_tables.log tables=system.dept table_exists_action=replace

-A schema backup when required
-Restore the same schema on the same database when requested
-A schema refresh from PROD to NON-PROD environment.
-A schema backup when requested and restore only set of tables from the backup dump.
-Taking multiple schemas backup backup and only restore one schema to the database or only one table of one schema to restore.

--A schema backup when required
Take the backup of SCOTT schema on MYDB database.

check the schema size:
select sum(byes/1024/1024) from dba_segments where owner='SCOTT';

check the directory location and its space:
select * from dba_directories;

expdp directory=dpbkps dumpfile=expdp_scott.dmp logfile=expdp_scott.log compression=all schema=SCOTT

Note: compression option will take longer time to export the backup.

expdp directory=dpbkps dumpfile=expdp_scott_%U.dmp logfile=expdp_scott.log compression=all schema=SCOTT parallel=2

--Restore the same schema on the same database when requested

drop the user if it is already available using cascade option.

impdp directory=dpbkp dumpfile=exp_scott_%U.dmp logfile=exp_scott.log parallel=2

--How to take DDL's backup only ?

expdp directory=dpbkps dumpfile=expdp_scott.dmp logfile=expdp_scott.log compression=all schema=SCOTT content=metadata_only

--A schema backup when requested and restore only set of tables from the backup dump.

impdp directory=dpbkp dumpfile=exp_scott_%U.dmp logfile=impdp_scott.dept.log parallel=2 tables=scott.dept table_exist_action=replace

--Taking multiple schemas backup backup and only restore one schema to the database or only one table of one schema to restore.

check the schema size:
select sum(byes/1024/1024) from dba_segments where owner in('SCOTT','OUTLN');

expdp directory=dpbkps dumpfile=expd_multi_%U.dmp logfile=exp_multi.log compression=all parallel=2 schemas=scott,outln

impdp directory=dpbkps dumpfile=exp_multi_%U.dmp logfile=impdp_multi.log parallel=2 tables=outln.ol$ table_exists_action=replace