Oracle数据库导入导出.docx
《Oracle数据库导入导出.docx》由会员分享,可在线阅读,更多相关《Oracle数据库导入导出.docx(19页珍藏版)》请在冰豆网上搜索。
Oracle数据库导入导出
Oracle数据库文件资料收集:
一、Oracle数据导入导出imp/exp命令10g以上expdp/impdp命令详细
A)数据导出:
1将数据库TEST完全导出,用户名system密码manager导出到D:
daochu.dmp中
expsystem/manager@TESTfile=d:
daochu.dmpfull=y
2将数据库中system用户与sys用户的表导出
expsystem/manager@TESTfile=d:
daochu.dmpowner=(system,sys)
3将数据库中的表inner_notify、notify_staff_relat导出
expaichannel/aichannel@TESTDB2file=d:
datanewsmgnt.dmptables=(inner_notify,notify_staff_relat)
4将数据库中的表table1中的字段filed1以"00"打头的数据导出
expsystem/manager@TESTfile=d:
daochu.dmptables=(table1)query="wherefiled1like'00%'"
上面是常用的导出,对于压缩,既用winzip把dmp文件可以很好的压缩。
也可以在上面命令后面加上compress=y来实现。
B)数据的导入
1将D:
daochu.dmp中的数据导入TEST数据库中。
impsystem/manager@TESTfile=d:
daochu.dmp
impaichannel/aichannel@HUSTfull=yfile=d:
datanewsmgnt.dmpignore=y
上面可能有点问题,因为有的表已经存在,然后它就报错,对该表就不进行导入。
在后面加上ignore=y就可以了。
2将d:
daochu.dmp中的表table1导入
impsystem/manager@TESTfile=d:
daochu.dmptables=(table1)
*注:
要先是将表彻底删除,然后导入。
操作者要有足够的权限,权限不够它会提示。
数据库时可以连上的。
可以用tnspingTEST来获得数据库TEST能否连上
当然,上面的方法在导出数据时可能会导出很大的包,那是因为你的用户可能在授权的时候授予了DBA的权限,所以可以采用下面方法来进行数据库备份:
二、数据库备份
建议系统过渡后,每周进行一次备份。
或者在数据表发生重大改变前,对要改变的数据表进行备份。
执行以下步骤,进行备份。
在命令行里,敲入“cmd”,回车,进入命令行窗口。
在窗口中,输入:
expmas/123456@mas
系统提示:
输入数组提取缓冲区大小:
4096>
可以直接回车;
系统提示:
导出文件:
EXPDAT.DMP>
此处输入导出文件的位置,其路径必须存在,Oracle在这里不会自动建立路径,但可以建立文件名。
备份文件以dmp作为后缀。
输入内容如:
e:
\work\mas_db_090925v1.dmp回车
系统提示:
(1)E(完整的数据库),
(2)U(用户)或(3)T(表):
(2)U>u
此处可以输入u,也可以直接回车,因为系统此时默认的是U
系统提示:
导出权限(yes/no):
yes>回车
系统提示:
导出表数据(yes/no):
yes>回车
系统提示:
压缩区(yes/no):
yes>回车
系统提示:
要导出的用户:
(RETURN以退出)>mas
系统提示:
要导出的用户:
(RETURN以退出)>回车
此时系统会自动进行备份
经验总结:
A.说明
oracle的exp/imp命令用于实现对数据库的导出/导入操作;
exp命令用于把数据从远程数据库服务器导出至本地,生成dmp文件;
imp命令用于把本地的数据库dmp文件从本地导入到远程的Oracle数据库中。
B.语法
可以通过在命令行输入imphelp=y获取imp的语法信息:
C:
\DocumentsandSettings\auduser>imphelp=y
Import:
Release9.0.1.1.1-Productionon星期二5月2018:
21:
572008
(c)Copyright2001OracleCorporation.Allrightsreserved.
可以通过输入IMP命令和您的用户名/口令
后接用户名/口令的命令:
A.导入:
例程:
IMPSCOTT/TIGER
或者,可以通过输入IMP命令和各种参数来控制“导入”
按照不同参数。
要指定参数,您可以使用关键字:
格式:
IMPKEYWORD=value或KEYWORD=(value1,value2,...,vlaueN)
例程:
IMPSCOTT/TIGERIGNORE=YTABLES=(EMP,DEPT)FULL=N
或TABLES=(T1:
P1,T1:
P2),如果T1是分区表
USERID必须是命令行中的第一个参数。
关键字说明(默认)关键字说明(默认)
--------------------------------------------------------------------------
USERID用户名/口令FULL导入整个文件(N)
BUFFER数据缓冲区大小FROMUSER所有人用户名列表
FILE输入文件(EXPDAT.DMP)TOUSER用户名列表
SHOW只列出文件内容(N)TABLES表名列表
IGNORE忽略创建错误(N)RECORDLENGTHIO记录的长度
GRANTS导入权限(Y)INCTYPE增量导入类型
INDEXES导入索引(Y)COMMIT提交数组插入(N)
ROWS导入数据行(Y)PARFILE参数文件名
LOG屏幕输出的日志文件CONSTRAINTS导入限制(Y)
DESTROY覆盖表空间数据文件(N)
INDEXFILE将表/索引信息写入指定的文件
SKIP_UNUSABLE_INDEXES跳过不可用索引的维护(N)
FEEDBACK每x行显示进度(0)
TOID_NOVALIDATE跳过指定类型ID的验证
FILESIZE每个转储文件的最大大小
STATISTICS始终导入预计算的统计信息
RESUMABLE遇到与空格有关的错误时挂起(N)
RESUMABLE_NAME用来标识可恢复语句的文本字符串
RESUMABLE_TIMEOUTRESUMABLE的等待时间
COMPILE编译过程,程序包和函数(Y)
下列关键字仅用于可传输的表空间
TRANSPORT_TABLESPACE导入可传输的表空间元数据(N)
TABLESPACES将要传输到数据库的表空间
DATAFILES将要传输到数据库的数据文件
TTS_OWNERS拥有可传输表空间集中数据的用户
==============================================
同样可以通过输入exphelp=y获取exp的语法信息
MicrosoftWindowsXP[版本5.1.2600]
(C)版权所有1985-2001MicrosoftCorp.
C:
\DocumentsandSettings\auduser>exphelp=y
Export:
Release9.0.1.1.1-Productionon星期二5月2018:
26:
342008
(c)Copyright2001OracleCorporation.Allrightsreserved.
通过输入EXP命令和用户名/口令,您可以
后接用户名/口令的命令:
B.导出例程:
EXPSCOTT/TIGER
或者,您也可以通过输入跟有各种参数的EXP命令来控制“导出”
按照不同参数。
要指定参数,您可以使用关键字:
格式:
EXPKEYWORD=value或KEYWORD=(value1,value2,...,valueN)
例程:
EXPSCOTT/TIGERGRANTS=YTABLES=(EMP,DEPT,MGR)
或TABLES=(T1:
P1,T1:
P2),如果T1是分区表
USERID必须是命令行中的第一个参数。
关键字说明(默认)关键字说明(默认)
--------------------------------------------------------------------------
USERID用户名/口令FULL导出整个文件(N)
BUFFER数据缓冲区大小OWNER所有者用户名列表
FILE输出文件(EXPDAT.DMP)TABLES表名称列表
COMPRESS导入到一个区(Y)RECORDLENGTHIO记录的长度
GRANTS导出权限(Y)INCTYPE增量导出类型
INDEXES导出索引(Y)RECORD跟踪增量导出(Y)
DIRECT直接路径(N)TRIGGERS导出触发器(Y)
LOG屏幕输出的日志文件STATISTICS分析对象(ESTIMATE)
ROWS导出数据行(Y)PARFILE参数文件名
CONSISTENT交叉表一致性CONSTRAINTS导出约束条件(Y)
FEEDBACK每x行显示进度(0)
FILESIZE每个转储文件的最大大小
FLASHBACK_SCN用于回调会话快照的SCN
FLASHBACK_TIME用来获得最接近于指定时间的SCN的时间
QUERY用来导出表的子集的选择子句
RESUMABLE遇到与空格有关的错误时挂起(N)
RESUMABLE_NAME用来标识可恢复语句的文本字符串
RESUMABLE_TIMEOUTRESUMABLE的等待时间
TTS_FULL_CHECK对TTS执行完全或部分相关性检查
TABLESPACES要导出的表空间列表
TRANSPORT_TABLESPACE导出可传输的表空间元数据(N)
TEMPLATE调用iAS模式导出的模板名称
三、使用示例
3.1数据导出:
1将数据库SampleDB完全导出,用户名system密码manager导出到E:
\SampleDB.dmp中
expsystem/manager@TestDBfile=E:
\sampleDB.dmpfull=y
2将数据库中system用户与sys用户的表导出
expsystem/manager@TestDBfile=E:
\sampleDB.dmpowner=(system,sys)
3将数据库中的表TableA,TableB导出
expsystem/manager@TestDBfile=E:
\sampleDB.dmptables=(TableA,TableB)
4将数据库中的表tableA中的字段filed1值为"王五"的数据导出
expsystem/manager@TestDBfile=E:
\sampleDB.dmptables=(tableA)query='wherefiled1='王五'
如果想对dmp文件进行压缩,可以在上面命令后面加上compress=y来实现。
3.2数据的导入
1将备份数据库文件中的数据导入指定的数据库SampleDB中,如果SampleDB已存在该表,则不再导入;
impsystem/manager@TESTfile=E:
\sampleDB.dmpfull=yignore=y
2将d:
\daochu.dmp中的表table1导入
impsystem/manager@TESTfile=E:
\sampleDB.dmptables=(table1)
3.导入一个完整数据库
impsystem/managerfile=bible_dblog=dible_dbfull=yignore=y
4.导入一个或一组指定用户所属的全部表、索引和其他对象
impsystem/managerfile=seaparklog=seaparkfromuser=seaparkimp
system/managerfile=seaparklog=seaparkfromuser=(seapark,amy,amyc,harold)
5.将一个用户所属的数据导入另一个用户
impsystem/managerfile=tanklog=tankfromuser=seaparktouser=seapark_copy
impsystem/managerfile=tanklog=tankfromuser=(seapark,amy)
touser=(seapark1,amy1)
6.导入一个表
impsystem/managerfile=tanklog=tankfromuser=seaparkTABLES=(a,b)
7.从多个文件导入
impsystem/managerfile=(paycheck_1,paycheck_2,paycheck_3,paycheck_4)
log=paycheck,filesize=1Gfull=y
8.使用参数文件
impsystem/managerparfile=bible_tables.par
bible_tables.par参数文件:
#ImportthesampletablesusedfortheOracle8iDatabaseAdministrator's
Bible.fromuser=seaparktouser=seapark_copyfile=seaparklog=seapark_import
参数文件示例见附录
9.增量导入
impsystem./managerinctype=RECTOREFULL=YFILE=A
*注:
不少情况下要先将表彻底删除,然后导入。
四、参数说明
4.1、8iEXP常用选项
1、FULL,这个用于导出整个数据库,在ROWS=N一起使用时,可以导出整个数据库的结构。
例如:
expsysfile=./db_str.dmplog=./db_str.logfull=yrows=ncompress=ydirect=y
2、BUFFER和FEEDBACK,在导出比较多的数据时,我会考虑设置这两个参数。
例如:
expnewfile=yw97_2003.dmplog=yw97_2003_3.logfeedback=10000buffer=100000000tables=WO4,OK_YT
3、FILL和LOG,这两个参数分别指定备份的DMP名称和LOG名称,包括文件名和目录,例子见上面。
需要说明的是,EXP可以直接备份到磁带中,即使用FILE=/dev/rmt0(磁带设备名),但是一般我们都不这么做,原因有二:
一、这样做的速度会慢很多,二、现在一般都是使用磁带库的,不建议直接对磁带进行操作。
至于没有使用磁带库的朋友可以考虑和UNIX的TAR结合使用。
如果你真想使用EXP直接到磁带,你可以参考Metalink文章“EXPORTINGTOTAPEONUNIXSYSTEMS”(文档号:
30428.1),该文中有详细解释。
4、COMPRESS参数将在导出的同时合并碎块,尽量把数据压缩到initial的EXTENT里,默认是N,一般建议使用。
DIRECT参数将告诉EXP直接读取数据,而不像传统的EXP那样,使用SELECT来读取表中的数据,这样就减少了SQL语句处理过程。
一般也建议使用。
不过有些情况下DIRECT参数是无法使用的。
5、如何使用SYSDBA执行EXP/IMP?
这是一个很现实的问题,有时候我们需要使用SYSDBA来执行EXP/IMP,如进行传输表空间的EXP/IMP,以及在9i下用SYS用户来执行EXP/IMP时,都需要使用SYSDBA才可。
我们可以使用下面方式连入EXP/IMP:
exp"'sys/sysassysdba'"file=1.dmptables=gototop.trows=n
6、QUERY参数后面跟的是where条件,值得注意的是,整个where子句需要使用""括起来,where子句的写法和SELECT中相同,如果是UNIX平台所有"和'都需要使用\u26469屏蔽它们的特殊含义:
expgototop/gototopfile=1.dmplog=1.logtables=cyx.tquery="wherec1=20andc2=gototop"
如果是windows平台,则使用下面的格式:
expc/c@ncnfile=c.dmplog=c.logtables=tquery="""whereid=1andname='gototop'"""
4.2、8iIMP常用选项
1、FROMUSER和TOUSER,使用它们实现将数据从一个SCHEMA中导入到另外一个SCHEMA中。
2、IGNORE、GRANTS和INDEXES,其中IGNORE参数将忽略表的存在,继续导入,这个对于需要调整表的存储参数时很有用,我们可以先根据实际情况用合理的存储参数建好表,然后直接导入数据。
而GRANTS和INDEXES则表示是否导入授权和索引,如果想使用新的存储参数重建索引,或者为了加快到入速度,我们可以考虑将INDEXES设为N,而GRANTS一般都是Y。
另外一个EXP/IMP都有的参数是PARFILE,它是用来定义EXP/IMP的参数文件,也就是说,上面的参数都可以写在一个参数文件中,但我们一般很少使用。
4.4、Oracle9iEXP功能描述
Oracle9iEXP在原有的基础上新增了部分新的参数,按功能主要分为以下几个部分:
1、OBJECT_CONSISTENT-用于设置EXP对象为只读以保持对象的一致性。
默认是N。
2、FLASHBACK_SCN和FLASHBACK_TIME-用于支持FLASHBACK功能而新增。
3、RESUMABLE、RESUMABLE_NAME和RESUMABLE_TIMEOUT-用于支持RESUMABLE空间分配而新增。
4、TTS_FULL_CHECK-用于在传输表空间时使用依赖性检查。
5、TEMPLATE-用于支持iAS。
6、TABLESPACES-设置表空间导出模式。
个人觉得对于一般用户而言,这个才是新增参数中最实用的一个,可以让用户在原来的FULL、OWNER、TABLES的基础上多了一种选择,使得EXP更加灵活。
五、不同版本的EXP/IMP问题?
一般来说,从低版本导入到高版本问题不大,麻烦的是将高版本的数据导入到低版本中,在Oracle9i之前,不同版本Oracle之间的EXP/IMP可以通过下面的方法来解决:
1、在高版本数据库上运行底版本的catexp.sql;
2、使用低版本的EXP来导出高版本的数据;
3、使用低版本的IMP将数据库导入到底版本数据库中;
4、在高版本数据库上重新运行高版本的catexp.sql脚本。
但在9i中,上面的方法并不能解决问题。
如果直接使用底版本EXP/IMP会出现如下错误:
EXP-00008:
ORACLEerror%luencountered
ORA-00904:
invalidcolumnname
这已经是一个公布的BUG,需要等到Oracle10.0才能解决,BUG号为2261,你可以到METALINK上去查看有关此BUG的详细信息。
BUG归BUG,我们的工作还是要做,在没有Oracle的支持之前,我们就自己解决。
在Oracle9i中执行下面的SQL重建exu81rls视图即可。
CREATEORREPLACEviewexu81rls
(objown,objnam,policy,polown,polsch,polfun,stmts,chkopt,enabled,spolicy)
ASselectu.name,o.name,r.pname,r.pfschma,r.ppname,r.pfname,
decode(bitand(r.stmt_type,1),0,'','SELECT,')
||decode(bitand(r.stmt_type,2),0,'','INSERT,')
||decode(bitand(r.stmt_type,4),0,'','UPDATE,')
||decode(bitand(r.stmt_type,8),0,'','DELETE,'),
r.check_opt,r.enable_flag,
DECODE(BITAND(r.stmt_type,16),0,0,1)
fromuser$u,obj$o,rls$r
whereu.user#=o.owner#
andr.obj#=o.obj#
and(uid=0or
uid=o.owner#or
exists(select*fromsession_roleswhererole='SELECT_CATALOG_ROLE')
)
/
grantselectonsys.exu81rlstopublic;
/
六、其他问题
本文只讨论了Oracle8i和9i中的EXP/IMP的一些情况,对于之前的版本,在8.0.X中,除了QUERY参数不能用外,其它差别不大。
针对没有QUERY的情况,我们可以先在数据库中使用查询条件建立临时中间表,然后使用EXP导出这个中间表即可。
至于Oracle7因为目前使用的人较少,gototop不打算在此做详细解释了,如果读者朋友有需求,你可以参考Metalink文档:
“OverviewofExportandImportinOracle7”(文档号:
61949.1)。
关于EXP/IMP的详细参数信息你可以通过EXP/IMPHELP=Y来获得。
另外关于传输表空间的更多信息可以参考下面的Metelink文档,本文不再详述。
[NOTE:
77523.1]TransportableTablespaces--AnExampletosetupanduse.
[NOTE:
100698.1]Performtablespacepoint-in-