項目背景:在之前開發環境數據庫管理比較亂,在表空間不足時只是添加數據文件,測試完後數據己刪除,但數據庫表空間所占的空間不能回收,導致數據庫的存儲目錄使用率達到97%以上實際使用只有10%,迫切需要將不用空間進行回收.
技術背景:Oracle不提供如刪除表、視圖一樣刪除數據文件的方法，數據文件是表空間的一部分，所以不能“移走”表空間。Oracle 10G R2開始，可以采用：Alter tablespace tablespace_name drop datafile file_name;來刪除一個空數據文件，並且相應的數據字典信息也會清除:
A.確認oracle版本(我是以本機(windows)查看數據庫版本,測試環境為AIX操作系統,但數據庫版本一致),版本滿足可以直接刪除數據文件
select * from v$version;
B.確認移除的數據文件
a.確定需要移去的數據文件,可以用數據文件最大擴展的大小和對象多少綜合評估一下,是否將這個文件進行移除
擴展大小:
select file_id,tablespace_name,max(t.block_id)*8192/1024/1024 from dba_extents t
where tablespace_name = 'FERMATDATA'
group by file_id,tablespace_name
對象多少:
select * from dba_extents t
where t.file_id =10
C.將數據文件中己有的對象move到其他表空間
a.對於segment_type為TABLE的非分區表采用以下語句
alter table bob_rwa3.db move tablespace ETLDATA;
批量語句:
select 'alter table '||t.owner||'.'||t.segment_name||' move tablespace ETLDATA ;' from dba_extents t
where t.file_id =10
and t.partition_name is null
b.對於segment_type為TABLE的分區表采用以下語句(非復合分區)
alter table BOB_RAY.T_TRANSITION_MATRIX move partition P199001010001 tablespace ETLDATA update global indexes ;
批量腳本:
select 'alter table '||t.owner||'.'||t.segment_name||' move partition '||t.partition_name||' tablespace ETLDATA update global indexes ;'
from dba_extents t
where t.file_id =10
and t.partition_name is not null ;
c.對於segment_type 為TABLE復合分區采用以下語句
alter table BOB_RAY.LOG_TABLE move subpartition P199001010001_NULL tablespace IRM_DATA;
批量腳本可以自己關聯dba_tab_subpartitions表寫出.
d.對於segment_type為LOBSEGMENT或LOBINDEX可以采用以下語句,因為LOB類型的字段會自動生成存儲數據和索引兩部份(lobsegment,lobindex)
1.查看找segment_type為lob的segment_name:
select * from dba_extents t
where t.file_id =10
and t.segment_type like '%LOB%';
2.查看segment_type為LOB對應的column
select * from dba_lobs t
where t.segment_name like '%SYS_LOB0000145648C00003%'
;
3.將segment_type為LOB類型轉移到其他表空間,因為move table只轉移動非lob字段,所以需要執行以下語句
alter table BOB_RAY.BIRT_RESOURCE move tablespace ETLDADA lob(CONTENT) store as (tablespace ETLDATA);
批量腳本關聯即可寫出
因為本項目中數據表空間和索引表空間是分開的,所以這裡不涉及索引的相關操作,如果有索引的情況估計與表的情況相似.
D.確認數據文件是否為空
a.查看dba_extents 是否有記錄(需要沒有記錄)
select * from dba_extents t
where t.file_id =10;
b.查看dba_segments是否有記錄(需要沒有記錄)
select * from dba_segments t
where t.header_file = 10
注意:如果dba_segments有回收站的數據,那麼在刪除表空間數據文件時會報錯 ora-00604 ora-01426
c.刪除回收站數據
purge recyclebin;
E.刪除表空間對應的數據文件
1.查看數據文件名稱
select * from dba_data_files t
where t.file_id = 10
;
2.刪除表空間數據文件
alter tablespace FERMATDATA drop datafile 'D:\APP\ORADATA\RWADB\FERMATDATA02.DBF';
注意:當執行完語句時數據庫服務器用df -g查看空間時沒有變化,重啟數據庫後才能看到空間明顯回收了
F.指定表存儲在固定的數據文件方法以(擴展)
alter table bob_ray.bis_risk_bucket allocate extent( datafie '/oracle/oradata/orcl/appdata.dbf' );
此語句不會改變表中現有數據的存儲,當新insert數據時才生效,
