[独孤九剑]Oracle知识点梳理(三)导入、导出
本系列链接导航:
[独孤九剑]Oracle知识点梳理(三)导入、导出
[独孤九剑]Oracle知识点梳理(四)SQL语句之DML和DDL
[独孤九剑]Oracle知识点梳理(五)数据库常用对象之Table、View
[独孤九剑]Oracle知识点梳理(六)数据库常用对象之Procedure、function、Sequence
[独孤九剑]Oracle知识点梳理(七)数据库常用对象之Cursor
[独孤九剑]Oracle知识点梳理(八)常见Exception
[独孤九剑]Oracle知识点梳理(九)数据库常用对象之package
[独孤九剑]Oracle知识点梳理(十)%type与%rowtype及常用函数
3、数据库的导入导出(在命令提示符cmd下直接执行)
Oracle 9i之前,使用imp/exp命令完成导入/导出操作。
3.1、数据导出
1 --将数据库orcl完全导出到D盘指定目录 2 exp userName/password@orcl file=d:\backup\userName01.dmp log=d:\backup\userName01.log full=y 3 --将 数据库中userName用户和userName2用户的相关内容导出 4 exp userName/password@orcl file=d:\backup\userName01.dmp log=d:\backup\userName01.log owner=(userName,userName2) 5 --将表t1和t2导出 6 exp userName/password@orcl file=d:\backup\userName01.dmp log=d:\backup\userName01.log tables=(t1,t2) 7 --将表t1中以00开头的数据导出 8 exp userName/password@orcl file=d:\backup\userName01.dmp log=d:\backup\userName01.log tables=(t1) query=\" where field1 like '00%'\"
3.2、数据导入
导入的前提:数据库中用于即将导入的数据的一致的表空间同名用户见的导入:
1 --将备份文件全部导入 2 imp userName/password@orcl file=d:\backup\userName01.dmp log=d:\backup\userName01.log full=y 3 --其中ignore=y表示忽略已存在的表,不加,遇到同名的表,对该表就不导入 4 imp userName/password@orcl file=d:\backup\userName01.dmp log=d:\backup\userName01.log full=y ignore=y 5 --将表t1 导入 6 imp userName/password@orcl file=d:\backup\userName01.dmp log=d:\backup\userName01.log tables=(t1)
不同名用户间的导入:
imp userName/password@orcl file=d:\backup\userName01.dmp
log=d:\backup\userName01.log
fromuser=userNameA touser=userNameB --注意fromuser和touser
Oracle 10i之后,Oracle推荐使用数据泵方式导入/导出数据【impdp/expdp】;使用数据泵方式,只能在服务器上进行导入/导出操作。
3.3、数据泵导出步骤:
a) 创建Directory
create directory dir_dp as 'D:\backup\dir_dp';
b) 授权
create read,write on directory dir_dp on userName;
b.1) 查看目录及权限
select privilege,directory_name,directory_path
from user_tab_privs t,all_directories d where t.table_name(+)=d.directory_name;
c) 执行导出
expdp userName/password@orcl schemas=userName
directory=dir_dp dmpfile=expdp_userName.dmp logfile=expdp_userName.log
注意:
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 userName/password@orcl schemas=userName
directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log,job_name=my_job1;
3、导出语句后面不要有分号,否则如上的导出语句中的job表名为‘my_job1;’,而不是my_job1。因此导致expdp userName/password attach=userName.my_job1执行该命令时一直提示找不到job表
4、创建的目录一定要在数据库所在的机器上。否则也是提示:
ORA-39002: 操作无效
ORA-39070: 无法打开日志文件。
ORA-39087: 目录名 DATA_PUMP_DIR; 无效
数据泵导出的各种模式:
- 按表模式导出:
expdp userName/password@orcl tables=userName.b$i_exch_info,userName.b$i_manhole_info
dumpfile =expdp_test2.dmp logfile=expdp_test2.log directory=dir_dp job_name=my_job
- 按查询条件导出:
expdp userName/password@orcl tables=userName.b$i_exch_info
dumpfile=expdp_test3.dmp logfile=expdp_test3.log directory=dir_dp job_name=my_job query='"where rownum<11"'
- 按表空间导出:
Expdp userName/password@orcl dumpfile=expdp_tablespace.dmp
tablespaces=GCOMM.DBF logfile=expdp_tablespace.log directory=dir_dp job_name=my_job
- 导出方案
Expdp userName/password DIRECTORY=dir_dp DUMPFILE=schema.dmp SCHEMAS=userName,gwm
- 导出整个数据库:
expdp userName/password@orcl dumpfile =full.dmp full=y logfile=full.log directory=dir_dp job_name=my_job
3.4、数据泵导入步骤:
准备:1.建库2.建表空间3.建用户并授权4.将aa.dmp拷贝到dpdump目录下(如果不修改并指定备份文件目录的话)
- 按表导入(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
- 按用户导入(可以将用户信息直接导入,即如果用户信息不存在的情况下也可以直接导入)
impdp gwm/gwm@fgisdb schemas=gwm dumpfile =expdp_test.dmp logfile=expdp_test.log directory=dir_dp job_name=my_job
- 不通过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是目的数据库上的目录
- 更换表空间(采用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
3.5、不同版本数据库间的数据导入/导出
3.5.1、Oracle 10g与Oracle 11g之间的导入导出
- 导出
1 create directory dir_dp as 'D:\backup\dir_dp'; 2 create read,write on directory dir_dp on userName; 3 expdp userName/password@orcl schemas=userName directory=dir_dp
dumpfile=expdp_userName.dmp logfile=expdp_userName.log version=10.2.0.1.0 --带上版本号
- 导入
impdp userName/password@orcl schemas=userName directory=dir_dp
dumpfile=expdp_userName.dmp logfile=expdp_userName.log version=10.2.0.1.0 --带上版本号
3.5.2、其他方法
用Notepad++或其它文本编辑工具软件查看dmp文件,将头部修改成你将导入目标数据库的版本号
以下对应的版本号: 11g R2:V11.02.00 11g R1:V11.01.00 10g:V10.02.01
这样,ORACLE10g就可以导入此文件了,当然也可能考验人品。