要求:使用DBMS_METADATA.GET_DDL查看该用户下所有表和视图的创建语句
set pagesize 0
set long 900000000
set linesize 32767
set feedback off
set echo off
--清空多余的空格,如:linesize过长
DECLARE
CON_OBJECT_TYPE VARCHAR2(20) := UPPER('&OBJECT_TYPE');
CON_SCHEMA VARCHAR2(20) := UPPER('&USER_NAME');
CON_DDL_TXT CLOB;
CURSOR C_TAB_NAME IS SELECT TABLE_NAME
FROM DBA_TABLES
WHERE OWNER=CON_SCHEMA;
C_RES VARCHAR2(30);
BEGIN
OPEN C_TAB_NAME;
loop
FETCH C_TAB_NAME INTO C_RES ;
EXIT WHEN C_TAB_NAME%notfound;
SELECT T.TXT || ' ;' INTO CON_DDL_TXT
FROM (SELECT DBMS_METADATA.GET_DDL(CON_OBJECT_TYPE,C_RES,CON_SCHEMA) as txt
from dual ) t;
DBMS_OUTPUT.put_line( '--'||C_RES);
DBMS_OUTPUT.put_line( CON_DDL_TXT);
end loop;
CLOSE C_TAB_NAME;
END;
使用时,需要输入两个参数:1、用户名,2、对象名。可以使用spool的方式将结果导出来