ORACLE 导出表结构方式较多,最方便的可能就属PL/SQL了,其次就是用EXP命令了。但是这些往往有时候达不到自己的需求要的效果,或者达到需求了,也较复杂些,例如EXP导出和版本有关系,就是一个令人头疼的问题。例如这次的项目就遇到一个需求:把甲数据库上SCHEMA下指定的几个表,迁移到乙数据库。两个库的表空间不一致,用户名不一致,并且版本也不一致,数据甲是10G,数据乙是9i。
经过对比,发现一个比较简单的方法,就是dbms_metadata.get_ddl()方式。
select dbms_metadata.get_ddl('TABLE','ZB_DEV_M_CARD_02_RPT','ZB') from dual; 表示导出ZB用户名下的实体表ZB_DEV_M_CARD_02_RPT 。
select dbms_metadata.get_ddl('VIEW','ZB_DEV_M_CARD_02_RPT_VIEW,'ZB') from dual ; 表示ZB用户下视图ZB_DEV_M_CARD_02_RPT_VIEW。
select dbms_metadata.get_ddl('INDEX','IND_ZB_DEV_M_CARD_02_RPT,'ZB') from dual ; 表示ZB用户下索引IND_ZB_DEV_M_CARD_02_RPT。
相信以上脚本已经可以满足文章开始的需求。在配合ORACLE 的SPOOL就可以导出一个SQL文件,这样里面的东西你就可以随便编译了,例如指定表空间。
SPOOL脚本如下:
set pagesize 0
set long 90000
set feedback off
set echo off
spool F:\DEPT.sql
select dbms_metadata.get_ddl('TABLE','ZB_DEV_M_CARD_02_RPT','ZB') from dual
select dbms_metadata.get_ddl('VIEW','ZB_DEV_M_CARD_02_RPT_VIEW,'ZB') from dual ;
select dbms_metadata.get_ddl('INDEX','IND_ZB_DEV_M_CARD_02_RPT,'ZB') from dual;
spool off;
以下扩展学习补充:
获取一个SCHEMA下的所有建表、视图和建索引的语法,以scott为例:
SELECT DBMS_METADATA.GET_DDL('TABLE',A.table_name) FROM USER_TABLES A;
SELECT DBMS_METADATA.GET_DDL('VIEW',A.VIEW_name) FROM USER_VIEWS A;
SELECT DBMS_METADATA.GET_DDL('INDEX',A.index_name) FROM USER_INDEXES A;
获取某个SCHEMA的全部过程的语法
select DBMS_METADATA.GET_DDL('PROCEDURE',A.object_name) from user_objects A where object_type = 'PROCEDURE';
当object_type='FUNCTION' 就是获取某个SCHEMA的建全部函数的语法
重点说明下:DBMS_METADATA.GET_DDL()包()内的参数都要大写。
如果你遇到以下错误:那就需要重点检查下()的参数是否有小写。
ERROR:
ORA-31600: invalid input value table for parameter OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 2682
ORA-06512: at "SYS.DBMS_METADATA", line 2733
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1