oracle 导出数据可以,oracle 导入导出数据

电玩女神 2022-10-21 11:41 293阅读 0赞

1.打开pl/sql界面,tools--->export user objects,选中你的表,导出生成sql脚本,假设取名为create-table.sql,里面包含表结构定义,但没有数据.

注意:因为我的表中有clob定义,如果使用常规的toos-->export tables-->sql inserts方式,无法导出.

使用oracle exports导出的是dmp文件,包含表结构定义和数据.

使用pl/sql developer导出的是pde文件.

在目的数据库(windows)

1.进入cmd命令行

c:/>sqlplus /nolog

sql>conn 用户名/密码@实例名-----conn sys/密码 @实例名 as sysdba

sql〉@sql脚本名---如：c:/oracle/script1.sql

。

执行sql脚本

首先执行建立表空间/建立用户/给用户授权的脚本，内容如下：

\----建立表空间----

create tablespace abc\_data ---表空间名和源的一致

logging

datafile 'h:/oracle11gdata/MYDB01.dbf'----给数据文件取名

size 50m

autoextend on

next 32m maxsize 2048m

extent management local;

\-- Create the user ---用户名和源服务器的一致

create user 名字 identified by "密码"

default tablespace abc\_DATA

temporary tablespace TEMP

profile DEFAULT

password expire;

\-- Grant/Revoke role privileges

grant connect to 用户名;

grant dba to 用户名;

grant resource to 用户名;

\-- Grant/Revoke system privileges

grant unlimited tablespace to 用户名;

\-----在源服务器pl/sql界面中左侧窗口选择用户这一文件夹,选择用户名,在右侧选择view sql,即可得到该用户的创建/赋权的sql语句,注意,密码部分显示为空,自己需要在导出的语句中手工加上密码.

为某用户(如user1)赋予访问某个表(如table1)的权限

create user user1 identified by "密码"

default tablespace abc\_DATA

temporary tablespace TEMP

profile DEFAULT

password expire;

\-- Grant/Revoke role privileges

grant connect to user1;

grant select on table1的owner.table1 to user1;

\-- Grant/Revoke system privileges

grant unlimited tablespace to 用户名;

然后执行建立表的脚本create-table.sql，该脚本在源服务器已经导出形成，直接拷贝到目的服务器---注意当前用户身份,应该重新登录切换到你新建的那个用户,不要用sys身份,否则表的owner有问题.

用sql〉@sql脚本名执行

2。导入数据。

此处我使用的是在源服务器导出的.dmp文件，

可以使用pl/sql图形界面导入

也可以在c：/执行命令行

imp username/password@SID file=XXX.dmp fromuser=XXX touser=XXX tables=(XXX,XXX)

其中，fromuser若为多个表空间的话，使用()将其括起来：fromuser=(a,b)；

touser参数仿fromuser参数；

若只导入一部分表，使用tables参数，用()括起要导入的表；如果想全部导入，不需要指定tables参数

但是，我的源服务器是11g，目的服务器是10g，从高版本到低版本导不进去，s悲剧了。。。

解决办法:自己写insert语句,存成脚本,执行,插入数据,现成的数据用不了。

/下面是我自己写了一个bat运行文件,安装oracle之后直接运行即可实现自动执行sql文件的目的.

首先,在.sql文件最后一行后面加上" quit;"这句,因为两个sql文件运行的身份不一样,我需要在运行完之后切换用户身份.

然后.写一个.bat文件,比如叫做god.bat

@echo off

sqlplus sys/sys的密码@实例名 as sysdba @l:/rmsdb.sql/1.sql---建立表空间,建立表的owner,给该用户授权

pause

sqlplus 表的owner/密码@实例名 @l:/rmsdb.sql/2.sql ----建立表

pause

imp 表的owner/表的owner的密码@实例名 file=l:/rmsdb.dmp/20110323.dmp full=y ignore = y---灌数据

导入导出方式总结

在命令行中使用exp/imp工具实现oracle数据导出/导入

Oracle数据导入导出imp/exp就相当于oracle数据还原与备份。exp命令可以把数据从远程数据库服务器导出到本地的dmp文件，imp命令可以把dmp文件从本地导入到远处的数据库服务器中。 利用这个功能可以构建两个相同的数据库，一个用来测试，一个用来正式使用。

执行环境：可以在SQLPLUS.EXE或者DOS(命令行)中执行，

DOS中可以执行时由于 在oracle 8i 中

安装目录ora81BIN被设置为全局路径，

该目录下有EXP.EXE与IMP.EXE文件被用来执行导入导出。

oracle用java编写，SQLPLUS.EXE、EXP.EXE、IMP.EXE这两个文件有可能是被包装后的类文件。

SQLPLUS.EXE调用EXP.EXE、IMP.EXE所包裹的类，完成导入导出功能。

下面介绍的是导入导出的实例。

数据导出：

1 将数据库TEST完全导出,用户名system 密码manager 导出到D:daochu.dmp中

exp system/manager@TEST file=d:daochu.dmp full=y

2 将数据库中system用户与sys用户的表导出

exp system/manager@TEST file=d:daochu.dmp owner=(system,sys)

3 将数据库中的表inner\_notify、notify\_staff\_relat导出

exp aichannel/aichannel@TESTDB2 file= d:datanewsmgnt.dmp tables=(inner\_notify,notify\_staff\_relat)

4 将数据库中的表table1中的字段filed1以"00"打头的数据导出

exp system/manager@TEST file=d:daochu.dmp tables=(table1) query=" where filed1 like '00%'"

上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。

也可以在上面命令后面 加上 compress=y 来实现。

数据的导入

1 将D:daochu.dmp 中的数据导入 TEST数据库中。

imp system/manager@TEST

file=d:daochu.dmp

imp aichannel/aichannel@HUST full=y

file=d:datanewsmgnt.dmp ignore=y

上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。

在后面加上 ignore=y 就可以了。

2 将d:daochu.dmp中的表table1 导入

imp system/manager@TEST

file=d:daochu.dmp

tables=(table1)

基本上上面的导入导出够用了。不少情况要先是将表彻底删除，然后导入。

注意：

操作者要有足够的权限，权限不够它会提示。

数据库时可以连上的。可以用tnsping TEST 来获得数据库TEST能否连上。

附录一：

给用户增加导入数据权限的操作

第一,启动sql\*puls

第二，以system/manager登陆

第三，create user 用户名 IDENTIFIED BY 密码 (如果已经创建过用户，这步可以省略)

第四，GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,

DROP ANY VIEW,EXP\_FULL\_DATABASE,IMP\_FULL\_DATABASE,

DBA,CONNECT,RESOURCE,CREATE SESSION

TO 用户名字

第五, 运行-cmd-进入dmp文件所在的目录,

imp userid=system/manager full=y file=\*.dmp

或者 imp userid=system/manager full=y file=filename.dmp

执行示例:

F:WorkOracle\_Databackup>imp userid=test/test full=y file=inner\_notify.dmp

屏幕显示

Import: Release 8.1.7.0.0 - Production on 星期四 2月 16 16:50:05 2006

连接到: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production

With the Partitioning option

JServer Release 8.1.7.0.0 - Production

经由常规路径导出由EXPORT:V08.01.07创建的文件

已经完成ZHS16GBK字符集和ZHS16GBK NCHAR 字符集中的导入

导出服务器使用UTF8 NCHAR 字符集 (可能的ncharset转换)

. 正在将AICHANNEL的对象导入到 AICHANNEL

. . 正在导入表

"INNER\_NOTIFY"

4行被导入

准备启用约束条件...

成功终止导入，但出现警告。

附录二：

Oracle 不允许直接改变表的拥有者, 利用Export/Import可以达到这一目的.

先建立import9.par,

然后，使用时命令如下：imp parfile=/filepath/import9.par

例 import9.par 内容如下：

FROMUSER=TGPMS

TOUSER=TGPMS2

(注：把表的拥有者由FROMUSER改为TOUSER，FROMUSER和TOUSER的用户可以不同)

ROWS=Y

INDEXES=Y

GRANTS=Y

CONSTRAINTS=Y

BUFFER=409600

file==/backup/ctgpc\_20030623.dmp

log==/backup/import\_20030623.log

以下我自己补充：

1.在导入导出命令中加上feedback=1000可以让过程显示一个不断增多的“...”，以改变以往的闪烁的光标

PS:IMP命令

命令参数说明 关键字 说明(默认)

\---------------------------------------------------

USERID 用户名/口令

FULL 导出整个文件 (N)

BUFFER 数据缓冲区的大小

OWNER 所有者用户名列表

FILE 输出文件 (EXPDAT.DMP)

TABLES 表名列表

COMPRESS 导入一个范围 (Y)

RECORDLENGTH IO 记录的长度

GRANTS 导出权限 (Y)

INCTYPE 增量导出类型

INDEXES 导出索引 (Y)

RECORD 跟踪增量导出 (Y)

ROWS 导出数据行 (Y)

PARFILE 参数文件名

CONSTRAINTS 导出限制 (Y)

CONSISTENT 交叉表一致性

LOG 屏幕输出的日志文件

STATISTICS 分析对象 (ESTIMATE)

DIRECT 直接路径 (N)

TRIGGERS 导出触发器 (Y)

FEEDBACK 显示每 x 行 (0) 的进度

FILESIZE 各转储文件的最大尺寸

QUERY 选定导出表子集的子句

EXP命令

命令参数说明

关键字 说明(默认) ----------------------------------------------

USERID 用户名/口令

FULL 导入整个文件 (N)

BUFFER 数据缓冲区大小

FROMUSER 所有人用户名列表

FILE 输入文件 (EXPDAT.DMP)

TOUSER 用户名列表

SHOW 只列出文件内容 (N)

TABLES 表名列表

IGNORE 忽略创建错误 (N)

RECORDLENGTH IO 记录的长度

GRANTS 导入权限 (Y)

INCTYPE 增量导入类型

INDEXES 导入索引 (Y)

COMMIT 提交数组插入 (N)

ROWS 导入数据行 (Y)

PARFILE 参数文件名

LOG 屏幕输出的日志文件

CONSTRAINTS 导入限制 (Y)

DESTROY 覆盖表空间数据文件 (N)

INDEXFILE 将表/索引信息写入指定的文件

SKIP\_UNUSABLE\_INDEXES 跳过不可用索引的维护 (N)

ANALYZE 执行转储文件中的 ANALYZE 语句 (Y)

r />FEEDBACK 显示每 x 行 (0) 的进度

TOID\_NOVALIDATE 跳过指定类型 id 的校验

FILESIZE 各转储文件的最大尺寸

RECALCULATE\_STATISTICS 重新计算统计值 (N)

另,把建表语句脚本,如上文的create-table.sql,使用powerdesigner,把.sql文件转换成.pdm文件,即物理模型文件,这样做的结果是,会自动生成这些表的表和字段定义图,表的ER图等等一张大图,类似vs2008开发工具中根据源码生成类图的那个工具,很速度,很好用,很清楚...

方法,新建一个physical data model, 然后, 找到database-->database reverse engine,选择你的.sql文件做为脚本,ok即可.

新建physical data model时,DBMS的文件夹在program files/sybase/powerdesigner12/resource file/DBMS,指明之后,会发现DBMS那项多了很多数据库选项,选择你的.sql文件使用的数据库平台即可(我用的oracle 10g).当时无意中更改了这个文件夹,结果s活新建不了pdm文件,那边等着我这封邮件呢...我干脆不折腾了,找了一个旧的pdm文件打开,把里面旧的ER图什么的全选再删除,然后点选database-->database reverse engine,选择新的.sql文件做为脚本,ok然后另存.就生成了我需要的pdm,发之...过程很对付,但结局还不错.