概述

由于之前开发在数据库设计时，将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方面的内容，感兴趣的朋友可以关注下~

玖叶教程网

前端编程开发入门

记一次生产数据库XX大表(600G)truncate回收方案

概述

一、环境确认

二、停机备份

三、truncate表

四、回收高水位并重新收集统计信息(以下秒执行)