Oracle移除資料表空間的資料檔案 ora-00604 ora-01426，ora-00604ora-01426

Oracle移除資料表空間的資料檔案 ora-00604 ora-01426，ora-00604ora-01426


項目背景:在之前開發環境資料庫管理比較亂,在資料表空間不足時只是添加資料檔案,測試完後資料己刪除,但資料庫資料表空間所佔的空間不能回收,導致資料庫的儲存目錄使用率達到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資料時才生效,

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。