记一次生产数据库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方面的内容，感兴趣的朋友可以关注下~