Topics· What is import/export and why does one need it? · How does one use the import/export utilities? · Can one export only a subset of a table? · Can one monitor how fast a table is imported? · Can one import tables to a different tablespace? · Does one need to drop/ truncate objects before importing? · Can one import/export between different versions of Oracle? · Can one export to multiple files?/ Can one beat the Unix 2 Gig limit? · How can one improve Import/ Export performance? · What are the common Import/ Export problems? · Where can one get more info about the Import/ Export utilities?
What is import/export and why does one need it?The Oracle export (EXP) and import (IMP) utilities are used to perform logical database backup and recovery. They are also used to move Oracle data from one machine, database or schema to another. The imp/exp utilities use an Oracle proprietary binary file format and can thus only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database. For more information on how to load and unload data from files, read the SQL*Loader FAQ. The export/import utilities are also commonly used to perform the following tasks:
- Backup and recovery (small databases only)
- Reorganization of data/ Eliminate database fragmentation
- Detect database corruption. Ensure that all the data can be read.
- Transporting tablespaces between databases
- Etc.
How does one use the import/export utilities?Look for the “imp” and “exp” executables in your $ORACLE_HOME/bin directory. One can run them interactively, using command line parameters, or using parameter files. Look at the imp/exp parameters before starting. These parameters can be listed by executing the following commands: “exp help=yes” or “imp help=yes“. The following examples demonstrate how the imp/exp utilities can be used: exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no exp scott/tiger file=emp.dmp tables=(emp,dept) imp scott/tiger file=emp.dmp full=yes imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept exp userid=scott/tiger@orcl parfile=export.txt … where export.txt contains: BUFFER=100000 FILE=account.dmp FULL=n OWNER=scott GRANTS=y COMPRESS=yNOTE: If you do not like command line utilities, you can import and export data with the “Schema Manager” GUI that ships with Oracle Enterprise Manager (OEM). · Back to top of file
Can one export a subset of a table?From Oracle8i one can use the QUERY= export parameter to selectively unload a subset of the data from a table. Look at this example: exp scott/tiger tables=emp query=\”where deptno=10\”· Back to top of file
Can one monitor how fast a table is imported?If you need to monitor how fast rows are imported from a running import job, try one of the following methods: Method 1: select substr(sql_text,instr(sql_text,’INTO “‘),30) table_name, rows_processed, round((sysdate-to_date(first_load_time,’yyyy-mm-dd hh24:mi:ss’))*24*60,1) minutes, trunc(rows_processed/((sysdate-to_date(first_load_time,’yyyy-mm-dd hh24:mi:ss’))*24*60)) rows_per_min from sys.v_$sqlarea where sql_text like ‘INSERT %INTO “%’ and command_type = 2 and open_versions > 0;For this to work one needs to be on Oracle 7.3 or higher (7.2 might also be OK). If the import has more than one table, this statement will only show information about the current table being imported. Contributed by Osvaldo Ancarola, Bs. As. Argentina. Method 2: Use the FEEDBACK=n import parameter. This command will tell IMP to display a dot for every N rows imported. · Back to top of file
Can one import tables to a different tablespace?Oracle offers no parameter to specify a different tablespace to import data into. Objects will be re-created in the tablespace they were originally exported from. One can alter this behaviour by following one of these procedures: Pre-create the table(s) in the correct tablespace:
- Import the dump file using the INDEXFILE= option
- Edit the indexfile. Remove remarks and specify the correct tablespaces.
- Run this indexfile against your database, this will create the required tables in the appropriate tablespaces
- Import the table(s) with the IGNORE=Y option.
Change the default tablespace for the user:
- Revoke the “UNLIMITED TABLESPACE” privilege from the user
- Revoke the user’s quota from the tablespace from where the object was exported. This forces the import utility to create tables in the user’s default tablespace.
- Make the tablespace to which you want to import the default tablespace for the user
- Import the table
Does one need to drop/ truncate objects before importing?Before one import rows into already populated tables, one needs to truncate or drop these tables to get rid of the old data. If not, the new data will be appended to the existing tables. One must always DROP existing Sequences before re-importing. If the sequences are not dropped, they will generate numbers inconsistent with the rest of the database. Note: It is also advisable to drop indexes before importing to speed up the import process. Indexes can easily be recreated after the data was successfully imported. · Back to top of file
Can one import/export between different versions of Oracle?Different versions of the import utility is upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility. This is quite an effective way of upgrading a database from one release of Oracle to the next. Oracle also ships some previous catexpX.sql scripts that can be executed as user SYS enabling older imp/exp versions to work (for backwards compatibility). For example, one can run $ORACLE_HOME/rdbms/admin/catexp7.sql on an Oracle 8 database to allow the Oracle 7.3 exp/imp utilities to run against an Oracle 8 database. · Back to top of file
Can one export to multiple files?/ Can one beat the Unix 2 Gig limit?From Oracle8i, the export utility supports multiple output files. This feature enables large exports to be divided into files whose sizes will not exceed any operating system limits (FILESIZE= parameter). When importing from multi-file export you must provide the same filenames in the same sequence in the FILE= parameter. Look at this example: exp SCOTT/TIGER FILE=D:\F1.dmp,E:\F2.dmp FILESIZE=10m LOG=scott.logUse the following technique if you use an Oracle version prior to 8i: Create a compressed export on the fly. Depending on the type of data, you probably can export up to 10 gigabytes to a single file. This example uses gzip. It offers the best compression I know of, but you can also substitute it with zip, compress or whatever. # create a named pipe mknod exp.pipe p # read the pipe – output to zip file in the background gzip < exp.pipe > scott.exp.gz & # feed the pipe exp userid=scott/tiger file=exp.pipe …Contributed by Jared K Still Read the Oracle/Unix FAQ for more examples on using Unix pipes. · Back to top of file
How can one improve Import/ Export performance?EXPORT:
- Set the BUFFER parameter to a high value (e.g. 2M)
- Set the RECORDLENGTH parameter to a high value (e.g. 64K)
- Stop unnecessary applications to free-up resources for your job.
- If you run multiple export sessions, ensure they write to different physical disks.
- DO NOT export to an NFS mounted filesystem. It will take forever.
IMPORT:
- Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.
- Place the file to be imported on a separate physical disk from the oracle data files
- Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file
- Set the LOG_BUFFER to a big value and restart oracle.
- Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)
- Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)
- Use COMMIT=N in the import parameter file if you can afford it
- Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements
- Remember to run the indexfile previously created
Contributed by Petter Henrik Hansen. · Back to top of file
What are the common Import/ Export problems?
- ORA-00001: Unique constraint (…) violated – You are importing duplicate rows. Use IGNORE=NO to skip tables that already exist (imp will give an error if the object is re-created).
- ORA-01555: Snapshot too old – Ask your users to STOP working while you are exporting or use parameter CONSISTENT=NO
- ORA-01562: Failed to extend rollback segment – Create bigger rollback segments or set parameter COMMIT=Y while importing
- IMP-00015: Statement failed … object already exists… – Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.
Where can one get more info about the Import/ Export utilities?
Filed under: Uncategorized
