Recover from import errors
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 following:
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.
Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.