data pump（数据泵）
先给出oracle给出的一个定义：
“Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another.“
听起来很牛的玩意，理论暂不去考虑，先来几个操作过过瘾；
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
查看一下预定义的目录情况
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------- ------------------------------ -----------------------------------------------------------------
SYS SUBDIR C:\oracle\product\10.2.0\db_1\demo\schema\order_entry\/2002/Sep
SYS XMLDIR C:\oracle\product\10.2.0\db_1\demo\schema\order_entry\
SYS MEDIA_DIR C:\oracle\product\10.2.0\db_1\demo\schema\product_media\
SYS LOG_FILE_DIR C:\oracle\product\10.2.0\db_1\demo\schema\log\
SYS WORK_DIR C:\ADE\aime_vista_ship\oracle/work
SYS DATA_FILE_DIR C:\oracle\product\10.2.0\db_1\demo\schema\sales_history\
SYS DATA_PUMP_DIR C:\oracle\product\10.2.0\admin\zmmctest\dpdump\
SYS ADMIN_DIR C:\ADE\aime_vista_ship\oracle/md/admin
已选择8行
在C盘下新建数据泵导入导出数据目录和日志目录etl、etl_log
之后用PL/SQL 在
数据库中注册刚才创建数据泵导入导出数据目录和日志目录
SQL>create directory data_dir as 'c:\etl'
目录已创建
SQL>create directory log_dir as 'c:\etl_log'
目录已创建
随后就把这两个目录的读写权限授予用户A
SQL>grant read,write on directory data_dir to orabw
授权成功
SQL>grant read,write on directory log_dir to orabw
授权成功
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------- ------------------------------ -----------------------------------------------------------------
SYS LOG_DIR c:\etl_log
SYS DATA_DIR c:\etl
SYS SUBDIR C:\oracle\product\10.2.0\db_1\demo\schema\order_entry\/2002/Sep
SYS XMLDIR C:\oracle\product\10.2.0\db_1\demo\schema\order_entry\
SYS MEDIA_DIR C:\oracle\product\10.2.0\db_1\demo\schema\product_media\
SYS LOG_FILE_DIR C:\oracle\product\10.2.0\db_1\demo\schema\log\
SYS WORK_DIR C:\ADE\aime_vista_ship\oracle/work
SYS DATA_FILE_DIR C:\oracle\product\10.2.0\db_1\demo\schema\sales_history\
SYS DATA_PUMP_DIR C:\oracle\product\10.2.0\admin\zmmctest\dpdump\
SYS ADMIN_DIR C:\ADE\aime_vista_ship\oracle/md/admin
已选择10行
再在 c:\etl 里创建数据泵导出文件并编辑,文件名称为expdp01.txt ,内容如下：
DIRECTORY=DATA_DIR
USERID=ORABW/ORABW
TABLES=PRODUCTS
CONTENT=ALL
DUMPFILE=expdat.DMP
LOGFILE=LOG_DIR:ETL_LOG.TXT
执行导出
C:\Documents and Settings\Administrator>expdp parfile=c:\etl\expdp01.txt
Export: Release 10.2.0.3.0 - Production on 星期二, 13 8月, 2013 18: