oracle中获取表空间ddl语句

最新推荐文章于 2021-04-05 19:22:19 发布

原创最新推荐文章于 2021-04-05 19:22:19 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #logging #存储 #system #insert #table

oracle 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一个Oracle存储过程，用于自动生成数据库中所有表空间的创建DDL语句。该过程区分了不同类型表空间（如临时、回退和永久表空间）并相应地调整DDL输出。

参考:

http://www.cndw.com/tech/data/2006051167313.asp

代码:

表结构:

-- Create table
create table BAK_DBA_TABLESAPCE
(
DDL_TXT VARCHAR2(2000)
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);

存储过程

create or replace procedure get_tabspace_ddl
is
type r_curdf is ref cursor;
v_tpname varchar2(30);

cursor v_curtp is select * from dba_tablespaces;
v_curdf r_curdf;

v_ddl varchar2(2000);
v_txt varchar2(2000);
v_txt1 varchar2(2000);
v_tp dba_tablespaces%rowtype;
v_df dba_data_files%rowtype;
v_count number;
begin

    OPEN V_CURTP;

    LOOP
    --<<aa>>
    FETCH v_curtp INTO v_tp;
    EXIT WHEN v_CURtp%NOTFOUND;

    V_TPNAME:=v_TP.tablespace_name;

    IF v_tp.CONTENTS='TEMPORARY' THEN ---临时表空间
        --DBMS_OUTPUT.PUT_LINE('CREATE TEMPORARY TABLESPACE '||v_tp.tablespace_name||' DATAFILE ');
        v_txt:='CREATE SMALLFILE TEMPORARY TABLESPACE '||v_tp.tablespace_name||' DATAFILE ';
        --insert into bak_dba_tablesapce(ddl_txt) values(v_txt);

        SELECT COUNT(*) INTO v_count ---获得游标v_curtp指向的当前表空间包含的临时数据文件数
        FROM DBA_TEMP_FILES
        WHERE tablespace_name=v_tp.tablespace_name;

    ELSIF v_tp.CONTENTS='UNDO' THEN ---回退表空间
        -- DBMS_OUTPUT.PUT_LINE('CREATE UNDO TABLESPACE '||v_tp.tablespace_name||' DATAFILE ');
        v_txt:='CREATE SMALLFILE UNDO TABLESPACE '||v_tp.tablespace_name||' DATAFILE ';
        --insert into bak_dba_tablesapce(ddl_txt) values(v_txt);

        SELECT COUNT(*) INTO v_count ---获得游标v_curtp指向的当前表空间包含的数据文件数
        FROM DBA_DATA_FILES
        WHERE tablespace_name=v_tp.tablespace_name;

    ELSIF v_tp.CONTENTS='PERMANENT' THEN ---普通表空间
      v_txt:='CREATE SMALLFILE TABLESPACE '||v_tp.tablespace_name||' DATAFILE ';
      --insert into bak_dba_tablesapce(ddl_txt) values(v_txt);

    END IF;

    --if v_tp.CONTENTS='TEMPORARY' THEN ----临时数据文件
    --OPEN V_CURDF for select * from dba_temp_files where tablespace_name=v_tpname;
    --goto aa;
    --else
    OPEN V_CURDF for select * from dba_data_files where tablespace_name=v_tpname;
    --end if;

    LOOP
    FETCH v_curdf INTO v_df; ---获取DATAFILE定义
    EXIT WHEN v_CURdf%NOTFOUND;

    --v_txt:= v_txt|| '''' || V_DF.file_name || ''''

    IF V_DF.AUTOEXTENSIBLE='YES' THEN
    V_DDL:='AUTOEXTEND ON NEXT '||(V_DF.INCREMENT_BY*8/1024)||'M ';
    ELSE
    V_DDL:='AUTOEXTEND OFF';
    END IF;

    IF v_curdf%rowcount=v_count THEN
        v_txt:= v_txt || ''''||v_df.file_name||''''||' SIZE '||(V_DF.BLOCKS*8/1024)||'M REUSE ';
        IF V_DF.AUTOEXTENSIBLE='YES' THEN
             v_txt:= v_txt ||V_DDL || ' MAXSIZE 32767M ';

             IF v_tp.LOGGING='LOGGING' THEN
                 v_txt:= v_txt || 'LOGGING';
             END IF;
             IF v_tp.CONTENTS='UNDO' THEN ---回退表空间存储参数
                   v_txt1:= '';
            ELSIF v_tp.CONTENTS='PERMANENT' THEN ---普通表空间存储参数
                v_txt:= v_txt || ' EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
            ELSIF v_tp.ALLOCATION_TYPE='UNIFORM' THEN ----统一分区尺寸
               v_txt:= v_txt || ' EXTENT MANAGEMENT '||V_TP.EXTENT_MANAGEMENT||' UNIFORM SIZE '||v_tp.INITIAL_EXTENT/(1024*1024)||'M';
            ELSIF v_tp.ALLOCATION_TYPE='SYSTEM' THEN ----系统自动管理分区尺寸
                v_txt:= v_txt || ' EXTENT MANAGEMENT '||V_TP.EXTENT_MANAGEMENT||' AUTOALLOCATE ' ;
            END IF;

            --IF v_tp.SEGMENT_SPACE_MANAGEMENT='AUTO' THEN ----系统自动管理段空间
            --insert into bak_dba_tablesapce(ddl_txt) values('SEGMENT SPACE MANAGEMENT AUTO');
            --END IF;
      END IF;
              --insert into bak_dba_tablesapce(ddl_txt) values(v_txt);

    ELSE
        v_txt:= v_txt || ''''||v_df.file_name||''''||' SIZE '||(V_DF.BLOCKS*8/1024)||'M REUSE ';
        IF V_DF.AUTOEXTENSIBLE='YES' THEN
           --v_txt:= v_txt ||V_DDL||' MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO , ';

           v_txt:= v_txt ||V_DDL || ' MAXSIZE 32767M ';

             IF v_tp.LOGGING='LOGGING' THEN
                 v_txt:= v_txt || 'LOGGING';
             END IF;
             IF v_tp.CONTENTS='UNDO' THEN ---回退表空间存储参数
                   v_txt1:= '';
            ELSIF v_tp.CONTENTS='PERMANENT' THEN ---普通表空间存储参数
                v_txt:= v_txt || ' EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';

            ELSIF v_tp.ALLOCATION_TYPE='UNIFORM' THEN ----统一分区尺寸
               v_txt:= v_txt || ' EXTENT MANAGEMENT '||V_TP.EXTENT_MANAGEMENT||' UNIFORM SIZE '||v_tp.INITIAL_EXTENT/(1024*1024)||'M';
            ELSIF v_tp.ALLOCATION_TYPE='SYSTEM' THEN ----系统自动管理分区尺寸
                v_txt:= v_txt || ' EXTENT MANAGEMENT '||V_TP.EXTENT_MANAGEMENT||' AUTOALLOCATE ' ;
            END IF;

            --IF v_tp.SEGMENT_SPACE_MANAGEMENT='AUTO' THEN ----系统自动管理段空间
            --insert into bak_dba_tablesapce(ddl_txt) values('SEGMENT SPACE MANAGEMENT AUTO');
            --END IF;

        END IF;
        --insert into bak_dba_tablesapce(ddl_txt) values(v_txt);

        IF v_curdf%rowcount > 1 THEN
          v_txt:= v_txt || ' , ' ;
        END IF;
    END IF;

    END LOOP;
    CLOSE V_CURDF;
    v_txt:= v_txt || '; ' ;
    insert into bak_dba_tablesapce(ddl_txt) values(v_txt);

    END LOOP;
    CLOSE V_CURTP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      if v_curtp%isopen then
      close v_curtp;
      if v_curdf%isopen then
      close v_curdf;
      end if;
      end if;
      ROLLBACK;
      RAISE;
END get_tabspace_ddl;