Datapump Dynamic List of Tables
Jump to navigation
Jump to search
In PeopleSoft, I needed to export out all configuration/system-owned tables as a backup each week. To achieve this, my first step was to create a view listing all these tables, by filtering out those which are not prefixed with "PS_"
CREATE OR REPLACE VIEW EXPORT_WEEKLY (TABLE_NAME) AS SELECT table_name FROM all_tables WHERE owner = 'SYSADM' AND NOT REGEXP_LIKE (table_name, '^PS(_)');
I then used Oracle Datapump to export the tables listed in this view ...
expdp username/password parfile=parfilename
parfile contents
DIRECTORY=dpump_dir1 DUMPFILE=dpump_dir1:dumpfile.dmp LOGFILE=dpump_dir1:logfile.log SCHEMAS=SCHEMANAME INCLUDE=TABLE:"IN (SELECT TABLE_NAME FROM EXPORT_WEEKLY)" CONTENT=DATA_ONLY