Import & Export in Oracle database
Imports and exports extract or insert an Oracle-readable copy of the actual data and structures in the database. The exports can be used to recover single data structures to the date and time the export was taken. Exports come in three types: full, cumulative, and incremental. Full, as its name implies, provides a full logical copy of the database and its structures. A cumulative provides a complete copy of altered structures since the last full or the last cumulative export. Incremental exports provide a complete copy of altered structures since the last incremental, cumulative, or full export.
Limitations on export/import:
* A database must be running to perform either an export or import.
* Export files shouldn’t be edited and can only be used by import.
* (Import only) imports full tables; it can’t be used to do a conditional load.
* Exported data is only a logical copy of the data. An export can only allow recovery to the date and time the export was taken.
* Imports and exports are accomplished using the Oracle IMPORT and EXPORT utilities.
example: exp scott/tiger FULL=y FILE=d:/krishna/exportdata.dmp LOG=d:/krishna/logfile.log CONSISTENT=y
| Keyword | Description (Default) |
| USERID | username/password |
| BUFFER | size of data buffer |
| FILE | output file (EXPDAT.DMP) |
| COMPRESS | import into one extent (Y) |
| GRANTS | export grants (Y) |
| INDEXES | export indexes(Y) |
| ROWS | export data rows (Y) |
| CONSTRAINTS | export table constraints (Y) |
| CONSISTENT | cross-table consistency (N) |
| LOG | log file of screen output (None) |
| STATISTICS | analyze objects (ESTIMATE) |
| DIRECT | Bypass the SQLcommand processing layer (N) (new in Oracle8) |
| FEEDBACK | Show a process meter (a dot) every X rows exported (0 – Xvalue) |
| HELP | Shows help listing |
| MLS | MLS_LABEL_FORMAT Used with secure Oracle; we won’t cover these. |
| FULL | export entire file (N) |
| OWNER | list of owner usernames |
| TABLES | list of table names |
| RECORDLENGTH | length of IO record |
| INCTYPE | incremental export type |
| RECORD | track incr. export (Y) |
| PARFILE | parameter file name |
IMPORT: IMP KEYWORD=value —or— KEYWORD=(list of values)
example: imp scott1/tiger1 FULL=y FIlE=d:/krishna/exportdata.dmp
| Keyword | Description (Default) |
| USERID | username/password |
| BUFFER | size of data buffer |
| FILE | Output file (EXPDAT.DMP) |
| SHOW | just list file contents (N) |
| IGNORE | Ignore create errors (N) |
| RECORDLENGTH | length of IOrecord |
| GRANTS | Import grants (Y) |
| INDEXES | Import indexes (Y) |
| ROWS | Import data rows (Y) |
| LOG | log file of screen output |
| INDEXFILE | write table/index info to specified file |
| FULL | Import entire file (N) |
| FROMUSER | list of owner usernames |
| TOUSER | list of usernames |
| TABLES | list of table names |
| FEEDBACK | Provide dot status graph (0) |
| INCTYPE | incremental import type |
| COMMIT | commit array insert (N) |
| PARFILE | parameter file name |
| DESTROY | overwrite tablespace data (N) |
| CHARSET | char. set of export file (NLS_LANG) |
Imports and exports extract or insert an Oracle-readable copy of the actual data and structures in the database. The exports can be used to recover single data structures to the date and time the export was taken. Exports come in three types: full, cumulative, and incremental. Full, as its name implies, provides a full logical…