对oracle数据库使用sql脚本进行导入导出

在源数据库
　　
　　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 useruser1 identified by "密码"
default tablespace abc_DATA
temporary tablespace TEMP
profile DEFAULT
password expire;
　　-- Grant/Revoke role privileges
grant connect to user1;
grantselect on table1的owner.table1to 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数据导出/导入
　　

2 推荐 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 (c) Copyright 2000 Oracle Corporation. All rights reserved. 连接到: 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 reverseengine,选择新的.sql文件做为脚本,ok然后另存.就生成了我需要的pdm,发之...过程很对付,但结局还不错.

　　
　　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 useruser1 identified by "密码"
default tablespace abc_DATA
temporary tablespace TEMP
profile DEFAULT
password expire;
　　-- Grant/Revoke role privileges
grant connect to user1;
grantselect on table1的owner.table1to 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数据导出/导入
　　

2 推荐 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 (c) Copyright 2000 Oracle Corporation. All rights reserved. 连接到: 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 reverseengine,选择新的.sql文件做为脚本,ok然后另存.就生成了我需要的pdm,发之...过程很对付,但结局还不错.