Expdp - Par file examples

 Expdp -  Par file examples 

Table par file examples

cat expdp_TABLE.par

USERID = '/ as sysdba'

DIRECTORY   = <Directory_name>

DUMPFILE    = Table_Target_%U.dmp

LOGFILE     = expdp_Table.log

tables     = <schema_name>.<Table_name>

PARALLEL    = 4

FILESIZE = 10G

EXCLUDE=STATISTICS

METRCIS=Y

LOGTIME=ALL

Schema par file examples 

cat expdp_Schemas.par

USERID = '/ as sysdba'

DIRECTORY   = <dump_directory>

DUMPFILE    = <dumpfile_name>%U.dmp

LOGFILE     = <logfile_name>.log

schemas    = <schema_name>,<schema_name>,<schema_name>

PARALLEL    = 4

FILESIZE = 10G

EXCLUDE=STATISTICS

METRCIS=Y

LOGTIME=ALL

Exclude Tables Examples : 

directory=<directory_name>

SCHEMAS=<schema_name>.<schema_name>

exclude=TABLE:"IN(select table_name from dba_tables where table_name like '<schema_name>.<table_name>')"

dumpfile=EXPDP_source%U.DMP

logfile=IMPDP_source.log

parallel=30

directory=<directory_name>

content =metadata_only

schemas    = <schema_name>,<schema_name>,<schema_name>

dumpfile=metadata_.DMP.%U

logfile=metadata__.log

full=y 

col OWNER for a20

col DIRECTORY_NAME for a30

col DIRECTORY_PATH for a70

set lines 200

select * from dba_directories;

Export example 

exp \'/ as sysdba\' file=<directory_location>/six_tabs.dmp log=<directory_location>/six_tabs.log tables=<schema_name>.<Table_name>,<schema_name>.<Table_name>,<schema_name>.<Table_name>  statistics=none feedback=10000

  

  BUFFER=100000

set lines 200

col JOB_NAME for a20

col OWNER_NAME for a30

col OPERATION for a20

col JOB_MODE for a30

col STATE for a30

SELECT owner_name, job_name, operation, job_mode,state, attached_sessions FROM dba_datapump_jobs ORDER BY 1,2;

select distinct tablespace_name from dba_segments where owner='<schema_name>';

select sum(bytes/1024/1024/1024) from dba_segments where owner in('<schema_name>','<schema_name>','<schema_name>','<schema_name>','<schema_name>','<schema_name>');

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

SELECT sum(bytes)/1024/1024/1024 as "Size in GB" from dba_segments WHERE owner = UPPER('&schema_name');

Set heading off 

SQL> select name from v$database;

SQL> spool /<directory_path>/drop_objects.sql

SQL> select 'drop '||object_type||' '||owner||'."'|| object_name||'"' ||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS purge;',';') from dba_objects where owner='<schema_name>';

SQL> spool off

set heading on

set echo on

set pagesize 0

set feedback on

set verify on

SQL> spool /<directory_path>/drop_objects.log

SQL> @/dvra01/<directory_path>/drop_objects.sql

SQL> spool off

SQL>

import  same schema but different tablespace:

 cat impdp.par

directory=<directory_name>

dumpfile=export_schemas.dmp

logfile=import_schemas.log

schemas=<Schema_name>,<schema_name>

REMAP_TABLESPACE=<source_tablespace>:<Target_tablespace>

REMAP_TABLESPACE=<source_tablespace>:<Target_tablespace>

REMAP_TABLESPACE=<source_tablespace>:<Target_tablespace>

parallel=2

Data only impdp on different tables 

impdp directory= dumpfile=.DMP logfile=impdp1.log  remap_table=<schema_name_source>.<tab_name_source>:<schema_name_trgt>.<trgt>  CONTENT=DATA_ONLY DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS

Impdp examples

cat impdp_target.par

directory=<dir_name>

SCHEMAS=<schema_name>

dumpfile=1.%U

logfile=IMPDP.log

parallel=11

transform=disable_archive_logging:Y

nohup expdp \"/ as sysdba\" parfile=fulldb_export.par &

cat impdp_table.par

USERID = '/ as sysdba'

DIRECTORY   = <>

DUMPFILE    = <>.dmp.%U

LOGFILE     = <>_impdp.log

CONTENT     = ALL

PARALLEL    = 40

tables     = <schema_name>.<table_name>,<schema_name>.<table_name>

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:table

exclude=STATISTICS

METRICS=Y

LOGTIME=ALL

Object _count 

SQL> SET SERVEROUTPUT ON SIZE 1000000

SQL> --

SQL> DECLARE

  2  VAL NUMBER;

  3  BEGIN

  4      DBMS_OUTPUT.ENABLE(1000000);

  5      FOR I IN (SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER IN ('<schema_name>') AND OBJECT_TYPE = 'TABLE'  ORDER BY OBJECT_NAME) LOOP

  6          EXECUTE IMMEDIATE 'SELECT count(*) FROM <schema_name>.' || i.OBJECT_NAME INTO val;

  7          DBMS_OUTPUT.PUT_LINE(I.OBJECT_NAME || ': ' || VAL );

  8      END LOOP;

  9  END;

 10  /

select SEGMENT_NAME,sum(bytes/1024/1024/1024) GB from dba_segments where OWNER='<>' and SEGMENT_NAME='<>' group by segment_name,segment_type;

TRIGGERS

SQL> select OWNER,TRIGGER_NAME,TABLE_OWNER,STATUS from DBA_TRIGGERS where OWNER='<>' and TRIGGER_NAME IN ('<>,<>,<>');

Table size check 

select segment_name,segment_type, sum(bytes/1024/1024/1024) GB

 from dba_segments

 where segment_name='&Your_Table_Name' 

group by segment_name,segment_type; 

Nohup expdp and impdp example 

nohup expdp \"/ as sysdba\" parfile=fulldb_export.par &

nohup impdp \"/ as sysdba\" parfile=fulldb_export.par &

pbrun -u oracle runshell

$nohup expdp parfile=expdp_DBNAME_SCHEMA.par &

$nohup impdp parfile=impdp_DBNAME_SCHEMA.par &