1.oracle数据泵是oracle提供的更新、更快、更灵活的数据导入、导出工具。
2.创建DIRECTORY
在使用数据泵之前,必须创建DIRECTORY并获得访问这么对象的权限。
例如:
SQL> conn / as sysdba
Connected.
SQL> CREATE DIRECTORY MY_DIR AS '/U01/oracle';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY MY_DIR TO TEST;
Grant succeeded.
现在,用户TEST就可以在目录MY_DIR上执行导入、导出了。
3.表的导出、导入
用TABLES参数来设置需要导入、导出的表,用CONTENT参数设置导入,导出的内容:ALL(包括表元数据和表数据), DATA_ONLY(仅包括数据),
expdp Test/oracle DIRECTORY=MY_DIR TABLES=MYOBJECT,OBJ JOB_NAME='Expdp_TEST_JOB' DUMPFILE=expTABLES.dmp
SQL> conn Test/oracle
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
OBJ
MYOBJECT
SQL> drop table OBJ PURGE;
Table dropped.
SQL> drop table MYOBJECT PURGE;
Table dropped.
SQL> select table_name from user_tables;
no rows selected
impdp Test/oracle DIRECTORY=MY_DIR TABLES=MYOBJECT,OBJ JOB_NAME='Expdp_TEST_JOB' DUMPFILE=expTABLES.dmp
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
OBJ
MYOBJECT
可见,表及其数据已经导入到数据库中了。
4.模式导入、导出
用SCHEMAS 参数设置要导入、导出的模式及其数据
例如:
expdp system/oracle schemas=TEST directory=MY_DIR dumpfile=TEST.dmp
SQL> conn / as sysdba
Connected.
SQL> drop user TEST cascade;
User dropped.
[oracle@Linux ~]$ impdp system/oracle schemas=TEST directory=MY_DIR dumpfile=TEST.dmp
Import: Release 11.2.0.1.0 - Production on Thu Jul 7 14:24:43 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."MYOBJECT"
. . imported "TEST"."OBJ"
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 14:24:58
SQL> select username from dba_users;
USERNAME
------------------------------
OUTLN
SYS
SYSTEM
TEST
ADMIN
TEST_B
TEST_A
APPQOSSYS
DBSNMP
DIP
ORACLE_OCM
11 rows selected.
SQL> conn test/oracle
Connected.
5.数据库导入、导出
用FULL参数标示需要数据库全导入、导出。
expdp system/oracle full=Y directory=MY_DIR dumpfile=DBFull.dmp logfile=expdpDBfull.log
SQL> conn / as sysdba
Connected.
SQL> drop user TEST cascade;
User dropped.
SQL> drop user TEST_A cascade;
User dropped.
SQL> drop user TEST_B cascade;
User dropped.
impdp system/oracle full=Y directory=MY_DIR dumpfile=DBFull.dmp logfile=expdpDBFull.log
SQL> conn / as sysdba
Connected.
SQL> select username from dba_users;
USERNAME
------------------------------
OUTLN
SYS
SYSTEM
TEST
ADMIN
TEST_B
TEST_A
APPQOSSYS
DBSNMP
DIP
ORACLE_OCM
11 rows selected.
6.INCLUDE 和EXLUDE
INCLUDE 和EXLUDE参数用户限定导入、导出特定的对象。INCLUDE参数设于设定导入导出的对象,EXCLUDE参数用于设定除特定对象为都要导入导出的对象。这两个参数是相斥的。
INCLUDE=object_type[:name_clause] [, ...]
EXCLUDE=object_type[:name_clause] [, ...]
对象类型包括:TABLE,VIEW,PACKAGE
name_clause:sql表达式,用于过滤对象,如IN ('DEPT','EMP'), LIKE ('D%'), regexp_like(table_name,’(*JOB*|*DEPAR*|*EMP*)’) ,='EMP' 等等。
例如:
expdp TEST/oracle DIRECTORY=MY_DIR DUMPFILE=TEST2.DMP schemas=TEST include=TABLE:\"=\'OBJ\'\"
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
OBJ
MYOBJECT
SQL> drop table obj purge;
Table dropped.
impdp TEST/oracle DIRECTORY=MY_DIR DUMPFILE=TEST2.DMP schemas=TEST include=TABLE:\"=\'OBJ\'\"
SQL>
TABLE_NAME
------------------------------
OBJ
MYOBJECT
7.网络导入导出
NETWORK_LIN参数用于标示一个作为数据导入导出源的数据库链。
用户必须有CREATE DATABASE LINK 的权限。本地用户和远程用户都需要授予EXP_FULL_DATABASE 角色。
SQL> GRANT CREATE DATABASE LINK TO TEST;
SQL> CREATE DATABASE LINK tlink connect to test identified by oracle using 'Instinct';
Database link created.
expdp Test/oracle tables=TEST.OBJ network_link=tlink directory=MY_DIR dumpfile=obj.dmp logfile=expdpobj.log
impdp Test/oracle tables=TEST.OBJ network_link=tlink directory=MY_DIR dumpfile=obj.dmp logfile=expdpobj.log
8.杂项信息
dmp文件和log文件存储在服务器上,而不是存储在客户端。
在导出导入执行的过程中,取消客户端进程不能停止数据泵任务,在数据泵任务执行的过程中,输入ctrl+C将停止客户端输出,显示命令行提示符。输入status来监视任务状态。
Export>status
9.PARALLEL
PARALLEL 参数用于并行导入导出。
例如:
expdp Test/oracle schemas=TEST directory=MY_DIR parallel=4 dumpfile=Test3dmp logfile=expdpTest.log
SQL> select
OWNER_NAME JOB_NAME
---------- -------------------------------- --------- ------------ ----------
TEST
10.Data Pump API
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
BEGIN
END;
/
11.外部表
可以用 ORACLE_DATAPUMP 来把数据导出导入
CREATE TABLE OBJ_XT
The data can then be queried using the following.
SELECT * FROM emp_xt;
也可以使用下列方式创建外部表
DROP TABLE OBJ_XT;
CREATE TABLE OBJ_XT (
12.help
expdp
expdp help=y
Export: Release 10.1.0.2.0 - Production on Tuesday, 23 March, 2004 8:33
Copyright (c) 2003, Oracle.
The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:
USERID must be the first parameter on the command line.
Keyword
------------------------------------------------------------------------------
ATTACH
CONTENT
DIRECTORY
DUMPFILE
ESTIMATE
ESTIMATE_ONLY
EXCLUDE
FILESIZE
FLASHBACK_SCN
FLASHBACK_TIME
FULL
HELP
INCLUDE
JOB_NAME
LOGFILE
NETWORK_LINK
NOLOGFILE
PARALLEL
PARFILE
QUERY
SCHEMAS
STATUS
TABLES
TABLESPACES
TRANSPORT_FULL_CHECK
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded.
VERSION
The following commands are valid while in interactive mode.
Note: abbreviations are allowed
Command
------------------------------------------------------------------------------
ADD_FILE
CONTINUE_CLIENT
EXIT_CLIENT
HELP
KILL_JOB
PARALLEL
START_JOB
STATUS
STOP_JOB
impdp
impdp help=y
Import: Release 10.1.0.2.0 - Production on Saturday, 11 September, 2004 17:22
Copyright (c) 2003, Oracle.
The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:
USERID must be the first parameter on the command line.
Keyword
------------------------------------------------------------------------------
ATTACH
CONTENT
DIRECTORY
DUMPFILE
ESTIMATE
EXCLUDE
FLASHBACK_SCN
FLASHBACK_TIME
FULL
HELP
INCLUDE
JOB_NAME
LOGFILE
NETWORK_LINK
NOLOGFILE
PARALLEL
PARFILE
QUERY
REMAP_DATAFILE
REMAP_SCHEMA
REMAP_TABLESPACE
REUSE_DATAFILES
SCHEMAS
SKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state.
SQLFILE
STATUS
STREAMS_CONFIGURATION Enable the loading of Streams metadata
TABLE_EXISTS_ACTION
TABLES
TABLESPACES
TRANSFORM
TRANSPORT_DATAFILES
TRANSPORT_FULL_CHECK
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be loaded.
VERSION
The following commands are valid while in interactive mode.
Note: abbreviations are allowed
Command
------------------------------------------------------------------------------
CONTINUE_CLIENT
EXIT_CLIENT
HELP
KILL_JOB
PARALLEL
START_JOB
STATUS
STOP_JOB