oracle 11g数据库清理，电脑多出近200G

数据库经过各种导表，imp，impdp等操作，转移，删表，删大表后，查询空间使用率，几个达不到80%，甚至只有10%的样子，但物理占盘却很大，盘都快满了。近些天领导又来一大批数据，这下真得清理了，不然再拿另外的服务器放？——不现实！

网上各种删表，删空间的句子，真正用起来总是踩坑，以下整理了一下，并记录此次清理空间的过程，以备后记。

一、删除不用的表

各种临时建的表，还有重复导入的一个季度数据，九个月数据...，这些在我心中不够完整的历史数据，砍了。这一步比较费脑费时，需要查仔细了，分辨清楚了再去删。用到各种统计表的关键字例如：按年度group by，max(日期），min(日期)，count（1），各种维度统计后判断数据表内容，确定是不再保留的——砍，需要备份的备份。

边砍边查空间利用率，适当收缩表空间。此时会遇到：

“ORA-03297 文件包含在请求的 RESIZE 值以外使用的数据”。不给收缩！

--查表空间利用率（重点、前后对比）
select
　　a.a1 表空间名称,　　c.c2 类型,　　c.c3 区管理,
　　round(b.b2/1024/1024/1024,2) 表空间大小G,
　　round((b.b2-a.a2)/1024/1024/1024,2) 已使用G,
　　substr((b.b2-a.a2)/b.b2*100,1,5) 利用率
　　from
　　(select tablespace_name a1, sum(nvl(bytes,0)) a2 
       from dba_free_space group by tablespace_name) a,
　　(select tablespace_name b1,sum(bytes) b2 
       from dba_data_files group by tablespace_name) b,
　　(select tablespace_name c1,contents c2,extent_management c3 
       from dba_tablespaces) c
　　where a.a1=b.b1 and c.c1=b.b1
union all
select
　　'' 表空间合计,　　'' 类型,　　'' 区管理,
　　sum(b.b2/1024/1024/1024) 表空间大小G,
　　sum((b.b2-a.a2)/1024/1024/1024) 已使用G,
　　'' 利用率
　　from
　　(select tablespace_name a1, sum(nvl(bytes,0)) a2 
       from dba_free_space group by tablespace_name) a,
　　(select tablespace_name b1,sum(bytes) b2 
       from dba_data_files group by tablespace_name) b,
　　(select tablespace_name c1,contents c2,extent_management c3 
       from dba_tablespaces) c
　　where a.a1=b.b1 and c.c1=b.b1;

--生成收缩语句，运行后使空间收缩到实际使用大小
select a.file#,b.tablespace_name,
       a.name,
       a.bytes / 1024 / 1024 CurrentMB,
       ceil(HWM * a.block_size / 1024 / 1024) Resizeto,
       (a.bytes - HWM * a.block_size) / 1024 / 1024 releaseMB,
       'alter database datafile ''' || a.name || ''' resize ' ||
       ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCmd
  from v$datafile a,
       (select file_id,tablespace_name, max(block_id + blocks - 1) HWM
          from dba_extents
         group by file_id,tablespace_name) b
 where a.file# = b.file_id(+)
   and (a.bytes - HWM * a.block_size) > 0
 order by 5

--删除大表时使用：
--1.查询表的水位分配情况
SELECT table_name,blocks, empty_blocks, num_rows 
 FROM user_tables 
 WHERE table_name = 'test';
       BLOCKS    EMPTY_BLOCKS    NUM_ROWS
    10818598    0    223533284

--2.删除大表 truncate  。。。这一步这次没做，这样删不知道可不可行
--3.分析
analyze table test estimate statistics;
--4.分析后再次查看高水位线情况 
       BLOCKS    EMPTY_BLOCKS    NUM_ROWS
        8    0

--5.整理表空间 整合表空间碎片增加表空间的连续性  这个后面经常会用到
alter tablespace tablespace_name coalesce;
--单个收缩可直接运行
alter database datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST_SPACE.DBF'
  resize 10M;

二、处理  ORA-03297，文件包含在请求的 RESIZE 值以外使用的数据

1、先查出数据文件的file_id号，然后查空间使用情况

--查文件file_id
select a.file#,a.name from v$datafile a
--查这个id的空间  用前面一段也是一样的
select d.file_name,d.file_id,d.bytes/1024/1024 as d_byte,
       sum(f.bytes/1024/1024) as free_byte
 from dba_data_files d,dba_free_space f
 where d.file_id=f.file_id and d.file_id=21
 group by d.file_name,d.file_id,d.bytes/1024/1024; 

结果： 

FILE_NAME FILE_ID D_BYTE FREE_BYTE
D:\ORACLE\ORADATA\ICAPP\IC_DATA6.ORA   21   1536    1482.0625
可以看到实际上ID=21的文件只使用了大概50M左右，只是数据分布在(按一定的顺序)50M甚至在300M以外的地方,所以这里虽然看到只使用了约50M空间，但是却不能resize datafile。
2、对文件上的表和索引移动一下位置（重点）：

--1、移动表前先对表空间做整理
alter tablespace tablespace_name coalesce;
--2、在dba_extents找到与ID=21的数据文件相关的表及索引
select segment_name,partition_name,segment_type
 from dba_extents
where file_id=21;
--3、对id=21的文件上的表和索引移动位置
--创建空白的数据表空间
create tablespace TEST_SPACE   datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST_SPACE.DBF' size 10M autoextend on next 10M  maxsize unlimited extent management local;
/*以下拼接结果在相应要迁移的账号里运行，如果不这样，需拼接上对应的用户名，
网上还有一个空间多个用户使用的，也是带上相应用户名*/
--移动表
select DISTINCT 'alter table '|| segment_name || ' move tablespace TEST_SPACE;' 
from dba_extents where segment_type='TABLE' and file_id=21;
--移动索引
select DISTINCT 'alter index '|| segment_name || ' rebuild tablespace TEST_SPACE;' 
from dba_extents where segment_type='INDEX' and file_id=21;
--移动分区表
select DISTINCT 'alter table '|| segment_name || ' move partition '|| partition_name || ' tablespace TEST_SPACE;'
 from dba_extents where segment_type='TABLE PARTITION' and file_id=21;
--移动分区索引
select DISTINCT 'alter index '|| segment_name || ' rebuild partition '|| partition_name ||'  tablespace TEST_SPACE;'
 from dba_extents where segment_type='INDEX PARTITION' and file_id=21;

3、迁移后再次收缩旧空间

alter database datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\OLD_SPACE.DBF' resize 10M;

4、重新移回去，同上第2步，不过对于已经建了N多个子数据块的，移回去时会再次平均分配到各个块上，不想这样，又不去删除过多的数据块，就不要移回去了，进入5删除老空间。

5、删除空表空间，包含物理文件 ，如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS。
drop tablespace CZ_DIP including contents and datafiles cascade constraint;

//下边为网上统计的各种删除的方法.
--删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
--删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
--删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
--删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

三、已执行删除，物理盘没有空出来

1、数据库回收站清理，清理后再次查看“我的电脑”看空出来了没有。

/*可能数据在当前账号下的回收站里，也可能 并没有挂在当前账号下，
当前空间并没有任何obj，但是查空间利用率就是不降下来，这时需要用到全库回收站，
清除前后多次对比，多方对比
*/
--清除用户回收站 使用当前用户账号则可
purge recyclebin;
--清除全库回收站   需使用sys账号进行 否则报错
purge dba_recyclebin;

2、进入物理盘dbf存放目录查看，部分数据块还存在，虽然已经收缩到10M，但再删除一下，手动删除提示：“操作无法完成，因为文件已在oracleServiceorcl中打开。”

进入“我的电脑-服务”，找到这个服务，关闭（不是暂停，暂停不顶用）。

手动删除不要的数据块，重新开启服务。

附上处理前后的记录，这个过程是不断的对比，不断的查询、记录的过程，处理数据库空间需要细心和耐心。