数据表处理的综合操作总结（记一次生产数据库XX大表）
数据表处理的综合操作总结（记一次生产数据库XX大表）手工关闭后，确认关闭后在执行以下备份表工作。1、关闭应用系统以及接口平台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; --查询表本身的外键
概述由于之前开发在数据库设计时，将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方面的内容，感兴趣的朋友可以关注下~
