Oracle縮表空間的完整解決實(shí)例
更新時(shí)間：2022年07月05日 11:31:27 作者：只是甲
在Oracle中經(jīng)常有這樣的情況,由于誤操作,使某個(gè)表空間過(guò)大,下面這篇文章主要給大家介紹了關(guān)于Oracle縮表空間的相關(guān)資料,文中通過(guò)示例代碼介紹的非常詳細(xì),需要的朋友可以參考下
備注:
Oracle 11.2.0.4
一. 需求
近期有一個(gè)日志庫(kù)，占用了比較多的空間，需要將歷史的清理，然后收縮空間。
如下圖所示，4T的空間已經(jīng)差不多用完。
二. 解決方案
首先想到的是清理掉超過(guò)半年的數(shù)據(jù)，然后resize 表空間。
2.1 清理過(guò)期數(shù)據(jù)
因?yàn)闃I(yè)務(wù)的表是 tablename_yearmonth格式，例如 log_202204,每個(gè)月一個(gè)表，所以直接進(jìn)行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語(yǔ)句拷貝出來(lái)執(zhí)行:
有部分報(bào)錯(cuò):
ORA-03297: file contains used data beyond requested RESIZE value
2.3 清理表碎片
因?yàn)槲沂褂玫氖莟runcate，理論上不會(huì)受高水位的影響，在網(wǎng)上找了幾個(gè)博客，也是說(shuō)要降低表的高水位，清理表碎片。
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%';
清理完碎片之后，重新執(zhí)行，依舊報(bào)錯(cuò)。
2.4 直接把相關(guān)的表drop掉
select 'drop table '|| t.TABLE_NAME ||'purge;' from user_tables t where t.TABLE_NAME like 'LOG%';
drop掉表之后，重新執(zhí)行，依舊報(bào)錯(cuò)。
2.5 把該表空間下其它的表移出此表空間
萬(wàn)能的itpub上有個(gè)博客:
Truncate table 或者 drop table 收縮數(shù)據(jù)文件,經(jīng)常遇到ORA-03297: file contains used data beyond requested RESIZE value 查詢dba_free_space 也有空閑空間。經(jīng)過(guò)查詢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.
意思是說(shuō)如果空閑的extent如果在文件的中間,此時(shí)無(wú)法進(jìn)行resize ,必須把尾部的object drop 然后重建 再resize datafile。以下是本人做的測(cè)試;
?[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 是連續(xù)的
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有原來(lái)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無(wú)法進(jìn)行resize
下面把tab1 drop 再測(cè)試
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依然報(bào)錯(cuò)
然后truncate tab2 再進(jìn)行測(cè)試
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此時(shí)只能收縮 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:
收縮數(shù)據(jù)文件和兩個(gè)因素有關(guān)
1 降低高水位
2 free extent在datafile 的尾部
本篇文章直接解釋了第二個(gè)
如果空閑的extent如果在文件的中間,此時(shí)無(wú)法進(jìn)行resize ,必須把尾部的object drop 然后重建 再resize datafile。
也就是說(shuō)同時(shí)期該用戶下其它表的寫(xiě)入，也在這個(gè)數(shù)據(jù)文件下，那么就不能進(jìn)行resize。
把其它表移動(dòng)到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_%';
再次運(yùn)行壓縮空間，成功
2.6 查看壓縮的空間
可以看到一下子多出了2.1T 的空間
收縮空間運(yùn)行速度還不錯(cuò)，50多個(gè)數(shù)據(jù)文件，幾分鐘就壓縮完成。
總結(jié)
到此這篇關(guān)于Oracle縮表空間的文章就介紹到這了,更多相關(guān)Oracle縮表空間內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Oracle使用rownum分頁(yè)方式實(shí)例代碼
ROWNUM是一個(gè)序列,是oracle數(shù)據(jù)庫(kù)從數(shù)據(jù)文件或緩沖區(qū)中讀取數(shù)據(jù)的順序,這篇文章主要給大家介紹了關(guān)于Oracle使用rownum分頁(yè)的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-07-07
show和set命令是兩條用于維護(hù)SQL*Plus系統(tǒng)變量的命令2009-05-05
基于oracle小數(shù)點(diǎn)前零丟失的問(wèn)題分析
本篇文章是對(duì)oracle小數(shù)點(diǎn)前零丟失的問(wèn)題進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-05-05
Oracle基礎(chǔ):程序中調(diào)用sqlplus的方式
今天小編就為大家分享一篇關(guān)于Oracle基礎(chǔ):程序中調(diào)用sqlplus的方式，小編覺(jué)得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來(lái)看看吧2018-12-12
Oracle 12c新特性之如何檢測(cè)有用的多列統(tǒng)計(jì)信息詳解
這篇文章主要給大家介紹了Oracle 12c新特性之如何檢測(cè)有用的多列統(tǒng)計(jì)信息的相關(guān)資料，文中介紹的非常詳細(xì)，對(duì)大家具有一定的參考價(jià)值，需要的朋友們下面來(lái)一起看看吧。2017-03-03
Oracle數(shù)據(jù)庫(kù)如何刪除歸檔日志文件
這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫(kù)如何刪除歸檔日志文件的相關(guān)資料,當(dāng)Oracle中的歸檔日志空間滿時(shí),則需要把它清空,否則將會(huì)影響數(shù)據(jù)庫(kù)正常運(yùn)行,將無(wú)法正常登入ORACLE,需要的朋友可以參考下2023-11-11
學(xué)習(xí)了這么長(zhǎng)的數(shù)據(jù)庫(kù)，突然意識(shí)到自己對(duì)于數(shù)據(jù)庫(kù)主外鍵的意義完全弄不懂，是唯一標(biāo)識(shí)一條記錄，是為連接查詢服務(wù)的，這樣的理解都不對(duì)，或不完全對(duì)。2009-08-08
oracle數(shù)據(jù)與文本導(dǎo)入導(dǎo)出源碼示例
這篇文章主要介紹了oracle數(shù)據(jù)與文本導(dǎo)入導(dǎo)出源碼示例，具有一定參考價(jià)值，需要的朋友可以了解下。2017-10-10
Oracle中的INSTR,NVL和SUBSTR函數(shù)的用法詳解
這篇文章主要介紹了Oracle中的INSTR,NVL和SUBSTR函數(shù)的用法詳解,需要的朋友可以參考下2017-02-02
這篇文章主要介紹了詳解oracle分頁(yè)查詢的原理，從實(shí)例測(cè)試數(shù)據(jù)具體分析了實(shí)現(xiàn)方式，一起來(lái)參考下。2017-12-12