Monday 5 October 2020

EXPDP - Example and Commands

       Scenario Preview:

                

Hi all these are some expdp and impdp expamples below for various scenarios  which is helpful to me. So sharing the same below and it will re-memories for me also.

Create Directory  for EXPDP and IMPDP example 

create or replace directory smrtestenv_64 as '/u04/MasterDB/expdp/smrtestenv';

  •  Drop talespace with datafiles Examples

SQL 'DROP TABLESPACE TEST INCLUDING CONTENTS AND DATAFILES';

  • Check number objects exists on schema

select count(*),object_type,status from dba_objects where owner='INFODBA' group by status,object_type;

select count(*),object_type,status from dba_objects where owner='INFODBA2' group by status,object_type;

  • Check valid invalid objects of schema

select object_type,count(*) from dba_objects where status='INVALID' and owner='MFUNDLIVE' group by object_type;

select object_type,count(*) from dba_objects where status='VALID' and owner='MFUNDLIVE' group by object_type;

  • Check distinct tablespace other than default tablespace

select distinct tablespace_name from dba_segments where owner='INFODBA_TEST';

  • Check expdp backup status 
select sid, serial#, sofar, totalwork, dp.owner_name, dp.state, dp.job_mode from gv$session_longops sl, gv$datapump_job dp where sl.opname = dp.job_name and sofar != totalwork;

  •  check job name during impdp expdp 

select message, time_remaining/3600 hrs from v$session_longops where sofar <> totalwork;

select t.username,t.START_TIME,t.LAST_UPDATE_TIME,t.TIME_REMAINING,t.ELAPSED_SECONDS,

t.opname,t.target_desc,t.sofar,t.totalwork,t.message from V$SESSION_LONGOPS t where/* t.USERNAME = 'user_name' and*/ t.TARGET_DESC = 'EXPORT';

  • Recompile invalid objects

@?/rdbms/admin/utlrp.sql

set time on

set timi on

exec dbms_stats.gather_schema_stats('INFODBA',cascade => TRUE,estimate_percent => 30);

EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'INFODBA');

  • Drop Users

drop user INFODBA1,INFODBA cascade;

purge recyclebin;

  • Undo Retention  increase values if  expdp | impdp above  500 GB
alter system set undo_retention=2500 scope=both;

  • Schema DDL Metadata 

set heading off

set echo off

set feedback off

set long 999999

spool Check_PERMISSIONS.txt

set long 100000

set pages 200

select dbms_metadata.get_ddl( 'USER','INFODBA' ) from dual;

select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'INFODBA' ) from dual;

select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'INFODBA' ) from dual;

select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'INFODBA' ) from dual;

spool off

set heading on

set feedback on

  • Kill Session Example 

ALTER SYSTEM KILL SESSION 'sid,serial#';

ALTER SYSTEM KILL SESSION '1345,16564';

ALTER SYSTEM KILL SESSION '176,42616' immediate;

  •  expdp - impdp schema  example 

Single expdp schema

expdp \"sys/system@prod1 as sysdba\" directory=expdp dumpfile=expdp%u.dmp logfile=expdptest.log schemas=infodba1 parallel=2 content=all exclude=statistics parallel=2

Multiple expdp schema with same tablespace

expdp \"sys/system@prod1 as sysdba\" directory=expdp dumpfile=expdp%u.dmp logfile=expdptest.log schemas=infodba1,infodba2 parallel=2 content=all exclude=statistics parallel=2

Same impdp schema

impdp \"sys/system@prod2 as sysdba\" directory=expdp dumpfile=expdp%u.dmp logfile=impdp_remap.log schemas=infodba parallel=2 

 Impdp With different schema same tablespace (remap_schema=Source_schema:Destination_schema)

impdp \"sys/system@prod2 as sysdba\" directory=expdp dumpfile=expdp%u.dmp logfile=impdp_remap.log remap_schema=infodba:infodba2 parallel=2 

impdp from source schema to destination schema example

impdp \"sys/sys@prod12c as sysdba\" directory=expdp dumpfile=expdp.dmp logfile=impdp_remap.log remap_schema=infodba:infodba1 parallel=2

  •  EXPDP  Schema using different directories 

expdp \"sys/sys@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01%u.dmp,expdp1:expdp02%u.dmp logfile=expdp:expdp.log schemas=infodba1 exclude=statistics

impdp \"sys/sys@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01%u.dmp,expdp1:expdp02%u.dmp logfile=expdp1:impdpdp.log schemas=infodba1 parallel=2

impdp \"sys/sys@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01%u.dmp,expdp1:expdp02%u.dmp logfile=expdp1:impdpdp.log remap_schema=infodba1:infodba parallel=2

  •  EXPDP FILESIZE 

full =y

expdp \"sys/sys@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01%u.dmp,expdp1:expdp02%u.dmp logfile=expdp:expdp.log full=y exclude=statistics filesize=200M parallel=2

  • expdp filesize using %U 

expdp \"sys/sys@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp logfile=expdp:expdp.log schemas=infodba1 exclude=statistics filesize=500M

output result file example:

Dump file set for SYS.SYS_EXPORT_S

  C:\VARUN\EXPDP\EXPDP0001.DMP

  C:\VARUN\EXPDP1\EXPDP0101.DMP

  C:\VARUN\EXPDP2\EXPDP0201.DMP

  C:\VARUN\EXPDP\EXPDP0002.DMP

  C:\VARUN\EXPDP1\EXPDP0102.DMP

  C:\VARUN\EXPDP2\EXPDP0202.DMP

  C:\VARUN\EXPDP\EXPDP0003.DMP

  C:\VARUN\EXPDP1\EXPDP0103.DMP

  •  Expdp | Impdp remap schema (multiple schema)

expdp \"sys/sys@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01%u.dmp,expdp1:expdp02%u.dmp logfile=expdp:expdp.log schemas=infodba1,infodba2 exclude=statistics

Same Tablespace  

impdp \"sys/system@smrprodb as sysdba\"  dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp logfile=expdp:impdp.log remap_schema=infodba1:infodba3,infodba2:infodba4 parallel=2 filesize =200m

Different Tablespace

impdp \"sys/system@smrprodb as sysdba\"  dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp logfile=expdp:impdp.log remap_schema=infodba1:infodba3,infodba2:infodba4  remap_tablespace= idata1:idata3,idata2:idata4 parallel=2 

impdp \"sys/system@smrprodb as sysdba\"  dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp logfile=expdp:impdp.log remap_schema=infodba1:infodba3,infodba2:infodba4 parallel=2 table_exist_action=replace

impdp \"sys/sys@smrprodb as sysdba\"  dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp logfile=expdp:impdp.log remap_schema=infodba1:infodba3,infodba2:infodba4 remap_tablespace=idata:idata3,idata:idata4,ilog:ilog4,indx:indx4


  • EXPDP Using Flashback SCN 

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp  logfile=full_flashback_scn.log full=y FLASHBACK_SCN=2106577

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp  logfile=full_flashback_scn.log schemas=infodba flashback_time="to_timestamp('13-12-2017 13:23:00', 'DD-MM-YYYY HH24:MI:SS')"

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp  logfile=full_flashback_scn.log schemas=infodba flashback_time=flashback_time=to_timestamp\(sysdate-15/1440\)

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp  logfile=full_flashback_scn.log schemas=infodba flashback_time=TO_TIMESTAMP('13-12-2017 13:23:00','DD-MM-YYYY HH24:MI:SS')


expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp  logfile=full_flashback_scn.log schemas=infodba FLASHBACK_TIME="2017/12/1315:34:01"

  • Reuse_dumpfiles 
expdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table.log tables=infodba.PITEM,infodba.PAM_TREE parallel=2 reuse_dumpfiles=Y

  •   EXPDP Tables

expdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table.log tables=infodba.PITEM,infodba.PAM_TREE parallel=2

expdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table.log tables=infodba.PITEM,infodba.PAM_TREE,testuser.test_table parallel=2

  • IMPDP TABLES 

impdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log tables=infodba.PITEM:infodbatest.pitem2,infodba.PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=infodba.PITEM:infodbatest.pitem2,infodba.PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=PITEM:infodbatest.pitem2,PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=PITEM:infodbatest.pitem2,PAM_TREE:infodbatest:pam_tree2 parallel=2

  • TABLE_EXISTS_ACTION REPLACE| TRUNCATE | APPEND | SKIP

impdp "sys/sys@dmenv AS SYSDBA" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=REPLACE

impdp \"sys/sys@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=REPLACE

impdp \"sys/sys@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=truncate

impdp "sys/sys@dmenv AS SYSDBA" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=REPLACE

impdp \"sys/sys@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=replace

impdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log tables=infodba.PITEM,infodba.pam_tree remap_schema=infodba:infodbatest parallel=2  TABLE_EXISTS_ACTION=append

  •  IMPDP  different tables same schema 

 impdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp  logfile=impdp_dmenv_table1.log  tables=PITEM:PITEM2,PFORM:PFORM2

impdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=PITEM:pitem2,pam_tree:pam_tree2  remap_schema=infodba:infodbatest parallel=2

  • ESTIMATE_ONLY 

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE_ONLY=yes logfile=full_log_estimate.log

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE=y logfile=full_log_estimate.log full=y

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE=BLOCKS logfile=full_log_estimate.log full=y

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE=STATISTICS logfile=full_log_estimate.log full=y

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE_ONLY=y logfile=full_log_estimate.log schemas=infodba

  •  Remap_tablespaces 

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=tablesace_dmenv.dmp  logfile=tablespace_dmenv.log tablespace=IDATA

imppdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=tablesace_dmenv.dmp  logfile=tablespace_dmenv.log remap_tablespaces=IDATA:IDATATEST

  • Transport_tablespaces 

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=transport_tablesace_dmenv.dmp  logfile=transport_tablespace_dmenv.log transport_tablespaces=IDATA

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=transport_tablesace_dmenv.dmp  logfile=transport_tablespace_dmenv.log transport_tablespaces=IDATA,ILOG

  • RAC Database expdp example 

service_name=RAC service name (optional)

cluster=y

expdp \"sys/system123@prodb as sysdba \" directory=expdpu01 dumpfile=SOE12%u.dmp logfile=expdp_soe.log schemas=soe cluster=y service_name=prodb


Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)