Oracle Data Pump usage

Hi. baidu. comhanbingxiaoshiitem63724f51aad754968c12edae1. The connection to the Oracle database SQLconnassysdba is connected. 2. Create an operating directory SQLcreatedirectorydump_dirase: dump. Note that you must also use the operating system command to create this physical directory on the hard disk. Directory

Http://hi.baidu.com/hanbingxiaoshi/item/63724f51aad754968c12edae 1. Connecting to the Oracle Database SQL conn/as sysdba is connected. 2. create an operating directory SQL create directory dump_dir as 'e: \ dump '. Note that you also need to use the operating system command to create this physical directory on the hard disk. Directory

Http://hi.baidu.com/hanbingxiaoshi/item/63724f51aad754968c12edae

1. Connect to the Oracle database
SQL> conn/as sysdba
Connected.

2. Create an operation directory
SQL> create directory dump_dir as 'e: \ dump ';

Note that you also need to use the operating system command to create this physical directory on the hard disk.

The directory has been created.

3. Use the following command to create an export file directory
SQL> grant read, write on directory dump_dir to scott;

Authorization successful.

4. Use the command expdp to export data

C: \> expdp scott/tiger directory = dump_dir dumpfile = 20080601.dmp tables = dept, emp

Export: Release 10.2.0.1.0-Production on Wednesday, 18 August 1, 2008 15:49:00

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
Start "SCOTT". "SYS_EXPORT_TABLE_01": scott/********* directory = dump_dir dumpfile = 20080601.dmp table
Dept, emp
Using the BLOCKS Method for estimation...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
The total Estimation Using the BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
.. Exported "SCOTT". "DEPT" 5.656 KB 4 lines
.. Exported "SCOTT". "EMP" 7.820 KB 14 rows
The master table "SCOTT". "SYS_EXPORT_TABLE_01" is successfully loaded/uninstalled"
**************************************** **************************************
The dump file set of SCOTT. SYS_EXPORT_TABLE_01 is:
E: \ DUMP \ 20080601.DMP
The job "SCOTT". "SYS_EXPORT_TABLE_01" was successfully completed at 15:49:22.

C: \>

**************************************** **************************************** ******************************
Common Errors

If a physical file is not created on the specified hard disk, the following error message is displayed:
The solution is to create a specified physical file directory on the hard disk and execute the expdp program.
Create directory dump_dir as 'e: \ dump 'in Oracle ';
The command only logically creates a connection between a directory that can be recognized by Oracle and a physical directory on the hard disk,
Physical directories cannot be created on hard disks.

**************************************** **************************************** ******************************
C: \> expdp scott/tiger directory = dump_dir dumpfile = 20080601.dmp tables = dept, emp

Export: Release 10.2.0.1.0-Production on Wednesday, 18 August 1, 2008 15:46:19

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: Invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: In "SYS. UTL_FILE", line 475
ORA-29283: invalid file operation

Import command:

Impdp scott/tiger directory = dump_dir dumpfile = 20080601.dmp tables = dept, emp

C: \> impdp scott/tiger directory = dump_dir dumpfile = 20080601.dmp tables = dept, emp

Import: Release 10.2.0.1.0-Production on Wednesday, 18 August 1, 2008 15:56:11

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
The master table "SCOTT". "SYS_IMPORT_TABLE_01" is successfully loaded/uninstalled"
Start "SCOTT". "SYS_IMPORT_TABLE_01": scott/********* directory = dump_dir dumpfile = 20080601.dmp table
Dept, emp
Processing object type TABLE_EXPORT/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.. Imported "SCOTT". "DEPT" 5.656 KB 4 lines
.. Imported "SCOTT". "EMP" 7.820 KB 14 rows
Processing object type TABLE_EXPORT/TABLE/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
The job "SCOTT". "SYS_IMPORT_TABLE_01" was successfully completed at 15:56:20.

C: \>

View the results and find that the deleted data table has been retrieved again:
SQL> set linesize 150;
SQL> select * from scott. emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-----------------------------------------------------------------------------------
7369 smith clerk 7902-12-80 800 20
7499 allen salesman 7698 20-2 month-81 1600 300 30
7521 ward salesman 7698 22-2 month-81 1250 500 30
7566 jones manager 7839 2975-81 20
7654 martin salesman 7698 28-9 month-81 1250 1400 30
7698 blake manager 7839 01-5 months-81 2850 30
7782 clark manager 7839-81 2450 10
7788 scott analyst 7566-87 3000 20
7839 king president 17-11 month-81 5000 10
7844 turner salesman 7698 month-81 1500 0 30
7876 adams clerk 7788 month-87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-----------------------------------------------------------------------------------
7900 james clerk 7698 03-12 months-81 950 30
7902 ford analyst 7566 03-12 months-81 3000 20
7934 miller clerk 7782 23-1 month-82 1300 10

14 rows have been selected.

SQL> select * from scott. dept;

DEPTNO DNAME LOC
-------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL>

**************************************** **************************************** **************************************** *******************
Analysis of data export from Data Pump in Oracle10g
**************************************** **************************************** **************************************** *******************
The Data Pump in Oracle10g can only be used on the server side, but not on the client side. It can export tables, schemes, tablespaces, and the entire database.

1. Export a table
C: \> expdp scott/tiger directory = dump_dir dumpfile = 20080601.dmp tables = dept, emp

2. Export Solution
The export scheme exports all database objects and database tables of the scheme to one file.

C: \> expdp hr/hr directory = dump_dir dumpfile = 20080601schema. dmp schemas = hr
C: \> expdp hr/hr directory = dump_dir dumpfile = 20080601schema. dmp schemas = hr

Export: Release 10.2.0.1.0-Production on Wednesday, 18 August 1, 2008 16:04:16

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
Start "HR". "SYS_EXPORT_SCHEMA_01": hr/********* directory = dump_dir dumpfile = 20080601schema. dmp sche
S = hr
Using the BLOCKS Method for estimation...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
The total Estimation Using the BLOCKS method: 448 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/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
.. Exported "HR". "COUNTRIES" 6.085 KB 25 lines
.. Exported "HR". "units" 6.632 KB 27 lines
.. Exported "HR". "EMPLOYEES" 15.76 KB 107 rows
.. Exported "HR". "JOBS" 6.609 KB 19 lines
.. Exported "HR". "JOB_HISTORY" 6.585 KB 10 rows
.. Exported "HR". "LOCATIONS" 7.710 KB 23 lines
.. Exported "HR". "REGIONS" 5.289 KB 4 rows
The master table "HR". "SYS_EXPORT_SCHEMA_01" is successfully loaded/uninstalled"
**************************************** **************************************
The dump file set of HR. SYS_EXPORT_SCHEMA_01 is:
E: \ DUMP \ 20080601SCHEMA. DMP
The job "HR". "SYS_EXPORT_SCHEMA_01" was successfully completed at 16:04:50.

C: \>

If you do not have the permission to operate the dump_dir directory for the hr user, the following error message is displayed:
C: \> expdp hr/hr directory = dump_dir dumpfile = 20080601schema. dmp schemas = hr

Export: Release 10.2.0.1.0-Production on Wednesday, 18 August 1, 2008 16:02:55

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: Invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: Invalid directory name DUMP_DIR

3. Export tablespace
Expdp system/sysadmin directory = dump_dir dumpfile = tablespace. dmp tablespaces = users

C: \> expdp system/sysadmin directory = dump_dir dumpfile = tablespace. dmp tablespaces = users

Export: Release 10.2.0.1.0-Production on Wednesday, 18 August 1, 2008 16:09:19

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
Start "SYSTEM". "SYS_EXPORT_TABLESPACE_01": system/********* directory = dump_dir dumpfile = tablespace.
P tablespaces = users
Using the BLOCKS Method for estimation...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-39139: Data Pumps do not support XMLSchema objects. TABLE_DATA: "OE". "PURCHASEORDER" will be skipped ".
Total estimation using the BLOCKS method: 1.187 MB
Processing object type TABLE_EXPORT/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/RLS_POLICY
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
.. Exported "OE". "LINEITEM_TABLE" 283.5 KB 2232 rows
.. Exported "OE". "ACTION_TABLE" 14.87 KB 132 rows
.. Exported "OE". "SUBCATEGORY_REF_LIST_NESTEDTAB" 6.398 KB 21 rows
.. Exported "OE". "PRODUCT_REF_LIST_NESTEDTAB" 12.32 KB 288 rows
.. Exported "OE". "CATEGORIES_TAB" 13.12 KB 22 rows
.. Exported "SCOTT". "DEPT" 5.656 KB 4 lines
.. Exported "SCOTT". "EMP" 7.820 KB 14 rows
.. Exported "SCOTT". "SALGRADE" 5.585 KB 5 rows
.. Exported "SCOTT". "BONUS" 0 KB 0 rows
The master table "SYSTEM". "SYS_EXPORT_TABLESPACE_01" is successfully loaded/uninstalled"
**************************************** **************************************
The dump file set of SYSTEM. SYS_EXPORT_TABLESPACE_01 is:
E: \ DUMP \ TABLESPACE. DMP
The job "SYSTEM". "SYS_EXPORT_TABLESPACE_01" has been completed, but there is one error (completed at 16:09:48)

C: \>

4. Export the entire database

Expdp system/sysadmin directory = dump_dir dumpfile = full. dmp full = y

**************************************** **************************************** **************************************** *******************
Analysis of Data Pump import data in Oracle10g
**************************************** **************************************** **************************************** *******************

1. Import table

Impdp scott/tiger directory = dump_dir dumpfile = dumptab. dmp tables = dept, emp

2. Import Solution

Generally, you can only import your own solutions. If you want to import other solutions, you must have EXP_FULL_DATABASE or DBA permissions.

Impdp scott/tiger directory = dump_dir dumpfile = dumptab. dmp schemas = scott

3. Import tablespace
Impdp system/sysadmin directory = dump_dir dumpfile = tablespace. dmp tablespaces = users

4. Import the entire database

Impdp system/sysadmin directory = dump_dir dumpfile = full. dmp full = y

**************************************** **************************************** **************************************** **************
Use the Oracle10g Data Pump to move the tablespace
**************************************** **************************************** **************************************** **************
You can use impdp and expdp to move tablespaces between different databases. Before Oracle10g, only on the same operating system platform
Move the tablespace between them. After Oracle10g, you can not only move tablespaces between the same operating system platform, but also on different operating systems.
Move the tablespace between them. You can view the database view v $ transportable_platform to find which operating system platforms can be moved
Tablespace, that is:

SQL> col platform_name format a60;
SQL> select * from v $ transportable_platform order by platform_id;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
-------------------------------------------------------------------------------------
1 Solaris [tm] OE (32-bit) Big
2 Solaris [tm] OE (64-bit) Big
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
5 HP Tru64 UNIX Little
6 AIX-Based Systems (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
-------------------------------------------------------------------------------------
12 Microsoft Windows 64-bit for AMD Little
13 Linux 64-bit for AMD Little
15 HP Open VMS Little
16 Apple Mac OS Big
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big

You have selected 17 rows.

To move a tablespace, the following conditions must be met:
1. The original database and target database must have the same database character set and National Character Set
2. You cannot move the table space with the same name to the target database. If the same name appears, only alter tablespace rename can be used.
Command to change the tablespace name of the source or target database.
3. The tablespace of the SYSTEM or SYS user object cannot be moved.
4. To move the tablespace between different operating systems, you must set the initialization parameter COMPATIBLE to 10.0 or above.
Because Oracle versions earlier than 10 Gb do not support moving tablespaces between different operating systems.
5. The tablespace set to be moved must meet the self-contained requirements. You can use the TRANSPORT_SET_CHECK check process of DBMS_TTS.
Whether it is self-contained, and then query the check results in the temporary table transprot_set_violations.

The following is an example of how to move the USERS tablespace:
Step 1: Set the tablespace to a read-only tablespace:
Alter tablespace users read only;

Step 2: Use expdp to export tablespaces
Expdp user name/password directory = directory name dumpfile = export file name transport_tablespaces = tablespace name

For example:
Expdp system/sysadmin directory = dump_dir dumpfile = tablespace. dmp tablespaces = users;

Step 3: run the operating system command to copy the exported file and the data file of the transferred tablespace to the target database.

Step 4: Use IMPDP on the target database to import the exported content of the source database to the target database.
The command format is as follows:

IMP user name/password directory = directory name dumpfile = export file name transport_tablespaces = tablespace name
Transport_datafiles =; data file name'

For example:
Impdp system/sysadmin directory = dump_dir dumpfile = tablespace. dmp tablespaces = users
Transport_datafiles = D: \ oracle \ oradata \ oramgr \ user01.dbf;

Step 5: Use the following command to restore the tablespace transmitted from the original database to read/write.
Alter tablespace users read write;