DBA指令碼 (1)

一、資料庫構架體系

1、表空間的監控是一個重要的任務，我們必須時刻關心表空間的設定，是否滿足現在應用的需求，以下的語句可以查詢到表空間的詳細資訊

SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,
MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS,
CONTENTS,LOGGING,
EXTENT_MANAGEMENT, -- Columns not available in v8.0.x
ALLOCATION_TYPE, -- Remove these columns if running
PLUGGED_IN, -- against a v8.0.x database
SEGMENT_SPACE_MANAGEMENT --use only in v9.2.x or later
FROM DBA_TABLESPACES
ORDER BY TABLESPACE_NAME;

2、對於某些資料檔案沒有設定為自動擴充套件的表空間來說，如果表空間滿了，就將意味著資料庫可能會因為沒有空間而停止下來。監控表空間，最主要的就是監控剩餘空間的大小或者是使用率。以下是監控表空間使用率與剩餘空間大小的語句

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

3、除了監控表空間的剩餘空間，有時候我們也有必要了解一下該表空間是否具有自動擴充套件空間的能力，雖然我們建議在生產系統中預先分配空間。以下語句將完成這一功能

SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,
DBA_DATA_FILES D
WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME

4、 我相信使用字典管理的表空間的也不少吧，因為字典管理的表空間中，每個表的下一個區間的大小是不可以預料的，所以我們必須監控那些表在字典管理的表空間中 的下一個區間的分配將會引起效能問題或由於是非擴充套件的表空間而導致系統停止。以下語句檢查那些表的擴充套件將引起表空間的擴充套件。

SELECT A.OWNER,A.TABLE_NAME,A.NEXT_EXTENT,A.TABLESPACE_NAME
FROM ALL_TABLES A,
(SELECT TABLESPACE_NAME, MAX(BYTES) BIG_CHUNK
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME
AND A.NEXT_EXTENT > F.BIG_CHUNK

5、段的佔用空間與區間數也是很需要注意的一個問題，如果一個段的佔用空間太大，或者跨越太多的區間（在字典管理的表空間中，將有嚴重的效能影響），如果段沒有可以再分配的區間，將導致資料庫錯誤。所以，段的大小與區間監控也是一個很重要的工作

SELECT S.OWNER,S.SEGMENT_NAME,S.SEGMENT_TYPE,S.PARTITION_NAME,
ROUND(BYTES/(1024*1024),2) "USED_SPACE(M)",
EXTENTS USED_EXTENTS,S.MAX_EXTENTS,S.BLOCKS ALLOCATED_BLOCKS,
S.BLOCKS USED_BOLCKS,S.PCT_INCREASE,S.NEXT_EXTENT/1024 "NEXT_EXTENT(K)"
FROM DBA_SEGMENTS S
WHERE S.OWNER NOT IN ('SYS','SYSTEM')
ORDER BY Used_Extents DESC

6、物件的空間分配與空間利用情況，除了從各個方面的分析，如分析表，查詢rowid等方法外，其實oracle提供了一個查詢空間的包dbms_space，如果我們稍封裝一下，將是非常好用的一個東西。

CREATE OR REPLACE PROCEDURE show_space
(p_segname in varchar2,
p_type in varchar2 default 'TABLE' ,
p_owner in varchar2 default user)
AS
v_segname varchar2(100);
v_type varchar2(10);
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
PROCEDURE p( p_label in varchar2, p_num in number )
IS
BEGIN
dbms_output.put_line( rpad(p_label,40,'.')|| p_num );
END;
BEGIN
v_segname := upper(p_segname);
v_type := p_type;
if (p_type = 'i' or p_type = 'I') then
v_type := 'INDEX';
end if;
if (p_type = 't' or p_type = 'T') then
v_type := 'TABLE';
end if;
if (p_type = 'c' or p_type = 'C') then
v_type := 'CLUSTER';
end if;
--以下部分不能用於ASSM
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => v_segname,
segment_type => v_type,
freelist_group_id => 0,
free_blks => l_free_blks );
--以上部分不能用於ASSM
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => v_segname,
segment_type => v_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
--顯示結果
p( 'Free Blocks', l_free_blks );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
END;

執行結果將如下所示

SQL> set serveroutput on;
SQL> exec show_space('test');
Free Blocks.............................1
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................6
Unused Bytes............................49152
Last Used Ext FileId....................1
Last Used Ext BlockId...................48521
Last Used Block.........................2
PL/SQL procedure successfully completed

8、資料庫的索引如果有比較頻繁的Delete操作，將可能導致索引產生很多碎片，所以，在有的時候，需要對所有的索引重新REBUILD，以便合併索引塊，減少碎片，提高查詢速度。

SQL> set heading off
SQL> set feedback off
SQL> spool d:index.sql
SQL> SELECT 'alter index ' || index_name || ' rebuild '
||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'
FROM all_indexes
WHERE ( tablespace_name != 'INDEXES'
OR next_extent != ( 256 * 1024 )
)
AND owner = USER
SQL>spool off

這個時候，我們開啟spool出來的檔案，就可以直接執行了。

9、表的主鍵是必要的，沒有主鍵的表可以說是不符合設計規範的，所以我們需要監控表是否有主鍵

SELECT table_name
FROM all_tables
WHERE owner = USER
MINUS
SELECT table_name
FROM all_constraints
WHERE owner = USER
AND constraint_type = 'P'