Oracle Backup and Restore Procedures

Backup Procedures

Create the following configuration file:

export-options.conf

BUFFER=4000000
COMPRESS=Y
GRANTS=Y
INDEXES=Y
ROWS=Y
TRIGGERS=Y
CONSTRAINTS=Y

Then at the command prompt in the same directory as the configuration file:

Command Prompt

exp USERA/PASSWORD@TNS FILE=FILENAME.DMP PARFILE=export-options.conf

First we need to drop all tables for the user we are going to import to. Using AQT or a similar database client, run the following SQL command:

SQL Command

SELECT 'DROP TABLE ' || owner || '.' || table_name || ' ' || 'CASCADE CONSTRAINTS' || ';' FROM all_tables WHERE owner = 'USERB'

This will generate a list of "Drop Table" statements which should then be run.

Run the following command at the command prompt:

Command Prompt

IMP USERB/PASSWORD@TNS BUFFER=4000000 IGNORE=NO ANALYZE=N FILE=FILENAME.DMP FROMUSER=USERA TOUSER=USERB