Oracle縮表空間的完整解決範例

2022-07-05 18:06:52

備註:

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_%';

再次執行壓縮空間，成功