Saturday, September 1, 2007

Export of Oracle Database schemas

Earlier I used to use exp and imp commands to do backup of schemas, these days Oracle introduced expdp and impdp to do the same tasks. Reason to switch are many. From Docs :"Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another."

Data Pump uses four mechanisms for moving data in and out of databases. They are as follows, in order of decreasing speed:
  • Data file copying
  • Direct path
  • External tables
  • Network link import
Commands I use to do this type export and import are:

You need a directory object:
CREATE DIRECTORY dpump_dir1 AS '/usr/apps/datafiles';
GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO hr;

Run the export command:
expdp hr/password SCHEMAS=hr TABLES=employees DIRECTORY=dpump_dir1
DUMPFILE=employees.dmp;

Run the import command
impdp hr/password SCHEMAS=hr TABLES=employees
DIRECTORY=dpump_dir1 DUMPFILE=employees.dmp;

SELECT directory_name, directory_path FROM dba_directories
WHERE directory_name='DATA_PUMP_DIR' WHERE directory_name='DATA_PUMP_DIR';

See OTN docs atUtilities Guide