記一次生產數據庫XX大表(600G)truncate回收方案

概述

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

一、環境確認

1、查看錶數據大小

<code> 
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
;
 
select
 owner, table_name, column_name, segment_name, index_name
  
from
 dba_lobs
 
where
 segment_name = 
'I_TRANSACTION_XML_BLOB'
/<code>

2、查看錶數據量

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

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

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

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

<code> 
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;/<code>

二、停機備份

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

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

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

2、確認rman備份正常

確保前一天rman正常備份

<code> 
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
;/<code>

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

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

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

<code>
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 
;/<code>

三、truncate表

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

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

1、truncate表

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

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

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

<code>
truncate
 
table
  I_TRANSACTION 
reuse
 
storage
;
 
 
 
 
 
 
 
/<code>

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

<code> 
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;/<code>

3、查看錶空間大小

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

4、轉移表空間

<code> 
alter
 
table
  I_TRANSACTION_bak200501 
move
 
tablespace
 LOB3;/<code>

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

注意：

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 相關表

<code>
Alter
 
table
 I_TRANSACTION 
enable
 
row
 
movement
;
alter
 
table
 I_TRANSACTION 
shrink
 
space
 
cascade
;    
 
ALTER
 
TABLE
 I_TRANSACTION 
MODIFY 
 
LOB
(XML_BLOB) (
SHRINK
 
SPACE
 
CASCADE
);
analyze
 
table
 I_TRANSACTION 
compute
 
statistics
;   
Alter
 
table
 I_TRANSACTION 
disable
 
row
 
movement
;/<code>

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