获取表空间ddl语句

欧阳不修

-----------------------------------------------------------------------------------
create table
-----------------------------------------------------------------------------------
create table bak_dba_tablesapce
        (ddl_txt varchar2(2000));

-----------------------------------------------------------------------------------
procedure
-----------------------------------------------------------------------------------

create or replace procedure get_tabspace_ddl as
   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_tp    dba_tablespaces%rowtype;
  v_df    dba_data_files%rowtype;
  v_count number;
begin
   
  OPEN V_CURTP;
      
  LOOP
    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 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 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 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;
    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;
      
       IF  V_DF.AUTOEXTENSIBLE='YES' THEN
         V_DDL:='ON';
       ELSE
         V_DDL:='OFF';
       END IF;
      
       IF  v_curdf%rowcount=v_count THEN        
           v_txt:=''''||v_df.file_name||''''||' SIZE '||(V_DF.BLOCKS*8/1024)||'M AUTOEXTEND '||V_DDL;
           insert into  bak_dba_tablesapce(ddl_txt) values(v_txt);
        
       ELSE
           v_txt:=''''||v_df.file_name||''''||' SIZE '||(V_DF.BLOCKS*8/1024)||'M AUTOEXTEND '||V_DDL||',';
           insert into  bak_dba_tablesapce(ddl_txt) values(v_txt);
        
       END IF;  
                 
    END LOOP;               
    CLOSE V_CURDF;
        
    IF v_tp.CONTENTS='UNDO' THEN         ---回退表空间存储参数        
          insert into  bak_dba_tablesapce(ddl_txt) values(V_TP.STATUS);
      
    ELSE                              ---普通表空间、临时表空间存储参数        
       IF v_tp.CONTENTS='PERMANENT' THEN    ---普通表空间存储参数      
          insert into  bak_dba_tablesapce(ddl_txt) values(V_TP.LOGGING);
          insert into  bak_dba_tablesapce(ddl_txt) values(V_TP.STATUS);      
          insert into  bak_dba_tablesapce(ddl_txt) values('PERMANENT');
       END IF;   
      
       IF v_tp.ALLOCATION_TYPE='UNIFORM' THEN      ----统一分区尺寸         
          v_txt:='EXTENT MANAGEMENT '||V_TP.EXTENT_MANAGEMENT||' UNIFORM SIZE '||v_tp.INITIAL_EXTENT/(1024*1024)||'M';
          insert into  bak_dba_tablesapce(ddl_txt) values(v_txt);   
                                               
       ELSIF v_tp.ALLOCATION_TYPE='SYSTEM'  THEN   ----系统自动管理分区尺寸
          v_txt:='EXTENT MANAGEMENT '||V_TP.EXTENT_MANAGEMENT||' AUTOALLOCATE ' ;
          insert into  bak_dba_tablesapce(ddl_txt) values(v_txt);
       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;
    v_txt:='BLOCKSIZE '||(V_TP.BLOCK_SIZE/1024)||'K ';
    insert into  bak_dba_tablesapce(ddl_txt) values(v_txt);         
    insert into  bak_dba_tablesapce(ddl_txt) values('/');
    insert into  bak_dba_tablesapce(ddl_txt) values('');
    commit;
  END LOOP;
  CLOSE V_CURTP;
      
  EXCEPTION
    WHEN OTHERS THEN
     if v_curtp%isopen then
       close v_curtp;
       if v_curdf%isopen then
         close v_curdf;
       end if;
     end if;   
      RAISE;      
END get_tabspace_ddl;
---------------------------------------------------------------------
get_tabspace_dll.sh
用于crontab 定时备份数据库表空间的ddl
---------------------------------------------------------------------
#!/bin/ksh
#生成 bill数据库的表空间ddl语句
#每天执行
#获取环境变量
. /oracle/.profile
username=sys
password=aaa123

########
sqlplus username/password<<EOF
---declare var here
begin
        get_tabspace_ddl;
end;
/
exit
/
EOF
if [ $? -ne 0 ];then
  echo "ERROR! execute procedure failed! please check it"   
  #mail ...
  exit 1
fi
sqlplus username/password <<!  
        set pages 0;
        set serveroutput on size 1000000;
        set heading off;
        set feedback off;
        set echo off;

        spool /ora_backup/orasysbak/bill_tabspace_ddl.sql
                select ddl_txt from bak_dba_tablesapce;  
        spool off;
        exit
!
if [ $? -ne 0 ];then
  echo "ERROR! generate tabspace ddl failed! please check it"   
  #mail ...
  exit 1
fi

希望对大家有用，祝大家新年快乐

欧阳不修

这东西是不是一点用都没有啊,失望

arshan

存了先，谢谢

sshd

Nice try, but the following code does not the same and does it much better
-----
set long 4000
set pagesize 0
select dbms_metadata.get_ddl('TABLESPACE', TABLESPACE_NAME)
from dba_tablespaces;
-----

sshd

"does not " -->; "does"