Knowledge transfer to Wisdom: Export/Import Example

=================Expdp/Impdp==========================
C:\Users\smohanty46>set ORACLE_SID=xe
C:\Users\smohanty46>sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 4 13:40:57 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
SQL>
SQL>
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
XE READ WRITE
SQL>
SQL>
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS ORACLECLRDIR
C:\oraclexe\app\oracle\product\11.2.0\server\bin\clr
SYS DATA_PUMP_DIR
C:\oraclexe\app\oracle\admin\xe\dpdump\
SYS XMLDIR
C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\xml
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS ORACLE_OCM_CONFIG_DIR
C:/ADE/jaikrish_xe/oracle\ccr\state
SQL> create directory test as 'C:\oraclexe\test';

Directory created.

SQL> grant read,write on directory test to hr;

Grant succeeded.
##############################full Database backup############################

C:\oraclexe\test>expdp userid=system/oracle directory=test full=y dumpfile=full_database.dmp logfile=full_database.log
#############################expdp using parfile##################

C:\oraclexe\test>expdp userid=system/oracle parfile=C:\Users\smohanty46\Documents\table.par

##############################schema backup##############################################
C:\oraclexe\test>expdp userid=system/oracle directory=test schemas=HR dumpfile=

r_schema.dmp logfile=hr_schmea.log
#########################Table backup############################################
C:\Users\smohanty46>expdp userid=hr/hr directory=test tables=jobs dumpfile=jobs.

dmp logfile=jobs.log

###############################metadata only#####################################
C:\Users\smohanty46>expdp userid=hr/hr directory=test tables=jobs content=metada

ta_only dumpfile=jobs1.dmp logfile=jobs1.log

###################################data only#########################################
C:\Users\smohanty46>expdp userid=hr/hr directory=test tables=jobs content=data_o

nly dumpfile=jobs2.dmp logfile=jobs2.log
########################object(view,sequence,procedure,trigger,index)################################################
C:\oraclexe\test>expdp userid=hr/hr include=view,sequence,procedure,trigger,inde

x directory=test dumpfile=object.dmp logfile=object.log
###########################Flash back expdp##############################################################
SQL> connect system/oracle
Connected.

SQL> create table hr.tb1(id number);

Table created.

SQL> insert into hr.tb1 values(1);

1 row created.

SQL> insert into hr.tb1 values(2);

1 row created.

SQL> insert into hr.tb1 values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
379525

SQL> insert into hr.tb1 values(4);

1 row created.

SQL> insert into hr.tb1 values(5);

1 row created.

SQL> insert into hr.tb1 values(6);

1 row created.

SQL> commit
2 ;

Commit complete.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
379544

C:\oraclexe\test>expdp userid=hr/hr directory=test flashback_scn=379525 dumpfile=till_3rows.dmp logfile=3rows.log tables=tb1
###########################Size estimation in block method##########################################

C:\oraclexe\test>expdp userid=hr/hr estimate=blocks tables=tb1 dumpfile=tb1_size.dmp logfile=tb1_size.log directory=test

###############################Size estimation with out creating the backup#################################

C:\oraclexe\test>expdp userid=hr/hr estimate_only=y tables=tb1 directory=test
############################Creating multiple dump file accomadate space###########################################

C:\oraclexe\test>expdp full=y directory=test userid=system/oracle filesize=10M dumpfile=full%U.dmp logfile=full.log job_name=myjob
############################Creating dump file of different sizes in different directory#######################

C:\oraclexe\test>expdp userid=system/oracle filesize=40m full=y dumpfile=test:file_1.dmp,ORACLECLRDIR:file_2.dmp,DATA_PUMP_DIR:file_3.dmp,XMLDIR:file_4.dmp logfile=test:multiple_directory.log

########################################Exclude table ############################

C:\oraclexe\test>expdp hr/hr directory=test exclude=table:"in\ ('JOBS'\)" dumpfile=exclude_job_table.dmp logfile=exclude_job_table.log

########################################Exclude object #########################

C:\oraclexe\test>expdp hr/hr exclude=procedure directory=test dumpfile=exclude_proc.dmp logfile=exclude_proc.log

#################Tablespace export#########################

C:\oraclexe\test>expdp userid=system/oracle tablespaces=users,temp dumpfile=tablespace.dmp logfile=tablespace.log directory=test

#########################stoping,Attaching,continuing,killing jobs###########################

C:\oraclexe\test>expdp system/oracle full=y directory=test dumpfile=dump_full.dmp logfile=dump_full.log

xport: Release 11.2.0.2.0 - Production on Wed Feb 5 16:24:29 2014

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y directory=test dumpfile=dump_full.dmp logfile=dump_full.log

Estimate in progress using BLOCKS method...

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 162.5 MB

Processing object type DATABASE_EXPORT/TABLESPACE

Processing object type DATABASE_EXPORT/PROFILE

Processing object type DATABASE_EXPORT/SYS_USER/USER

Processing object type DATABASE_EXPORT/SCHEMA/USER

Processing object type DATABASE_EXPORT/ROLE

Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA

Processing object type DATABASE_EXPORT/RESOURCE_COST

Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK

Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK

Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE

Export> STOP_JOB=IMMEDIATE

Are you sure you wish to stop this job ([yes]/no): yes

C:\oraclexe\test>expdp system/oracle attach=SYS_EXPORT_FULL_01

Export: Release 11.2.0.2.0 - Production on Wed Feb 5 16:25:48 2014

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Job: SYS_EXPORT_FULL_01

Owner: SYSTEM

Operation: EXPORT

Creator Privs: TRUE

GUID: CCBD86DB9C574F7EAFFA108C6D2FDBF4

Start Time: Wednesday, 05 February, 2014 16:25:51

Mode: FULL

Instance: xe

Max Parallelism: 1

EXPORT Job Parameters:

Parameter Name Parameter Value:

CLIENT_COMMAND system/******** full=y directory=test dumpfile=dump_full.dmp logfile=dump_full.log

State: IDLING

Bytes Processed: 0

Current Parallelism: 1

Job Error Count: 0

Dump File: C:\oraclexe\test\dump_full.dmp

bytes written: 4,096

Worker 1 Status:

Process Name: DW00

State: UNDEFINED

Object Schema: APEX_040000

Object Name: WWV_FLOW_PAGE_PLUGS

Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Completed Objects: 377

Worker Parallelism: 1

Export> CONTINUE_CLIENT

Job SYS_EXPORT_FULL_01 has been reopened at Wednesday, 05 February, 2014 16:25

Restarting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y directory=test dumpfile=dump_full.dmp logfile=dump_full.log

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

Export> KILL_JOB

Are you sure you wish to stop this job ([yes]/no): yes

C:\oraclexe\test>sqlplus system/oracle

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 5 16:26:54 2014

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> select * from dba_datapump_jobs;

no rows selected

==========================Import==========================================

####################import table with data and metadata#########

C:\oraclexe\test>impdp userid=system/oracle directory=test tables=master.jobs dumpfile=master.dmp logfile=master_imp.log content=all

#######################################default of table_exists_action=skip###########

C:\oraclexe\test>impdp userid=system/oracle directory=test tables=master.jobs dumpfile=master.dmp logfile=master_imp.log content=all

#########################################table_exists_action=append###################

C:\oraclexe\test>impdp userid=system/oracle directory=test tables=master.jobs dumpfile=master.dmp logfile=master_imp.log table_exists_action=append

######################################table_exists_action=truncate######################

C:\oraclexe\test>impdp userid=system/oracle directory=test tables=master.jobs dumpfile=master.dmp logfile=master_imp.log table_exists_action=truncate

#############################################table_exists_action=replace###############

C:\oraclexe\test>impdp userid=system/oracle directory=test tables=master.jobs dumpfile=master.dmp logfile=master_imp.log table_exists_action=replace

######################content=data_only################################

C:\oraclexe\test>impdp userid=system/oracle directory=test tables=master.jobs dumpfile=master.dmp logfile=master_imp.log content=data_only

###############################content=metadata_only##############################

C:\oraclexe\test>impdp userid=system/oracle directory=test tables=master.jobs dumpfile=master.dmp logfile=master_imp.log content=metadata_only

###############################content=all##########################################

C:\oraclexe\test>impdp userid=system/oracle directory=test tables=master.jobs dumpfile=master.dmp logfile=master_imp.log content=all

################################import schema to a different schema#######

C:\oraclexe\test>impdp userid=system/oracle directory=test remap_schema=hr:mamuni dumpfile=hr_schema.dmp logfile=hr_schema1_imp.log

################################import table into table###########################

C:\oraclexe\test>impdp userid=system/oracle directory=test remap_table=master.jobs:mamuni.darling dumpfile=master.dmp logfile=master_table_import.log

############################import full database#########

C:\oraclexe\test>impdp userid=system/oracle directory=test full=y dumpfile=full_db_exter.dmp logfile=full_db_exter_imp.log

###############################remap tablespace##########################

SQL> create tablespace test datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\test.dbf' size 5m;

Tablespace created.

SQL> create user mamuni1 identified by mamuni1;

User created.

SQL> grant connect,resource to mamuni1;

Grant succeeded.

SQL> alter user mamuni1 default tablespace test;

User altered.

SQL> alter user mamuni1 quota unlimited on test;

User altered.

SQL> create table mamuni1.employees as select * from hr.employees;

Table created.

SQL> create table mamuni1.jobs as select * from hr.jobs;

Table created.

SQL> select username,default_tablespace from dba_users where username='MAMUNI1';

USERNAME DEFAULT_TABLESPACE

------------------------------ ------------------------------

MAMUNI1 TEST

C:\oraclexe\test>expdp userid=system/oracle directory=test tablespaces=test dumpfile=test_tablespace.dmp logfile=test_tablespace.log

Export: Release 11.2.0.2.0 - Production on Thu Feb 6 19:57:17 2014

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01": userid=system/******** directory=test tablespaces=test dumpfile=test_tablespace.dmp logfile=test_tablespace.log

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "MAMUNI1"."EMPLOYEES" 16.81 KB 107 rows

. . exported "MAMUNI1"."JOBS" 6.992 KB 19 rows

Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:

C:\ORACLEXE\TEST\TEST_TABLESPACE.DMP

Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at 19:57:31

SQL> create tablespace test1 datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\test1.dbf' size 2m;

Tablespace created.

C:\oraclexe\test>impdp userid=system/oracle directory=test dumpfile=test_tablespace.dmp logfile=imp_test_tablespace.log remap_tablespace=test:test1 table_exists_action=replace

Import: Release 11.2.0.2.0 - Production on Thu Feb 6 19:59:59 2014

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01": userid=system/******** directory=test dumpfile=test_tablespace.dmp logfile=imp_test_tablespace.log remap_tablespace=test:test1 table_exists_action=replace

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "MAMUNI1"."EMPLOYEES" 16.81 KB 107 rows

. . imported "MAMUNI1"."JOBS" 6.992 KB 19 rows

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 20:00:03

C:\oraclexe\test>

SQL> select table_name,tablespace_name from dba_tables where owner='MAMUNI1';

TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

EMPLOYEES TEST1

JOBS TEST1

SQL> create table mamuni1.employee1 as select * from mamuni1.employees;

Table created.

SQL> select table_name,tablespace_name from dba_tables where owner='MAMUNI1';

TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

EMPLOYEE1 TEST

EMPLOYEES TEST1

JOBS TEST1

######################################DDL of the dumpfile using sqlfile paramaeter######

C:\oraclexe\test>impdp userid=system/oracle directory=test dumpfile=test_tablespace.dmp sqlfile=test_tablespace_ddl.sql

Knowledge transfer to Wisdom

Labels

Wednesday, February 19, 2014

Export/Import Example

No comments:

Post a Comment

Labels