通过DBA_EXTENTS查看占用空间的表--释放表空间

通过查询dba_data_files和dba_extents高水位标识可以收缩一部分空间

select /*+ ordered use_hash(a,c) */
  'alter database datafile '''||a.file_name||''' resize '
   ||round(a.filesize - (a.filesize - c.hwmsize-100) *0.8)||'M;',
  a.filesize,
  c.hwmsize
from 
(
select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files
) a,
(
select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents
group by file_id) c
where a.file_id = c.file_id
  and a.filesize - c.hwmsize > 100;

如果表空间中有多个对象，以前的对象空间没释放，新建对象肯定在原对象的高水位标识之后，即使以后数据删除，空间仍然无法释放。

这时候必须重新找到实际空间大小以后的对象，把这些对象移走才可能释放空间。

1.找到数据文件对应的文件号

select file#,name from v$datafile where name like '%LOADTBS.dbf';

2.找到文件中最大的块号
select max(block_id) from dba_extents where file_id=49;


3.计算一下文件中最大使用块占用的位置
select 39616145*8/1024 from dual;


4.查看高块中的表 segment_name
select segment_name,segment_type,tablespace_name,extent_id,bytes,blocks from dba_extents where file_id=49 and   block_id='39616145';


5.创建一个新的表空间，把block_id比较高的几个表移出表空间
    CREATE bigfile TABLESPACE  LOADTBS1  
    NOLOGGING 
    DATAFILE '/u02/oradata/vids/stat_tmp/LOADTBS1.dbf' SIZE 100M    
    AUTOEXTEND 
    ON NEXT 10M MAXSIZE UNLIMITED;


6.把block_id比较高的几个表移动到新的表空间

SELECT distinct  'alter table '|| SEGMENT_NAME||' move tablespace baseinfo_bak; ' FROM DBA_EXTENTS WHERE TABLESPACE_NAME='BASEINFO' AND FILE_ID=5 and block_id>1159985 and segment_type='TABLE' ;