HR HR HR Hrdev: Example: Using Data Pump Export and Data Pump Import

Example: Using Data Pump Export and Data Pump Import
In this example, suppose that you want to make some changes to

the HR sample schema and then test those changes without affecting the
current HR schema. You could export the HR schema and then import it into a
new HRDEV schema, where you could perform development work and conduct
testing. To do this, take the following steps:
1. Do one of the following:

o On Windows: Log in to the Oracle Database XE host computer as
the user who installed Oracle Database XE, and then open a
command window.
o On Linux: Start a terminal session and log in to the Oracle
Database XE host computer with the oracle user account.
2. On Linux, ensure that environment variables are set according to the
instructions in "Setting Environment Variables on the Linux Platform".
3. At the command prompt, issue the command appropriate to your
operating system, to create the directory where the exported files will be
placed:
On Windows:
MKDIR c:\oraclexe\app\tmp
On Linux:
mkdir /usr/lib/oracle/xe/tmp
4. Start SQL Command Line (SQL*Plus) and connect as user SYSTEM by

entering the following at the command prompt:
5. sqlplus SYSTEM/password
6.
where password is the password that you specified for

the SYS and SYSTEM user accounts upon installation (Windows) or
configuration (Linux) of Oracle Database XE.
7. At the SQL prompt, enter the following commands to create a directory

object named dmpdir for the tmp directory that you just created, and to
grant read and write access to it for user HR.
On Windows:
CREATE OR REPLACE DIRECTORY dmpdir AS

'c:\oraclexe\app\tmp';
GRANT READ,WRITE ON DIRECTORY dmpdir TO hr;
On Linux:
CREATE OR REPLACE DIRECTORY dmpdir AS

'/usr/lib/oracle/xe/tmp';
GRANT READ,WRITE ON DIRECTORY dmpdir TO hr;
8. Export the HR schema to a dump file named schema.dmp by issuing the

following command at the system command prompt:
9. expdp SYSTEM/password SCHEMAS=hr DIRECTORY=dmpdir
DUMPFILE=schema.dmp LOGFILE=expschema.log
10.
where password is the password for the SYSTEM user.
As the export operation takes place, messages similar to the following

are displayed:
Export: Release 10.2.0.1.0 - Production on Tuesday, 13

December, 2005 11:48:01
Copyright (c) 2003, 2005, Oracle. All rights

reserved.
Connected to: Oracle Database 10g Express Edition

Release 10.2.0.1.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":
SYSTEM/******** SCHEMAS=hr
DIRECTORY=dmpdir DUMPFILE=schema.dmp
LOGFILE=expschema.log
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
SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
SCHEMA_EXPORT/PROCEDURE/PROCEDURE
SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES"
6.093 KB 25 rows
. . exported "HR"."DEPARTMENTS"
6.640 KB 27 rows
. . exported "HR"."EMPLOYEES"
15.77 KB 107 rows
. . exported "HR"."JOBS"
6.609 KB 19 rows
. . exported "HR"."JOB_HISTORY"
6.585 KB 10 rows
. . exported "HR"."LOCATIONS"
7.710 KB 23 rows
. . exported "HR"."REGIONS"
5.296 KB 4 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01"
successfully loaded/unloaded
******************************************************
************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
C:\ORACLEXE\APP\TMP\SCHEMA.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully
completed at 11:48:46
The schema.dmp file and the expschema.log file are written to

the dmpdir directory.
11. Import the dump file, schema.dmp, into another schema, in this
case, HRDEV. You use the REMAP_SCHEMA command parameter to
indicate that objects are to be imported into a schema other than their
original schema. Because the HRDEV user account does not already
exist, the import process automatically creates it. In this example, you will
import everything except constraints, ref_constraints, and indexes. If a
table already exists, it is replaced with the table in the export file.
At the operating system command prompt, issue the following command:
impdp SYSTEM/password SCHEMAS=hr DIRECTORY=dmpdir

DUMPFILE=schema.dmp
REMAP_SCHEMA=hr:hrdev EXCLUDE=constraint,
ref_constraint, index
TABLE_EXISTS_ACTION=replace LOGFILE=impschema.log
where password is the password for the SYSTEM user.
As the import operation takes place, messages similar to the following

are displayed (this output is also written to the impschema.log file in
the dmpdir directory):
Import: Release 10.2.0.1.0 - Production on Tuesday, 13

December, 2005 11:49:29
Copyright (c) 2003, 2005, Oracle. All rights

reserved.
Connected to: Oracle Database 10g Express Edition

Release 10.2.0.1.0 - Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01"
successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":
SYSTEM/******** SCHEMAS=hr
DIRECTORY=dmpdir DUMPFILE=schema.dmp
REMAP_SCHEMA=hr:hrdev
EXCLUDE=constraint, ref_constraint, index
TABLE_EXISTS_ACTION=replace LOGFILE=impschema.log
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
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 "HRDEV"."COUNTRIES"
6.093 KB 25 rows
. . imported "HRDEV"."DEPARTMENTS"
6.640 KB 27 rows
. . imported "HRDEV"."EMPLOYEES"
15.77 KB 107 rows
. . imported "HRDEV"."JOBS"
6.609 KB 19 rows
. . imported "HRDEV"."JOB_HISTORY"
6.585 KB 10 rows
. . imported "HRDEV"."LOCATIONS"
7.710 KB 23 rows
. . imported "HRDEV"."REGIONS"
5.296 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
SCHEMA_EXPORT/PROCEDURE/PROCEDURE
SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully
completed at 11:49:49
The HRDEV schema is now populated with data from the HR schema.
12. Assign a password to the newly created HRDEV user account. To do so,
start SQL Command Line and connect as user SYSTEM (as you did in
step 4), and then at the SQL prompt, enter the
following ALTER USER statement:
13. ALTER USER hrdev IDENTIFIED BY hrdev;
14.
This statement assigns the password hrdev.
You can now work in the HRDEV schema without affecting your
production data in the HR schema.
Exporting and Importing Data with the Export and Import Utilities
The Export and Import utilities provide a simple way for you to transfer data
objects between Oracle databases. They are invoked with
the exp and imp commands, respectively. These utilities provide support for
XMLType data, whereas the Data Pump Export and Import utilities do not.
Note:

HR HR HR Hrdev: Example: Using Data Pump Export and Data Pump Import

Uploaded by

Copyright:

Available Formats

HR HR HR Hrdev: Example: Using Data Pump Export and Data Pump Import

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

HR HR HR Hrdev: Example: Using Data Pump Export and Data Pump Import

Uploaded by

Copyright:

Available Formats

Example: Using Data Pump Export and Data Pump Import

In this example, suppose that you want to make some changes to

1. Do one of the following:

4. Start SQL Command Line (SQL*Plus) and connect as user SYSTEM by

where password is the password that you specified for

7. At the SQL prompt, enter the following commands to create a directory

CREATE OR REPLACE DIRECTORY dmpdir AS

CREATE OR REPLACE DIRECTORY dmpdir AS

8. Export the HR schema to a dump file named schema.dmp by issuing the

where password is the password for the SYSTEM user.

As the export operation takes place, messages similar to the following

Export: Release 10.2.0.1.0 - Production on Tuesday, 13

Copyright (c) 2003, 2005, Oracle. All rights

Connected to: Oracle Database 10g Express Edition

The schema.dmp file and the expschema.log file are written to

At the operating system command prompt, issue the following command:

impdp SYSTEM/password SCHEMAS=hr DIRECTORY=dmpdir

As the import operation takes place, messages similar to the following

Import: Release 10.2.0.1.0 - Production on Tuesday, 13

Copyright (c) 2003, 2005, Oracle. All rights

Connected to: Oracle Database 10g Express Edition

This statement assigns the password hrdev.

You might also like