ORACLE数据文件收缩步骤

本人使用的是19c版本，使用使用过程中发现预分配空间冗余率太高，通过下述步骤成功进行文件收缩。

-----1 查询各表空间文件使用情况
SELECT
*
FROM
(
SELECT
a.tablespace_name,
to_char( a.bytes / 1024 / 1024, '999,999' )||'M' total_bytes,
to_char( b.bytes / 1024 / 1024, '999,999' )||'M' free_bytes,
to_char( a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024, '999,999' )||'M' use_bytes,
to_char( ( 1 - b.bytes / a.bytes ) * 100, '990.99' ) || '%' USE
FROM
( SELECT tablespace_name, sum( bytes ) bytes FROM dba_data_files GROUP BY tablespace_name ) a,
( SELECT tablespace_name, sum( bytes ) bytes FROM dba_free_space GROUP BY tablespace_name ) b
WHERE
a.tablespace_name = b.tablespace_name UNION ALL
SELECT
c.tablespace_name,
to_char( c.bytes / 1024 / 1024, '999,999' )||'M' total_bytes,
to_char( ( c.bytes - d.bytes_used ) / 1024 / 1024, '999,999' )||'M' free_bytes,
to_char( d.bytes_used / 1024 / 1024, '999,999' )||'M' use_bytes,
to_char( d.bytes_used * 100 / c.bytes, '990.99' ) || '%' USE
FROM
( SELECT tablespace_name, sum( bytes ) bytes FROM dba_temp_files GROUP BY tablespace_name ) c,
( SELECT tablespace_name, sum( bytes_cached ) bytes_used FROM v$temp_extent_pool GROUP BY tablespace_name ) d
WHERE
c.tablespace_name = d.tablespace_name
) ;

---2 了解各数据文件详情
select * from dba_data_files t where t.file_name like '%USERS%';

---3 了解各数据文件的剩余空间详情
SELECT * FROM DBA_FREE_SPACE ORDER BY 2;

---4 统计表空间合计空间大小
 SELECT tablespace_name, sum( bytes ) bytes FROM dba_data_files GROUP BY tablespace_name ;
 
 ---5 统计指定数据文件的数据位置大小，block_size=8k
 
 ---查询数据文件对应的file_id
 select file#,name from v$datafile;
 
select max(block_id) from dba_extents where file_id=8;  

----数据的使用占用容量大小
 select 621952*8/1024 from dual;
 

 ---621952  已使用4859M
select max(block_id) from dba_extents where file_id=9;  
 
select 621952*8/1024 from dual;
 
---6 结合总空间和剩余空间，计算收缩后的数据文件大小
alter database datafile 'D:\SOFTWARE\ORACLE_19C\A\ORADATA\ORCL\USERS03.dbf' resize 6000M ;
 
alter database datafile 'D:\SOFTWARE\ORACLE_19C\A\ORADATA\ORCL\USERS02.dbf' resize 16000M ;
    
 commit;