ORACLE--分區表數據清理，oracle--分區表清理

ORACLE--分區表數據清理，oracle--分區表清理

由於分區表數據增加；沒做清除操作；導致表空間告急。需要清理很久之前的數據；釋放空間。步驟如下

一，查看哪個表占的空間

SELECT t.segment_name, SUM(t.bytes / 1024 / 1024)
  FROM user_segments t
 GROUP BY t.segment_name
 ORDER BY SUM(t.bytes / 1024 / 1024) DESC

二、查看每個子分區的記錄

SELECT a.table_name,a.partition_name FROM user_tab_partitions  a WHERE a.table_name = 'TBL_SMSMT_AUTOACTIV_BILL_HIS';

三、檢查分區表的索引是否LOCAL類型
--先查看該表的索引

SELECT * FROM User_Indexes a WHERE a.table_name  = 'TBL_SMSMT_AUTOACTIV_BILL_HIS';

 --在查看索引是否為local類型；若不存在；則為全局索引

select table_name,index_name,LOCALITY from user_part_indexes where table_name='TBL_SMSMT_AUTOACTIV_BILL_HIS' ;

四、如果都是LOCAL索引，直接清除表分區數據（如果是全局索引，清除完成後需要重建)

     本地索引在你刪除子分區表的時候會跟著刪除；不會影響。

ALTER TABLE TBL_SMSMT_AUTOACTIV_BILL_HIS DROP PARTITION P201305;
ALTER TABLE TBL_SMSMT_AUTOACTIV_BILL_HIS DROP PARTITION P201306;
ALTER TABLE TBL_SMSMT_AUTOACTIV_BILL_HIS DROP PARTITION P201307;
ALTER TABLE TBL_SMSMT_AUTOACTIV_BILL_HIS DROP PARTITION P201308;
ALTER TABLE TBL_SMSMT_AUTOACTIV_BILL_HIS DROP PARTITION P201309;
ALTER TABLE TBL_SMSMT_AUTOACTIV_BILL_HIS DROP PARTITION P201310;

ALTER INDEX SYS_C00105590 REBUILD;           
ALTER INDEX I_TBL_SMSMT_AUTOACTIV_BILL_HIS REBUILD;        --其中SYS_C00105590，I_TBL_SMSMT_AUTOACTIV_BILL_HIS是全局索引

 五, 驗證是否釋放空間；(可忽略)

  再執行第一步；

oracle中怎刪除分區表

可以用 ALTER TABLE DROP PARTITION 來刪除分區，元數據和數據將被一並刪除。
全刪除
ALTER TABLE yourTable DROP PARTITION partionName1;
清數據
ALTER TABLE yourTable TRUNCATE PARTITION partionName1;
語句雖簡單、操作需謹慎。
 

oracle數據庫分區表備份

create table par_tab(
data_date varchar2(8),
col_n varchar2(20)
)
partition by range(data_date)(
partition part_201000 values less than ('201001') tablespace tab_201000,
partition part_201001 values less than ('201002') tablespace tab_201003,
partition part_201002 values less than ('201003') tablespace tab_201003,
partition part_201003 values less than ('201004') tablespace tab_201003,
partition part_201004 values less than ('201005') tablespace tab_201006,
partition part_201005 values less than ('201006') tablespace tab_201006,
partition part_201006 values less than ('201007') tablespace tab_201006,
partition part_201007 values less than ('201008') tablespace tab_201009,
partition part_201008 values less than ('201009') tablespace tab_201009,
partition part_201009 values less than ('201010') tablespace tab_201009,
partition part_201010 values less than ('201011') tablespace tab_201012,
partition part_201011 values less than ('201012') tablespace tab_201012,
partition part_201012 values less than ('201013') tablespace tab_201012,
partition part_201099 values less than ('201099') tablespace tab_201099,
partition part_201100 values less than ('201001') tablespace tab_201000,
partition part_201101 values less than ('201002') tablespace tab_201003,
partition part_201102 values less than ('201003') tablespace tab_201003,
partition part_201103 valu......余下全文>>