IT俱乐部 Oracle Oracle表空间利用率不足的处理流程

Oracle表空间利用率不足的处理流程

发布: 2024年 7月 3日 498阅读

1 前言

在生产环境中，一般设置表空间告警阈值是90%，在接到监控报警后，并不是需要立刻对表空间进行扩容。

决定是否扩容主要看表空间最近的增量是多少，假如剩余10%的空间还能支持1个月的增量，那就不需要急着扩容。如果剩余的空间只能坚持几天，那么最好是立即扩容，以防止数据突增。

接到告警后，一般工作过程如下：

  • 查看表空间利用率和剩余容量；
  • 查看表空间增量；
  • 扩容或者释放空间；
  • 找出数据增量异常的对象。

根据下面的常用sql脚本排查。

2 处理流程

2.1 查看表空间利用率

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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 查看表空间增量

日增量：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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;

累计增量，根据awr保留时间而定，默认为8天：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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';

根据上述查出来的表空间日增量和累计增量结果，可以大概估算出剩余的空间可以坚持多久，根据实际情况决定是否扩容。

2.3 查看数据文件路径

此步骤主要是查看表空间数据文件路径，为表空间扩容添加数据文件做好环境调研。

1
2
3
4
5
6
7
8
9
10
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

3 表空间扩容

表空间扩容可以选择添加数据文件，或者拓展数据文件。

3.1 添加数据文件

添加数据文件的时候一定要注意：

  • 在RAC集群环境中，切记不要将数据文件创建到本地，这样就会造成集群节点间的不一致，可能会导致其他节点起不来。
  • 也不要将数据文件创建到其他磁盘组中，这样不够规范。

以表空间ts_test为例：

1
2
3
4
5
--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 拓展数据文件

假设原来ts_test.274.1171146701大小为100M，我们可以将其拓展到200M以达到扩容的目的：

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

3.3 扩容后检查

扩容后需要检查表空间使用率是否下降：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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 后续排查

如果表空间时短时间内激增，则在扩容后还需要排查，找出是哪个对象数据突增影响的。

4.1 查看snap_id

1
2
3
4
5
6
7
8
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 查看某个表空间下增量最多的对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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

以上就是Oracle表空间利用率不足的处理流程的详细内容，更多关于Oracle表空间利用率不足的资料请关注IT俱乐部其它相关文章！

本文收集自网络，不代表IT俱乐部立场，转载请注明出处。https://www.2it.club/database/oracle/12579.html
68赞
标签:
上一篇
下一篇

相关推荐

联系我们

联系我们

在线咨询: QQ交谈

邮箱: 1120393934@qq.com

工作时间：周一至周五，9:00-17:30，节假日休息

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

返回顶部