--------------------------------------------------------------在cmd中执行--------------------------------
#71 data pump export导出数据
1:由于data pump 需要为将要创建和读取的数据文件和日志文件创建目录,可以使用create directory 命令创建指向将要使用
的外部目录。要访问data pump 的用户必须拥有对于目录的read和write权限。
sqlplus /nolog;
conn /as sysdba;
create directory dtpump_file_dir as 'e:\dtpump'; (需要DBA权限)
grant read,write on directory dtpump_file_dir to anzhen;
grant datapump_imp_full_database to anzhen; #给anzhen用户导入权限
grant datapump_exp_full_database to anzhen; #给anzhen用户导出权限
2:使用 pump export 命令:
expdp anzhen/anzhen #用户名和密码
directory =dtpump_file_dir #数据存的目录
dumpfile=t_bss_cust_info.dmp #dump文件名
nologfile=y #不产生日志文件
reuse_dumpfiles=yes #覆盖原来的同名数据文件
include=table:" in ('t_bss_cust_info','t_student') " #导出的表t_bss_cust_info,t_student
query=anzhen.t_bss_cust_info:"where user_code ='JYPNN0027'" #导出表的具体条件
reuse_dumpfiles=yes ; #覆盖原来的同名数据文件
备注:
1、directory=dir_dp必须放在前面,如果将其放置最后,会提示 ORA-39002: 操作无效
ORA-39070: 无法打开日志文件。
ORA-39087: 目录名 DATA_PUMP_DIR; 无效
2、在导出过程中,DATA DUMP 创建并使用了一个名为SYS_EXPORT_SCHEMA_01的对象,此对象就是DATA DUMP导出过程中所用的JOB名字,如果在执
行这个命令时如果没有指定导出的JOB名字那么就会产生一个默认的JOB名字,如果在导出过程中指定JOB名字就为以指定名字出现
如下改成:
expdp lttfm/lttfm@fgisdb schemas=lttfm directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log,job_name=my_job1;
3、导出语句后面不要有分号,否则如上的导出语句中的job表名为'my_job1;’,而不是my_job1。因此导致
expdp lttfm/lttfm attach=lttfm.my_job1执行该命令时一直提示找不到job表
#所有的exp的参数汇总
expdp anzhen/123456@orcl #用户名为chenanzhen,密码为123456,使用的数据库为orcl
attach==[schema_name.]job_name | job_name #Schema_name用于指定方案名,job_name用于指定导出作业名
cluster=y | n #是否利用集群资源
compression=all | data_only | metadata_only | none #指定压缩的内容
content =all | data_only |metadata_only #指定内容
data_options=xml_clobs #数据层选项标记
directory=directory_object #指定导出的数据文件存放的目录
dumpfile=scott1.dmp,dumpdir2:scott2.dmp,dumpdir3:scott3.dmp #指定目录下的数据文件和数据文件
encryption all | data_only |encryption_columns_only | none #指定加密数据
encryption_algorithm=aes192 | aes256 #指定加密方式
encryption_mode= dual | password | transparent #指定生成加密秘钥的方法
encryption_password=123456 #指定创建加密数据的口令秘钥
estimate=blocks | statistics #计算作业估计值
exclude=TABLE:\"LIKE \'B%\'\" #指定类型
filesize=1024k #以字节为单位的文件大小
flashback_scn=138246 #指定重置会话快照的SCN值
flashback_time='2016-03-23' #指定重置会话快照的时间
full=y | n #是否导出整个数据库
help=y | n #是否需要帮助
include=TABLE:\"NOT LIKE \'B$%\'\" #指定特定的对象类型
job_name=job_name #导出作业的名称
logfile=export11.log #导出的日志文件
network_link=database_link_name #指定远程连接的db_link名称
nologfile=y | n #是否入日志文件
parfile=parfile_name #指定参数文件名称
query=employees:"where department_id>10" #指定查询的条件
reuse_dumpfiles=y |n #指定是否覆盖转存文件
sample=40% #指定导出数据的百百分比
schemas=schemas_name #指定schemas
service_name=service_name #指定约束rac资源活动的服务器名
status=0 #监视状态的频率
tables=hr.employees,sh.sales #指定表
tablespace=tablespace_name #指定表空间名称
transport_full_check=always | newer #指定传输方式
version=lastest | compatible ; #指定数据库的版本
数据泵导出的分类:
A:参数文件接口类(就是把要设置的很多参数命令包装成一个参数文件,这个文件可以重用。)
vi exp_file.par #编辑一个参数文件的内容
tables=employees,jobs
dumpfile=exp_hr.dmp
reuse_dumpfiles=yes
logfile=export_hr.log
#使用参数文件调用data pump export
expdp anzhen/anzhen
parfile=/home/oracle/expfile.par;
备注:
1、directory=dir_dp必须放在前面,如果将其放置最后,会提示 ORA-39002: 操作无效
ORA-39070: 无法打开日志文件。
ORA-39087: 目录名 DATA_PUMP_DIR; 无效
B:命令行接口
C:交互式命令行接口:指的是作业在执行的时候出现异常,需要对作业进行干预和监测,这时可以按ctrl+c进入交互式的界
面,expdp attach jobname命令进入。
expdp anzhen/anzhen #用户名和密码
directory =dtpump_file_dir #数据存的目录
dumpfile=dtpump_file_dir:t_bss_cust_info.dmp #dtpump_file_dir目录下的dump文件名
nologfile=yes #不产生日志文件
reuse_dumpfiles=yes #覆盖原来的同名数据文件
full=yes #完全导出模式
kill_job=job_name1 #指定杀死的job名称
help=y #需要帮助文档
add_file=file_name1 #添加文件
start_job=skip_current #启动作业
stop_job=immediate; #停止作业
如果程序运行中出现错误和异常,可以使用expdp attach jobname命令,exit_client命令是退出客户端
,continue_client命令是查看生产的日志项,stop_job命令是停止运行,start_job命令是重新运行。
五种导出方式:
create directory dtpump_file_dir as 'e:\dtpump'; (需要DBA权限)
grant read,write on directory dtpump_file_dir to anzhen;
(1)完全导出模式(full export mode)
这个数据库(包括元数据)都会导出,必须具有datapump_exp_full_database权限。
expdp anzhen/anzhen #用户名和密码
directory =dtpump_file_dir #数据存的目录
dumpfile=dtpump_file_dir:t_bss_cust_info.dmp #dtpump_file_dir目录下的dump文件名
nologfile=yes #不产生日志文件
reuse_dumpfiles=yes #覆盖原来的同名数据文件
full=yes ; #完全导出模式
(2)schema导出模式(schema export mode)
如果用户没有datapump_exp_full_database权限,只能导出自己的schema。有的话可以导出多个schema
这个数据库(包括元数据)都会导出,必须具有datapump_exp_full_database权限。
grant datapump_exp_full_database to anzhen; #datapump_exp_full_database权限
expdp anzhen/anzhen #用户名和密码
schemas =lida,wangqi,anzhen #导出lida,wangqi,anzhen这3个schema。
directory =dtpump_file_dir #数据存的目录
dumpfile=dtpump_file_dir:t_bss_cust_info.dmp #dtpump_file_dir目录下的dump文件名
nologfile=yes #不产生日志文件
reuse_dumpfiles=yes ; #覆盖原来的同名数据文件
(3)表导出模式(table export mode)
如果用户没有datapump_exp_full_database权限,只能导出自己的表。有的话可以导出其他用户的表
grant datapump_exp_full_database to anzhen; #datapump_exp_full_database权限
expdp system/system #用户名和密码
tables=anzhen.employees,wangqi.student_t #导出employees,student_t这2个表。
transportable=always #仅有元数据会被导出
directory =dtpump_file_dir #数据存的目录
dumpfile=dtpump_file_dir:t_bss_cust_info.dmp #dtpump_file_dir目录下的dump文件名
reuse_dumpfiles=no ; #覆盖原来的同名数据文件
(4)表空间导出模式(tablespace export mode)
如果用户没有datapump_exp_full_database权限,只能导出自己的表空间。有的话可以导出其他用户的表空间
alter tablespace users read write; #对于表空间有读写权限
grant datapump_exp_full_database to anzhen; #datapump_exp_full_database权限
expdp system/system #用户名和密码
tablespaces=uses,temp #导出uses,temp 这2个表空间。
transportable=always #仅有元数据会被导出
directory =dtpump_file_dir #数据存的目录
dumpfile=dtpump_file_dir:t_bss_cust_info.dmp #dtpump_file_dir目录下的dump文件名
logfile=dtpump_file_dir:dump_user.log #日志文件dump_user.log放在dtpump_file_dir目录下
reuse_dumpfiles=no #覆盖原来的同名数据文件
job_name=exp_zeng; #设置job的名称
(5)传输表空间导出模式(transportable tablespace export mode)
指的是在指定的表空间的表和其依赖的对象会被导出。
如果用户没有datapump_exp_full_database权限,只能导出自己的传输表空间。有的话可以导出其他用户的传输表空间
alter tablespace users read write; #对于表空间有读写权限
grant datapump_exp_full_database to anzhen; #datapump_exp_full_database权限
impdp system/system #用户名和密码
transport_tablespace=users #导出user这1个表空间。
transportable=always #仅有元数据会被导出
directory =dtpump_file_dir #数据存的目录
dumpfile=dtpump_file_dir:t_bss_cust_info.dmp #dtpump_file_dir目录下的dump文件名
例子:
使用exclude,include导出数据
1、Include导出用户中指定类型的指定对象
--仅导出lttfm用户下以B开头的所有表,包含与表相关的索引,备注等。不包含过程等其它对象类型:
expdp lttfm/lttfm@fgisdb dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job include=TABLE:\"LIKE \'B%\'\"
--导出lttfm用户下排除B$开头的所有表:
expdp lttfm/lttfm@fgisdb schemas=lttfm dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job include=TABLE:\"NOT LIKE \'B$%\'\"
--仅导出lttfm用户下的所有存储过程:
expdp lttfm/lttfm@fgisdb schemas=lttfm dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job include=PROCEDURE;
2、Exclude导出用户中指定类型的指定对象
--导出lttfm用户下除TABLE类型以外的所有对象,如果表不导出那么与表相关的索引,约束等与表有关联的对象类型也不会被导出:
expdp lttfm/lttfm@fgisdb schemas=lttfm dumpfile=exclude_1.dmp logfile=exclude_1.log directory=dir_dp job_name=my_job exclude=TABLE;
--导出lttfm用户下排除B$开头的所有表:
expdp lttfm/lttfm@fgisdb dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job exclude=TABLE:\"LIKE\'b$%\'\";
--导出lttfm用户下的所有对象,但是对于表类型只导出以b$开头的表:
expdp lttfm/lttfm@fgisdb dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job exclude=TABLE:\"NOT LIKE \'b$%\'\";
----------------------------------------------------------cmd中执行---------------------------------------------------------------
#72 data pump import 导入数据
#所有的imp的参数汇总
impdp anzhen/123456@orcl #用户名为chenanzhen,密码为123456,使用的数据库为orcl
attach==[schema_name.]job_name | job_name #Schema_name用于指定方案名,job_name用于指定导出作业名
cluster=y | n #是否利用集群资源
content =all | data_only |metadata_only #指定内容
data_options=skip_constraint_erro #数据层选项标记
directory=directory_object #指定导出的数据文件,日志文件,sql文件存放的目录
dumpfile=scott1.dmp,dumpdir2:scott2.dmp,dumpdir3:scott3.dmp #指定目录下的数据文件和数据文件
encryption all | data_only |encryption_columns_only | none #指定加密数据
encryption_algorithm=aes192 | aes256 #指定加密方式
encryption_mode= dual | password | transparent #指定生成加密秘钥的方法
encryption_password=123456 #指定创建加密数据的口令秘钥
estimate=blocks | statistics #计算作业估计值
exclude=TABLE:\"LIKE \'B%\'\" #排除特定的对象类型
filesize=1024k #以字节为单位的文件大小
flashback_scn=138246 #指定重置会话快照的SCN值
flashback_time='2016-03-23' #指定重置会话快照的时间
full=y | n #是否导出整个数据库
help=y | n #是否需要帮助
include=PROCEDURE #指定特定的对象类型
job_name=job_name #导出作业的名称
logfile=export11.log #导出的日志文件
network_link=database_link_name #指定远程连接的db_link名称
nologfile=y | n #是否入日志文件
parfile=parfile_name #指定参数文件名称
query=employees:"where department_id>10" #指定查询的条件
partition_options=departition | merge | none #指定分区
remap_schema=schema_name #将一个方案的对象映射到另外一个方案
remap_table=hr.employees:emps #将表名重新映射到另外一张表
remap_tablespace=tablespace_name #将表空间映射到另外表空间
schemas=schemas_name #指定schemas
service_name=service_name #指定约束rac资源活动的服务器名
sqlfile=file_dir #指定sql
status=0 #指定状态
table_exists_action=append | replace | skip | truncate #导入对象时执行的操作
tables=hr.employees,sh.sales #指定表
tablespace=tablespace_name #指定表空间名称
transportable=always | never #用于选择可传输数据移动的选项
transport_full_check=always | newer #指定传输方式
version=lastest | compatible ; #指定数据库的版本
IMPDP命令行选项与EXPDP有很多相同的,不同的有:
1、REMAP_DATAFILE
该选项用于将源数据文件名转变为目标数据文件名,在不同平台之间搬移表空间时可能需要该选项.
REMAP_DATAFIEL=source_datafie:target_datafile
2、REMAP_SCHEMA
该选项用于将源方案的所有对象装载到目标方案中.
REMAP_SCHEMA=source_schema:target_schema
3、REMAP_TABLESPACE
将源表空间的所有对象导入到目标表空间中
REMAP_TABLESPACE=source_tablespace:target:tablespace
4、REUSE_DATAFILES
该选项指定建立表空间时是否覆盖已存在的数据文件.默认为N
REUSE_DATAFIELS={Y | N}
5、SKIP_UNUSABLE_INDEXES
指定导入是是否跳过不可使用的索引,默认为N
6、SQLFILE
指定将导入要指定的索引DDL操作写入到SQL脚本中
SQLFILE=[directory_object:]file_name
Impdp scott/tiger DIRECTORY=dump DUMPFILE=tab.dmp SQLFILE=a.sql
7、STREAMS_CONFIGURATION
指定是否导入流元数据(Stream Matadata),默认值为Y.
8、TABLE_EXISTS_ACTION
该选项用于指定当表已经存在时导入作业要执行的操作,默认为SKIP
TABBLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | FRPLACE }
当设置该选项为SKIP时,导入作业会跳过已存在表处理下一个对象;当设置为APPEND时,会追加数据,
为TRUNCATE时,导入作业会截断表,然后为其追加新数据;当设置为REPLACE时,导入作业会删除已存在
表,重建表病追加数据,注意,TRUNCATE选项不适用与簇表和NETWORK_LINK选项
9、TRANSFORM
该选项用于指定是否修改建立对象的DDL语句
TRANSFORM=transform_name:value[:object_type]
Transform_name用于指定转换名,其中SEGMENT_ATTRIBUTES用于标识段属性(物理属性,存储属性,表空间,日志等信息),STORAGE用于标识段存储属性,VALUE用于指定是否包含段属性或段存储属性,object_type用于指定对象类型.
Impdp scott/tiger directory=dump dumpfile=tab.dmp Transform=segment_attributes:n:table
10、TRANSPORT_DATAFILES
该选项用于指定搬移空间时要被导入到目标数据库的数据文件
TRANSPORT_DATAFILE=datafile_name
Datafile_name用于指定被复制到目标数据库的数据文件
Impdp system/manager DIRECTORY=dump DUMPFILE=tts.dmp
TRANSPORT_DATAFILES=’/user01/data/tbs1.f’调用IMPDP
参数文件接口实现导入:
#创建一个参数文件impfile.txt的内容如下
file='e:\oracle\imp\d01.dmp'
rows=y
fromuser=test
commit=y
feedback=5000
buffer=5000000
导入的命令:
imp system/system
parfile='e:\oracle\imp\impfile.txt'
五种导入的方法:
(1)全库导入方式 full 导入整个数据库、
(2)用户导入模式 user 导入指定用户的数据
(3)表导入模式 table 导入指定的数据表
(4)表空间导入模式 tablespace 导入指定的表空间
(5)可移动表空间导入模式 transport tablespace 导入可移动的表空间数据
impdp导入模式:
1、按表导入
p_street_area.dmp文件中的表,此文件是以gwm用户按schemas=gwm导出的:
impdp gwm/gwm@fgisdb dumpfile =p_street_area.dmp logfile=imp_p_street_area.log directory=dir_dp tables=p_street_area job_name=my_job
2、按用户导入(可以将用户信息直接导入,即如果用户信息不存在的情况下也可以直接导入)
impdp gwm/gwm@fgisdb schemas=gwm dumpfile =expdp_test.dmp logfile=expdp_test.log directory=dir_dp job_name=my_job
3、不通过expdp的步骤生成dmp文件而直接导入的方法:
--从源数据库中向目标数据库导入表p_street_area
impdp gwm/gwm directory=dir_dp NETWORK_LINK=igisdb tables=p_street_area logfile=p_street_area.log job_name=my_job
igisdb是目的数据库与源数据的链接名,dir_dp是目的数据库上的目录
4、更换表空间
采用remap_tablespace参数
--导出gwm用户下的所有数据
expdp system/orcl directory=data_pump_dir dumpfile=gwm.dmp SCHEMAS=gwm
注:如果是用sys用户导出的用户数据,包括用户创建、授权部分,用自身用户导出则不含这些内容
--以下是将gwm用户下的数据全部导入到表空间gcomm(原来为gmapdata表空间下)下
impdp system/orcl directory=data_pump_dir dumpfile=gwm.dmp remap_tablespace=gmapdata:gcomm