oracle中获取表空间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;