热搜：编程 Python Microsoft 编程语言 C++

首页> 数据库 > Oracle > 正文

Oracle缩表空间的完整如何解决实例

发布时间：2022-12-24 19:54:38 来源：网络

.

路上的杨柳依然神彩奕奕的低垂着黄绿色的发丝，好像冬天的降温没带给她们多少伤害。我倒感受到她们内心的寒冷，叶子摸着十分冰冷，也缺少了昔日的水分。冬天的到来，摧残了多少无辜的生命，又演绎了多少生命的童话。

备注:

Oracle 11.2.0.4

一. 需求

近期有一个日志库，占用了比较多的空间，需要将历史的清理，然后收缩空间。

如下图所示，4T的空间已经差不多用完。

二. 解决方案

首先想到的是清理掉超过半年的数据，然后resize 表空间。

2.1 清理过期数据

因为业务的表是 tablename_yearmonth格式，例如 log_202204,每个月一个表，所以直接进行truncate即可。

找到大表:

select t.segment_name,t.BYTES/1024/1024/1024 GB,t.segment_type
from user_segments t
where t.segment_type in ('TABLE','TABLE PARTITION')
order by nvl(t.BYTES/1024/1024/1024,0) desc;

truncate 大表:

select  'truncate table '|| t.TABLE_NAME ||';'
  from user_tables t
 where t.TABLE_NAME  like 'LOG%';

2.2 收缩表空间

select a.tablespace_name,
a.file_name,
a.totalsize as totalsize_MB,
b.freesize as freesize_MB,
'ALTER DATABASE DATAFILE ''' || a.file_name || ''' RESIZE ' ||
round((a.totalsize - b.freesize) + 200) || 'M;' as "alter datafile"
from (select a.file_name,
a.file_id,
a.tablespace_name,
a.bytes / 1024 / 1024 as totalsize
from dba_data_files a) a,
(select b.tablespace_name,
b.file_id,
sum(b.bytes / 1024 / 1024) as freesize
from dba_free_space b
group by b.tablespace_name, b.file_id) b
where a.file_id = b.file_id
and b.freesize > 100
and a.tablespace_name  in ('TBS_LOG_DATA')
order by a.tablespace_name

将上一步的 alter datafile语句拷贝出来执行:

有部分报错:

ORA-03297: file contains used data beyond requested RESIZE value

2.3 清理表碎片

因为我使用的是truncate，理论上不会受高水位的影响，在网上找了几个博客，也是说要降低表的高水位，清理表碎片。

select 'alter table '||t.TABLE_NAME||' enable row movement;',
       'alter table '||t.TABLE_NAME||' shrink space cascade;'
  from user_tables t
 where t.TABLE_NAME like 'LOG%';

清理完碎片之后，重新执行，依旧报错。

2.4 直接把相关的表drop掉

select  'drop table '|| t.TABLE_NAME ||'purge;'
  from user_tables t
 where t.TABLE_NAME  like 'LOG%';

drop掉表之后，重新执行，依旧报错。

2.5 把该表空间下其它的表移出此表空间

万能的itpub上有个博客:

Truncate table 或者 drop table 收缩数据文件,经常遇到ORA-03297: file contains used data beyond requested RESIZE value 查询dba_free_space 也有空闲空间。经过查询MOS(Doc ID 1029252.6)得知

If you have a large extent in the middle of a datafile, and some object taking up room at the end of the datafile, you can use the query FINDEXT.SQL below to find this object. If you export this object, then drop it, you should then free up contiguous space at the end of your datafile so you will be able to resize it smaller.

Make sure you leave enough room in the datafile for importing the object back into the tablespace.

意思是说如果空闲的extent如果在文件的中间,此时无法进行resize ,必须把尾部的object drop 然后重建再resize datafile。以下是本人做的测试;

[oracle@bogon ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 31 11:10:41 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> create tablespace test2 datafile '/u01/app/oracle/oradata/orcl/test2.dbf' size 10M autoextend on next 1M;
Tablespace created.
SQL> create table tab1 tablespace test2 as select * from dba_objects;
Table created.
SQL> select file#,name,bytes/1024/1024 bytes from v$datafile where name like '%test2%';
FILE# NAME                             BYTES

----- ------------------------------------------------------------ -----

 23 /u01/app/oracle/oradata/orcl/test2.dbf             11
SQL> create table tab2 tablespace test2 as select * from dba_objects;
Table created.
SQL> select file#,name,bytes/1024/1024 bytes from v$datafile where name like '%test2%';
FILE# NAME                             BYTES

----- ------------------------------------------------------------ -----

 23 /u01/app/oracle/oradata/orcl/test2.dbf             21
SQL> select SEGMENT_NAME,FILE_ID,EXTENT_ID,BLOCK_ID,blocks from dba_extents where file_id=23 order by BLOCK_ID;
SEGMENT_NA  FILE_ID EXTENT_ID  BLOCK_ID   BLOCKS

---------- ---------- ---------- ---------- ----------

TAB1        23     0     9     8
TAB1        23     1     17     8
TAB1        23     2     25     8
TAB1        23     3     33     8
TAB1        23     4     41     8
TAB1        23     5     49     8
TAB1        23     6     57     8
TAB1        23     7     65     8
TAB1        23     8     73     8
TAB1        23     9     81     8
TAB1        23     10     89     8
SEGMENT_NA  FILE_ID EXTENT_ID  BLOCK_ID   BLOCKS

---------- ---------- ---------- ---------- ----------

TAB1        23     11     97     8
TAB1        23     12    105     8
TAB1        23     13    113     8
TAB1        23     14    121     8
TAB1        23     15    129     8
TAB1        23     16    137    128
TAB1        23     17    265    128
TAB1        23     18    393    128
TAB1        23     19    521    128
TAB1        23     20    649    128
TAB1        23     21    777    128
SEGMENT_NA  FILE_ID EXTENT_ID  BLOCK_ID   BLOCKS

---------- ---------- ---------- ---------- ----------

TAB1        23     22    905    128
TAB1        23     23    1033    128
TAB1        23     24    1161    128
TAB2        23     0    1289     8
TAB2        23     1    1297     8
TAB2        23     2    1305     8
TAB2        23     3    1313     8
TAB2        23     4    1321     8
TAB2        23     5    1329     8
TAB2        23     6    1337     8
TAB2        23     7    1345     8
SEGMENT_NA  FILE_ID EXTENT_ID  BLOCK_ID   BLOCKS

---------- ---------- ---------- ---------- ----------

TAB2        23     8    1353     8
TAB2        23     9    1361     8
TAB2        23     10    1369     8
TAB2        23     11    1377     8
TAB2        23     12    1385     8
TAB2        23     13    1393     8
TAB2        23     14    1401     8
TAB2        23     15    1409     8
TAB2        23     16    1417    128
TAB2        23     17    1545    128
TAB2        23     18    1673    128
SEGMENT_NA  FILE_ID EXTENT_ID  BLOCK_ID   BLOCKS
---------- ---------- ---------- ---------- ----------
TAB2        23     19    1801    128
TAB2        23     20    1929    128
TAB2        23     21    2057    128
TAB2        23     22    2185    128
TAB2        23     23    2313    128
TAB2        23     24    2441    128

50 rows selected.

Block_id 是连续的

SQL> truncate table tab1
 2 ;
Table truncated.
SQL> select * from dba_free_space where file_id=23;
TABLESPACE_NAME     FILE_ID  BLOCK_ID   BYTES   BLOCKS RELATIVE_FNO

-------------------- ---------- ---------- ---------- ---------- ------------

TEST2            23     17 ##########    1272      23
TEST2            23    2569 ##########    120      23

有原来tab1 的free blocks 1272

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 12M;
alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 12M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

无法进行resize

下面把tab1 drop 再测试

SQL> drop table tab1 purge;
Table dropped.
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 12M;
alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 12M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

依然报错

然后truncate tab2 再进行测试

SQL> truncate table tab2;

Table truncated.

SQL> select * from dba_free_space where file_id=23;

TABLESPACE_NAME     FILE_ID  BLOCK_ID   BYTES   BLOCKS RELATIVE_FNO

-------------------- ---------- ---------- ---------- ---------- ------------

TEST2            23     9 ##########    1280      23

TEST2            23    1297 ##########    1392      23

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 12M;

Database altered.

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 6M;
alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 6M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

此时只能收缩 tab2 的空间但是不能收缩 tab1的空间

然后再drop tab2

SQL> drop table tab2 purge
 2 ;
Table dropped.
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 6M;
Database altered.
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test2.dbf' resize 1M;
Database altered.

可以收缩tab1的空间

note:

收缩数据文件和两个因素有关

1 降低高水位

2 free extent在datafile 的尾部

本篇文章直接解释了第二个

如果空闲的extent如果在文件的中间,此时无法进行resize ,必须把尾部的object drop 然后重建再resize datafile。

也就是说同时期该用户下其它表的写入，也在这个数据文件下，那么就不能进行resize。

把其它表移动到users表空间:

select 'alter index '||index_NAME||' rebuild tablespace users;' from user_indexes where TABLE_NAME not like 'LOG_%';
select 'alter table '||TABLE_NAME||' move tablespace users;' from user_tables where TABLE_NAME not like 'LOG_%';

再次运行压缩空间，成功

2.6 查看压缩的空间

可以看到一下子多出了2.1T 的空间

收缩空间运行速度还不错，50多个数据文件，几分钟就压缩完成。