Oracle Database - Reading an Export File Using Import Without Physically Importing Data
The purpose of this post is to briefly describe how to list objects in an export file without physically importing the data.
Here are two simple ways to minimize the occurrence of import errors to perform a dummy import. One way uses the SHOW parameter and the other way creates objects separately from a .SQL file produced from an import with the INDEXFILE parameter.
1. Import using the SHOW parameter
Example:
$ imp file=exp.dmp userid=uname/pass rows=n show=y log=imp.log
When you use SHOW = Y, the contents of the export file are listed but data is not imported into the database. If you use this qualifier in conjunction with the ‘log’ parameter, the contents are spooled to the log file. Check the log file to make sure that all expected objects are contained in the export file.
2. Import using the ‘INDEXFILE’ and ‘CONSTRAINTS’ parameters
When you use the INDEXFILE parameter, data is not imported into the database. However, a .SQL file is produced which contains all the CREATE object statements. The file can be used to pre-create the tables or create the indexes after the import has completed.
Example:
1. Run the import utility with the indexfile option:
$ imp file=exp.dmp userid=uname/pass indexfile=create_indexes.sql
2. Update create_indexes.sql to remove the ‘REM’ characters from the ‘create table’ statements.
3. Run the create_indexes.sql script to create the tables and indexes:
svrmgrl> @create_indexes.sql
4. In order to import the data, the following import command imports the data into the pre-created tables:
$ imp file=exp.dmp userid=uname/pass ignore=Y indexes=N log=imp.log
The indexfile parameter is primarily intended for use when importing table data, without importing indexes. This helps make table data available more quickly to users, as the resulting index.sql script may be run to create the indexes after the import.
The file also contains the CREATE TABLE statements, preceded with ‘REM’. Therefore, a second use of the file is to create all objects separately, before you import the data. This is useful, as it provides the option of updating storage information if needed.
Importing with CONSTRAINTS = Y causes table constraints to be written to the index file.
In step (4), import attempts to create the tables from the export dump file and finds the objects already exist which can cause errors. The IGNORE parameter is used because it indicates that all object creation errors should be ignored. Secondly, the INDEXES parameter is used since the indexes were created in step (3).