oracle 索引触发器,如何获取Oracle数据库中某个用户下的所有表、索引、约束、触发器...

在Oracle数据库中，如何快速获取某个用户下的所有表、索引、约束及触发器的创建SQL语句呢？本文给出一个简单的示例脚本，经过测试，除位于回收站下的对象及只读视图的约束语句不能获取，其它对象基本均可获取CREATE语句。

脚本内容如下：

[oracle@oracledg ~]$ cat get_ddl.sql

-- Be sure the variable 'S' means the Schema name,it should be UPPERCASE.

-- And this script COULDNOT get the constraints CREATE DDL,which CONSTRAINT_TYPE is 'O',cause it's READ ONLY on a view.

def S='&Schema'

set serveroutput on

set long 20000

set lines 2000 pages 0 ver off echo off head off feed off

set newpage none

set trimspool on

set long 5000000

spool &S..sql

DECLARE

CURSOR C1 IS

SELECT '&S' AS u_name, table_name AS tab_name FROM user_tables;

CURSOR C2 IS

SELECT '&S' AS u_name, index_name AS idx_name FROM user_indexes;

CURSOR C3 IS

SELECT '&S' AS u_name, constraint_name AS cons_name FROM user_constraints where constraint_type not in('R','O');

CURSOR C4 IS

SELECT '&S' AS u_name, table_name AS tab_name FROM user_constraints where constraint_type in('R');

CURSOR C5 IS

SELECT '&S' AS u_name, trigger_name AS trig_name FROM user_triggers;

V_MSG CLOB;

BEGIN

DBMS_OUTPUT.put_line('TheCREATEDDLforschema&S''s Tables are:');

FOR x IN C1 LOOP

select dbms_metadata.get_ddl('TABLE', x.tab_name, x.u_name) INTO V_MSG from dual;

DBMS_OUTPUT.put_line(V_MSG);

END LOOP;

DBMS_OUTPUT.put_line('TheCREATEDDLforschema&S''s Indexes are:');

FOR x IN C2 LOOP

select dbms_metadata.get_ddl('INDEX', x.idx_name, x.u_name) INTO V_MSG from dual;

DBMS_OUTPUT.put_line(V_MSG);

END LOOP;

DBMS_OUTPUT.put_line('TheCREATEDDLforschema&S''s Constraints are:');

FOR x IN C3 LOOP

select dbms_metadata.get_ddl('CONSTRAINT', x.cons_name, x.u_name) INTO V_MSG from dual;

DBMS_OUTPUT.put_line(V_MSG);

END LOOP;

DBMS_OUTPUT.put_line('TheCREATEDDLforschema&S''s Reference Constraints are:');

FOR x IN C4 LOOP

select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', x.tab_name, x.u_name) INTO V_MSG from dual;

DBMS_OUTPUT.put_line(V_MSG);

END LOOP;

DBMS_OUTPUT.put_line('TheCREATEDDLforschema&S''s Triggers are:');

FOR x IN C5 LOOP

select dbms_metadata.get_ddl('TRIGGER', x.trig_name, x.u_name)INTOV_MSGfromdual;

DBMS_OUTPUT.put_line(V_MSG);

ENDLOOP;

END;

/

spooloff;

[oracle@oracledg ~]$