TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!

Import and export are two of Oracle’s most stable and
reliable utilities. You can use them 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.

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. This is the most critical part of the
import. If there are any failures during this phase, you need to make a list of
the schemas and tables that fail to load data. (Don’t worry about table
creation errors that say “table already exists”; this is normal if
any tables were pre-created manually or by initial database creation.) For each
table that is partially loaded, you need to truncate those tables with the

truncate table schema.mytable;

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)

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 syntax that’s appropriate for your
operating system—the syntax below 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. For example, the output from a SHOW=Y session that looks like
Listing A could be converted into the SQL that it’s in
Listing B.

The “ALTER SCHEMA” output was used in Oracle8.
Oracle9 replaces this line with the matching SQL statement: “ALTER SESSION
SET CURRENT_SCHEMA”. Unfortunately, this output tends to be ambiguous. A
SQL command that is exactly 75 characters long would look like it continues on
the next line. The only way to tell that such lines are complete is to look at
the next line and see if it starts with a command word.

Any database object with SQL or PL/SQL “bodies”
can also be ambiguous. You must scan for additional lines until you encounter a
new SQL command. However, this method isn’t foolproof because something may
look like a valid SQL command and still be part of the middle of a SQL or
PL/SQL body.

If your database is in the gigabyte or higher range, the
output of the SHOW=Y may be extremely difficult to edit manually into a SQL
script with a standard text editor. It also may be extremely time consuming
since you have to be careful about lines that are
exactly 78 characters long or to find the start and end of views, functions,
procedures, and packages.

Listing C
contains a Perl script that will automatically format the output of “imp
show=y” as a SQL script.