因為資料庫空間不足,需要對曆史資料進行清理,查詢涉及的表竟然有550G,和開發溝通之後将曆史資料使用應用程式遷移到其他機器上,之後對舊表進行删除!(對于此種情況多少有些無奈,入職之前表已經存在了,建表的時候應該考慮使用分區表,清理資料會更友善)
檢視表的大小
YANG@yangdb>set timing on;
YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB';
SEGMENT_NAME BYTES/1024/1024/1024
------------------------------ --------------------
YANGTAB 550.075195
Elapsed: 00:00:00.98
YANG@yangdb>
使用 truncate的reuse storage 特性,預設時是drop storage,這樣會直接對object占用的删除之後并不直接drop storage ,這樣可以避免回收大量的extent 太多導緻系統資源緊張的情況
YANG@yangdb>truncate table YANG.YANGTAB reuse storage;
Table truncated.
Elapsed: 00:04:19.31
Elapsed: 00:00:00.09
YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 563277M;
ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 563277M
*
ERROR at line 1:
ORA-03230: segment only contains 72099438 blocks of unused space above high water mark
Elapsed: 00:00:00.27
第一次 size 設定的有點大!
YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 503277M;
Table altered.
Elapsed: 00:00:17.99
YANGTAB 491.481628
Elapsed: 00:00:00.03
YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 453277M;
Elapsed: 00:00:14.50
YANGTAB 442.653503
YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 403277M;
Elapsed: 00:00:14.86
YANGTAB 393.825378
YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 323277M;
Elapsed: 00:00:22.53
YANGTAB 315.700378
Elapsed: 00:00:00.02
YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 253277M;
Elapsed: 00:00:28.05
YANGTAB 247.341003
YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 183277M;
Elapsed: 00:00:54.36
YANGTAB 178.981628
Elapsed: 00:00:00.13
YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 123277M;
Elapsed: 00:00:33.64
YANGTAB 120.387878
YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 83277M;
Elapsed: 00:00:22.36
YANGTAB 81.3253784
YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 53277M;
Elapsed: 00:00:14.35
YANGTAB 52.0285034
YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 33277M;
Elapsed: 00:00:09.40
YANGTAB 32.4972534
YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 13277M;
Elapsed: 00:00:09.29
YANGTAB 12.9660034
YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 3277M;
Elapsed: 00:00:04.44
YANGTAB 3.20037842
YANG@yangdb>ALTER table YANG.YANGTAB DEALLOCATE UNUSED KEEP 277M;
Elapsed: 00:00:03.08
YANGTAB .270690918
Elapsed: 00:00:00.07
YANG@yangdb>drop table YANG.YANGTAB;
Table dropped.
Elapsed: 00:00:01.11
YANG@yangdb>SELECT segment_name,bytes/1024/1024/1024 FROM user_Segments WHERE segment_name='YANGTAB_NEW';
YANGTAB_NEW 83.7783203
Elapsed: 00:00:00.01
YANG@yangdb>rename YANGTAB_new to YANGTAB;
Table renamed.
Elapsed: 00:00:00.10
YANGTAB 83.7783203
<b>附上操作過程中遇到的低級錯誤</b>
<b>1 oracle 和mysql 之間對表的重命名的文法混淆了,汗!</b>
YANG@yangdb>rename table YANG.YANGTAB_new to YANG.YANGTAB;
rename table YANG.YANGTAB_new to YANG.YANGTAB
*
ORA-00903: invalid table name
<b>2 表名不允許帶owner</b>
YANG@yangdb>rename YANG.YANGTAB_new to YANG.YANGTAB;
rename YANG.YANGTAB_new to YANG.YANGTAB
ORA-01765: specifying owner s name of the table is not allowed
參考自己的另一篇文章
<a href="http://space.itpub.net/22664653/viewspace-704778">http://space.itpub.net/22664653/viewspace-704778</a>