数据库空间清理总结

数据库空间清理（临时表、备份表）清理

背景：随着项目业务使用，数据库空间占用越来越大，空间占用中包含业务数据和垃圾数据(备份表及由程序产生的五万多冗余临时表)，需先将垃圾数据清理后，进行缩库工作（创建新的数据库实例，重新建表并导入数据）。

该文档讲述的是垃圾表（临时表、备份表）删除操作过程，在表删除之前需要形成四个文档(待清理临时表、待清理备份表、大于1G的数据表、表空间占用数据表)与项目业务团队进行确认后再进行删除，所有信息都通过执行下方SQL获取。

（1）获取数据库存在的临时表数量填入数据库信息摸排表

（2）执行生成待清理临时表的SQL，将导出的excle文件，复制到txt文件中，命名格式为某业务系统数据库+待清理临时表。

--查找数据库存在的临时表数量
select count(1) from user_tables where table_name like 'TMPTB%';

--生成待清理临时表的SQL，并导出形成excel文件。	
select 'DROP TABLE ' || TABLE_NAME || ';' from user_tables where table_name like 'TMPTB%';	

（3）获取清理备份表数量、待清理备份表占用空间填入数据库信息摸排表。

--查找带待清理备份表数量，待清理备份表占用空间														select COUNT(se.RELNAME) AS 备份表数量 ,SUM(se.SIZE/(1024*1024*1024)) AS 备份表占用的空间  from v_sys_segment_info se ,sys_class cl,sys_namespace nm
where se.RELID = cl.OID 
and nm.OID = cl.RELNAMESPACE
and cl.RELKIND = 'r'
and nm.NSPNAME = 'GSCLOUD'
AND se.RELNAME LIKE '%_BAK%';

（4）执行生成待删除备份表的SQL，将导出的excle文件，复制到txt文件中，命名格式为命名格式为某业务系统数据库+待删除备份表的SQL。

--查看待清理备份表名称及占用空间
select se.RELNAME ,se.SIZE/(1024*1024) MB from v_sys_segment_info se ,sys_class cl,sys_namespace nm
where se.RELID = cl.OID 
and nm.OID = cl.RELNAMESPACE
and cl.RELKIND = 'r'
and nm.NSPNAME = 'GSCLOUD'
AND se.RELNAME LIKE '%_BAK%'
order by MB desc;

--生成待删除备份表的SQL，并导出形成sql文件
select  'DROP TABLE ' || se.RELNAME  || ';' from v_sys_segment_info se ,sys_class cl,sys_namespace nm
where se.RELID = cl.OID 
and nm.OID = cl.RELNAMESPACE
and cl.RELKIND = 'r'
and nm.NSPNAME = 'GSCLOUD'
AND se.RELNAME LIKE '%_BAK%';

（5）执行查询数据库所以表的名称和大小SQL（按照数据库表大小倒叙排列），导出大于1G的表，用于排查是否有大于1G的数据表，命名格式为某业务系统数据库+大于1G的数据表。

--查询数据库所以表的名称和大小SQL，按照数据库表大小倒叙排列。
select se.RELNAME ,se.SIZE/(1024*1024*1024) gb from v_sys_segment_info se ,sys_class cl,sys_namespace nm
where se.RELID = cl.OID 
and nm.OID = cl.RELNAMESPACE
and cl.RELKIND = 'r'
and nm.NSPNAME = 'GSCLOUD'
order by gb desc;

（6）执行查看数据库表空间大小SQL，将结果导出excle 填入数据库信息摸排表。

--查看数据库表空间大小SQL
SELECT --B.file_name "文件名",
       A.TABLESPACE_NAME "表空间名",
       TOTAL "表空间大小",
       FREE "表空间剩余大小",
       (TOTAL - FREE) "表空间使用大小",
       TOTAL / (1024 * 1024 * 1024) "表空间大小(G)",
       FREE / (1024 * 1024 * 1024) "表空间剩余大小(G)",
       (TOTAL - FREE) / (1024 * 1024 * 1024) "表空间使用大小(G)",
       ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "使用率 %"
FROM   (SELECT TABLESPACE_NAME,
               SUM(BYTES) FREE
        FROM   DBA_FREE_SPACE
        GROUP  BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME,
             --  file_name,
               SUM(BYTES) TOTAL
        FROM   DBA_DATA_FILES
        GROUP  BY TABLESPACE_NAME/*,file_name*/) B
WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME;

数据库空间清理（临时表、备份表）清理

CATALOG

FEATURED TAGS

FRIENDS