Toad收缩oracle表空间数据文件时使用的SQL

我这里先将oracle11G的在NEU表空间所使用的用户与用户对像已经删除了，

再用toad工具软件收缩的数据文件。

具体表空间水位与收缩原理请百度。

只是记录下。

--------------------------------------------------------------------------------
Timestamp: 2021/2/2 11:12:11


Select file_id, bytes / blocks block_size
FROM DBA_DATA_FILES
WHERE file_name = 'G:\APP\ADMINISTRATOR\ORADATA\DB1\NEU.DBF'




fname=['G:\APP\ADMINISTRATOR\ORADATA\DB1\NEU.DBF']




Elapsed time: 0.013


--------------------------------------------------------------------------------
Timestamp: 2021/2/2 11:12:11


Select MAX (block_id + blocks) highblock
FROM DBA_EXTENTS
WHERE  tablespace_name = 'NEU'
AND    file_id = 6




tsn=['NEU']
fid=[6]




Elapsed time: 0.083


--------------------------------------------------------------------------------
Timestamp: 2021/2/2 11:12:53


ALTER DATABASE 
  DATAFILE 'G:\APP\ADMINISTRATOR\ORADATA\DB1\NEU.DBF'
 RESIZE 2M




Elapsed time: 0.119


--------------------------------------------------------------------------------
Timestamp: 2021/2/2 11:12:53


Select MAX(d.bytes) total_bytes,
       nvl(SUM(f.Bytes), 0) free_bytes,
       d.file_name,
       MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
       ROUND(SQRT(MAX(f.BLOCKS)/SUM(f.BLOCKS))*(100/SQRT(SQRT(COUNT(f.BLOCKS)) )), 2) frag_idx
from   DBA_FREE_SPACE f , DBA_DATA_FILES d
where  f.tablespace_name(+) = d.tablespace_name
and    f.file_id(+) = d.file_id
and    d.tablespace_name = 'NEU'
group by d.file_name




tname=['NEU']




Elapsed time: 0.004


--------------------------------------------------------------------------------
Timestamp: 2021/2/2 11:12:53


Select file_name, BYTES, blocks, autoextensible, 
       nvl(increment_by, 0) increment_by, maxbytes, maxblocks, status, maxblocks maxextend, file_id
from   sys.dba_data_files
where  TABLESPACE_NAME = 'NEU'




Tname=['NEU']




Elapsed time: 0.003