Oracle | Import Utility

Oracle Import 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 Imports any and all objects that can be exported with the corresponding, legacy, export utility (see link at page bottom).

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

Import Syntax

Help imp -help

imp -help

Authority imp userid=<schema_owner/password@service_name@service_name>

imp uwclass/uwclass

      

      imp userid=uwclass/uwclass@pdbdev file=c:\temp\uw_test.dmp

File Name imp userid=<schema_owner/password@service_name@service_name> FILE=<file_name>

imp uwclass/uwclass@pdbdev file=c:\temp\uw_test.dmp

Log File Name imp userid=<schema_owner/password@service_name@service_name> LOG=<file_name>

imp uwclass/uwclass@pdbdev log=c:\temp\uw.log

Buffer Size -- O/S dependent and can usually be ignored imp userid=<schema_owner/password@service_name@service_name> BUFFER=<integer>

-- rows_in_array * maximum_row_size

imp uwclass/uwclass@pdbdev buffer=64000

Commit imp userid=<schema_owner/password@service_name@service_name> COMMIT=<Y|N>

imp uwclass/uwclass@pdbdev commit=Y

Compile imp userid=<schema_owner/password@service_name@service_name> COMPILE=<Y|N>

imp uwclass/uwclass@pdbdev compile=N

Constraints imp userid=<schema_owner/password@service_name@service_name> CONSTRAINTS=<Y|N>

imp uwclass/uwclass@pdbdev constraints=N

Data Files imp userid=<schema_owner/password@service_name@service_name> DATAFILES=(df1, df2, ... df#)

imp uwclass/uwclass@pdbdev datafiles=(c:\oradata\datasml1.dbf')

Destroy imp userid=<schema_owner/password@service_name> DESTROY=<Y|N>

imp uwclass/uwclass@pdbdev destroy=Y

Feedback imp userid=<schema_owner/password@service_name> FEEDBACK=<integer DEFAULT 0>

imp uwclass/uwclass@pdbdev feedback=100

File Size -- if used in exporting must match export specification imp userid=<schema_owner/password@service_name> FILESIZE=<integer> [KB | MB]

imp uwclass/uwclass@pdbdev filesize=2GB

From User imp userid=<schema_owner/password@service_name> FROMUSER=<schema_name>

imp uwclass/uwclass@pdbdev fromuser=uwclass touser=abc

Full imp userid=<schema_owner/password@service_name> FULL=<Y|N>

imp uwclass/uwclass@pdbdev full=N

Grants imp userid=<schema_owner/password@service_name> GRANTS=<Y|N>

imp uwclass/uwclass@pdbdev grants=N

Ignore imp userid=<schema_owner/password@service_name> IGNORE=<Y|N>

imp uwclass/uwclass@pdbdev ignore=Y

Indexes imp userid=<schema_owner/password@service_name> INDEXES=<Y|N>

imp uwclass/uwclass@pdbdev indexes=N

Index File -- creates a file of index creation statements: INDEXES parameter must be N imp userid=<schema_owner/password@service_name> INDEXFILE=<file_name>

imp uwclass/uwclass@pdbdev indexfile=c:\temp\index_dml.sql

Parameter File imp userid=<schema_owner/password@service_name> PARFILE=<parameter_file_name>

imp uwclass/uwclass@pdbdev parfile=c:\temp\uwparfile.ctl

Record Length imp userid=<schema_owner/password@service_name> RECORDLENGTH=<bytes>

imp uwclass/uwclass@pdbdev recordlength=32000

Resumable imp userid=<schema_owner/password@service_name> RESUMABLE=<Y|N>

imp uwclass/uwclass@pdbdev resumable=Y

Resumable Name imp userid=<schema_owner/password@service_name> RESUMABLE_NAME = 'User USERNAME (USERID), Session SESSIONID, Instance INSTANCEID'

imp uwclass/uwclass@pdbdev resumable_name='UWCLASS'

Resumable Timeout imp userid=<schema_owner/password@service_name> RESUMABLE_TIMEOUT=<seconds> DEFAULT 7200 (2 hours)

imp uwclass/uwclass@pdbdev resumable_timeout=18000

Rows imp userid=<schema_owner/password@service_name> ROWS=<Y|N>

imp uwclass/uwclass@pdbdev rows=N

Show imp userid=<schema_owner/password@service_name> SHOW=<Y|N>

imp uwclass/uwclass@pdbdev show=Y

Skip Unusable Indexes -- default is parameter from spfile imp userid=<schema_owner/password@service_name> SKIP_UNUSABLE_INDEXES=<Y|N>

imp uwclass/uwclass@pdbdev skip_unusable_indexes=N

Statistics imp userid=<schema_owner/password@service_name> STATISTICS=<ALWAYS | NONE | RECALCULATE | SAFE>

imp uwclass/uwclass@pdbdev statistics=RECALCULATE

Streams Configuration imp userid=<schema_owner/password@service_name> STREAMS_CONFIGURATION=<Y|N>

imp uwclass/uwclass@pdbdev streams_configuration=N

Streams Instantiation imp userid=<schema_owner/password@service_name> STREAMS_INSTANTIATION=<Y|N>

imp uwclass/uwclass@pdbdev streams_instantiation=Y

Tables imp userid=<schema_owner/password@service_name> TABLES=(t1, t2, ... t#)

imp uwclass/uwclass@pdbdev tables=(emp, dept, bonus)

Tablespaces imp userid=<schema_owner/password@service_name> TABLES=(t1, t2, ... t#)

imp uwclass/uwclass@pdbdev tablespaces=(example, uwdata)

Table Object ID No Validate imp userid=<schema_owner/password@service_name> TOID_NOVALIDATE=([schemaname.]typename [, ...])

imp uwclass/uwclass@pdbdev table=jobs toid_novalidate=typ1

To User imp userid=<schema_owner/password@service_name> TOUSER=<schema_name>

imp uwclass/uwclass@pdbdev fromuser=uwclass touser=abc

Transport Tablespace imp userid=<schema_owner/password@service_name> TRANSPORT_TABLESPACE=<Y|N>

imp uwclass/uwclass@pdbdev transport_tablespace=Y

TTS Owners imp userid=<schema_owner/password@service_name> TTS_OWNERS=(o1, o2, ... o#)

imp uwclass/uwclass@pdbdev tts_owners=(uwclass, ids)

Volume Size imp userid=<schema_owner/password@service_name> VOLSIZE <integer> [KB|MB|GB]

imp uwclass/uwclass@pdbdev volsize=10GB

Demo

DDL for migration from 11gR2 to 12c to 20c imp userid=<schema_owner/password@service_name> full=Y show=Y 2>path_and_file

imp uwclass/uwclass@pdbdev file=c:\temp\uwclass.dmp log=c:\temp\imp_uwclass.log full=Y show=Y