写了个存储过程获得table的完整定义(转载)

包括create table, comment, index, trigger, object grant.不知道还缺啥？

我放在了自己常用的一个package里（dbms_ut 包，包括常用脚本）。

过程如下：

    PROCEDURE get_table_ddl(
        p_table_name IN VARCHAR2,
        p_owner IN VARCHAR2 DEFAULT USER,
        p_storage IN BOOLEAN DEFAULT TRUE,
        p_segment_attributes IN BOOLEAN DEFAULT TRUE,
        p_ref_constraints IN BOOLEAN DEFAULT FALSE,
        p_constraints_as_alter IN BOOLEAN DEFAULT FALSE
    )
    IS
        v_temp VARCHAR2(1024) := '';
        v_table_ddl CLOB := '';
        v_comment_ddl CLOB := '';
        v_constraint_ddl CLOB := '';
        v_ref_constraint_ddl CLOB := '';
        v_index_ddl CLOB := '';
        v_trigger_ddl CLOB := '';
        v_object_grant_ddl CLOB := '';
    BEGIN
        --输出信息采用缩排或换行格式化
        DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'PRETTY', FALSE);
        --确保每个语句都带分号
        DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SQLTERMINATOR', TRUE);
        DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'TABLESPACE', TRUE);
        DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'CONSTRAINTS', TRUE);
        DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'REF_CONSTRAINTS',
            p_ref_constraints);
        DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER',
            p_constraints_as_alter);
        DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'STORAGE', p_storage);
        DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES',
            p_segment_attributes);

        --get table ddl
        BEGIN
            SELECT REPLACE(DBMS_METADATA.get_ddl('TABLE', UPPER(p_table_name), UPPER(p_owner)), '"', '')
              INTO v_table_ddl
              FROM DUAL;

            put_line(v_table_ddl, TRUE, FALSE);
        EXCEPTION
            WHEN OTHERS THEN
                put_line('--no object:' || p_table_name);
                v_temp := SQLERRM;
                put_line(v_temp);
                RETURN;
        END;

        --get comment ddl
        BEGIN
            SELECT DBMS_METADATA.get_dependent_ddl('COMMENT', UPPER(p_table_name), UPPER(p_owner))
              INTO v_comment_ddl
              FROM DUAL;

            put_line(v_comment_ddl, TRUE, FALSE);
        EXCEPTION
            WHEN e_ora_31608 THEN
                put_line('--no comment');
            WHEN OTHERS THEN
                put_line('--no comment');
                put_line(SQLERRM);
        END;

        --get constraint ddl
        BEGIN
            SELECT DBMS_METADATA.get_dependent_ddl('CONSTRAINT', UPPER(p_table_name), UPPER(p_owner))
              INTO v_constraint_ddl
              FROM DUAL;

            put_line(v_constraint_ddl, TRUE, FALSE);

            SELECT DBMS_METADATA.get_dependent_ddl('REF_CONSTRAINT', UPPER(p_table_name), UPPER(p_owner))
              INTO v_ref_constraint_ddl
              FROM DUAL;

            put_line(v_ref_constraint_ddl, TRUE, FALSE);
        EXCEPTION
            WHEN e_ora_31608 THEN
                put_line('--no constraint');
            WHEN OTHERS THEN
                put_line('--no constraint');
                put_line(SQLERRM);
        END;

        --get index ddl
        BEGIN
            SELECT REPLACE(
                       DBMS_METADATA.get_dependent_ddl('INDEX', UPPER(p_table_name), UPPER(p_owner)),
                       '"',
                       ''
                   )
              INTO v_index_ddl
              FROM DUAL;

            put_line(v_index_ddl, TRUE, FALSE);
        EXCEPTION
            WHEN e_ora_31608 THEN
                put_line('--no index');
            WHEN OTHERS THEN
                put_line('--no index');
                put_line(SQLERRM);
        END;

        -- get trigger ddl
        BEGIN
            SELECT DBMS_METADATA.get_dependent_ddl('TRIGGER', UPPER(p_table_name), UPPER(p_owner))
              INTO v_trigger_ddl
              FROM DUAL;

            put_line(v_trigger_ddl, TRUE, FALSE);
        EXCEPTION
            WHEN e_ora_31608 THEN
                put_line('--no trigger');
            WHEN OTHERS THEN
                put_line('--no trigger');
                put_line(SQLERRM);
        END;

        -- get object grant ddl
        BEGIN
            SELECT REPLACE(
                       DBMS_METADATA.get_dependent_ddl('OBJECT_GRANT', UPPER(p_table_name), UPPER(p_owner)),
                       '"',
                       ''
                   )
              INTO v_object_grant_ddl
              FROM DUAL;

            put_line(v_object_grant_ddl, TRUE, TRUE);
        EXCEPTION
            WHEN e_ora_31608 THEN
                put_line('--no object grant');
            WHEN OTHERS THEN
                put_line('--no object grant');
                put_line(SQLERRM);
        END;
    END;

测试一下：

先建立测试表：

SQL> CREATE TABLE tab_test2 (col2 VARCHAR2(10) PRIMARY KEY);

Table created.

SQL> CREATE TABLE tab_test
2     (
3     col1 NUMBER(10) CONSTRAINT pk_test PRIMARY KEY,
4     col2 VARCHAR2(10) CONSTRAINT fk_test REFERENCES tab_test2 (col2),
5     col3 VARCHAR2(10) DEFAULT 'test'
6     );

Table created.

SQL> COMMENT ON TABLE tab_test IS 'table test';

Comment created.

SQL> COMMENT ON COLUMN tab_test.col1 IS 'table test col1';

Comment created.

SQL> COMMENT ON COLUMN tab_test.col2 IS 'table test col2';

Comment created.

SQL> CREATE INDEX idx_test ON tab_test(col2,col3);

Index created.

SQL> CREATE OR REPLACE TRIGGER trg_test
2         AFTER INSERT OR UPDATE OR DELETE
3         ON tab_test
4         FOR EACH ROW
5     BEGIN
6         DBMS_OUTPUT.put_line('heihei!');
7     END;
8     /

Trigger created.

SQL> GRANT SELECT,UPDATE ON tab_test TO utcore;

Grant succeeded.

执行过程获得table的完整定义：

SQL> set serverout on
SQL> exec dbms_ut.get_table_ddl('tab_test');
------------------------------------------------------------

CREATE TABLE WACOS.TAB_TEST (COL1 NUMBER(10,0), COL2 VARCHAR2(10), COL3
VARCHAR2(10) DEFAULT 'test', CONSTRAINT PK_TEST PRIMARY KEY (COL1) USING INDEX
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 21474
83645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE WACOS ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS
1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE WACOS ;

------------------------------------------------------------

   COMMENT ON COLUMN "WACOS"."TAB_TEST"."COL1" IS 'table test col1';

COMMENT ON COLUMN "WACOS"."TAB_TEST"."COL2" IS 'table test col2';

   COMMENT
ON TABLE "WACOS"."TAB_TEST" IS 'table test';

------------------------------------------------------------

ALTER TABLE "WACOS"."TAB_TEST" ADD CONSTRAINT "PK_TEST" PRIMARY KEY ("COL1")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT
1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL D
EFAULT) TABLESPACE "WACOS" ENABLE;

------------------------------------------------------------

ALTER TABLE "WACOS"."TAB_TEST" ADD CONSTRAINT "FK_TEST" FOREIGN KEY ("COL2")
REFERENCES "WACOS"."TAB_TEST2" ("COL2") ENABLE;

------------------------------------------------------------

CREATE INDEX WACOS.IDX_TEST ON WACOS.TAB_TEST (COL2, COL3) PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE WACOS ;

CREATE UNIQUE INDEX WACOS.PK_TEST ON WACOS.TAB_TEST (COL1) PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE WACOS ;

------------------------------------------------------------

CREATE OR REPLACE TRIGGER "WACOS"."TRG_TEST" 
            AFTER INSERT OR UPDATE OR
DELETE
            ON tab_test
            FOR EACH ROW
        BEGIN
        
DBMS_OUTPUT.put_line('heihei!');
        END;
/
ALTER TRIGGER "WACOS"."TRG_TEST"
ENABLE;

------------------------------------------------------------

GRANT SELECT ON WACOS.TAB_TEST TO UTCORE;

GRANT UPDATE ON WACOS.TAB_TEST
TO UTCORE;

------------------------------------------------------------

PL/SQL procedure successfully completed.

SQL>

转载自：http://blog.youkuaiyun.com/cunxiyuan108/article/details/12280929 如有侵权请联系博主