Expdp selected table when the table list is big
If table list is big then There may be two option which can help us to expdp selected tables
1. we can use select query in the INCLUDE parameter in expdp with filter condition
2. Manual Approach we can use par file and pass those files which has to take expdp backup
Let suppose if we want to export only those table which has records, then we would have to specify INCLUDE parameter in the EXPDP and specify NUM_ROW>0
include=table:\"in \(select table_name from user_tables where NUM_ROWS >0 \)\"
Below are the demonstration example
C:\Users>expdp Dilip/PasswD123#@PDB1 DIRECTORY=PDB1_DUMP_DIR DUMPFILE=TESTDUMP12JUNE2023_ROWGT0.dmp include=table:\"in \(select table_name from user_tables where NUM_ROWS >0 \)\"
Export: Release 12.2.0.1.0 - Production on Mon Jun 12 17:19:28 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "DILIP"."SYS_EXPORT_SCHEMA_01": Dilip/********@PDB1 DIRECTORY=PDB1_DUMP_DIR DUMPFILE=TESTDUMP12JUNE2023_ROWGT0.dmp include=table:"in \(select table_name from user_tables where NUM_ROWS >0 \)"
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "DILIP"."TESTING_TBL_01" 1.781 GB 34023780 rows
. . exported "DILIP"."TESTING_TBL_02" 21.78 KB 1 rows
. . exported "DILIP"."TESTING_TBL_03" 20.64 KB 165 rows
. . exported "DILIP"."TESTING_TBL_04" 19.60 KB 291 rows
. . exported "DILIP"."TESTING_TBL_05" 17.38 KB 164 rows
Master table "DILIP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DILIP.SYS_EXPORT_SCHEMA_01 is:
D:\BACKUP1\PDB_DUMP_DIR\TESTDUMP12JUNE2023_ROWGT0.DMP
Job "DILIP"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Jun 12 17:20:36 2023 elapsed 0 00:01:06
2 Second option is Manual Approach
I create parfile and save it as expdp_table_12062023.par
with below contents I will use this parfile in expdp parameter
We can get table list which has more than 0 rows using below query
select table_name||',' from user_tables where NUM_ROWS>0;
DIRECTORY=PDB1_DUMP_DIR
DUMPFILE=TESTDUMP12JUNE2023_ROWGT01.DMP
TABLES=
TESTING_TBL_01,
TESTING_TBL_02,
TESTING_TBL_03,
TESTING_TBL_04,
TESTING_TBL_05
LOGFILE=expdp_TESTDUMP12JUNE2023_ROWGT01.log
C:\Users>expdp Dilip/PasswD123#@PDB1 parfile=expdp_table_12062023.par
Export: Release 12.2.0.1.0 - Production on Mon Jun 12 18:13:09 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "DILIP"."SYS_EXPORT_TABLE_01": Dilip/********@PDB1 parfile=expdp_table_12062023.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "DILIP"."TESTING_TBL_01" 1.781 GB 34023780 rows
. . exported "DILIP"."TESTING_TBL_02" 403.8 KB 3493 rows
. . exported "DILIP"."TESTING_TBL_03" 347.5 KB 2695 rows
. . exported "DILIP"."TESTING_TBL_04" 170.7 KB 1677 rows
. . exported "DILIP"."TESTING_TBL_05" 174.4 KB 3103 rows
Master table "DILIP"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DILIP.SYS_EXPORT_TABLE_01 is:
D:\BACKUP1\PDB_DUMP_DIR\TESTDUMP12JUNE2023_ROWGT01.DMP
Job "DILIP"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jun 12 18:13:44 2023 elapsed 0 00:00:34
Command
#expdp \"sys/Oracle_4U@PDB1 as sysdba \" directory=PDB1_DUMP_DIR schemas=LOCAL_SCH1,LOCAL_SCH2 dumpfile=LOCAL_SCH_21JULY2023.dmp logfile=Expdp_LOCAL_SCH_21JULY2023.log
expdp directory=DATA_PUMP_DIR dumpfile=gokhan.dmp include=table:\"in \(select table_name from user_tables where table_name like \'INVOICE%\' or table_name like \'PRODUCT%\' \)\”
expdp APP_SCHEMA/Oracle_4U@PDB1 directory=EXPORT_BACKUP_DIR dumpfile=APP_SCHEMA_DULICATED_TABLES_12MAY2023.dmp include=table:\"in \(select table_name from user_tables where DUPLICATED = \'Y\' \)\"
expdp APP_SCHEMA/Oracle_4U@PDB directory=EXPORT_BACKUP_DIR dumpfile=APP_SCHEMA_sharded_TABLES_12MAY2023.dmp include=table:\"in \(select table_name from user_tables where sharded = \'Y\' \)\"
--Exclude big table
expdp directory=DATA_EXPORT_DIR schemas=SCOTT dumpfile=TESTSCHEMA_07102024.dmp logfile=TESTSCHEMA_07102024.log exclude=TABLE:"IN('BIG_TABLE_01')"
No comments:
Post a Comment