快速进行表空间清理方案的编写和操作

一、查询数据库表空间使用率
select total.tablespace_name,
round(total.gb, 2) total_gb,
round(total.gb, 2) - round(nvl(free.gb, 0), 2) used_gb,
round(nvl(free.gb, 0), 2) free_gb,
round( 100 ( 1 - nvl( free.gb, 0 ) / total.gb ), 2 ) "USED RATE(%)",
round(nvl(free.gb, 0) / total.gb 100, 2) "FREE RATE(%)"
from (select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 GB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 GB
from dba_data_files
group by tablespace_name) total
where total.tablespace_name = free.tablespace_name(+)
order by 5 desc;

注：文章中星号不显示，如果有报错右括号丢失，自己加上这两个位置

二、查询表空间T_SPACES占用空间比较大的对象
select *
from (select segment_name,
segment_type,
tablespace_name,
sum(bytes / 1024 / 1024 / 1024) used_gb
from dba_segments
where tablespace_name = 'T_SPACES'
group by segment_name, segment_type, tablespace_name )
order by 4 desc;

经分析：占用表空间T_SPACES使用率如上图所示。
注：写的比较久了，图不见了，根据上面语句可以查询出来占用空间最大的就是最前面的，因为使用了ORDER BY DESC倒序排列。
结论：我们需要清理上图表中数据，可将表空间T_SPACES使用率降低。
三、建议删除T_TAB1表数据（因为该表与历史表T_TAB1_his有重复数据）
数据分布情况
1、T_TAB1分布情况如下：
select substr(tran_dt,1,6),count(*) from T_TAB1 group by substr(tran_dt,1,6) order by 1;

备份
exp test@test file=/aas/database_bak/T_TAB1.dmp tables=T_TAB1 log=/aas/database_bak/T_TAB1.log

清理：
truncate table T_TAB1;

分区表：
备份：
exp test@test file=/aas/database_bak/test1.dmp tables=test1 query=\"where tran_dt>='20190101'\" log=/aas/database_bak/test1.log STATISTICS=NONE DIRECT=Y compress=N INDEXES=N CONSTRAINTS=N
清理：
alter table test1 truncate partition M20181201;
alter table test1 truncate partition M20190101 update global indexes;
查看索引是否失效：
select index_name,table_name,tablespace_name,status from dba_indexes where table_name='test1';
按照条件进行备份和清理:
备份：
exp test@test file=/aas/database_bak/test2.dmp tables=test2 query=\"where tran_dt<'20180101'\" log=/aas/database_bak/test2.log

清理：
delete from test2 where tran_dt <20180101;
三种备份清理方法：面对非分区表、分区表、条件备份

查看索引是否失效：
select index_name,table_name,tablespace_name,status from dba_indexes where table_name='test2';

四、清理T_TAB1表数据（201611—201703）
1、查询数据记录数
select count(*) from T_TAB1 partition (M20170101) ;--3800371

2、truncate分区表T_TAB1数据
alter table T_TAB1 truncate partition M20170101 update global indexes;

五、查看索引是否失效(VALID有效），若失效重建索引
select status from dba_indexes where table_name='T_TAB1';
注：若有失效的索引，需要重建索引，重建索引语句如下：
alter index P_T_TAB1 rebuild online;

六、清理索引空间
在线重建索引（T_TAB1、T_TAB2）
1、T_TAB1
alter index INX1_T_TAB1 rebuild online;
alter index INX2_T_TAB1 rebuild online;
alter index PK_T_TAB1 rebuild online;

七、收集统计信息，数据导入完成以后，表统计信息还没有执行，手动执行统计信息收集
BEGIN
DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME => 'test',
TABNAME => 'test1',
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
METHOD_OPT => 'for all columns size repeat',
DEGREE => 8,
CASCADE => TRUE);
END;
/

BEGIN
DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME => 'test',
TABNAME => 'test2',
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
METHOD_OPT => 'for all columns size repeat',
DEGREE => 8,
CASCADE => TRUE);
END;
/

以上是非常简便的ORACLE数据库表空间清理方案，可以直接拿去使用。
小知识：
数据库分区索引的几个视图
dba_ind_partitions：分区索引的分区情况和统计信息。
dba_part_indexes：分区索引的概要统计信息，可以查看表中有那些分区索引，和分区索引的类型。
dba_indexes minus dba_part_indexes 可以得到每个表中哪些非分区索引
分区索引分为：本地索引和全局索引。
分区索引不能对整体重建，必须对每个分区重建。

--查询该用户下有哪些是分区表
select * from dba_part_tables where owner='SROOT'
dba_part_tables是sys用户下的表.
将表迁移到其他表空间的命令
alter test1 move tablespace table_space;
重建索引
alter index test1_px rebuild online tablespace space_index;