Oracle Import
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. 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.
Purpose Importing tablespaces, schemas and objects.

Critically important when moving to 12c is that you MUST specify the PDB container based on the corresponding TNSNAMES service entry.
 
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@pdbdev destroy=Y
Feedback imp userid=<schema_owner/password@service_name> FEEDBACK=<integer DEFAULT 0>
imp uwclass/uwclass@pdbdev@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@pdbdev filesize=100MB
From User imp userid=<schema_owner/password@service_name> FROMUSER=<schema_name>
imp uwclass/uwclass@pdbdev@pdbdev fromuser=uwclass touser=abc
Full imp userid=<schema_owner/password@service_name> FULL=<Y|N>
imp uwclass/uwclass@pdbdev@pdbdev full=N
Grants imp userid=<schema_owner/password@service_name> GRANTS=<Y|N>
imp uwclass/uwclass@pdbdev@pdbdev grants=N
Ignore imp userid=<schema_owner/password@service_name> IGNORE=<Y|N>
imp uwclass/uwclass@pdbdev@pdbdev ignore=Y
Indexes imp userid=<schema_owner/password@service_name> INDEXES=<Y|N>
imp uwclass/uwclass@pdbdev@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@pdbdev indexfile=c:\temp\index_dml.sql
Parameter File imp userid=<schema_owner/password@service_name> PARFILE=<parameter_file_name>
imp uwclass/uwclass@pdbdev@pdbdev parfile=c:\temp\uwparfile.ctl
Record Length imp userid=<schema_owner/password@service_name> RECORDLENGTH=<bytes>
imp uwclass/uwclass@pdbdev@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, uwclass)
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
 
MLib Demo
DDL for 11gR2 to 12cR1 migration 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

Related Topics
Data Pump
DBMS_TTS Built-in Package
Export
Tablespaces
Transportable Tablespaces

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-2014 Daniel A. Morgan All Rights Reserved