Author: Scott Stephens

Page: 1 2

Import and export are amongst Oracle's most critical yet reliable utilities. These utilities can be used to recover or rebuild a database, even on a completely different machine and operating system. You can resize database objects by creating them manually before importing data. But with multi-gigabyte or terabyte databases, locating and fixing errors can be a difficult and very time consuming recovery effort.

Running Imports:

When an import runs, it creates tables and loads data before loading other schema objects such as views, synonyms, and sequences, which you can create with simple SQL statements.

Import Failure:

If there are any failures during this phase, you need to make a list of the schemas and tables that fail to load data.

For each partially loaded table, you need to truncate those tables with the truncate table schema.mytable; command.

Then, you need to reload the tables that use Import's USERS and TABLES options.

You must run a separate import job for each schema user. For example, to handle the failure of user1.table1, user1.table2, user2.table3, user2.table4, you could run these commands from a batch script:

impuserid=system/manager USERS=(user1) TABLES=(table1,table2)

imp userid=system/manager USERS=(user2) TABLES=(table3,table4)

Manual Continuation after Import Fail:

If the import fails after table creation and loading, there's a technique that you can use to continue manually using SQL*Plus.

First, run the import with "show=y" as an option. This will send the SQL statements that would have been executed to the terminal output, but it will not execute them. Even if you specify a LOG parameter, the output will still be sent to STDERR, so you may want to pipe it to a file rather than tying up the network or CPU with terminal output. Use the syntax that is appropriate for your operating system. Below syntax will work on Windows and "sh" or "ksh" variants on UNIX.

impuserid=system/manager full=y SHOW=Y LOG=imp.log 2> imp.out

The format of the SHOW=Y output is structured enough to re-format into a SQL script, which you can run from SQL*Plus. You can also eliminate any commands that were executed before the point of failure. The format of the output file is a list of SQL statements enclosed in double quotes with normal IMP output prompts. If a line is greater than 75 characters, it will be continued on the next line. Lines shorter than 75 characters are either complete lines, or they're the continuation of a previous line and may be zero length for a blank line.

Below output is from a SHOW=Y session

. importing RMAN's objects into RMAN
 "ALTER SCHEMA = "RMAN""
 "CREATE SEQUENCE "RMAN_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 "
 "INCREMENT BY 1 START WITH 1838 CACHE 20 NOORDER NOCYCLE"

The above output can be converted to SQL.

ALTER SESSION SET CURRENT_SCHEMA = RMAN;

CREATE SEQUENCE "RMAN_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999
 INCREMENT BY 1 START WITH 12 CACHE 20 NOORDER NOCYCLE;

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Important Oracle Issues and Solutions: A Must Read!!
   Determining Oracle TEMP Size Usage!!
   Secrets of Resources with AWR Reports, Great!!
   Saving and Restoring Old Statistics in Oracle!!