資料清理的遺留問題處理(二)

之前嘗試了歷史資料的清理，在邏輯層面清除了資料，可以參見 http://blog.itpub.net/23718752/viewspace-1814000/
但是從物理層面來看，資料檔案還是那麼大，空間還是沒有釋放掉。
從計劃的500多G資料空間清理到了90G
SEGMENT_TYPE          SIZE_MB
------------------ ----------
INDEX PARTITION        260279
TABLE PARTITION        294120
然後在經過一番清理之後，對應的段空間一下子就將下來了。
EGMENT_TYPE          SIZE_MB
------------------ ----------
INDEX PARTITION         31823
TABLE PARTITION         47218

最後全部清理完
check segement size summary from year xxx
no rows selected

現在問題來了。清理資料檔案該怎麼做。思路應該是在dba_segments裡面去找是否存在相應的段。如果沒有即代表著可以刪除這些資料檔案。
有的朋友可能會說為什麼一定要清掉這些資料檔案，這個也有歷史原因，就是表空間和時間也有關係，比如某年的某月可能就會根據邏輯生成一個表空間，按照這種思路，儘管我清除了端資料，但是放著那些資料檔案，它們是不會被使用到的，還是佔著地方，所以需要直接清掉，給後續的資料留空間。
目前想達到的效果就是，根據表空間能夠查出來，如果某個表空間沒有對應的段物件，顯示為0，標誌著可以清理。
select tablespace_name,count(*) from dba_segments where tablespace_name in ('xxxxx','xxx') group by tablespace_name;
xxxxx   0
xxx     0
但是實際上去寫的時候發現還是沒那麼好弄，經過一番折騰寫出了一個基本實現要求的語句，但是算是一個初始版本，因為結果顯示還是有些牽強。
select tablespace_name,count(*)cnt from dba_segments where tablespace_name like '%DATA%2012%' group by tablespace_name
 union
select tablespace_name,null cnt from dba_segments where tablespace_name  like '%DATA%2012%' group by tablespace_name

TABLESPACE_NAME                       CNT
------------------------------ ----------
SERLOG_DATA_20120705                  0
SERLOG_DATA_20120705                 11
SERLOG_DATA_20120706                  0
SERLOG_DATA_20120706                 19
SERLOG_DATA_20121007                  0
SERLOG_DATA_20121007                 31
SERLOG_DATA_20121008                  0
SERLOG_DATA_20121010                  0
按照這個情況，只有表空間SERLOG_DATA_20121010是可以刪除的。其他的都會和一個dummy列並存。
當然sql寫成這樣也是醉了，能實現基本需求也行，但是成百上千的表空間，我覺得還是不好。繼續改進。使用了minus的方式。
select tablespace_name from dba_tablespaces s1 where tablespace_name like '%DATA%2012%'
minus
select tablespace_name  from dba_segments s1 where tablespace_name like '%DATA%2012%' group by s1.tablespace_name

這種方式就會做一個減法，先把相關的表空間羅列出來，然後在dba_segements裡面過濾組合只會顯示有段物件的表空間。
這樣那些隱藏著的表空間都會一一羅列出來。
直接使用drop tablespace來清理。
檢視asm磁碟空間，清理前剩餘80多G
GROUP_NUMBER NAME                              FREE_MB   TOTAL_MB
------------ ------------------------------ ---------- ----------
           1 ARCH                               204740     204800
           2 DATA                                84336    6383104
清除多餘的資料檔案，可以看到立馬剩出了快400G.
GROUP_NUMBER NAME                    FREE_MB   TOTAL_MB
------------ -------------------- ---------- ----------
           1 ARCH                     204740     204800
           2 DATA                     390360    6383104
這個時候別被這些成績所迷惑，刪除分割槽的同時會清楚分割槽索引，這些分割槽索引的空間又是不少。至於有多少呢，我們還是使用minus的方式來清理。
清除多餘的索引資料檔案，清理之後剩餘了近1T的空間。
GROUP_NUMBER NAME                    FREE_MB   TOTAL_MB
------------ -------------------- ---------- ----------
           1 ARCH                     203906     204800
           2 DATA                     981640    6383104
這個例子不是說明minus清理有多好，是想說清理的時候，邏輯清理100%完成，物理清理很可能會漏掉，清理了不到50%,這樣我們的工作就不徹底，半途而廢。