PL/SQL导出、导入表数据【实例】
PL/SQL导出、导入表数据【实例】
2016年04月21日 ⁄ 数据库 ⁄ 共 2057字 ⁄ 暂无评论 ⁄ 阅读 1,205 次最近需要从A库将数据导到B库，A库使用的是Oracle 10g，B库使用的是Oracle 11g，本地未安装数据库服务。
【EXP导出失败】
最开始想使用EXP导出数据，结果在使用11g导出10g数据时会报错“EXP-00008: 遇到 ORACLE 错误 904”。错误原因是，Oracle只向下兼容，也就是必须使用10g及以前版本才能导出。为导数据，将本地数据库更换或在其他电脑新装10g的时间成本较高，且可能有未知困难（WIN7操作系统和Oracle 10g数据库之间不兼容），故只能采取其他手段来实现数据迁移。扩展阅读：Oracle数据库导入导出逻辑备份方法总结
【PLSQL功能Oracle Export导出失败】
使用本地电脑PL/SQL导出数据，Tools=>Export Tables=>Oracle Export。结果发现无论如何右下角的Export都是灰色的，无法导出！查找原因，发现PL/SQL虽有Export功能，但该功能是基于EXP的，本地电脑未安装Oracle服务也就没有EXP工具，故该功能是无法使用的。
【PLSQL功能PL/SQL Developer导出成功】
更换导出方式为，Tools=>Export Tables=>PL/SQL Developer（不使用SQL Inserts是因为该选项导出数据量小没问题，但数据量大时效率非常低，尤其是在B库上导入时，耗时更久，而且容易卡死）。结果发现在编译过程中也报错了“stream read error”，查找原因发现是因为导出的部分表中有大字段（CLOB或BLOB），而PL/SQL Developer并不支持大字段数据类型。所以，必须先把含有大字段的表找出来，可使用系统表ALL_TAB_COLUMNS，SQL如下：
| 1 2 3 4 5 6 | SELECT DISTINCT t.TABLE_NAME FROM SYS.ALL_TAB_COLUMNS t WHERE t.TABLE_NAME NOT LIKE 'BIN%' AND t. OWNER = ‘USER_NAME’ AND t.DATA_TYPE IN ( 'CLOB', 'BLOB') ORDER BY t.TABLE_NAME; |
注意，USER_NAME是导出用户的用户名，需要更改。
若需要导出的表较少，有大字段的表也不多，则可以手工按shift配合ctrl来选择要导出的不带大字段的表。若要导出的表多，且有大字段的表也多，用手工选择表的方式就比较麻烦了，可以使用PL/SQL自带的Object Selection功能。
1）使用SQL语句拼出Load Object Selection所需文件内容，SQL如下：
| 1 2 3 4 5 | SELECT DISTINCT ( 'TABLE "' || t. OWNER || '"."' || t.TABLE_NAME || '"') FROM SYS.ALL_TAB_COLUMNS t WHERE t.TABLE_NAME NOT LIKE 'BIN%' AND t. OWNER = 'USER_NAME' AND t.DATA_TYPE NOT IN ( 'CLOB', 'BLOB'); |
注意，USER_NAME是导出用户的用户名，需要更改。
2）编辑osf文件，在Tools=>Export Tables=>PL/SQL Developer的选择框里随便选择一个表，右键=>Save Object Selection，选择文件存放位置，然后生成文件。
使用文本编辑工具（如UltraEdit）打开osf文件，将上面SQL语句的结果粘贴进文件，然后保存。osf文件内容样式如下：
PL/SQL Developer Object Selection File
1
TABLE "SCOTT"."EMP"
TABLE "SCOTT"."DEPT"
3）在选择框里，右键=>Load Object Selection，然后选择上面做成的osf文件，然后就会发现，想要导出的且不包含大字段的表已经被选中了，然后在右下Export前选择导出文件的位置及命名文件，然后点Export就可以等待文件导出了。若导出的表较多，该步可能耗时较长。
【PL/SQL功能Export User Objects导出非表数据】
Export Tables导出的只有表及表数据，但还有些其他数据是我们所需要的，比如序列、视图、存储过程和触发器等，这就需要用到Tools=>Export User Objects功能了。
【PLSQL功能PL/SQL Developer导入表】
A库导出的数据，要导入B库。因环境原因，需要将数据同样导入B库的相同User下。故，需要先在B库建立相同用户及表空间表（若用户已存在则不需要建，但若表空间不同需要新建同名表空间并更改用户的默认表空间）。具体SQL如下：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | /*查看A库用户默认表空间及临时表空间*/ SELECT * FROM dba_users t WHERE t.username = 'PWEB'; /*查看B库是否已存在同名表空间、临时表空间或数据文件*/ SELECT * FROM dba_users t WHERE t.username = 'PWEB'; /*创建表空间*/ --查看表空间数据文件存储位置 SELECT * FROM DBA_DATA_FILES; --删除不规范或误建的表空间及数据文件，注：TBS_NAME要更改为指定表空间表 DROP TABLESPACE TBS_NAME INCLUDING CONTENTS AND DATAFILES; --根据需求配置临时表空间位置及大小 create temporary tablespace tbs_name tempfile 'D:\ORACLE\ORADATA\ORCL\tbs_name.dbf' size 50m autoextend on next 50m maxsize 2048m extent management local; --根据需求配置表空间位置及大小 create tablespace tbs_name datafile 'D:\ORACLE\ORADATA\ORCL\tbs_name.dbf' size 50m autoextend on next 50m maxsize 2048m extent management local; /*创建用户并赋权*/ --删除错建的用户 drop user user_name cascade; --新建用户设定密码指定默认表空间 create user user_name identified by user_name default tablespace tbs_name temporary tablespace temp_name; --给新建的用户赋权 grant connect, resource to user_name; |
使用Tools=>Import Tables功能导入数据，选择文件，导入。
然而，若两库之间字符集不一样，可能会报错“ORA-12899”，这是因为中文在UTF-8中占3个字节，ZHS16GBK中占2个字节，A库字符集是ZHS16GBK而B库字符集为UTF-8的库里，所以会出现值长度超过最大长度的问题，从而报错“ORA-12899”。
解决方法1：要么修改B库相应表字段的最大长度，然后将未导入的数据手工导入。
解决方法2：删除已经导入的数据，并重启修改B库字符集
/*单步按顺序执行下列语句，若有不懂去网上查，此处不详解*/
| 1 2 3 4 5 6 7 8 9 | SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER SYSTEM ENABLE RESTRICTED SESSION; ALTER SYSTEM SET JOB_QUEUE_PROCESSES =0; ALTER SYSTEM SET AQ_TM_PROCESSES =0; ALTER DATABASE OPEN; ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK; SHUTDOWN IMMEDIATE; STARTUP; |
执行SQL文件导入非表数据：
在PL/SQL新建Command Window，直接在SQL后执行SQL文件，样式如下：
SQL>@ ‘D:\data\scott.sql’
注意：不要漏掉两个单引号。
» 声明：本站文章源于个人经验总结或书籍、互联网转载，内容仅用于个人学习，请勿转载，否则后果自负！ » 本文地址： http://www.ecdoer.com/post/plsql-exp-imp.htm本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！