概述

由於之前開發在資料庫設計時，將xml文件存放到資料庫上，幾年後這些xml數據已累計占用了600多G，業務部門確認可以刪除後開始執行大表回收計劃。

一、環境確認

1、查看錶數據大小

--查看大表
select d.owner,d.segment_name,d.partition_name,d.segment_type,d.tablespace_name,d.bytes/1024/1024/1024 GB 
from dba_segments d order by d.bytes desc;
--查看LOB SEGMENT對應的表
select owner, table_name, column_name, segment_name, index_name
  from dba_lobs
 where segment_name = 'I_TRANSACTION_XML_BLOB'

2、查看錶數據量

目前I_TRANSACTION表總數為11860346條，I_TRANSACTION表2020-01-01後數據量為1522162

 select count(*) from I_TRANSACTION;
 select count(*) from I_TRANSACTION where insert_date>to_date('2020-01-01','yyyy-mm-dd');

3、查看錶定義、主鍵、外鍵、觸發器（備份）

表定義、主鍵、索引從PLSQL獲取即可

--觸發器
SELECT * FROM DBA_TRIGGERS WHERE TABLE_NAME='I_TRANSACTION';
SELECT DBMS_METADATA.GET_DDL('TRIGGER','觸發器名字','GLOGOWNER') FROM DUAL;
--查詢表本身的外鍵
select u.owner, u.table_name, 'alter table ' || table_name || ' drop constraint ' ||constraint_name || ';',u.status from user_constraints u where constraint_type = 'R' and table_name = 'I_TRANSACTION';
--查詢表與表之間的主外鍵關係
select a.owner "zhujian_owner",a.table_name "zhujian_tab",b.column_name "zhujian_col",C.OWNER "waijian_owner",c.table_name "waijian_tab",d.column_name "waijian_col",C.constraint_name,'alter table ' || C.table_name || ' drop constraint ' ||
C.constraint_name || ';' "drop constraint" from user_constraints a
left join user_cons_columns b on a.constraint_name = b.constraint_name
left join user_constraints C ON C.R_CONSTRAINT_NAME = a.constraint_name
left join user_cons_columns d on c.constraint_name = d.constraint_name
where a.constraint_type = 'P' and a.table_name = 'I_TRANSACTION'
order by a.table_name;

二、停機備份

計劃保留2020年4月15日後的數據，之前的數據不做保留。

1、關閉應用系統以及接口平台

手工關閉後，確認關閉後在執行以下備份表工作。

2、確認rman備份正常

確保前一天rman正常備份

--查看備份成功的歷史記錄
SELECT * FROM V$RMAN_STATUS 
WHERE START_TIME >= TO_DATE(&START_TIME,'YYYY-MM-DD HH24:MI:SS')
  AND END_TIME   <= TO_DATE(&END_TIME  ,'YYYY-MM-DD HH24:MI:SS')
  AND OPERATION ='BACKUP'
  AND STATUS ='COMPLETED'
--查看所有備份集詳細信息
SELECT A.RECID "BACKUP SET",
       A.SET_STAMP,
        DECODE (B.INCREMENTAL_LEVEL,
                '', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
                1, 'Incr-1級',
                0, 'Incr-0級',
                B.INCREMENTAL_LEVEL)
           "Type LV",
        B.CONTROLFILE_INCLUDED "包含CTL",
        DECODE (A.STATUS,
                'A', 'AVAILABLE',
                'D', 'DELETED',
                'X', 'EXPIRED',
                'ERROR')
           "STATUS",
        A.DEVICE_TYPE "Device Type",
        A.START_TIME "Start Time",
        A.COMPLETION_TIME "Completion Time",
        A.ELAPSED_SECONDS "Elapsed Seconds",
        A.BYTES/1024/1024/1024 "Size(G)",
        A.COMPRESSED,
        A.TAG "Tag",
        A.HANDLE "Path"
   FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
  WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'
RDER BY A.COMPLETION_TIME DESC;
--驗證資料庫還原（暫不考慮）
restore database validate;

3、備份大表最近半個月數據並檢查數據

因為耗時過久這裡不考慮expdp備份，提前在正式環境模擬備份表測試，保留4月15號後表的數據，耗時5380s，DATA表空間消耗17G。

備份時同時觀察undo表空間、數據表空間以及告警日誌。

create table  I_TRANSACTION_bak200501 as select * from   I_TRANSACTION where 
insert_date>to_date('2020-04-15 00:00:00','yyyy-mm-dd hh24:mi:ss');
select count(*) from I_TRANSACTION_bak200501;
select * from I_TRANSACTION order by insert_date desc;

三、truncate表

因為delete一個600G的表幾天是做不了的，且產生歸檔日誌過大，很容易影響資料庫性能，所以採取truncate方案。

刪除時同時觀察undo表空間、數據表空間以及告警日誌。

1、truncate表

truncate是一個DDL命令，這樣一旦執行，事務將無法回滾。將更新數據字典，將數據字典里相關的數據予以刪除，然後將表的數據塊全部釋放，並且將表的HWM下降到最低，但是，在我們處理很大的表的時候，如果處理的表占巨大的空間，在truncate去釋放表的數據塊的消耗是巨大的，在這個過程中對處理的表是不能訪問。

為了儘量減小truncate大表是對系統的影響，加上 reuse storage, 這樣通知處理表的時候，在更新完數據字典以後，並不馬上釋放所有的數據塊，HWM也進行更新，下降到低水位，然後用 deallocate unused keep xxM在系統比較空閒的時候，來釋放數據塊。

在執行keep 0mb 之前，其他用戶已經向表里插入了數據，則不會真的把表所有數據塊釋放，只是釋放沒有用的數據塊而已。

truncate table  I_TRANSACTION reuse storage;
--alter table  I_TRANSACTION deallocate unused keep 600G;
--alter table  I_TRANSACTION deallocate unused keep 500G;
--alter table  I_TRANSACTION deallocate unused keep 400G;
--alter table  I_TRANSACTION deallocate unused keep 300G;
--alter table  I_TRANSACTION deallocate unused keep 200G;
--alter table  I_TRANSACTION deallocate unused keep 100G;
--alter table  I_TRANSACTION deallocate unused keep 50G;
--alter table  I_TRANSACTION deallocate unused keep 0M;

2、確認表數據量、主鍵、外鍵是否有影響

--觸發器
SELECT * FROM DBA_TRIGGERS WHERE TABLE_NAME='I_TRANSACTION';
SELECT DBMS_METADATA.GET_DDL('TRIGGER','觸發器名字','GLOGOWNER') FROM DUAL;
--查詢表本身的外鍵
select u.owner, u.table_name, 'alter table ' || table_name || ' drop constraint ' ||constraint_name || ';',u.status from user_constraints u where constraint_type = 'R' and table_name = 'I_TRANSACTION';
--查詢表與表之間的主外鍵關係
select a.owner "zhujian_owner",a.table_name "zhujian_tab",b.column_name "zhujian_col",C.OWNER "waijian_owner",c.table_name "waijian_tab",d.column_name "waijian_col",C.constraint_name,'alter table ' || C.table_name || ' drop constraint ' ||
C.constraint_name || ';' "drop constraint" from user_constraints a
left join user_cons_columns b on a.constraint_name = b.constraint_name
left join user_constraints C ON C.R_CONSTRAINT_NAME = a.constraint_name
left join user_cons_columns d on c.constraint_name = d.constraint_name
where a.constraint_type = 'P' and a.table_name = 'I_TRANSACTION'
order by a.table_name;

3、查看錶空間大小

可以看到表空間已降下來了

4、轉移表空間

 alter table  I_TRANSACTION_bak200501 move tablespace LOB3;

四、回收高水位並重新收集統計信息(以下秒執行)

注意：

alter table I_TRANSACTION shrink space compact; --壓縮階段 (oracle建議在尖峰時間壓縮)

alter table I_TRANSACTION shrink space; --收縮階段(oracle建議在不忙的時候收縮，收縮會產生排他鎖，因此其他用戶不能對收縮的表經行任何操作)

alter table I_TRANSACTION shrink space cascade; --不僅收縮I_TRANSACTION 表的，還收縮I_TRANSACTION 相關表

Alter table I_TRANSACTION enable row movement;
alter table I_TRANSACTION shrink space cascade;   --收縮階段
-- Shrink a LOB segment
ALTER TABLE I_TRANSACTION MODIFY LOB(XML_BLOB) (SHRINK SPACE CASCADE);
analyze table I_TRANSACTION compute statistics;  --收集統計信息
Alter table I_TRANSACTION disable row movement;

覺得有用的朋友多幫忙轉發哦！後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注下~