DBA常用SQL之数据库系统架构

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
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，如 果我们稍封装一下，将是非常好用的一个东西。

/* Formatted on 2008/10/15 16:16 (Formatter Plus v4.8.0) */
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

7、数据库的常规参数我就不说了，除了V$parameter中的常规参数外，ORACLE还有大量的隐含参数，下面的语句就可以查询到数据库的所 有隐含参数以及其值与参数的描述。

SELECT NAME
,VALUE
,decode(isdefault, 'TRUE','Y','N') as "Default"
,decode(ISEM,'TRUE','Y','N') as SesMod
,decode(ISYM,'IMMEDIATE', 'I',
'DEFERRED', 'D',
'FALSE', 'N') as SysMod
,decode(IMOD,'MODIFIED','U',
'SYS_MODIFIED','S','N') as Modified
,decode(IADJ,'TRUE','Y','N') as Adjusted
,description
FROM ( --GV$SYSTEM_PARAMETER
SELECT x.inst_id as instance
,x.indx+1
,ksppinm as NAME
,ksppity
,ksppstvl as VALUE
,ksppstdf as isdefault
,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM
,decode(bitand(ksppiflg/65536,3),
1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM
,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD
,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ
,ksppdesc as DESCRIPTION
FROM x$ksppi x
,x$ksppsv y
WHERE x.indx = y.indx
AND substr(ksppinm,1,1) = '_'
AND x.inst_id = USERENV('Instance')
)
ORDER BY NAME

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 wner = USER
SQL>spool off

9、表的主键是必要的，没有主键的表可以说是不符合设计 规 范的，所以我们需要监控表是否有主键
SELECT table_name
FROM all_tables
WHERE wner = USER
MINUS
SELECT table_name
FROM all_constraints
WHERE wner = USER
AND constraint_type = 'P'