Export /Import dump - Oracle 10g/11g -(Linux)

Scenario: I have a db and, it contains a schema called "hr". So i want to get a export dump of hr schema and import the same dump to some other schema in same db.

First of all you must create a file location for the dump files.

Log in as Oracle user and create a file wherever you want. My file name is "dumps"

[oracle@prod ~]$ mkdir /u01/dumps
[oracle@prod ~]$ ls /u01/
app database dumps
[oracle@prod ~]$


I have few schemas in my db and I gonna take the export for hr schema.
So first of all you must log into the database as the schema user.

[oracle@prod ~]$ sqlplus hr/hr

Then issue below command to create the directory

SQL> create or replace directory export_dp as '/u01/dumps/';

Directory created.

• export_dp is the logical name that you can give for your directory and the '/u01/dumps/' is the physical path.

You can check yous directory using below command

SQL> select directory_name from all_directories;

DIRECTORY_NAME
------------------------------
EXPORT_DP

now exit from sql promt and come back to oracle promt and issue below statement to take the export.

[oracle@prod ~]$ expdp hr/hr@DB11G directory=export_dp dumpfile=dump_hr.dmp
logfile=log_hr.log schemas=hr

Export: Release 11.1.0.6.0 - Production on Thursday, 19 April, 2012 13:20:26

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/********@DB11G directory=export_dp
dum pfile=dump_hr.dmp logfile=log_hr.log schemas=hr
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "HR"."COUNTRIES" 6.375 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.015 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."JOBS" 6.984 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . exported "HR"."LOCATIONS" 8.273 KB 23 rows
. . exported "HR"."REGIONS" 5.484 KB 4 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
/u01/dumps/dump_hr.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:23:42

[oracle@prod ~]$

As you can see tables have been exported.. Cross verify whether dump file is there in the location.
[oracle@prod ~]$ ls /u01/dumps/
dump_hr.dmp log_hr.log
[oracle@prod ~]$

Now login to database as SYS user and create the new schema.. First of all you must create the tablespace for the user and then create the user. My tablespace name is "fin" and the user is "finance".

[oracle@prod ~]$ sqlplus / as sysdba
SQL> create tablespace fin datafile 'fin.dat' size 20M autoextend on;

Tablespace created.

Then create user finance and assign fin tablespace for the user

SQL> create user finance identified by finance default tablespace fin
2 temporary tablespace temp
3 quota unlimited on fin;

User created.

Now grant whatever the access you need for the user...I give dba writes for the user.

SQL> grant dba to finance;

Grant succeeded.

SQL>

Now Log in as user and create import directory

SQL> connect finance/finance
Connected.

SQL> create or replace directory import_dp as '/u01/dumps/';

Directory created.

now exit from sql and come to oracle prompt and issue below.

[oracle@prod ~]$ impdp finance/finance@DB11G directory=import_dp dumpfile=dump_hr.dmp logfile=log_fin.log REMAP_SCHEMA=hr:finance

Import: Release 11.1.0.6.0 - Production on Thursday, 19 April, 2012 13:43:23

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "FINANCE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "FINANCE"."SYS_IMPORT_FULL_01": finance/********@DB11G directory=import_dp dumpfile=dump_hr.dmp logfile=log_fin.log REMAP_SCHEMA=hr:finance
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"FINANCE" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "FINANCE"."COUNTRIES" 6.375 KB 25 rows
. . imported "FINANCE"."DEPARTMENTS" 7.015 KB 27 rows
. . imported "FINANCE"."EMPLOYEES" 16.80 KB 107 rows
. . imported "FINANCE"."JOBS" 6.984 KB 19 rows
. . imported "FINANCE"."JOB_HISTORY" 7.054 KB 10 rows
. . imported "FINANCE"."LOCATIONS" 8.273 KB 23 rows
. . imported "FINANCE"."REGIONS" 5.484 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "FINANCE"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 13:44:04

[oracle@prod ~]$

Now connect as finance user and check one table to confirm the import

[oracle@prod ~]$ sqlplus finance/finance

SQL> desc employees
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

This is all about export/import using expdp and impdp command.....

Note: if you are importing to same user/schema in a different database, then you dont need to use remap_schema clause in impdp command. It's all up to you how you want to do the import.