Oracle Export

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version to Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Have you viewed the Library's main Table of Contents? If not ... [Click Here] ... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
Purpose Expoting tablespaces, schemas and objects.

Critically important when moving to 12c: you MUST specify the PDB container based on the corresponding TNSNAMES service entry.
Export Syntax
Help exp -help
exp -help
Authority exp userid=<schema_owner/password@service_name>
exp uwclass/uwclass@pdbdev

exp userid=uwclass/uwclass@pdbdev file=c:\temp\uw_test.dmp
File Name exp userid=<schema_owner/password@service_name> FILE=<file_name>
exp uwclass/uwclass@pdbdev file=c:\temp\uw_test.dmp
Log File Name exp userid=<schema_owner/password@service_name> LOG=<file_name>
exp uwclass/uwclass@pdbdev log=c:\temp\uw.log
Buffer Size -- O/S dependent and can usually be ignored

exp userid=<schema_owner/password@service_name@service_name> BUFFER=<integer>
-- rows_in_array * maximum_row_size

exp uwclass/uwclass@pdbdev buffer=64000
Compress -- The default is Y and it is best to override it.

exp userid=<schema_owner/password@service_name@service_name> COMPRESS=<Y|N>
exp uwclass/uwclass@pdbdev compress=N
Consistent -- Implements SET TRANSACTION READ ONLY

exp userid=<schema_owner/password@service_name@service_name> CONSISTENT=<Y|N>
exp uwclass/uwclass@pdbdev file=c:\temp\cnsstnt.dmp consistent=Y
Constraints exp userid=<schema_owner/password@service_name@service_name> CONSTRAINTS=<Y|N>
exp uwclass/uwclass@pdbdev file=c:\temp\cnstrnt.dmp constraints=N
Direct Path exp userid=<schema_owner/password@service_name> DIRECT=<Y|N>
exp uwclass/uwclass@pdbdev file=c:\temp\dirpath.dmp direct=Y
Feedback exp userid=<schema_owner/password@service_name> FEEDBACK=<integer | 0>
exp uwclass/uwclass@pdbdev file=c:\temp\fback.dmp feedback=100
File Size (default unlimited) exp userid=<schema_owner/password@service_name> FILESIZE=<integer> [ KB | MB | GB]
exp uwclass/uwclass@pdbdev file=c:\temp\fsize.dmp filesize 20GB
Flashback By SCN exp userid=<schema_owner/password@service_name> FLASHBACK_SCN=<SCN>
exp uwclass/uwclass@pdbdev file=c:\temp\fbscn.dmp flashback_scn=7632619
Flashback By Timestamp exp userid=<schema_owner/password@service_name> FLASHBACK_TIME=<timestamp>
exp uwclass/uwclass@pdbdev flashback_time="TIMESTAMP '2002-05-01 11:00:00'"


exp uwclass/uwclass@pdbdev flashback_time="TO_TIMESTAMP('12-02-2001 14:35:00', 'DD-MM-YYYY HH24:MI:SS')"
Full -- Yes requires the user to have the EXP_FULL_DATABASE role

exp userid=<schema_owner/password@service_name> FULL=<Y|N>
exp uwclass/uwclass@pdbdev full=Y
Grants exp userid=<schema_owner/password@service_name> GRANTS=<Y|N>
exp uwclass/uwclass@pdbdev grants=N
Indexes exp userid=<schema_owner/password@service_name> INDEXES=<Y|N>
exp uwclass/uwclass@pdbdev indexes=N
Object Consistent exp userid=<schema_owner/password@service_name> OBJECT_CONSISTENT=<Y|N>
exp uwclass/uwclass@pdbdev object_consistent=Y
Owner exp userid=<schema_owner/password@service_name> OWNER=(o1, o2, ... o#)
exp uwclass/uwclass@pdbdev owner=(uwclass, ids, webapps)
Parameter File exp userid=<schema_owner/password@service_name> PARFILE=<parameter_file_name>
exp uwclass/uwclass@pdbdev parfile=c:\temp\uwparfile.ctl
Query exp userid=<schema_owner/password@service_name> QUERY=<query_string>
exp uwclass/uwclass@pdbdev owner=SCOTT tables=emp query=\"WHERE job=\'MANAGER\' AND sal \>50000\"
Record Length exp userid=<schema_owner/password@service_name> RECORDLENGTH=<bytes>
exp uwclass/uwclass@pdbdev recordlength=32000
Resumable exp userid=<schema_owner/password@service_name> RESUMABLE=<Y|N>
exp uwclass/uwclass@pdbdev resumable=Y
Resumable Name exp userid=<schema_owner/password@service_name> RESUMABLE_NAME = 'User USERNAME (USERID), Session SESSIONID, Instance INSTANCEID'
exp uwclass/uwclass@pdbdev resumable_name 'UWCLASS
Resumable Time Out exp userid=<schema_owner/password@service_name> RESUMABLE_TIMEOUT=<seconds> DEFAULT 7200 (2 hours)
exp uwclass/uwclass@pdbdev resumable_timeout=18000
Rows exp userid=<schema_owner/password@service_name> ROWS=<Y|N>
exp uwclass/uwclass@pdbdev rows=N
Statistics exp userid=<schema_owner/password@service_name> STATISTICS=<ESTIMATE | COMPUTE | NONE>
exp uwclass/uwclass@pdbdev statistics=COMPUTE
Tables exp userid=<schema_owner/password@service_name> TABLES=(t1, t2, ... t#)
exp uwclass/uwclass@pdbdev tables=(emp, dept, bonus)
Tablespaces exp userid=<schema_owner/password@service_name> TABLESPACES=(tbsp1, tbsp2, ... tbsp#)
exp uwclass/uwclass@pdbdev tablespaces=(uwdata, user_data)
Transportable Tablespaces exp userid=<schema_owner/password@service_name> TRANSPORT_TABLESPACE <Y|N>
exp uwclass/uwclass@pdbdev transport_tablespace=Y
Triggers exp userid=<schema_owner/password@service_name> TRIGGERS <Y|N>
exp uwclass/uwclass@pdbdev triggers=N
TTS Full Check exp userid=<schema_owner/password@service_name> TTS_FULL_CHECK <Y|N>
exp uwclass/uwclass@pdbdev tts_full_check=Y
Volume Size exp userid=<schema_owner/password@service_name> VOLSIZE <integer> [KB|MB|GB]
exp uwclass/uwclass@pdbdev volsize=10GB

Related Topics
Data Pump
DBMS_TTS Built-in Package
Transportable Tablespaces
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved