expdp DATAPUMP

datapump schema structure only
expdp directory=DP_DIR dumpfile=ZZT_SERVICE_LOG_%U.dmp content=metadata_ only schemas=HMA_TM_PROD_GEN2_MONITOR
CREATE OR REPLACE DIRECTORY DP_DIR AS '/ora_nfs/naipsoadb01/pnicon/';
GRANT READ, WRITE ON DIRECTORY DP_DIR TO SVADAPALLI;
impdp directory=IMP_DIR dumpfile=hmf_tm_prod_gen2_monitor_metadata.dmp
DROP DIRECTORY IMP_DIR;
select count(*) from dba_objects where owner like 'HMA_TM_PROD_GEN2_MONITOR';
_______________________________________________________________________________________________________________________________
Full Database


nohup expdp \"\/@shsub_sys as sysdba\" full=y directory=EXPDP_DUMPDIR dumpfile=shsub_full_10232014_1_%U.dmp parallel=4 filesize=5G logfile=shsub_full_10232014_1.log &

expdp \"\/@shsub_sys as sysdba\" full=y directory=EXPDP_DUMPDIR dumpfile=shsub_full_10292014_220000_%U.dmp parallel=4 filesize=5G logfile=shsub_full_10292014_220000.log

gzip shsub_full_10292014_220000.tar

tar cvf shsub_full_10292014_220000.tar shsub_full_10292014_220000_*.dmp

to gunzip and tar
tar -zcvf srav.tar.gz <filepath/name>

Tar

tar cvf shsub_full_10232014_220001.tar shsub_full_10232014_220001_*.dmp
_______________________________________________________________________________________________________________________________

Datapump tables
expdp directory=DP_DIR dumpfile= hma_month_inv%U.dmp tables=HMA_DM_SALES.SLM_TH_MONTH_INVENTORY compression=all parallel=8;

or

expdp directory=? dumpfile=? tables=schemaname.tablename, schemaname.tablename2,3 compression=all parallel=4

impdp directory=dp_dir dumpfile= hma_month_inv%U.dmp parallel=8

impdp directory=DP_DIR dumpfile=exp_pktms_cons%U.dmp schemas=WGPISSUSR parallel=4 table_exists_action=skip (for just objects other than tables)


Mark
expdp \"\/ as sysdba\" directory=DP_DIR dumpfile=ZZT_SERVICE_LOG_%U.dmp logfile=ZZT_SERVICE_LOG.log tables=ICON_ADMIN.ZZT_SERVICE_LOG parallel=6 compression=all

impdp \"\/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=QM_TF_INVENTORY_SUMMARY_23OCT2014_%U.dmp logfile=23oct2014_table.log table_exists_action=REPLACE parallel=4;

 expdp \"\/ as sysdba\" dumpfile=sktms3_23oct2014 directory=EXPDP_DUMPDIR logfile=23oct2014_exp.log full=Y compression=all parallel=6

 expdp \"\/@sktms_sys as sysdba\" dumpfile=sktms_01062015_uvods_%U.dmp directory=EXPDP_DUMPDIR logfile=01062015_exp_uvods.log Schemas=UVODS compression=all parallel=6

  impdp \"\/@sktms_sys as sysdba\" directory=EXPDP_DUMPDIR dumpfile=sktms3_11262014_uvods_%U.dmp logfile=11262014_imp_uvods.log  parallel=6;


  expdp \"\/ as sysdba\" full=y directory=EXPDP dumpfile=pktms_full_12032014_%U.dmp parallel=6 logfile=pktms_full_12032014.log compression=all



impdp \'/@dktms_sys as sysdba\' directory=DP_DIR dumpfile=pktms_full_12032014_%U.dmp logfile=imp_pktms_wgpccwusr_12032014.log schemas=WGPCCWUSR table_exists_action=replace


_______________________________________________________________________________________________________________________________

Time consistent expdp
expdp \’/ as sysdba \’

directory=DP_DIR
dumpfile=exp_pktms_cons%U.dmp
logfile=exp_pktms_cons.log
FLASHBACK_TIME="to_timestamp('03-10-2014 11:00:00','DD-MM-YYYY HH24:MI:SS')"
full=Y
compression=all
parallel=6

expdp directory=DP_DIR file=exp_pktms_cons%U.dmp log=exp_pktms_cons.log FLASHBACK_TIME=”to_timestamp(’03-SEP-2014 11:00:00′,’DD-MON-YYYY HH24:MI:SS’)” full=Y compression=all parallel=6

impdp directory=DP_DIR dumpfile=exp_pktms_cons%U.dmp table_exists_action=replace parallel=8

_______________________________________________________________________________________________________________________________


To kill a datapump job

select * from dba_datapump_jobs;

see the user of job and name for example svadapalli executing SYS_EXPORT_FULL_01

Now exit sql

expdp  svadapalli attach=SYS_EXPORT_FULL_01

export> kill_job

are you sure?

Yes


___________________________________________________________________________________________________________________________________


Monitoring EXPDP

select * from ( select opname, target, sofar, totalwork,units, elapsed_seconds, message from v$session_longops order by start_time desc) where rownum <=1;

select JOB_NAME,STATE from dba_datapump_jobs;

select sum(bytes/1024/1024) from dba_segments;

__________________________________________________________________________

for structure only then use content=metadata_only

___________________________________________________________________________________________________________________________________

In datavault env

login as data vault owner and run this below with user name so he can export will full access

EXEC DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('SVADAPALLI');

______________________________________________________________________________________________________________________________________


you can export whole tablespace using datapump with parameter tablespaces=users

______________________________________________________________________________________________________________________________________

You can export from 11g and import to 10g

For this while exporting use parameter version=10.2014

___________________________________________________________________________________________________________________________________________

Exclude schemas

expdp \'/ as sysdba\' directory=refresh dumpfile=fulldhods_%U.dmp parallel=4 compression=all full=y EXCLUDE=SCHEMA:\"IN \(\'DEDQ_EDQRESULTS\', \'DEDQ_EDQCONFIG\'\)\"

Exclude tables

expdp system/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=abc.dmp FULL=Y 
EXCLUDE=TABLE:\"IN \(\'NAME\', \'ADDRESS\' , \'EMPLOYEE\' , \'DEPT\'\)\" 
EXCLUDE=SCHEMA:\"IN \(\'WMSYS\', \'OUTLN\'\)\"