Oracle DBA常用SQL语句系列
时间：2025-04-23
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 --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,
Oracle DBA常用SQL语句系列.doc
将本文的Word文档下载到电脑
