Go Use of Oracle Data pump

Exporting data using a data pump

1. Connect to Oracle Database
Sql> Conn/as SYSDBA
is connected.

2. Create an Operation directory
Sql> Create directory Dump_dir as ' E:\dump ';
Note You will also need to create this physical directory on your hard disk using the operating system command.

The catalog has been created.

3. Create an export file directory using the following command
HR User Actions Dump_dir permissions for the directory,

Sql>grant read,write on directory My_dir to Scott;

Authorization is successful.

4. Export Data Using command EXPDP (can be exported according to user mode, by table, by table space, and by Whole library)

C:\>EXPDP Scott/tiger Directory=dump_dir dumpfile=20090517scotttab.dmp tables=dept,emp
s=

Export:release 10.2.0.1.0-production on Wednesday, 18 June, 2008 15:49:00

Copyright (c) 2003, 2005, 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

Estimating using the BLOCKS method ...

Working with Object Types Table_export/table/table_data

Total estimate using the BLOCKS method: KB

Working with Object Types table_export/table/table

Working with Object Types Table_export/table/index/index

Working with Object Types Table_export/table/constraint/constraint

Working with Object Types Table_export/table/constraint/ref_constraint

. . "SCOTT" was exported. " DEPT "5.656 KB 4 rows

. . "SCOTT" was exported. " EMP "7.820 KB 14 Line

The main table "SCOTT" has been successfully loaded/uninstalled. " Sys_export_table_01 "

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

SCOTT. The sys_export_table_01 dump file set is:

E:\DUMP\20080601.DMP

The job "SCOTT". Sys_export_table_01 "was successfully completed at 15:49:22

C:\>

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

Common errors

If you do not establish a physical file on the specified hard disk, the following error message appears:

The workaround is to create the specified physical file directory on the hard disk now, and then execute the EXPDP program.

Create directory Dump_dir as ' E:\dump ' in Oracle;

The command only logically creates a connection between a directory that is recognized by Oracle and a physical directory on the hard disk,

Does not really create a physical directory on the hard disk.

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

C:\>EXPDP Scott/tiger Directory=dump_dir dumpfile=20080601.dmp tables=dept,emp

Export:release 10.2.0.1.0-production on Wednesday, 18 June, 2008 15:46:19

Copyright (c) 2003, 2005, 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 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=my_dir dumpfile=20090517scotttab.dmp tables=employee

Import:release 10.2.0.1.0-production on Wednesday, 18 June, 2008 15:56:11

Copyright (c) 2003, 2005, 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 main table "SCOTT" has been successfully loaded/uninstalled. " Sys_import_table_01 "

Start "SCOTT". " Sys_import_table_01 ": scott/******** directory=dump_dir dumpfile=20080601.dmp TABLE

Dept,emp

Working with Object Types table_export/table/table

Working with Object Types Table_export/table/table_data

. . "SCOTT" was imported. " DEPT "5.656 KB 4 rows

. . "SCOTT" was imported. " EMP "7.820 KB 14 Line

Working with Object Types Table_export/table/index/index

Working with Object Types Table_export/table/constraint/constraint

Working with Object Types Table_export/table/constraint/ref_constraint

The job "SCOTT". Sys_import_table_01 "was successfully completed at 15:56:20

C:\>

Looking at the results, we found that the deleted data sheets have been returned:

Sql> set linesize 150;

Sql> select * from Scott.emp;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

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

7369 SMITH Clerk 7902 1 July-December-80 800 20

7499 ALLEN salesman 7698 20月-February-81 1600 300 30

7521 WARD salesman 7698 2 February-February-81 1250 500 30

7566 JONES MANAGER 7839 February-April-81 2975 20

7654 MARTIN salesman 7698 2 August-September-81 1250 1400 30

7698 BLAKE MANAGER 7839 January-May-81 2850 30

7782 CLARK MANAGER 7839 September-June-81 2450 10

7788 SCOTT ANALYST 7566 1 September-April-87 3000 20

7839 KING President 1 July-November-81 5000 10

7844 TURNER salesman 7698 August-September-81 1500 0 30

7876 ADAMS Clerk 7788 2 March-May-87 1100 20

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

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

7900 JAMES Clerk 7698 March-December-81 950 30

7902 FORD ANALYST 7566 March-December-81 3000 20

7934 MILLER Clerk 7782 2 March-January-82 1300 10

14 rows have been selected.

Sql> select * from Scott.dept;

DEPTNO dname LOC

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

Ten ACCOUNTING NEW YORK

DALLAS

SALES CHICAGO

OPERATIONS BOSTON

Sql>

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

Analysis of data pump export data in oracle10g

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

The data pump in oracle10g can be used only on the server side, not the client, and it exports tables, scenarios, tablespaces, and the entire database.

1. Export table

C:\>EXPDP Scott/tiger Directory=dump_dir dumpfile=20080601.dmp tables=dept,emp

2. Export scheme

The export scenario exports all the database objects and database tables for the scenario to a file.

C:\>EXPDP Scott/tiger Directory=my_dir dumpfile=20090517scottschema.dmp Schemas=scott

Export:release 10.2.0.1.0-production on Wednesday, 18 June, 2008 16:04:16

Copyright (c) 2003, 2005, 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

Estimating using the BLOCKS method ...

Working with Object Types Schema_export/table/table_data

Total estimate using the BLOCKS method: 448 KB

Working with Object Types Schema_export/pre_schema/procact_schema

Working with Object Types Schema_export/sequence/sequence

Working with Object Types schema_export/table/table

Working with Object Types Schema_export/table/grant/owner_grant/object_grant

Working with Object Types Schema_export/table/index/index

Working with Object Types Schema_export/table/constraint/constraint

Working with Object Types Schema_export/table/index/statistics/index_statistics

Working with Object Types Schema_export/table/comment

Working with Object Types Schema_export/procedure/procedure

Working with Object Types Schema_export/procedure/alter_procedure

Working with Object Types Schema_export/view/view

Working with Object Types Schema_export/table/constraint/ref_constraint

Working with Object Types Schema_export/table/trigger

Working with Object Types Schema_export/table/statistics/table_statistics

. . "HR" was exported. " Countries "6.085 KB 25 rows

. . "HR" was exported. " Departments "6.632 KB 27 rows

. . "HR" was exported. " EMPLOYEES "15.76 KB 107 rows

. . "HR" was exported. " JOBS "6.609 KB 19 rows

. . "HR" was exported. " Job_history "6.585 KB 10 rows

. . "HR" was exported. " LOCATIONS "7.710 KB 23 rows

. . "HR" was exported. " Regions "5.289 KB 4 rows

The main table "HR" has been successfully loaded/uninstalled. " Sys_export_schema_01 "

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

HR. The sys_export_schema_01 dump file set is:

E:\DUMP\20080601SCHEMA. DMP

Job "HR". " Sys_export_schema_01 "was successfully completed at 16:04:50

C:\>

If the HR user is not granted permission to manipulate the Dump_dir directory, the following error message appears:

C:\>EXPDP hr/hr Directory=dump_dir dumpfile=20080601schema.dmp schemas=hr

Export:release 10.2.0.1.0-production on Wednesday, 18 June, 2008 16:02:55

Copyright (c) 2003, 2005, 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 log file.

ORA-39087: Directory name Dump_dir invalid

3. Export Table Space

EXPDP system/sysadmin Directory=dump_dir dumpfile=tablespace.dmp tablespaces=users

C:\>EXPDP system/sysadmin directory=my_dir dumpfile=tablespace.dmp Tablespaces=scott;

Export:release 10.2.0.1.0-production on Wednesday, 18 June, 2008 16:09:19

Copyright (c) 2003, 2005, 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

Estimating using the BLOCKS method ...

Working with Object Types Table_export/table/table_data

ORA-39139: The data pump does not support XmlSchema objects. Will skip Table_data: "OE". " PurchaseOrder ".

Total estimate using the BLOCKS method: 1.187 MB

Working with Object Types table_export/table/table

Working with Object Types Table_export/table/index/index

Working with Object Types Table_export/table/constraint/constraint

Working with Object Types Table_export/table/index/statistics/index_statistics

Working with Object Types Table_export/table/rls_policy

Working with Object Types Table_export/table/constraint/ref_constraint

Working with Object Types Table_export/table/trigger

. . "OE" was exported. " Lineitem_table "283.5 KB 2232 rows

. . "OE" was exported. " Action_table "14.87 KB 132 rows

. . "OE" was exported. " Subcategory_ref_list_nestedtab "6.398 KB 21 rows

. . "OE" was exported. " Product_ref_list_nestedtab "12.32 KB 288 rows

. . "OE" was exported. " Categories_tab "13.12 KB 22 rows

. . "SCOTT" was exported. " DEPT "5.656 KB 4 rows

. . "SCOTT" was exported. " EMP "7.820 KB 14 Line

. . "SCOTT" was exported. " Salgrade "5.585 KB 5 rows

. . "SCOTT" was exported. " BONUS "0 KB 0 rows

The main Table "SYSTEM" was successfully loaded/uninstalled. " Sys_export_tablespace_01 "

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

SYSTEM. The sys_export_tablespace_01 dump file set is:

E:\DUMP\TABLESPACE. DMP

Job "SYSTEM". " Sys_export_tablespace_01 "has been completed, but there are 1 errors (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 Scheme

You can generally import only your own scenarios, and if you want to import other scenarios, you must have exp_full_database or DBA authority.

IMPDP Scott/tiger Directory=dump_dir dumpfile=dumptab.dmp Schemas=scott

3. Import Table Space

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 oracle10g data pump to move table space

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

Using IMPDP and EXPDP, you can move table spaces between different databases. Before oracle10g, only on the same operating system platform

Move the table space between. After oracle10g, you can not only move tablespaces between the same operating system platform, but also on different operating systems

Move the table space between. Users can v$transportable_platform query to which operating system platforms can be moved by viewing the database view

Table spaces, namely:

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

Ten Linux IA (32-bit) Little

Linux IA (64-bit) Little

platform_id Platform_name Endian_format

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

Microsoft Windows 64-bit for AMD Little

Linux 64-bit for AMD Little

HP Open VMS Little

Apple Mac OS Big

Operating Solaris System (x86) Little

IBM Power Based Linux Big

17 rows have been selected.

Tasks frequently performed by DBAs

To move a tablespace, the following conditions must be met:

1, the original database and the target database must have the same database character set and the national character set

2. You cannot move to a target database that has a tablespace with the same name. If the same name appears, you can only use alter tablespace RENAME

command to change the tablespace name of the source database or target database.

3. You cannot move the table space where the system or sys user object resides.

4. To move the tablespace between different operating systems, the initialization parameter compatible parameter must be set to more than 10.0.

Because the previous version of oracle10g does not support moving table spaces between different operating systems.

5. The set of tablespaces to be moved must be self-contained and can be checked using Dbms_tts's procedure Transport_set_check

Whether it is self-contained, and then queries the check results within the temporary table transprot_set_violations.

Here are the steps to move the table space to move the users table space as an example:

Step one: Set the tablespace as read-only table space:

Alter TABLESPACE users read only;

Step two: Export table spaces using EXPDP

EXPDP username/password directory= directory name dumpfile= export file name tablespaces= table space name

For example:

EXPDP system/sysadmin directory=dump_dir dumpfile=tablespace.dmp tablespaces=users;

Step three: Use the operating system command to copy the exported file and the data files of the transport table space to the target database.

Fourth step: Use IMPDP on the target database to import the exported contents of the source database into the target database, IMPDP import the table space

The command format is as follows:

IMP User name/password directory= directory name dumpfile= export file name tablespaces= tablespace name

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;

Fifth step: Use the command to restore the original database's transfer table space to read and write

Alter tablespace users read write;

Go Use of Oracle Data pump