-----------------------------------------------------------------------------------
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
|
