EXPORT & IMPORT DATABASE
EXPORT DATABASE PARAMETERS
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
IMPORT PARAMETERS
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.
[oracle@oracletraining ~]$
User creation: create
a new user and grant the exp full database permission
SQL> create tablespace vignesh datafile'/u01/app/oracle/oradata/ORACLETRAIN/datafile/vignesh.dbf'size
200m;
Tablespace created.
SQL> create user vi identified
by vi
2 default tablespace vignesh
3 quota unlimited on vignesh;
User created.
SQL> grant connect,resource to
vi;
Grant succeeded.
SQL> grant EXP_FULL_DATABASE,
IMP_FULL_DATABASE to vi:
Grant succeeded.
User connect and table process
SQL> conn vi/vi
Connected.
SQL> create table student(rno
number(10),name varchar2(10),address varchar2(20));
Table created.
SQL> insert into student values
(&no,'&name','&add');
Enter value for no: 101
Enter value for name: vignesh
Enter value for add: pollachi
old 1: insert into student values
(&no,'&name','&add')
new 1: insert into student values
(101,'vignesh','pollachi')
Commit complete.
SQL> create table mark(tamil
number(10),eng number(10),maths number(10));
Table created.
SQL> insert into mark
values(&t,&e,&m);
Enter value for t: 100
Enter value for e: 80
Enter value for m: 70
old 1: insert into mark
values(&t,&e,&m)
new 1: insert into mark values(100,80,70)
1 row created.
SQL> select * from student;
RNO NAME ADDRESS
---------- ----------
--------------------
101 vignesh pollachi
102 ajay nlc
103 vijay cbe
104 praeep dindukal
105 raj tpj
SQL> select * from mark;
8 rows selected.
Export single table:
[oracle@oracletraining ~]$ cd
vignesh/
[oracle@oracletraining vignesh]$ exp userid=system/oracle
file='/home/oracle/exp_imp/stu.dmp' log='/home/oracle/exp_imp/stu.log'
tables=vi.student
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 ...
Current user changed to VI
. . exporting table STUDENT 5 rows exported
Export terminated successfully
without warnings.
[oracle@oracletraining vignesh]$
EXPORT MULTIPLE TABLE
[oracle@oracletraining vignesh]$ exp userid=system/oracle
file='/home/oracle/vignesh/stu2.dmp' log='/home/oracle/vignesh/stu2.log'
tables=vi.student,vi.mark
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 ...
Current user changed to VI
. . exporting table STUDENT 5 rows exported
. . exporting table MARK 8 rows exported
Export terminated successfully
without warnings.
[oracle@oracletraining vignesh]$
Row level export
[oracle@oracletraining vignesh]$ exp userid=system/oracle
file='/home/oracle/vignesh/stu3.dmp' log='/home/oracle/vignesh/stu3.log'
tables=vi.student query=\"where rno=\'101\'\"
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 ...
Current user changed to VI
. . exporting table STUDENT 1 rows exported
Export terminated successfully
without warnings.
[oracle@oracletraining vignesh]$
Schema/ user level
[oracle@oracletraining vignesh]$ exp userid=system/oracle
file='/home/oracle/vignesh/stu4.dmp' log='/home/oracle/vignesh/stu.log'
OWNER=vi
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 VI
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type
definitions for user VI
About to export VI's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export VI's tables via
Conventional Path ...
. . exporting table MARK 8 rows exported
. . exporting table
STUDENT 5 rows exported
Export terminated successfully
without warnings.
[oracle@oracletraining vignesh]$
Multi schema/multi owner
[oracle@oracletraining vignesh]$ exp userid=system/oracle
file='/home/oracle/vignesh/stu5.dmp' log='/home/oracle/vignesh/stu5.log' OWNER=vi,msd
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 VI
. exporting foreign function
library names for user MSD
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type
definitions for user VI
. exporting object type
definitions for user MSD
About to export VI's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export VI's tables via
Conventional Path ...
. . exporting table MARK 8 rows exported
. . exporting table STUDENT 5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
About to export MSD's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export MSD's tables via
Conventional Path ...
. . exporting table CRICKET 3 rows exported
EXP-00091: Exporting questionable
statistics.
. 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 referential integrity
constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and
extensible indexes
. exporting posttables actions
. exporting materialized views
Full database permission
SQL> grant EXP_FULL_DATABASE,IMP_FULL_DATABASE
to vi;
Grant succeeded.
Full database export
[oracle@oracletraining vignesh]$ exp userid=system/oracle
file='/home/oracle/emp_imp/stu6.dmp' log='/home/oracle/exp_imp/stu6.log'
OWNER=vi
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 VI
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type
definitions for user VI
About to export VI's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export VI's tables via
Conventional Path ...
. . exporting table
MARK 8 rows exported
. . exporting table STUDENT 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@oracletraining vignesh]$
Tablespace level export
[oracle@oracletraining vignesh]$
[oracle@oracletraining vignesh]$ exp userid=system/oracle
file='/home/oracle/
exp_imp/ stu11.dmp'
log='/home/oracle/
exp_imp /stu11.log' tablespaces=vignesh,sysaux
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 VIGNESH ...
. exporting cluster definitions
. exporting table definitions
. . exporting table MARK 8 rows exported
. . exporting table STUDENT 5 rows exported
. exporting referential integrity
constraints
. exporting triggers
Export terminated successfully
without warnings.
[oracle@oracletraining vignesh]$
FLASHBACK_SCN
LEVEL BACKUP
[oracle@sample
exp_imp]$ exp userid=system/oracle
file='/home/oracle/exp_imp/vi2.dmp' log='/home/oracle/exp_imp/vi2.log'
tables=vi.sample flashback_scn=2169060;
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 ...
Current
user changed to VI
. . exporting table SAMPLE 4 rows exported
EXP-00091:
Exporting questionable statistics.
Export
terminated successfully with warnings.
[oracle@sample
exp_imp]$ exp userid=system/oracle
file='/home/oracle/exp_imp/vi2.dmp' log='/home/oracle/exp_imp/vi2.log'
tables=vi.sample flashback_scn=2169075;
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 ...
Current
user changed to VI
. . exporting table SAMPLE 3 rows exported
EXP-00091:
Exporting questionable statistics.
Export
terminated successfully with warnings.
[oracle@sample
exp_imp]$
FLASHBACK_TIME
LEVEL BACKUP
[oracle@sample ~]$ cd exp_imp/
[oracle@sample exp_imp]$ exp userid=system/oracle
file='/home/oracle/exp_imp/vi2.dmp' log='/home/oracle/exp_imp/vi2.log'
tables=vi.sample flashback_time=\"to_timestamp\(\'06-08-2021 21:42:55\',
\'DD-MM-YYYY HH24:MI:SS\'\)\"
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 ...
Current user changed to VI
.
. exporting table
SAMPLE 3 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@sample exp_imp]$ exp userid=system/oracle
file='/home/oracle/exp_imp/vi2.dmp' log='/home/oracle/exp_imp/vi2.log'
tables=vi.sample flashback_time=\"to_timestamp\(\'06-08-2021 21:42:18\',
\'DD-MM-YYYY HH24:MI:SS\'\)\"
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 ...
Current user changed to VI
.
. exporting table
SAMPLE 4 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@sample exp_imp]$
PAR FILE
BACKUP
[oracle@sample
exp_imp]$ vi par_bp.par
[oracle@sample
exp_imp]$ cat par_bp.par
file=par.dmp
log=par.log
tables=vi.sample
[oracle@sample
exp_imp]$ exp
PARFILE='/home/oracle/exp_imp/par_bp.par'
Username:
system
Password:
oracle
Connected
to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version
19.3.0.0.0
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 ...
Current
user changed to VI
. . exporting table SAMPLE 3 rows exported
EXP-00091:
Exporting questionable statistics.
Export
terminated successfully with warnings.
[oracle@sample
exp_imp]$
[oracle@sample
exp_imp]$ exp userid=system/oracle file='/home/oracle/exp_imp/vic.dmp'
log='/home/oracle/exp_imp/vic.log' tables=vi.sample FEEDBACK=10000
Connected
to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version
19.3.0.0.0
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 ...
Current
user changed to VI
. .
exporting table
SAMPLE
..............................................................................
786432 rows exported
EXP-00091:
Exporting questionable statistics.
Export
terminated successfully with warnings.
[oracle@sample
exp_imp]$
Avoiding constraints,indexes
& grants
[oracle@sample
exp_imp]$ exp userid=system/oracle file='/home/oracle/exp_imp/vic1.dmp'
log='/home/oracle/exp_imp/vic1.log' OWNER=vi
INDEXES=n CONSTRAINTS=n GRANTS=n
Connected
to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version
19.3.0.0.0
Export
done in US7ASCII character set and AL16UTF16 NCHAR character set
server
uses AL32UTF8 character set (possible charset conversion)
Note: grants on
tables/views/sequences/roles will not be exported
Note: indexes on tables will not
be exported
Note: constraints on tables will
not be exported
About to
export specified users ...
.
exporting pre-schema procedural objects and actions
.
exporting foreign function library names for user VI
.
exporting PUBLIC type synonyms
.
exporting private type synonyms
.
exporting object type definitions for user VI
About to
export VI's objects ...
.
exporting database links
.
exporting sequence numbers
.
exporting cluster definitions
. about
to export VI's tables via Conventional Path ...
. . exporting table MARK 8 rows exported
EXP-00091:
Exporting questionable statistics.
. . exporting table SAMPLE 786432 rows exported
EXP-00091:
Exporting questionable statistics.
. . exporting table STUDENT 5 rows exported
EXP-00091:
Exporting questionable statistics.
.
exporting refresh groups and children
.
exporting dimensions
.
exporting post-schema procedural objects and actions
. exporting
statistics
Export
terminated successfully with warnings.
[oracle@sample
exp_imp]$
Only
table definition
oracle@sample
exp_imp]$ exp userid=system/oracle
file='/home/oracle/exp_imp/vic2.dmp' log='/home/oracle/exp_imp/vic2.log'
OWNER=vi rows=n
Export
done in US7ASCII character set and AL16UTF16 NCHAR character set
server
uses AL32UTF8 character set (possible charset conversion)
Note: table data (rows) will not
be exported
About to
export specified users ...
About to
export VI's objects ...
. exporting
database links
.
exporting sequence numbers
.
exporting cluster definitions
. about
to export VI's tables via Conventional Path ...
. . exporting table MARK
EXP-00091:
Exporting questionable statistics.
. . exporting table SAMPLE
EXP-00091:
Exporting questionable statistics.
. . exporting table STUDENT
EXP-00091:
Exporting questionable statistics.
.
exporting synonyms
.
exporting views
.
exporting stored procedures
.
exporting operators
.
exporting referential integrity constraints
Export
terminated successfully with warnings.
[oracle@sample
exp_imp]$
IMPORT
DATABASE
Export
[oracle@sample exp_imp]$ exp userid=system/oracle
file='/home/oracle/exp_imp/imp.dmp' log='/home/oracle/exp_imp/imp.log'
tables=vi.sample
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 ...
Current user changed to VI
. . exporting table SAMPLE 786432 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@sample exp_imp]$
IMPORT
[oracle@sample exp_imp]$ imp userid=system/oracle
file='/home/oracle/exp_imp/imp1.dmp' log='/home/oracle/exp_imp/imp1.log' fromuser=vi
touser=bt
IMP-00002: failed to open
/home/oracle/exp_imp/imp1.dmp for read
Import file: expdat.dmp >
Export file created by EXPORT:V19.00.00 via
conventional path
import done in US7ASCII character set and AL16UTF16
NCHAR character set
import server uses AL32UTF8 character set (possible
charset conversion)
IMP-00403:
. importing VI's objects into BT
. .
importing table
"MARK" 8 rows
imported
. .
importing table
"STUDENT" 5
rows imported
Import terminated successfully with warnings.
[oracle@sample exp_imp]$
Avoiding constraints index
[oracle@sample
exp_imp]$ imp userid=system/oracle file='/home/oracle/exp_imp/imp2.dmp'
log='/home/oracle/exp_imp/imp2.log' fromuser=vi
touser=bt CONSTRAINTS=n INDEXES=n
Export
file created by EXPORT:V19.00.00 via conventional path
import
done in US7ASCII character set and AL16UTF16 NCHAR character set
import
server uses AL32UTF8 character set (possible charset conversion)
IMP-00403:
. importing VI's objects into BT
IMP-00015:
following statement failed because the object already exists:
"CREATE TABLE "MARK"
("TAMIL" NUMBER(10, 0), "ENG" NUMBER(10, 0),
"MATHS" NU"
"MBER(10, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL"
" 65536 NEXT 1048576 MINEXTENTS 1
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL "
"DEFAULT) LOGGING NOCOMPRESS"
IMP-00015:
following statement failed because the object already exists:
"CREATE TABLE "STUDENT"
("RNO" NUMBER(10, 0), "NAME" VARCHAR2(10),
"ADDRESS""
" VARCHAR2(20)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITI"
"AL 65536 NEXT 1048576 MINEXTENTS 1
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POO"
"L DEFAULT) LOGGING NOCOMPRESS"
Import
terminated successfully with warnings.
Data_only
option
[oracle@sample
exp_imp]$ imp userid=system/oracle
file='/home/oracle/exp_imp/imp.dmp' log='/home/oracle/exp_imp/imp.log'
fromuser=vi touser=bt DATA_ONLY=y
Export
file created by EXPORT:V19.00.00 via conventional path
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
"/home/oracle/exp_imp/imp_sys.sql" which contains DDL that failed due
to a privilege issue.
. importing VI's objects into BT
. . importing table "SAMPLE" 786432 rows imported
Import
terminated successfully with warnings.
[oracle@sample
exp_imp]$
***********************************************************************
