- 传统导出导入与数据数据泵导出导入的区别
在10g之前,传统的导出和导入分别使用EXP工具和IMP工具,从10g开始,不仅保留了原有的EXP和IMP工具,还提供了数据泵导出导入工具EXPDP和IMPDP.使用EXPDP和IMPDP时应该注意的事项：
数据泵导出包括导出表,导出方案,导出表空间,导出数据库4种方式.
- exp命令导出数据
用exp命令导出数据时有如下常用的命令参数：
参数
|
说 明
|
USERID
|
确定执行导出实用程序的用户名和口令
|
BUFFER
|
确定导出数据时所使用的缓冲区大小，其大小用字节表示
|
FILE
|
指定导出的二进制文件名称，默认的扩展名是.dmp
|
FULL
|
指定是否导出整个数据库，只有授权DBA权限的用户才可使用此参数
|
OWNER
|
要导出的数据库用户列表
|
ROWS
|
确定是否要导出表中的数据
|
TABLES
|
按表方式导出时，指定需导出的表和分区的名称
|
TABLESPACES
|
按表空间方式导出时，指定要导出的表空间名
|
LOG
|
指定一个存放导出信息的日志文件
|
COMPRESS
|
指明在装入期间是否将表中数据压缩到一个区域中。如果在卸出数据时，指定参数COMPRESS=Y，那么装入时，就会将数据压缩到一个初始区域中。这种选择可以保持初始化区域的原始大小。缺省为“Y”。
|
1.导出用户数据
exp system/dba@oradb10 file=d:/mini_back.dmp owner=mini log=d:/exp_mini.log
--将mini用户下的数据导出
set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK set ORACLE_SID=smartkq set W=%DATE:~13,1% if '%W%'=='日' set D=0 if '%W%'=='一' set D=1 if '%W%'=='二' set D=2 if '%W%'=='三' set D=3 if '%W%'=='四' set D=4 if '%W%'=='五' set D=5 if '%W%'=='六' set D=6 exp smart/smart file=z:\smart%D%.dmp log=z:\smart%D%.log owner=smart
2.按表方式导出数据
exp mini/mini@accp tables=(emp, dept) file=d:/mini_back_tab.dmp
--将表emp、dept导出
exp scott/HondaHsu2011@dw buffer=65000 file=e:\fact_device_and_dim_date_new.dmp tables=(fact_device,dim_date_new)
exp system/dba buffer=8192 file=d:/exp_db.dmp full=y compress=n log=d:\exp_db.log
exp scott/HondaHsu2011@DW buffer=65000 file=e:\dim_date_new.dmp tables=(dim_date_new) query=\"where year_id > 2010\"
- imp命令导入数据
用imp导入数据时有如下常用的命令参数
|
参数 |
说 明 |
|
USERID |
确定执行导出实用程序的用户名和口令 |
|
BUFFER |
确定导出数据时所使用的缓冲区大小，其大小用字节表示 |
|
COMMIT |
指定是否在每个数组（其大小由BUFFER参数设置）插入后进行提交 |
|
FILE |
指定导出的二进制文件名称，默认的扩展名是.dmp |
|
FROMUSER |
指定要从导出文件中导入的用户名 |
|
TOUSER |
指定要将对象导入的用户名，FROMUSER与TOUSER可以不同 |
|
FULL |
指定是否要导入整个导出文件，当指定了Fromuser、Touser时，不能再指定FULL. |
|
OWNER |
要导出的数据库用户列表， 当指定了Fromuser、Touser时，不能再指定owner. |
|
ROWS |
确定是否要导出表中的数据 |
|
TABLES |
按表方式导出时，指定需导出的表和分区的名称,当指定了FULL是，不能再指定TABLES |
|
TABLESPACES |
按表空间方式导出时，指定要导出的表空间名 |
|
IGNORE |
导入时是否忽略遇到的创建错误，默认为N |
|
LOG |
指定一个存放导出信息的日志文件 |
imp scott/HondaHsu2011@dw file=E:\fact_device_and_dim_date_new.dmp full=y;
imp atm01/HondaHsu2011@dw file=e:/fact_device_and_dim_date_new.dmp fromuser=scott touser=atm01 tables=(fact_device,dim_date_new) log=e:/imp_tab.log
--将scott用户的表导入到atm01用户
- EXPDP命令导出数据
用expdp导出数据时有如下常用的命令参数
|
参数 |
说明 |
|
ATTACH |
连接到现有作业, 例如 ATTACH [=作业名]。 |
|
COMPRESSION |
减小有效的转储文件内容的大小,关键字值为: (METADATA_ONLY) 和 NONE。 |
|
CONTENT |
指定要卸载的数据, 其中有效关键字为: (ALL), DATA_ONLY 和 METADATA_ONLY。 |
|
DIRECTORY |
供转储文件和日志文件使用的目录对象。 |
|
DUMPFILE |
目标转储文件 (expdat.dmp) 的列表,例如 DUMPFILE=scott1.dmp, scott2.dmp,dmpdir:scott3.dm |
|
ENCRYPTION_PASSWORD |
用于创建加密列数据的口令关键字。 |
|
ESTIMATE |
计算作业估计值, 其中有效关键字为:(BLOCKS) 和 STATISTICS。 |
|
ESTIMATE_ONLY |
在不执行导出的情况下计算作业估计值。 |
|
EXCLUDE |
排除特定的对象类型, 例如EXCLUDE=TABLE:EMP。 |
|
FILESIZE |
以字节为单位指定每个转储文件的大小。 |
|
FLASHBACK_SCN |
用于将会话快照设置回以前状态的 SCN。 |
|
FLASHBACK_TIME |
用于获取最接近指定时间的 SCN 的时间。 |
|
FULL |
导出整个数据库 (N)。 |
|
HELP |
显示帮助消息 (N)。 |
|
INCLUDE |
包括特定的对象类型, 例如INCLUDE=TABLE_DATA。 |
|
JOB_NAME |
要创建的导出作业的名称。 |
|
LOGFILE |
日志文件名 (export.log)。 |
|
NETWORK_LINK |
链接到源系统的远程数据库的名称。 |
|
NOLOGFILE |
不写入日志文件 (N)。 |
|
PARALLEL |
更改当前作业的活动 worker 的数目。 |
|
PARFILE |
指定参数文件。 |
|
QUERY |
用于导出表的子集的谓词子句。 |
|
SAMPLE |
要导出的数据的百分比; |
|
SCHEMAS |
要导出的方案的列表 (登录方案)。 |
|
STATUS |
在默认值 (0) 将显示可用时的新状态的情况下,要监视的频率 (以秒计) 作业状态。 |
|
TABLES |
标识要导出的表的列表 - 只有一个方案。 |
|
TABLESPACES |
标识要导出的表空间的列表。 |
|
TRANSPORT_FULL_CHECK |
验证所有表的存储段 (N)。 |
|
TRANSPORT_TABLESPACES |
要从中卸载元数据的表空间的列表。 |
|
VERSION |
要导出的对象的版本, 其中有效关键字为:(COMPATIBLE), LATEST 或任何有效的数据库版本。 |
使用EXPDP工具时,其转储文件只能被存放在DIRECTORY对象对应的OS目录中,而不能直接指定转储文件所在的OS目录.因此,使用EXPDP工具时,必须首先建立DIRECTORY对象.并且需要为数据库用户授予使用DIRECTORY对象权限。如下示例
SQL>create directory dump_dir as 'e:\emp';
(注，此处只是逻辑上创建了目录，还需要创建物理目录)
SQL>grant read, write on directory dump_dir to scott;
expdp scott/HondaHsu2011@dw directory=dump_dir dumpfile=dept.dmp tables=dept
3.导出方案
expdp system/HondaHsu2011@dw as sysdba directory=dump_dir dumpfile=sche_system.dmp logfile=sche_system.log schemas=system
expdp scott/HondaHsu2011@dw directory=dump_dir dumpfile=users_table_space.dmp logfile=tb.log tablespaces=users
expdp system/HondaHsu2011@dw DIRECTORY=dump_dir dumpfile=full.dmp full=y
expdp scott/HondaHsu2011@dw DIRECTORY=dump_dir dumpfile=full.dmp full=y
注：提示scott用户没有相应的权限，给scott相应的权限或使用system来做全库导出。
SQL> grant exp_full_database to scott;
- impdp命令导出数据
impdp scott/HondaHsu2011@dw directory=dump_dir dumpfile=full.dmp tables=scott.dept remap_schema=scott:scott
impdp scott/HondaHsu2011@dw directory=dump_dir dumpfile=full.dmp tables=scott.test1 remap_schema=scott:system
第一种方法表示将dept表导入到SCOTT方案中,第二种方法表示将test1表导入的SYSTEM方案中.
注意,如果要将表导入到其他方案中,必须指定REMAP SCHEMA选项.
remap_schema=a:b 将数据的schema从a 转换为b
remap_tablespace=a:b 将数据的tablespace 从a 转换为b
impdp scott/HondaHsu2011@dw directory=dump_dir dumpfile=full.dmp schemas=scott
impdp scott/HondaHsu2011@dw directory=dump_dir dumpfile=schema.dmp schemas=scott remap_schema=scott:system
impdp scott/HondaHsu2011@dw directory=dump_dir dumpfile=tablespace.dmp tablespaces=users
impdp scott/HondaHsu2011@dw directory=dump_dir dumpfile=full.dmp full=y
-----查看oralce OEM CONSOLE------------- emtcl status dbconsole imp userid=SCOTT/HondaHsu2011 file=E:\ipb_grade_detail.dmp full=y; imp userid=BIDATA/HondaHsu2011 file=E:\ps_project.dmp full=y; imp userid=civilize/HondaHsu2011 file=E:\PUB_USER_REMINDER_RELATION.DMP full=y grants=n rows=y ignore=y; imp userid=lcam_develop/lcam4ddl file=E:\MM_RECEIPT.DMP full=y; imp userid=comtop/comtop@SZ_SUPERVISORY1526 file=E:\BICONTENT.dmp full=y; imp userid=BICONTENT/HondaHsu2011@orcl108 file=E:\BICONTENT.dmp full=y; imp atm01/HondaHsu2011@dw file=e:/fact_device_and_dim_date_new.dmp fromuser=scott touser=atm01 tables= (fact_device,dim_date_new) log=e:/imp_tab.log EXP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y EXP lcam_read/lcam_read@lcam_0805 BUFFER=65000 FILE=E:\MM_RECEIPT_ITEM.DMP TABLES=(MM_RECEIPT_ITEM) EXP SCOTT/HondaHsu2011@DW BUFFER=65000 FILE=E:\FACT_DEVICE.DMP TABLES=(FACT_DEVICE); EXP lcam_read/lcam_read@lcam_0805 BUFFER=65000 FILE=E:\MM_RECEIPT_ITEM.DMP owner=BICONTENT EXP BICONTENT/HondaHsu2011@DW BUFFER=65000 FILE=E:\BICONTENT.DMP owner=BICONTENT EXP lcam_0927/zcpzg1z_0927@lcam_0705 BUFFER=65000 FILE=E:\MM_RECEIPT.DMP TABLES=(MM_RECEIPT); EXP sz_730/lcam_sz730@lcam_gz730 BUFFER=65000 FILE=E:\MM_RECEIPT.DMP TABLES=(MM_RECEIPT); EXP lcam_130/zcpzg1z_130@lcam_130 BUFFER=65000 FILE=E:\PUB_USER_VISIT_DATA.DMP owner=LCAM_130 tables= (PUB_USER_VISIT_DATA) query=\"where user_visit_id > 72278"; exp lcam_130/zcpzg1z_130@lcam_130 BUFFER=65000 FILE=E:\PUB_USER_REMINDER_RELATION.DMP tables=(PUB_USER_REMINDER_RELATION) query=\"where feature_value_id>36398241\" exp lcam_130/zcpzg1z_130@lcam_130 BUFFER=65000 FILE=E:\PUB_USER_REMINDER_RELATION.DMP tables=(PUB_USER_REMINDER_RELATION) query=\"where modify_date is null\" ----------------查询端口---------------------- netstat -aon|findstr "7001" ---------------虚拟机------------------------- put E:\upload_linux\linux_11gR2_database_1of2.zip /home/oracle/common/ create spfile from pfile='D:\oracle\product\10.2.0\admin\dw\pfile\init.ora.816201111039'; EXP gz_0730/lcam_gz0730@lcam_gz730 BUFFER=65000 FILE=E:\EXPT\ imp userid=gz_0730/lcam_gz0730@lcam_gz730 file=E:\EXPT\
[oracle@atmdb impdp]$ cat 0_expdp_scheme.sh #!/bin/sh today=`date +%w` echo "----------Begin time is ATMDB_DMOLVIEW,PPMREAD,SYSBK,ATM_HIS,BDUSER,VIEW_ADMIN,ATMVIEW,JIESHUN,HRONLINE,ATM_EAI,ATMCENTER --------------" >/data/impdp/exptime0_$today.log echo $(date +%Y"."%m"."%d" "%k":"%M":"%S) >>/data/impdp/exptime0_$today.log export ORACLE_SID=atmdb export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/Apache/Apache/bin impdp lcc/lcc#123 directory=imp_dp schemas=ATMDB_DMOLVIEW,PPMREAD,SYSBK,ATM_HIS,BDUSER,VIEW_ADMIN,ATMVIEW,JIESHUN,HRONLINE,ATM_EAI,ATMCENTER logfile=0.log network_link=toatmold parallel=4 echo "----------End time is --------------" >>/data/impdp/exptime0_$today.log echo $(date +%Y"."%m"."%d" "%k":"%M":"%S) >>/data/impdp/exptime0_$today.log
[oracle@atmdb impdp]$ cat 1_0_expdp_exclude.sh #!/bin/sh today=`date +%w` echo "----------Begin time is ATM --------------" >/data/impdp/exptime1_$today.log echo $(date +%Y"."%m"."%d" "%k":"%M":"%S) >>/data/impdp/exptime1_$today.log export ORACLE_SID=atmdb export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/Apache/Apache/bin impdp lcc/lcc#123 directory=imp_dp schemas=ATM logfile=1.log network_link=toatmold parallel=4 exclude=TABLE:\"in\(\'CHKINOUT_TEMP_HIS\',\'CHKINOUT_HIS\',\'AC_KQ_DAYDATA_STATIC_HIS\',\'KQDAYDATA_HIS\',\'ATM_KQ_DAY_SALARY_STATIC_HIS\',\'KQDAYAPPLYDETAIL_HIS\',\'CHECKON_ACCOUNT_ALIGNMENT_HIS\',\'AC_KQ_INVERSE_WORK_HIS\',\'AS_LOG_PUBLIC_OPERATION_HIS\',\'ATM_REGISTER_TRIP_DTL_BAK\',\'CHECKON_ACCOUNT_ALIGNMENT_BAK\',\'PS_SBOM_CUSTOMER_TEMP\'\)\" echo "----------End time is --------------" >>/data/impdp/exptime1_$today.log echo $(date +%Y"."%m"."%d" "%k":"%M":"%S) >>/data/impdp/exptime1_$today.log
