Note: Technical Talk being moved to:
www.nazimcricket.com/wiki


Friday, 5 June 2009

Data Pump - Dynamic List of Tables to Export

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

No comments: