How to export/import an Oracle Schema

In this tutorial we will see how we can export and import the content of one Oracle Schema using Oracle tools called EXPDP and IMPDP. The syntax for this task is : 

expdp user/passwd
 directory=DATA_PUMP_DIR
 schemas=schema_name
 dumpfile=dump_file.dmp
 logfile=log_file.log
The options for this command are as follows bellow: 
 CLUSTER {Y | N }
  COMPRESSION={METADATA_ONLY | NONE}
  CONTENT={ALL | DATA_ONLY | METADATA_ONLY}
  DATA_OPTIONS XML_CLOBS
  DIRECTORY=directory_object
  DUMPFILE=[directory_object:]file_name [,[directory_object:]file_name...]
  ESTIMATE={ BLOCKS | STATISTICS}
  ESTIMATE_ONLY={Y|N}
  EXCLUDE={object_type [:name_clause]} [,EXCLUDE=...]
  ENCRYPTION { ALL | DATA_ONLY | METADATA_ONLY }
  ENCRYPTION_PASSWORD=password
  ENCRYPTION_ALGORITHM { AES128 | AES192 | AES256 }
  ENCRYPTION_MODE { DUAL | PASSWORD | TRANSPARENT }
  FILESIZE=number_of_bytes
  FLASHBACK_SCN=scn_value
  FLASHBACK_TIME=timestamp
  INCLUDE={object_type [:name_clause]} [,INCLUDE=...]
  JOB_NAME=jobname_string
  LOGFILE=[directory_object:]file_name
  NOLOGFILE={Y|N}
  PARFILE=[directory_path]file_name
  NETWORK_LINK=database_link
  PARALLEL=int
  REMAP_DATA
  QUERY=[schema_name.][table_name:]query_clause
  REUSE_DUMPFILES      Overwrite destination dump file if it exists (N)
  SAMPLE=[schema_name.][table_name:]sample_percent
  SERVICE_NAME
  SOURCE_EDITION
  STATUS [=int]
  TRANSPORTABLE { ALWAYS | NEVER }
  VERSION={COMPATIBLE | LATEST | version_string}
We are not going to use many of them here because is not the case, we will simply create a Schema dump and import the dump into a development database carrying the same schema name.
  • 1- Start by verifying if you have an available directory, to do so you need to run the query bellow:
 select directory_name,directory_path from dba_directories;

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ --------------------------------------------------------------------------------
SQLT$BDUMP                     /oracle/app/admin/PCORP1/bdump
DATA_PUMP_DIR                  /oracle/oradata/PCORP1/flash_recovery_area/PCORP1/datapump
ORACLE_OCM_CONFIG_DIR          /oracle/app/product/10.2.0/db_1/ccr/state
SQLT$UDUMP                     /oracle/app/admin/PCORP1/udump
SQLT$STAGE                     /oracle/app/admin/PCORP1/udump
TRCA$INPUT1                    /oracle/app/admin/PCORP1/udump
TRCA$STAGE                     /oracle/app/admin/PCORP1/udump
TRCA$INPUT2                    /oracle/app/admin/PCORP1/bdump
EXPDPFURH                      /l00/oradata/DB/flash_recovery_area/DB/datapump
EXPDPFURD                      /l00/oradata/DCORP1/flash_recovery_area/DCORP1/datapump
ADUMP                          /oracle/app/admin/PCORP1/adump
EXPDP                          /export/oradata/PCORP1/datapump

12 rows selected
For our example we will chhode the EXPDP - DIRECTORY that is fold in "/export/oradata/PCORP1/datapump"
  • 2- In case you don't have one create one as follows:
Make sure the directory path exists and oracle user has rights on it. 
CREATE OR REPLACE DIRECTORY PUMP_DIR AS '/oracle/app/admin/utl';
  • 3- Let's create the command now:
 . oraenv
ORACLE_SID = [PCORP1] ? PCORP1
oracle@dc8004:/opt/oracle/otk/home[PCORP1]
> expdp system/xxxxxxxx   directory=EXPDP  schemas=SEG    dumpfile=GVC.dmp  logfile=GVC.log

Export: Release 10.2.0.5.0 - 64bit Production on Quarta-Feira, 16 Outubro, 2013 11:46:12

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Conectado a: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
The process will create a dump file called GVC.dmp that will be located in our /export/oradata/db/datapump location. Depending on your schema size it can take some time.
  • 4- Copy the dump archive to the host where the Oracle instance is running.
Make sure you find the DIRECTORY as we did on Step 1 on the development environment and copy the dump into it. 
scp oracle@HOST:/DIRECTORY
  • 5- This step is very important(more or less!), We need to remove all the objects that are in the receiving Schema.
This is to avoid invalid objects and error messages when importing the dump. Just place the name of your schema into the script. Drop the tables: 
select
 'drop table '||owner||'.'||table_name ||' cascade constraints PURGE;'
from all_tables where owner=schema;
Drop the rest of the objects: 
 select
 'drop '||object_type ||' '||owner||'.'||object_name ||';'
from all_objects where owner=schema  and object_type not in('INDEX','TABLE');
You need to run the output of the scripts in order to remove the objects.
  • 6- Import your dump into your development environment.
The syntax 
impdp system/  schemas=xxxx directory=DATA_PUMP_DIR dumpfile=xxx.dmp logfile=impdp.log
remap_schema=old:new
remap_tablespace=old:new
exclude=grant
  • 7- Run the command now.
  . oraenv
ORACLE_SID = [DCORP1] ? DB
The /oracle/app/product/10.2.0/db_1/bin/orabase binary does not exist
You can set ORACLE_BASE manually if it is required.
oracle:/oracle/app/admin/utl[DB]
> impdp system/xxxxx  schemas=SEG  directory=PUMP_DIR dumpfile=SEG.dmp logfile=SEG.log remap_schema=SEG:SEG  remap_tablespace=TSSEG:TSSEG exclude=grant

Import: Release 10.2.0.5.0 - 64bit Production on Quarta-Feira, 16 Outubro, 2013 12:55:21

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
  • 8- Last step is to recompile all your objects using the utlrp.sql script.
 locate utlrp.sql
/oracle/app/product/11.2.0/dbhome_2/rdbms/admin/utlrp.sql
oracle:/u02[DB]
> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Qua Out 16 13:38:00 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 @/oracle/app/product/11.2.0/dbhome_2/rdbms/admin/utlrp.sql
...
...
...
...
Tips: Make sure you have your eyes on the instance alert log all the time to see any possible errors. 
  oracle@:/u02[DB]
> locate alert_db.log
/oracle/app/admin/DB/bdump/alert_DB.log

oracle@:/u02[DB]
>
oracle@:/u02[DB]
> tail -f /oracle/app/admin/DB/bdump/alert_DB.log
  Current log# 2 seq# 2206 mem# 0: /l00/oradata/DB/redo2.rdo
Wed Oct 16 13:42:37 BRT 2013
Deleted Oracle managed file /u01/oradata/DB/flash_recovery_area/DB/archivelog/2013_09_30/o1_mf_1_1849_94kxvw08_.arc
Wed Oct 16 13:42:41 BRT 2013
Thread 1 cannot allocate new log, sequence 2207
Checkpoint not complete
  Current log# 2 seq# 2206 mem# 0: /l00/oradata/DB/redo2.rdo
Wed Oct 16 13:42:44 BRT 2013
....
...
...
..
..
..
.

Copyright 2020. All Rights Reserved