Oracle表空间数据库文件收缩的示例分析

共计 6092 个字符，预计需要花费 16 分钟才能阅读完成。

这篇文章主要介绍了 Oracle 表空间数据库文件收缩的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。

我们经常会遇到数据库磁盘空间爆满的问题，或由于归档日志突增、或由于数据文件过多、大导致磁盘使用紧俏。这里主要说的场景是磁盘空间本身很大，但表空间对应的数据文件初始化的时候就直接顶满了磁盘空间，导致经常收到磁盘空间满的报警。

一、错误信息

告警内容如下：

【发现异常】地产客储系统数据库 Oracle_192.168.xx.xx，192.168.xx.xx, 数据库 customer, 连接错误,0 ORA-00257: archiver error. Connect internal only, until freed.

【发生时间】2018.07.04 09:12:21

二、错误原因

上述错误一看大致就知道是由于磁盘空间不足，导致归档无法完成所致，我们只需要清理足够的磁盘空间即可。但在磁盘清理的时候发现磁盘空间本身可清理的不多，被很多很大的数据文件占用，而实际使用的 segment 大小总共不足 400G，磁盘空间本身 1T，所以我们可以通过收缩数据文件的方式回收磁盘空间。

数据文件初始化方式：

1. 我们创建表空间一般有两种方式初始化其数据文件，即指定初始大小为 32G（很大的值）或指定初始大小为 100M（很小的值）然后通过自动扩展方式慢慢按需增长。

2. 第一种初始数据文件方法坏处就是开始不管你用不用到那么大，都会占用这么大的磁盘空间（这种数据迁移的时候可以使用）。第二种初始化方法按需增长，比较好的监控实际使用磁盘空间，所以推荐初始值很小，使用自动扩展慢慢增长的方式。

三、处理步骤

1. 查看磁盘空间大小

2. 查看数据库表空间大小

#!/bin/bash
sqlplus -S /nolog  EOF
conn /as sysdba;
set echo off heading on underline on;
column inst_num heading  Inst Num  new_value inst_num format 99999;
column inst_name heading  Instance  new_value inst_name format a12;
column db_name heading  DB Name  new_value db_name format a12;
column dbid heading  DB Id  new_value dbid format 9999999999 just c;
prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~
select d.dbid dbid
 , d.name db_name
 , i.instance_number inst_num
 , i.instance_name inst_name
 from v\$database d,
 v\$instance i;
set term on feedback off lines 130 pagesize 999 tab off trims on
column MB format 999,999,999 heading  Total MB 
column free format 9,999,999 heading  Free MB 
column used format 99,999,999 heading  Used MB 
column Largest format 999,999 heading  LrgstMB 
column tablespace_name format a20 heading  Tablespace 
column status format a3 truncated
column max_extents format 99999999999 heading  MaxExt 
col extent_management for a1 trunc head  M 
col allocation_type for a1 trunc head  A 
col Ext_Size for a4 trunc head  Init 
column pfree format a3 trunc heading  %Fr 
break on report
compute sum of MB on report
compute sum of free on report
compute sum of used on report
select 
 d.tablespace_name, 
 decode(d.status, 
  ONLINE ,  OLN ,
  READ ONLY ,  R/O ,
 d.status) status,
 d.extent_management, 
 decode(d.allocation_type,
  USER , ,
 d.allocation_type) allocation_type,
 (case 
 when initial_extent   1048576 
 then lpad(round(initial_extent/1024,0),3)|| K  
 else lpad(round(initial_extent/1024/1024,0),3)|| M  
 end) Ext_Size,
 NVL (a.bytes / 1024 / 1024, 0) MB,
 NVL (f.bytes / 1024 / 1024, 0) free, 
 (NVL (a.bytes / 1024 / 1024, 0) - NVL (f.bytes / 1024 / 1024, 0)) used,
 NVL (l.large / 1024 / 1024, 0) largest, 
 d.MAX_EXTENTS ,
 lpad(round((f.bytes/a.bytes)*100,0),3) pfree,
 (case when round(f.bytes/a.bytes*100,0)  = 20 then     else  *  end) alrt
FROM sys.dba_tablespaces d,
 (SELECT tablespace_name, SUM(bytes) bytes
 FROM dba_data_files
 GROUP BY tablespace_name) a,
 (SELECT tablespace_name, SUM(bytes) bytes
 FROM dba_free_space
 GROUP BY tablespace_name) f,
 (SELECT tablespace_name, MAX(bytes) large
 FROM dba_free_space
 GROUP BY tablespace_name) l
WHERE d.tablespace_name = a.tablespace_name(+)
 AND d.tablespace_name = f.tablespace_name(+)
 AND d.tablespace_name = l.tablespace_name(+)
 AND NOT (d.extent_management LIKE  LOCAL  AND d.contents LIKE  TEMPORARY)
UNION ALL
select 
 d.tablespace_name, 
 decode(d.status, 
  ONLINE ,  OLN ,
  READ ONLY ,  R/O ,
 d.status) status,
 d.extent_management, 
 decode(d.allocation_type,
  UNIFORM , U ,
  SYSTEM , A ,
  USER , ,
 d.allocation_type) allocation_type,
 (case 
 when initial_extent   1048576 
 then lpad(round(initial_extent/1024,0),3)|| K  
 else lpad(round(initial_extent/1024/1024,0),3)|| M  
 end) Ext_Size,
 NVL (a.bytes / 1024 / 1024, 0) MB,
 (NVL (a.bytes / 1024 / 1024, 0) - NVL (t.bytes / 1024 / 1024, 0)) free,
 NVL (t.bytes / 1024 / 1024, 0) used, 
 NVL (l.large / 1024 / 1024, 0) largest, 
 d.MAX_EXTENTS ,
 lpad(round(nvl(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,100),0),3) pfree,
 (case when nvl(round(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,0),100)  = 20 then     else  *  end) alrt
FROM sys.dba_tablespaces d,
 (SELECT tablespace_name, SUM(bytes) bytes
 FROM dba_temp_files
 GROUP BY tablespace_name order by tablespace_name) a,
 (SELECT tablespace_name, SUM(bytes_used ) bytes
 FROM v\$temp_extent_pool
 GROUP BY tablespace_name) t,
 (SELECT tablespace_name, MAX(bytes_cached) large
 FROM v\$temp_extent_pool
 GROUP BY tablespace_name order by tablespace_name) l
WHERE d.tablespace_name = a.tablespace_name(+)
 AND d.tablespace_name = t.tablespace_name(+)
 AND d.tablespace_name = l.tablespace_name(+)
 AND d.extent_management LIKE  LOCAL 
 AND d.contents LIKE  TEMPORARY 
 ORDER by 1
prompt
EOF

3. 查询可直接收缩表空间数据文件

这里查看的是可以直接收缩的数据文件大小，比如最开始初始化的数据文件为 32G，在数据文件高水位以下的为 20G，那么可直接回收的为 12G。

select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
 ceil(HWM * a.block_size)/1024/1024 ResizeTo,
 (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
  alter database datafile  ||a.name||  resize  ||
 ceil(HWM * a.block_size/1024/1024) ||   ResizeCMD
 from v$datafile a,
 (select file_id,max(block_id+blocks-1) HWM
 from dba_extents
 group by file_id) b
 where a.file# = b.file_id(+)
 and (a.bytes - HWM *block_size)

4. 直接收缩数据文件

alter database datafile  /oracle/oradata/bi/data01.dbf  resize 1548M;

5. 再次查看磁盘空间，已释放很多，可手动完成归档测试。

四、总结

针对 oracle 的数据文件收缩（磁盘空间收缩），我们一般可通过当前磁盘空间查看（df -h）—— 执行可直接收缩的查询命令和收缩命令—— 执行大表高水位收缩—— 执行表空间高水位收缩（降低文件高水位线）—— 再次执行直接回收表空间数据文件命令

直接收缩数据文件的方式参考本文上述步骤即可完成。

那么如何降低表空间的数据文件高水位，进而完成表空间数据文件回收呢？

1. 查看大于 10G 的数据文件

select file_name,file_id,tablespace_name,(bytes/1024/1024/1024) file_size_gb from dba_data_files where (bytes/1024/1024/1024)  10 order by file_id;

2. 查看大于 10G 的数据文件对应的数据块信息

select file_id,max(block_id+blocks-1) HWM,block_id
 from dba_extents
 where file_id =14
 group by file_id,block_id
 order by hwm desc ;

3. 查看大表对应的数据块信息

## 查看大表
select file_name,file_id,tablespace_name,(bytes/1024/1024/1024) file_size_gb from dba_data_files where (bytes/1024/1024/1024)  10 order by file_id;
## 查看大表对应的块
 select owner,segment_name,file_id,block_id,blocks from dba_extents where segment_name= TABLE_NAME 

4. 降低表的高水位

alter table table_name move;
alter index idx_name rebuild;

5. 查看数据文件对应的最大的 block_id

SELECT MAX(block_id)
 FROM dba_extents
 WHERE tablespace_name =  TABLESPACE_NAME

6. 执行数据文件收缩

(block_id+blocks-1) 数据文件的 HWM
alter database datafile  /oracle/oradata/bi/data01.dbf  resize xxxM;

感谢你能够认真阅读完这篇文章，希望丸趣 TV 小编分享的“Oracle 表空间数据库文件收缩的示例分析”这篇文章对大家有帮助，同时也希望大家多多支持丸趣 TV，关注丸趣 TV 行业资讯频道，更多相关知识等着你来学习!