這篇文章主要介紹“Oracle縮表空間問題如何解決”的相關知識，小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“Oracle縮表空間問題如何解決”文章能幫助大家解決問題。
Oracle 11.2.0.4
近期有一個日志庫，占用了比較多的空間，需要將歷史的清理，然后收縮空間。
如下圖所示，4T的空間已經差不多用完。
首先想到的是清理掉超過半年的數據，然后resize 表空間。
因為業務的表是 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%';
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
因為我使用的是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%';
清理完碎片之后，重新執行，依舊報錯。
select 'drop table '|| t.TABLE_NAME ||'purge;' from user_tables t where t.TABLE_NAME like 'LOG%';
drop掉表之后，重新執行，依舊報錯。
萬能的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 12850 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多個數據文件，幾分鐘就壓縮完成。
關于“Oracle縮表空間問題如何解決”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識，可以關注億速云行業資訊頻道，小編每天都會為大家更新不同的知識點。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
