fix import object creation errors in Oracle
DBAs normally use export/import for schema level import or table level import. In such scenarios, if we receive any error with table creation in import or if the import terminates half way because of any DDL error, then you could still restart the process from where import terminated.
This method holds good only for DDL creation errors that arise during import not for data import errors(DML errors).
When an import runs, it creates tables and loads data before creating/loading other schema objects such as views, synonyms, and sequences, for which import uses simple SQL statements. If there are any failures in object creation during import, prepare a list of the schemas and tables that failed to load data during import. Truncate any table that is partially loaded with the following command:
truncate table schema.tablename;
First lets try importing tables one by one. If it fails then we will use SQL to import them later.
imp userid=system/manager USERS=(user1) TABLES=(table1
imp userid=system/manager USERS=(user1) TABLES=(table2)
imp userid=system/manager USERS=(user2) TABLES=(table3,table4)
If the above import failed then run the import with “show=y” as an option. The show=y option show the creates DDL statements that import would require. The SQL statements will not be executed but displayed to the screen. Even if you specify a LOG parameter, the output will still be sent to screen, so you may want to pipe it to a file as below:
imp userid=system/manager full=y SHOW=Y LOG=imp.log 2> imp.out
You could also use imp indexfile=filename to show the ddl statements from an export dump
You could use impdp sqlfile=filename to show the ddl statements from export data pump utility
An example of using index file is below:
REM CREATE TABLE “SCOTT”.”XYZ” (“ENO” NUMBER, “ENAME” VARCHAR2(20),
REM “DEPTNO” NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
REM STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE
REM “TS_DBUSER” LOGGING NOCOMPRESS ;
REM … 10 rows
On linux/solaris you can delete the REM by using awk command like
awk ‘{ $1= “”; print}’
Once the table creation is done, we will use imp rows=y option and import all data.
