Datapump Dynamic List of Tables

From NazimWIKI
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