Oracle資料庫表空間整理回收與釋放作業

這篇文章為大家帶來了關於Oracle的相關知識，其中主要介紹了資料庫表空間整理回收與釋放作業的相關問題，包括了降低表空間高水位、在刪除時進行釋放等等內容，下面一起來看一下，希望對大家有幫助。

推薦教學：《Oracle影片教學》

在使用Oracle進行輸入測試時，我們會製造許多測試數據，而使用刪除後表空間的高水位依舊無法自動下調，導致出現表空間出現空白數據，影響表空間效能的同時也佔用了過多的儲存空間。

一、降低表空間高水位

1.查看表空間和對應物理檔名

程式碼如下：

select 
b.file_id 物理文件号,
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024  已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5)  利用率 
from dba_free_space a,dba_data_files b 
where a.file_id=b.file_id 
group by b.tablespace_name,b.file_id,b.file_name,b.bytes 
order by b.tablespace_name

根據利用率可以直觀的判斷有那些表空間可以進行釋放。

2.清空回收站

程式碼如下：

-- 清除用户回收站
purge recyclebin;
-- 清除全库回收站
purge dba_recyclebin;

清除刪除過程中遺留的資料。

3.整理表空間

程式碼如下：

alter tablespace tablespace_name coalesce;

整合表空間的碎片增加表空間的連續性

4.產生RESIZE程式碼

程式碼如下：

select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
ceil(HWM * a.block_size)/1024/1024 ResizeTo,
(a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
'alter database datafile '''||a.name||''' resize '||
ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
from v$datafile a,
(select file_id,max(block_id+blocks-1) HWM
from dba_extents where file_id in
(select b.file# From v$tablespace a ,v$datafile b
where a.ts#=b.ts# and a.name='tablespace_name')
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
order by 5

上述程式碼執行後，可以產生一個對應表空間RESIZE的SQL程式碼，此時執行對應的SQL程式碼即可。

二、在刪除時進行釋放

1.清空表資料並釋放表空間

程式碼如下：

-- 清空表数据
truncate table table_name;
-- 释放表空间
alter table table_name deallocate UNUSED KEEP 0;

先清空表數據，此時表依舊存在，Truncate不支援回滾，且不能truncate一個有外鍵的表，如果要刪除先取消外鍵，然後再刪除；
注意如果不加KEEP 0的話，表空間是不會釋放的。

2.遷移表資料

在出現以下錯誤時，我們不僅可以透過整理表空間或清楚回收站來解決，還可以透過將待釋放表空間的表資料全部遷移到其他表空間，將空間釋放後再遷移回原表空間。

	--需移动的表数据
   select DISTINCT 'alter table '|| owner||'.'||segment_name || ' move tablespace user_test;' from dba_extents where segment_type='TABLE' and file_id=4;
	--需移动的索引数据
   select DISTINCT 'alter index '|| owner||'.'|| segment_name || ' rebuild tablespace user_test;' from dba_extents where segment_type='INDEX' and file_id=4;
   --需移动的分区表数据
   select DISTINCT 'alter table '|| owner||'.'|| segment_name || ' move partition '|| partition_name || ' tablespace user_test;' from dba_extents where segment_type='TABLE PARTITION' and file_id=4;
   --需移动的分区表索引数据
   select DISTINCT 'alter index '|| owner||'.'|| segment_name || ' rebuild partition '|| partition_name || ' tablespace user_test;' from dba_extents where segment_type='INDEX PARTITION' and file_id=4;

上述程式碼中file_id可以透過查看表空間進行比對更改，上述程式碼執行後可以取得對應的自動產生的SQL程式碼，此時執行對應的SQL程式碼即可遷移表資料

推薦教學：《Oracle影片教學》

#

以上是Oracle資料庫表空間整理回收與釋放作業的詳細內容。更多資訊請關注PHP中文網其他相關文章！