Oracle | Export

Oracle Export Version 20c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Legacy export utility.

When a PDB container is specified it must be referenced based by the name of its service entry in TNSNAMES.ORA.

Exportable with the Export Utility

Some of the objects listed at right have limitations so be sure to check the online docs.

Analyze	Job queues	Security Policies
Auditing	Libraries	Sequences
Clusters	Materialized Views	Snapshots
Comments	Nested Tables	Snapshot Logs
Constraints	Object Privileges	Synonyms
Contexts	Object Tables	System Privileges
Database Links	Operators	Table Data
Dimensions	Packages	Tables Definitions
Directory Aliases	Password History	Tablespace Definitions
External Tables	Procedures	Tablespace Quotas
Functions	Profiles	Triggers
Grants	Refresh Groups	Types
Indexes	Resource Costs	Users
Java Classes	Roles	User Proxies
Java Resources	Rollback Segment Definitions	Views

Export Syntax

Help exp -help

exp -help

Authority / UserID 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'"

or

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