Export & Import utilities
Export & Import utilities in OracleORACLE Export Import (exp & imp) 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. It is an export utility handling in OS Export Mode to Run the utilty
There are five LEVELS
Parameter verified by exp help=y
[oracle@tesdb datapump]$ exp help=y
Export: Release 12.2.0.1.0 - Production on Tue Oct 31 21:20:54 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:
Example: EXP SCOTT/TIGER
Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:
Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL export entire file (N)
BUFFER size of data buffer 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
INDEXES export indexes (Y) RECORD track incr. 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) CONSTRAINTS export constraints (Y)
OBJECT_CONSISTENT transaction set to read only during object export (N)
FEEDBACK display progress every x 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
TABLESPACES list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE template name which invokes iAS mode export
Export terminated successfully without warnings.
[oracle@tesdb datapump]$ imp help=y
Import: Release 12.2.0.1.0 - Production on Tue Oct 31 21:21:42 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
You can let Import prompt you for parameters by entering the IMP
command followed by your username/password:
Example: IMP SCOTT/TIGER
Or, you can control how Import runs by entering the IMP command followed
by various arguments. To specify parameters, you use keywords:
Format: IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL import entire file (N)
BUFFER size of data buffer FROMUSER list of owner usernames
FILE input files (EXPDAT.DMP) TOUSER list of usernames
SHOW just list file contents (N) TABLES list of table names
IGNORE ignore create errors (N) RECORDLENGTH length of IO record
GRANTS import grants (Y) INCTYPE incremental import type
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 data file (N)
INDEXFILE write table/index info to 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)
DATA_ONLY import only data (N)
VOLSIZE number of bytes in file on each volume of a file on tape
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
Import terminated successfully without warnings.
TABLE LEVEL
[oracle@tesdb ~]$ mkdir export [oracle@tesdb ~]$ cd export/ [oracle@tesdb export]$ ll total 0 [oracle@tesdb export]$ exp file=emp.dmp log=emp.log tables=emp statistics=none direct=y Export: Release 12.2.0.1.0 - Production on Tue Oct 31 21:29:10 2023 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Username: scott/tiger Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified tables via Direct Path ... . . exporting table EMP 14 rows exported Export terminated successfully without warnings. [oracle@tesdb export]$ [oracle@sdbt export]$ ll total 20 -rw-r--r--. 1 oracle oinstall 16384 Oct 31 21:30 emp.dmp -rw-r--r--. 1 oracle oinstall 408 Oct 31 21:30 emp.logSCHEMA LEVEL [oracle@tesdb export]$ exp file=sche.dmp log=sche.log owner=scott statistics=none direct=y Export: Release 12.2.0.1.0 - Production on Tue Oct 31 21:34:26 2023 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified users ... . exporting pre-schema procedural objects and actions . exporting foreign function library names for user SCOTT . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user SCOTT About to export SCOTT's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export SCOTT's tables via Direct Path ... . . exporting table BONUS 0 rows exported . . exporting table DEPT 4 rows exported . . exporting table EMP 14 rows exported . . exporting table ROSHAN 14 rows exported . . exporting table SALGRADE 5 rows exported . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions . exporting statistics Export terminated successfully without warnings. [oracle@tesdb export]$ [oracle@tesdb export]$ ll -rw-r--r--. 1 oracle oinstall 16384 Oct 31 21:35 sche.dmp -rw-r--r--. 1 oracle oinstall 1591 Oct 31 21:35 sche.log [oracle@tesdb export]$QUERY LEVEL [oracle@tesdb export]$ exp file=q.dmp log=query.log tables=emp statistics=estimate query=\"where sal between 3000 and 50000\" Export: Release 12.2.0.1.0 - Production on Tue Oct 31 21:42:52 2023 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Username: scott/tiger Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table EMP 3 rows exported Export terminated successfully without warnings. [oracle@tesdb export]$TABLESPACE LEVEL [oracle@tesdb export]$ exp file=tbss.dmp log=tbs.log statistics=estimate tablespaces=users Export: Release 12.2.0.1.0 - Production on Tue Oct 31 21:46:33 2023 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export selected tablespaces ... For tablespace USERS ... . exporting cluster definitions . exporting table definitions . . exporting table T1 3 rows exported . . exporting table T2 4 rows exported . . exporting table BONUS 0 rows exported . . exporting table DEPT 4 rows exported . . exporting table EMP 14 rows exported . . exporting table ROSHAN 14 rows exported . . exporting table SALGRADE 5 rows exported . . exporting table EMP 14 rows exported EXP-00091: Exporting questionable statistics. . . exporting table BONUS 0 rows exported . . exporting table DEPT 4 rows exported EXP-00091: Exporting questionable statistics. . . exporting table EMP 14 rows exported EXP-00091: Exporting questionable statistics. . . exporting table SALGRADE 5 rows exported . exporting referential integrity constraints . exporting triggers Export terminated successfully with warnings. [oracle@tesdb export]$ -rw-r--r--. 1 oracle oinstall 1521 Oct 31 21:47 tbs.log -rw-r--r--. 1 oracle oinstall 24576 Oct 31 21:47 tbss.dmp [oracle@tesdb export]$FULL DATABASE EXPORT [oracle@tesdb export]$ exp file=full.dmp log=full.log statistics=estimate full=y Export: Release 12.2.0.1.0 - Production on Tue Oct 31 21:50:49 2023 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export the entire database ... . exporting tablespace definitions . exporting profiles . exporting user definitions . exporting roles . exporting resource costs . exporting rollback segment definitions . exporting database links . exporting sequence numbers . exporting directory aliases . exporting context namespaces . exporting foreign function library names . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions . exporting system procedural objects and actions . exporting pre-schema procedural objects and actions . exporting cluster definitions . about to export AUDSYS's tables via Conventional Path ... . about to export SYSTEM's tables via Conventional Path ... . . exporting table OL$ . . exporting table OL$HINTS . . exporting table OL$NODES . . exporting table SQLPLUS_PRODUCT_PROFILE 0 rows exported EXP-00091: Exporting questionable statistics. . about to export OUTLN's tables via Conventional Path ... . . exporting table OL$ 0 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. . . exporting table OL$HINTS 0 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. . . exporting table OL$NODES 0 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. . about to export REMOTE_SCHEDULER_AGENT's tables via Conventional Path ... . about to export DBSFWUSER's tables via Conventional Path ... . . exporting table ACL$_OBJ 0 rows exported EXP-00091: Exporting questionable statistics. . . exporting table EXADIRECT_ACL 0 rows exported EXP-00091: Exporting questionable statistics. . . exporting table IP_ACL 0 rows exported EXP-00091: Exporting questionable statistics. . about to export SYS$UMF's tables via Conventional Path ... . about to export MDDATA's tables via Conventional Path ... . about to export SPATIAL_CSW_ADMIN_USR's tables via Conventional Path ... . about to export U1's tables via Conventional Path ... . . exporting table T1 3 rows exported . about to export U2's tables via Conventional Path ... . . exporting table T2 4 rows exported . about to export SCOTT's tables via Conventional Path ... . . exporting table BONUS 0 rows exported . . exporting table DEPT 4 rows exported . . exporting table EMP 14 rows exported . . exporting table ROSHAN 14 rows exported . . exporting table SALGRADE 5 rows exported . about to export ROSHAN's tables via Conventional Path ... . . exporting table EMP 14 rows exported EXP-00091: Exporting questionable statistics. . about to export TESDB's tables via Conventional Path ... . . exporting table EMP 14 rows exported EXP-00091: Exporting questionable statistics. . about to export ARJUN's tables via Conventional Path ... . . exporting table BONUS 0 rows exported . . exporting table DEPT 4 rows exported EXP-00091: Exporting questionable statistics. . . exporting table EMP 14 rows exported EXP-00091: Exporting questionable statistics. . . exporting table SALGRADE 5 rows exported . exporting synonyms . exporting views . exporting referential integrity constraints . exporting stored procedures . exporting operators . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting triggers . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions . exporting user history table . exporting default and system auditing options . exporting statistics Export terminated successfully with warnings. [oracle@tesdb export]$IMPORT SCHEMA [oracle@tesdb export]$ imp file=sche.dmp log=impsc.log fromuser=scott touser=u1 Import: Release 12.2.0.1.0 - Production on Wed Nov 1 07:10:12 2023 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Username: scott/tiger Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Export file created by EXPORT:V12.02.00 via direct path Warning: the objects were exported by SYS, not by you import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) IMP-00403: Warning: This import generated a separate SQL file "impsc_sys.sql" which contains DDL that failed due to a privilege issue. . importing SCOTT's objects into U1 . . importing table "BONUS" IMP-00009: abnormal end of export file Import terminated successfully with warnings. [oracle@tesdb export]$ [oracle@tesdb export]$ ll -rw-r--r--. 1 oracle oinstall 669 Nov 1 07:11 impsc.log -rw-r--r--. 1 oracle oinstall 0 Nov 1 07:11 impsc_sys.sql -rw-r--r--. 1 oracle oinstall 16384 Oct 31 21:35 sche.dmp [oracle@tesdb export]$USING PARFILE METHOD [oracle@tesdb export]$ vi tesdb.par [oracle@tesdb export]$ cat tesdb.par file=parschmea.dmp log=parsche.log buffer=4000 owner=scott [oracle@tesdb export]$ exp parfile=tesdb.par Export: Release 12.2.0.1.0 - Production on Wed Nov 1 07:19:20 2023 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified users ... . exporting pre-schema procedural objects and actions . exporting foreign function library names for user SCOTT . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user SCOTT About to export SCOTT's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export SCOTT's tables via Conventional Path ... . . exporting table BONUS 0 rows exported . . exporting table DEPT 4 rows exported . . exporting table EMP 14 rows exported . . exporting table ROSHAN 14 rows exported . . exporting table SALGRADE 5 rows exported . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions . exporting statistics Export terminated successfully without warnings. [oracle@tesdb export]$ |