1. 首先要制定資料清理的方法和政策,具體内容略。
2. 對現網資料量進行摸底調查,哪些表需要清理并整理出。
3. 具體清理。
3.1. 分區表資料清理:
先判定該分區表的索引是LOCAL索引還是GLOBAL索引,這涉及到如何對分區表進行資料清理。
先通過如下語句來檢視分區表上建立的索引類型:
select t.table_name,i.index_name,i.table_owner
from dba_indexes i,dba_tables t
where i.table_name = t.table_name
and t.partitioned='yes'
and i.partitioned='no';
3.1.1 将全局索引修改為本地索引的方法見下:
首先要删除原有分區表的全局GLOBAL索引,也分為兩種情況,一種是索引是主鍵,一種是索引不是主鍵。
3.1.1.1 索引為主鍵的情況,先将原主鍵及索引删除:
alter table 表名 drop constraint 限制名 cascade;
然後再建立主鍵及本地索引,注意local和online參數:
alter table 表名 add constraint 限制名
primary key (列名, 列名2) using index local online
TABLESPACE 表空間
PCTFREE等原有的存儲參數
nologging;
3.1.1.2 另一種索引不為主鍵的情況,先将索引删除:
dorp index 索引名;
然後再用下面語句為該分區表建立本地索引:
create index 索引名 on 表名.列名 local online
TABLESPACE 表空間
3.1.2 确認所有的分區表上的索引均為本地索引後,按如下方法進行分區資料的清理,并恢複分區的最高水位值到初始點:
alter table 表名 truncate partition 分區名;
3.2. 非分區表的資料清理:
有3種清理資料的方法:
3.2.1 使用循環delete的方法對表資料進行删除。
例:
declare
begin
loop
delete from 表名
where 日期字段 < sysdate-180 --或流水号字段,時間根據實際情況調整
and rownum<5000; --每次删5000條
exit when SQL%ROWCOUNT=0;
commit;
end loop;
exception
when others then
rollback;
return;
end;
3.2.2 使用“create …… as select * from…… where……”備份原表,再将原表truncate,
最後将備份表的資料插回原表insert …… select *,執行個體省略。
這個方法的主線是:
Rename備份表->建立表(不建索引)->資料插回->重建索引;
或者:
備份表->truncate原表->原表索引限制失效,删除索引->資料插回->原表索引限制激活,重建索引
3.2.3 将A表資料exp出為A.dmp檔案,再drop掉A表,重建A表後再将A.dmp資料imp回去(一般用于清理高水位表,如果要實作清理資料,則導出dmp時增加query條件過濾舊資料,原理和方法2一樣)。
導出資料:
exp userid=域名/密碼@SID file= e:\temp\A.dmp log= e:\temp\dmp.log buffer=1046000000 tables=A query=' where 字段名>sysdate-180'
drop原表: drop table A;
導回原表:
imp userid=域名/密碼@SID file= e:\temp\A.dmp ignore=y fromuser=使用者名 touser=使用者名 log= e:\temp\dmp.log
對于首次執行清理,如果要清理大量資料,隻保留少量資料時用第二種方法,該方法除了可以清理資料外,還能夠解決高水位的問題;但是如果清理的資料量不大,或者已經進入持續的自動階段,則用第一種方法。
操作前說明:
對于将本地索引重建為分區索引的,還需要注意以下事項:
1、 要有足夠的臨時表空間,因為重建索引需要對資料進行排序,要占用大量的臨時表空間。
--用下面語句來檢查索引的資料庫空間大小:
select segment_name,segment_type,sum(bytes)
from dba_segments
where segment_type like 'INDEX%'
and segment_name='索引名'
group by segment_name,segment_type;