oracle中擷取資料表空間ddl語句

來源:互聯網
上載者:User
oracle|語句 -----------------------------------------------------------------------------------
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


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。