使用DBMS_METADATA.GET_DDL备份DDL

最新推荐文章于 2023-02-01 13:49:53 发布

ZhongWeicheng

最新推荐文章于 2023-02-01 13:49:53 发布

阅读量124

点赞数

本文链接：https://blog.csdn.net/staricqxyz/article/details/17512183

版权

SQL> CREATE OR REPLACE FUNCTION SYS.F_GET_DDL (v_object_type   varchar2,
  2                                            v_object_name   varchar2,
  3                                            v_object_owner  varchar2) return CLOB is
  4  v_ddl_clob   clob;
  5  BEGIN
  6    v_ddl_clob := DBMS_METADATA.GET_DDL (v_object_type, v_object_name, v_object_owner);
  7    RETURN v_ddl_clob;
  8  END;
  9  /

Function created.

SQL> grant EXECUTE ON SYS.F_GET_DDL TO PUBLIC;

Grant succeeded.

CREATE OR REPLACE PROCEDURE ZWC.SP_BACKUP_DDL is

  v_ddl_clob           clob;
  v_object_name        varchar2(128);
  v_object_id          number;
  v_object_type        varchar2(18);
  v_object_type_ddl    varchar2(128);
  v_status             varchar2(60);
  v_owner              varchar2(30);
  v_created            date;
  v_last_ddl_time      date;
  v_backup_create_dttm date := sysdate;
  v_instance_nm        varchar2(128) := sys_context('USERENV',
                                                    'DB_UNIQUE_NAME');
  v_sql_to_execute     varchar2(32000);

  TYPE cur_typ IS REF CURSOR;
  c1 cur_typ;

BEGIN

  v_sql_to_execute := ' SELECT OBJECT_NAME,                                                                    ' ||
                      '        OBJECT_ID,                                                                      ' ||
                      '        OBJECT_TYPE,                                                                    ' ||
                      '        CASE WHEN OBJECT_TYPE = `DATABASE LINK`          THEN `DB_LINK`                 ' ||
                      '             WHEN OBJECT_TYPE = `MATERIALIZED VIEW`      THEN `MATERIALIZED_VIEW`       ' ||
                      '             WHEN OBJECT_TYPE = `MATERIALIZED VIEW LOG`  THEN `MATERIALIZED_VIEW_LOG`   ' ||
                      '             WHEN OBJECT_TYPE = `JAVA SOURCE`            THEN `JAVA_SOURCE`             ' ||
                      '             ELSE OBJECT_TYPE END OBJECT_TYPE_DDL,                                      ' ||
                      '        STATUS,                                                                         ' ||
                      '        OWNER,                                                                          ' ||
                      '        CREATED,                                                                        ' ||
                      '        LAST_DDL_TIME                                                                   ' ||
                      '   FROM SYS.DBA_OBJECTS                                                                 ' ||
                      '  WHERE (OBJECT_TYPE IN (`TABLE`, `INDEX`, `DATABASE LINK`, `VIEW`, `FUNCTION`,         ' ||
                      '                         `JAVA SOURCE`, `PACKAGE`, `PROCEDURE`, `SEQUENCE`, `SYNONYM`,  ' ||
                      '                         `TRIGGER`, `MATERIALIZED VIEW`, `MATERIALIZED VIEW LOG`,       ' ||
                      '                         `LIBRARY`, `TYPE`, `DIRECTORY`)                                ' ||
                      '          AND OWNER NOT IN (`SYS`,`PUBLIC`,`SYSTEM`,`OUTLN`,`WMSYS`))                   ' ||
                      '     OR  (OBJECT_TYPE IN (`DATABASE LINK`, `DIRECTORY`) AND OWNER IN (`SYS`,`PUBLIC`))  ';

  v_sql_to_execute := replace(v_sql_to_execute, '`', CHR(39));

  OPEN c1 FOR v_sql_to_execute;
  LOOP
    FETCH c1
      INTO v_object_name,
           v_object_id,
           v_object_type,
           v_object_type_ddl,
           v_status,
           v_owner,
           v_created,
           v_last_ddl_time;
    EXIT WHEN c1%NOTFOUND;
  
    BEGIN
    
      v_ddl_clob := SYS.F_GET_DDL(v_object_type_ddl,
                                  v_object_name,
                                  CASE
                                    WHEN v_object_type_ddl = 'DIRECTORY' then
                                     null
                                    else
                                     v_owner
                                  END);
    
    EXCEPTION
      WHEN OTHERS THEN
        v_ddl_clob := 'DDL not available';
    END;
  
    INSERT INTO ZWC.BACKUP_DDL
      (BACKUP_CREATE_DTTM,
       INSTANCE_NM,
       OBJECT_ID,
       OBJECT_OWNER,
       OBJECT_NM,
       OBJECT_TYPE,
       OBJECT_STATUS,
       OBJECT_DDL,
       OBJECT_CREATE_DTTM,
       OBJECT_UPDATE_DTTM)
    VALUES
      (v_backup_create_dttm,
       v_instance_nm,
       v_object_id,
       v_owner,
       v_object_name,
       v_object_type,
       v_status,
       v_ddl_clob,
       v_created,
       v_last_ddl_time);
    COMMIT;
  END LOOP;
END;