Export and Import utilities in Oracle
Export and Import are the Oracle utilities that allow us to make exports & imports of the data objects, and transfer the data across databases that reside on different hardware platforms on different Oracle versions.
Export (exp) and import (imp) utilities are used to perform logical database backup and recovery. When exporting, database objects are dumped to a binary file which can then be imported into another Oracle database.
catexp.sql (in $ORACLE_HOME/rdbms/admin) will create EXP_FULL_DATABASE & IMP_FULL_DATABASE roles (no need to run this, if you ran catalog.sql at the time of database creation).
Before using these commands, you should set ORACLE_HOME, ORACLE_SID and PATH environment variables.
exp utility
Objects owned by SYS cannot be exported.
If you want to export objects of another schema, you need EXP_FULL_DATABASE role.
exp utility
Objects owned by SYS cannot be exported.
If you want to export objects of another schema, you need EXP_FULL_DATABASE role.
$ exp help=y
Keyword
|
Description (Default)
|
USERID
|
username/password
|
FULL
|
export entire file (N). To do full database export, that user must have EXP_FULL_DATABASE role
|
BUFFER
|
size of data buffer. OS dependent
|
OWNER
|
list of owner usernames
|
FILE
|
output files (EXPDAT.DMP)
|
TABLES
|
list of table names
|
COMPRESS
|
import into one extent (Y)
|
RECORDLENGTH
|
length of IO record
|
GRANTS
|
export grants (Y)
|
INCTYPE
|
incremental export type. valid values are
COMPLETE, INCREMENTAL, CUMULATIVE |
INDEXES
|
export indexes (Y)
|
RECORD
|
track incremental export (Y)
|
DIRECT
|
direct path (N)
|
TRIGGERS
|
export triggers (Y)
|
LOG
|
log file of screen output
|
STATISTICS
|
analyze objects (ESTIMATE)
|
ROWS
|
export data rows (Y)
|
PARFILE
|
parameter filename
|
CONSISTENT
|
cross-table consistency(N). Implements SET TRANSACTION READ ONLY
|
CONSTRAINTS
|
export constraints (Y)
|
OBJECT_CONSISTENT
|
transaction set to read only during object export (N)
|
FEEDBACK
|
display progress (a dot) for every N rows (0)
|
FILESIZE
|
maximum size of each dump file
|
FLASHBACK_SCN
|
SCN used to set session snapshot back to
|
FLASHBACK_TIME
|
time used to get the SCN closest to the specified time
|
QUERY
|
select clause used to export a subset of a table
|
RESUMABLE
|
suspend when a space related error is encountered(N)
|
RESUMABLE_NAME
|
text string used to identify resumable statement
|
RESUMABLE_TIMEOUT
|
wait time for RESUMABLE
|
TTS_FULL_CHECK
|
perform full or partial dependency check for TTS
|
VOLSIZE
|
number of bytes to write to each tape volume (not available from Oracle 11g Release2)
|
TABLESPACES
|
list of tablespaces to export
|
TRANSPORT_TABLESPACE
|
export transportable tablespace metadata (N)
|
TEMPLATE
|
template name which invokes iAS mode export
|
Examples:
$ exp system/manager file=emp.dmp log=emp_exp.log full=y
==> exporting full database.
$ exp system/manager file=owner.dmp log=owner.log owner=owner direct=y STATISTICS=none
==> exporting all the objects of a schema.
==> exporting all the objects of a schema.
$ exp file=schemas.dmp log=schemas.log owner=master,owner,user direct=y STATISTICS=none
==> exporting all the objects of multiple schemas.
==> exporting all the objects of multiple schemas.
$ exp file=testdb_emp.dmp log=testdb_emp.log tables=scott.emp direct=y STATISTICS=none
==> exporting all the rows in table (emp table records in scott schema).
==> exporting all the rows in table (emp table records in scott schema).
$ exp file=itinitem.dmp log=itinitem.log tables=tom.ITIN,tom.ITEM
query=\"where CODE in \(\'OT65FR7H\',\'ATQ56F7H\'\)\"
statistics=none
==> exporting the records of some tables which satisfies a particular criteria.
query=\"where CODE in \(\'OT65FR7H\',\'ATQ56F7H\'\)\"
statistics=none
==> exporting the records of some tables which satisfies a particular criteria.
$ exp transport_tablespace=y tablespaces=THU statistics=none file=THU.dmp log=thu_exp.log
==> exporting at tablespace level.
==> exporting at tablespace level.
$ exp FILE=file1.dmp,file2.dmp,file3.dmp FILESIZE=10M LOG=multiple.log
==> exporting to multiple files.
==> exporting to multiple files.
$ exp file=scott.dmp log=scott.log inctype=complete
==> exporting full database (after some incremental/cumulative backups).
==> exporting full database (after some incremental/cumulative backups).
$ exp file=scott.dmp log=scott.log inctype=cumulative
==> exporting cumulatively (taking backup from last complete or cumulative backup).
==> exporting cumulatively (taking backup from last complete or cumulative backup).
$ exp file=scott.dmp log=scott.log inctype=incremental
==> exporting incrementally (taking backup from last complete or cumulative or incremental backup).
imp utility
imp provides backward compatibility i.e. it will allows you to
import the objects that you have exported in lower Oracle versions also.
imp doesn't recreate already existing objects. It either abort the import process (default) or ignores the errors (if you specify IGNORE=Y).
==> exporting incrementally (taking backup from last complete or cumulative or incremental backup).
imp utility
imp provides backward compatibility i.e. it will allows you to
import the objects that you have exported in lower Oracle versions also.
imp doesn't recreate already existing objects. It either abort the import process (default) or ignores the errors (if you specify IGNORE=Y).
USERID must be the first parameter on the command line.
$ imp help=y
Keyword
|
Description (Default)
|
USERID
|
username/password
|
FULL
|
import entire file (N). To do the full database import, that user must have IMP_FULL_DATABASE role
|
BUFFER
|
size of data buffer. OS dependent
|
FROMUSER
|
list of owner usernames
|
FILE
|
input files (EXPDAT.DMP)
|
TOUSER
|
list of usernames
|
SHOW
|
just list file contents (N), will be used to check the validity of the dump file
|
TABLES
|
list of table names
|
IGNORE
|
ignore create errors (N)
|
RECORDLENGTH
|
length of IO record
|
GRANTS
|
import grants (Y)
|
INCTYPE
|
incremental import type. valid keywords are
SYSTEM (for definitions), RESTORE (for data) |
INDEXES
|
import indexes (Y)
|
COMMIT
|
commit array insert (N)
|
ROWS
|
import data rows (Y)
|
PARFILE
|
parameter filename
|
LOG
|
log file of screen output
|
CONSTRAINTS
|
import constraints (Y)
|
DESTROY
|
overwrite tablespace datafile (N)
|
INDEXFILE
|
will write DDLs of the objects in the dumpfile into the specified file
|
SKIP_UNUSABLE_INDEXES
|
skip maintenance of unusable indexes (N)
|
FEEDBACK
|
display progress every x rows(0)
|
TOID_NOVALIDATE
|
skip validation of specified type ids
|
FILESIZE
|
maximum size of each dump file
|
STATISTICS
|
import precomputed statistics (ALWAYS)
|
RESUMABLE
|
suspend when a space related error is encountered(N)
|
RESUMABLE_NAME
|
text string used to identify resumable statement
|
RESUMABLE_TIMEOUT
|
wait time for RESUMABLE
|
COMPILE
|
compile procedures, packages, and functions (Y)
|
STREAMS_CONFIGURATION
|
import streams general metadata (Y)
|
STREAMS_INSTANTIATION
|
import streams instantiation metadata (N)
|
VOLSIZE
|
number of bytes in file on each volume of a file on tape (not available from Oracle 11g Release2)
|
DATA_ONLY
|
import only data (N) (from Oracle 11g Release2)
|
The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE import transportable tablespace metadata (N)
TABLESPACES tablespaces to be transported into database
DATAFILES datafiles to be transported into database
TTS_OWNERS users that own data in the transportable tablespace set
Examples:$ imp system/manager file=emp.dmp log=emp_imp.log full=y
==> importing all the exported data.
$ imp system/manager file=testdb_emp.dmp log=testdb_emp_imp.log tables=tester.employee
==> importing all the records of table (employee table records in tester schema).
==> importing all the records of table (employee table records in tester schema).
$ imp FILE=two.dmp LOG=two.log IGNORE=Y GRANTS=N INDEXES=N COMMIT=Y TABLES=(brand, book)
==> importing all the records of couple of tables.
==> importing all the records of couple of tables.
$ imp system/manager file=intenary.dmp log=intenary.log FROMUSER=tom TOUSER=jerry ignore=y
==> importing data of one schema into another schema
==> importing data of one schema into another schema
$ imp "/as sysdba" file=TUE.dmp TTS_OWNERS=OWNER tablespaces=TUE transport_tablespace=y datafiles=TUE.dbf
$ imp file=transporter3.dmp log=transporter3.log inctype=system
==> importing definitions from backup.
==> importing definitions from backup.
$ imp file=transporter3.dmp log=transporter3.log inctype=restore
==> importing data from backup.
==> importing data from backup.
$ imp file=spider.dmp log=spider.log show=y
==> checks the validity of the dumpfile.
==> checks the validity of the dumpfile.
$ imp file=scott.dmp log=scott.log indexfile=scott_schema.sql
==> will write DDLs of the objects in exported dumpfile (scott schema) into specified file. This command won't import the objects.
How to improve Export & Import
exp:
1. Set the BUFFER parameter to a high value. Default is 256KB.
2. Stop unnecessary applications to free the resources.
3. If you are running multiple sessions, make sure they write to different disks.
4. Do not export to NFS (Network File Share). Exporting to disk is faster.
5. Set the RECORDLENGTH parameter to a high value.
6. Use DIRECT=yes (direct mode export).
How to improve Export & Import
exp:
1. Set the BUFFER parameter to a high value. Default is 256KB.
2. Stop unnecessary applications to free the resources.
3. If you are running multiple sessions, make sure they write to different disks.
4. Do not export to NFS (Network File Share). Exporting to disk is faster.
5. Set the RECORDLENGTH parameter to a high value.
6. Use DIRECT=yes (direct mode export).
imp:
1. Place the file to be imported in separate disk from datafiles.
2. Increase the DB_CACHE_SIZE.
3. Set LOG_BUFFER to big size.
4. Stop redolog archiving, if possible.
5. Use COMMIT=n, if possible.
6. Set the BUFFER parameter to a high value. Default is 256KB.
7. It's advisable to drop indexes before importing to speed up the import process or set INDEXES=N and building indexes later on after the import. Indexes can easily be recreated after the data was successfully imported.
8. Use STATISTICS=NONE
9. Disable the INSERT triggers, as they fire during import.
10. Set Parameter COMMIT_WRITE=NOWAIT(in 10g) or COMMIT_WAIT=NOWAIT (in 11g) during import.
Related Views
DBA_EXP_VERSION
DBA_EXP_FILES
DBA_EXP_OBJECTS
1. Place the file to be imported in separate disk from datafiles.
2. Increase the DB_CACHE_SIZE.
3. Set LOG_BUFFER to big size.
4. Stop redolog archiving, if possible.
5. Use COMMIT=n, if possible.
6. Set the BUFFER parameter to a high value. Default is 256KB.
7. It's advisable to drop indexes before importing to speed up the import process or set INDEXES=N and building indexes later on after the import. Indexes can easily be recreated after the data was successfully imported.
8. Use STATISTICS=NONE
9. Disable the INSERT triggers, as they fire during import.
10. Set Parameter COMMIT_WRITE=NOWAIT(in 10g) or COMMIT_WAIT=NOWAIT (in 11g) during import.
Related Views
DBA_EXP_VERSION
DBA_EXP_FILES
DBA_EXP_OBJECTS
Comments
Post a Comment