Oracle数据库导入导出.docx

资源描述

Oracle数据库导入导出.docx

《Oracle数据库导入导出.docx》由会员分享，可在线阅读，更多相关《Oracle数据库导入导出.docx（19页珍藏版）》请在冰点文库上搜索。

Oracle数据库导入导出.docx

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作为后缀。

输入内容如：

\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的语法信息:

\DocumentsandSettings\auduser>imphelp=y

Import:

Release9.0.1.1.1-Productionon星期二5月2018:

21:

572008

可以通过输入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]

\DocumentsandSettings\auduser>exphelp=y

Export:

Release9.0.1.1.1-Productionon星期二5月2018:

26:

342008

通过输入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-

展开阅读全文