oracle中提供了系统包来提取对象的DDL语句,使用比较简单,不妨参考[1,2,3]。使用过程中,经常发现调用会出错,具体原因就不深究了,估计是跟数据字典有关系。
- -- 创建临时表,保存对象的ddl语句
- CREATE TABLE lcm_ddl
- (
- owner VARCHAR2(30),
- object_name VARCHAR2(30),
- object_type VARCHAR2(19),
- ddl_stmt CLOB
- );
- --TRUNCATE TABLE lcm_ddl;
- -- 关闭或打开ddl语句中的STORAGE子句
- DECLARE
- v_ddl_stmt CLOB;
- BEGIN
- DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE); ---去除storage等多余参数
- END;
- -- 遍历每个用户对象,生成ddl语句
- DECLARE
- v_ddl_stmt CLOB;
- BEGIN
- FOR cur IN (SELECT *
- FROM ALL_OBJECTS
- WHERE 1 = 1
- AND OWNER = 'TA'
- AND object_name NOT LIKE 'BIN$%') LOOP -- 去除10g回收站内对象
- BEGIN
- v_ddl_stmt := DBMS_METADATA.GET_DDL(cur.object_type, cur.object_name, cur.owner);
- EXCEPTION
- WHEN OTHERS THEN
- v_ddl_stmt := SQLERRM;
- END;
- INSERT INTO lcm_ddl(owner, object_name, object_type, ddl_stmt)
- VALUES (cur.owner, cur.object_name, cur.object_type, v_ddl_stmt);
- END LOOP;
- COMMIT;
- END;
[1] oracle9i在线文档,http://www.dbanotes.net/Oracle/DBMS_METADATA_DDL.htm
[2] Fenng大牛的博客,http://www.dbanotes.net/Oracle/DBMS_METADATA_DDL.htm
[3] huaihe0410博客,http://blog.chinaunix.net/u2/60332/showart.php?id=471335