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
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
