Oracle表空間利用率不足的處理流程

更新時(shí)間：2024年06月12日 10:31:32 作者：lu9up的數(shù)據(jù)庫(kù)筆記

在生產(chǎn)環(huán)境中,一般設(shè)置表空間告警閾值是90%,在接到監(jiān)控報(bào)警后,并不是需要立刻對(duì)表空間進(jìn)行擴(kuò)容,本文給大家介紹了Oracle表空間利用率不足的處理流程,需要的朋友可以參考下

1 前言

在生產(chǎn)環(huán)境中，一般設(shè)置表空間告警閾值是90%，在接到監(jiān)控報(bào)警后，并不是需要立刻對(duì)表空間進(jìn)行擴(kuò)容。

決定是否擴(kuò)容主要看表空間最近的增量是多少，假如剩余10%的空間還能支持1個(gè)月的增量，那就不需要急著擴(kuò)容。如果剩余的空間只能堅(jiān)持幾天，那么最好是立即擴(kuò)容，以防止數(shù)據(jù)突增。

接到告警后，一般工作過(guò)程如下：

查看表空間利用率和剩余容量；
查看表空間增量；
擴(kuò)容或者釋放空間；
找出數(shù)據(jù)增量異常的對(duì)象。

根據(jù)下面的常用sql腳本排查。

2 處理流程

2.1 查看表空間利用率

col tablespace_name for a20
col pct_used for a10
select a.tablespace_name,
	   a.total_mb,
	   a.total_mb - b.free_mb used_mb,
	   b.free_mb,
	   case when a.total_mb <> 0 
	        then round((a.total_mb - b.free_mb) / a.total_mb * 100,2)
			else null 
	   end || '%' pct_used
  from (select ts.tablespace_name,
	   		   round(sum(bytes) / 1024 / 1024,2) total_mb
	      from dba_tablespaces ts,
	   		   dba_data_files df  
	     where ts.tablespace_name = df.tablespace_name
	     group by ts.tablespace_name) a,
	   (select fs.tablespace_name,
	   		   round(sum(bytes) / 1024 /1024,2) free_mb
	      from dba_free_space fs
	     group by fs.tablespace_name) b
 where a.tablespace_name = b.tablespace_name
   and a.tablespace_name = '&tsb_name'
 order by 1;

2.2 查看表空間增量

日增量：

set line 200
col ts_name for a30
col pct_used for a10
SELECT a.snap_id,
       c.tablespace_name ts_name,
       to_char(to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd hh24:mi') rtime,
       round(a.tablespace_size * c.block_size / 1024 / 1024, 2) ts_size_mb,
       round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb,
       round((a.tablespace_size - a.tablespace_usedsize) * c.block_size / 1024 / 1024,2) ts_free_mb,
	   round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) - 
	        lag(round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2),1) 
	        over(order by a.tablespace_id,to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss')) inc_mb,
       round(a.tablespace_usedsize / a.tablespace_size * 100, 2) || '%' pct_used
  FROM dba_hist_tbspc_space_usage a, 
       (SELECT tablespace_id,
               substr(rtime, 1, 10) rtime,
               max(snap_id) snap_id
          FROM dba_hist_tbspc_space_usage nb
         group by tablespace_id, substr(rtime, 1, 10)) b,
         dba_tablespaces c,
         v$tablespace d
 where a.snap_id = b.snap_id
   and a.tablespace_id = b.tablespace_id
   and a.tablespace_id=d.TS#
   and d.NAME=c.tablespace_name  
   and d.NAME = '&tbs_name'
   and to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') >=sysdate-30
   order by a.tablespace_id,to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') desc;

累計(jì)增量，根據(jù)awr保留時(shí)間而定，默認(rèn)為8天：

set line 200
col ts_name for a30
col pct_used for a10
with ts as(
	SELECT a.snap_id,
		   c.tablespace_name ts_name,
		   to_char(to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd hh24:mi') rtime,
		   round(a.tablespace_size * c.block_size / 1024 / 1024, 2) ts_mb,
		   round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb,
		   round((a.tablespace_size - a.tablespace_usedsize) * c.block_size / 1024 / 1024,2) ts_free_mb,
		   round(a.tablespace_usedsize / a.tablespace_size * 100, 2) || '%' pct_used
	  FROM dba_hist_tbspc_space_usage a, 
		   (SELECT tablespace_id,
				   substr(rtime, 1, 10) rtime,
				   max(snap_id) snap_id
			  FROM dba_hist_tbspc_space_usage nb
			 group by tablespace_id, substr(rtime, 1, 10)) b,
			 dba_tablespaces c,
			 v$tablespace d
	 where a.snap_id = b.snap_id
	   and a.tablespace_id = b.tablespace_id
	   and a.tablespace_id=d.TS#
	   and d.NAME=c.tablespace_name
	   and to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') >=sysdate-30)
select f.ts_name,f.ts_mb,f.ts_used_mb begin_used_mb,f.rtime begin_time,
	   t.ts_used_mb end_used_mb,t.rtime end_time,t.ts_used_mb - f.ts_used_mb inc_mb,
	   round(to_date(t.rtime,'yyyy-mm-dd hh24:mi:ss') - to_date(f.rtime,'yyyy-mm-dd hh24:mi:ss'),2) inc_days
  from (select a.*,row_number()over(partition by a.ts_name order by a.snap_id desc) rn
		  from ts a) t,  
       (select b.*,row_number()over(partition by b.ts_name order by b.snap_id) rn
          from ts b) f
 where t.rn = 1 and f.rn = 1 
   and t.ts_name = f.ts_name
   and t.ts_name = '&ts_name';

根據(jù)上述查出來(lái)的表空間日增量和累計(jì)增量結(jié)果，可以大概估算出剩余的空間可以堅(jiān)持多久，根據(jù)實(shí)際情況決定是否擴(kuò)容。

2.3 查看數(shù)據(jù)文件路徑

此步驟主要是查看表空間數(shù)據(jù)文件路徑，為表空間擴(kuò)容添加數(shù)據(jù)文件做好環(huán)境調(diào)研。

set lines 200
set pagesize 300
col file_name for a60
col size_mb for 999999.99
select * from (
 select file_name,file_id,tablespace_name,round(bytes / 1024 / 1024,2) size_mb,status,autoextensible
   from dba_data_files
  where tablespace_name = '&ts_name'
  order by 2 desc)
  where rownum <= 10;

3 表空間擴(kuò)容

表空間擴(kuò)容可以選擇添加數(shù)據(jù)文件，或者拓展數(shù)據(jù)文件。

3.1 添加數(shù)據(jù)文件

添加數(shù)據(jù)文件的時(shí)候一定要注意：

在RAC集群環(huán)境中，切記不要將數(shù)據(jù)文件創(chuàng)建到本地，這樣就會(huì)造成集群節(jié)點(diǎn)間的不一致，可能會(huì)導(dǎo)致其他節(jié)點(diǎn)起不來(lái)。
也不要將數(shù)據(jù)文件創(chuàng)建到其他磁盤(pán)組中，這樣不夠規(guī)范。

以表空間ts_test為例：

--ASM:
SQL> alter tablespace ts_test add datafile '+DATA' size 100M;

--File System:
SQL> alter tablespace ts_test datafile '/u01/app/oracle/oradata/datafile/ts_test02.dbf' size 100M;

3.2 拓展數(shù)據(jù)文件

假設(shè)原來(lái)ts_test.274.1171146701大小為100M，我們可以將其拓展到200M以達(dá)到擴(kuò)容的目的：

alter database datafile'+DATA/orcl/datafile/ts_test.274.1171146701' resize 200M;

3.3 擴(kuò)容后檢查

擴(kuò)容后需要檢查表空間使用率是否下降：

col tablespace_name for a20
col pct_used for a10
select a.tablespace_name,
	   a.total_mb,
	   a.total_mb - b.free_mb used_mb,
	   b.free_mb,
	   case when a.total_mb <> 0 
	        then round((a.total_mb - b.free_mb) / a.total_mb * 100,2)
			else null 
	   end || '%' pct_used
  from (select ts.tablespace_name,
	   		   round(sum(bytes) / 1024 / 1024,2) total_mb
	      from dba_tablespaces ts,
	   		   dba_data_files df  
	     where ts.tablespace_name = df.tablespace_name
	     group by ts.tablespace_name) a,
	   (select fs.tablespace_name,
	   		   round(sum(bytes) / 1024 /1024,2) free_mb
	      from dba_free_space fs
	     group by fs.tablespace_name) b
 where a.tablespace_name = b.tablespace_name
   and a.tablespace_name = '&tsb_name'
 order by 1;

4 后續(xù)排查

如果表空間時(shí)短時(shí)間內(nèi)激增，則在擴(kuò)容后還需要排查，找出是哪個(gè)對(duì)象數(shù)據(jù)突增影響的。

4.1 查看snap_id

set line 200
select distinct snap_id,
to_char(begin_interval_time,‘yyyy-mm-dd hh24:mi:ss') begin_interval_time,
to_char(end_interval_time,‘yyyy-mm-dd hh24:mi:ss') end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,‘yyyy-mm-dd hh24:mi:ss') >=
to_char(sysdate - &day_ago,‘yyyy-mm-dd hh24:mi:ss')
order by snap_id desc;

4.2 查看某個(gè)表空間下增量最多的對(duì)象

set lines 200
col object_name for a30
select * from
(select obj.owner,obj.object_name,sum(hs.db_block_changes_delta) db_block_changes_delta,
round(sum(hs.space_used_delta) / 1024 / 1024,2) space_delta_mb
from dba_hist_seg_stat hs,
v$tablespace ts,
dba_objects obj,
dba_hist_snapshot sn
where hs.ts# = ts.ts#
and hs.snap_id = sn.snap_id
and hs.obj# = obj.object_id
and ts.name = ‘&tbs_name'
and sn.begin_interval_time >= sysdate - &day_ago
group by obj.owner,obj.object_name
order by space_delta_mb desc)
where rownum <= 10;

以上就是Oracle表空間利用率不足的處理流程的詳細(xì)內(nèi)容，更多關(guān)于Oracle表空間利用率不足的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: