表空间回收记录

1 表空间使用率不是很高，但数据文件已经导致磁盘空间不足

2 尝试resize 数据文件，报错如下：

2.1 表空间上有事务未提交，杀会话解决。

        'sp_close_session('''
        ||sess_id ||''');'
from
        v$sessions
where    sess_id !=sessid() and
        trx_id in
        (
                select trx_id from v$lock where table_id=0 and trx_id<>0
        );

2.2 无法回收簇，检查数据文件尾部extent_id ，查看数据类型。

通过vdatafile 查看想要回收数据文件的group_id 通过VEXTENTS order 查看尾部对象的SEG_ID
通过v$SEGMENT_INFOS 查看尾部对象的obj_id
通过dba_objects 确定对象类型、名称

select *from dba_objects where object_id='33627934'
select *from v$SEGMENT_INFOS where SEG_ID='111706' --查obj_id33591173
select *from V$EXTENTS where ts_id=17 order by extent_id desc  --使用数据文件的ts_id查SEG_ID
select *from SYS.V$DATAFILE；--group_id=17

每一个尾部的extent_id 变成 free 或 seg_id=0，就可以 resize 减少32*32K 的空间。根据空间使用率，计划空间resize到300g以下。

2.3 批量处理

预估想要将数据文件resize到300g。

select * from dba_objects where object_id in (
select distinct obj_id from v$segment_infos where seg_id in(
select  seg_id from v$extents where ts_id=17 and state<>'FREE' and seg_id>0 and extent_id >300000
order by extent_id desc
))；

本次案例查询完：有31个对象的extent_id>300000。将这些对象进行处理，如：表move到其他表空间，物化试图临时删除。
清理后执行CALL SP_RECLAIM_TS_FREE_EXTENTS(‘表空间名’);重组表空间空闲簇，可快速清理puger，如还是无法清理，可重启数据库。

计算可以reszie的大小

--显示多少就可以resize多少。
SELECT   
    c.name tablespace_name ,a.path file_name,  b.ts_id,b.file_id,
    page  / 1024.0 AS "Blk. size(Kb)",  
    CEIL( NVL(b.hwm, 1) * (page/1024.0/1024.0) ) AS "smallest(Mb) - HWM",   --向上取整
    a.TOTAL_SIZE * (page/1024.0/1024.0) AS "currsize(Mb)",  
    a.TOTAL_SIZE * (page/1024.0/1024.0) -  NVL(b.hwm, 1) * (page/1024.0/1024.0)  AS "savings(Mb)"  
    
FROM   
    V$datafile a  
LEFT JOIN   
    (  
SELECT   
         ts_id, file_id,  
         MAX(1.0*V_EXT.EXTENT_ID * SF_GET_EXTENT_SIZE + V_EXT.USED ) AS hwm  
     FROM   
         V$EXTENTS as V_EXT  where state not in ('FREE')  and seg_id>0
     GROUP BY   
         ts_id,file_id   
    ) b ON (a.id = b.file_id and a.group_id=b.ts_id)  join v$tablespace c on a.GROUP_ID = c.ID

WHERE   
    a.status$ != 0  
    and a.path like '%1217.DBF'  --改为指定的数据文件即可 
ORDER BY   
"smallest(Mb) - HWM" DESC, tablespace_name, file_name;  

alter tablespace "HRORG_1217" resize datafile 'HRORG_1217.DBF' to 279397;