Oracle表空间数据库文件收缩-linux运维-PHP中文网

Oracle表空间数据库文件收缩

蓮花仙者

发布： 2025-07-12 10:24:23

原创

451人浏览过

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

一、错误信息

报警内容如下：

【发现异常】地产客储系统数据库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
prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~
<p>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"</p><p>break on report
compute sum of MB on report
compute sum of free on report
compute sum of used on report</p><p>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 = 20 then ' ' else '<em>' 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 = 20 then ' ' else '</em>' 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
exit
EOF

登录后复制

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

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

select a.file#,a.name,a.bytes/1024/1024 CurrentMB, ceil(HWM <em> a.block_size)/1024/1024 ResizeTo, (a.bytes - HWM </em> a.block_size)/1024/1024 ReleaseMB, 'alter database datafile '''||a.name||''' resize '|| ceil(HWM <em> a.block_size/1024/1024) || 'M;' 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 </em>block_size)>0;

登录后复制

4.直接收缩数据文件

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

登录后复制

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

美间AI

美间AI：让设计更简单

查看详情

四、总结

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

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

大表的高水位收缩参考：https://www.php.cn/link/85b69add5be1d3b636552fc2ae8f51f3

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

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;

登录后复制

Oracle表空间数据库文件收缩

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;</p><h2>查看大表对应的块</h2><p>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;

登录后复制

以上就是Oracle表空间数据库文件收缩的详细内容，更多请关注php中文网其它相关文章！