查看数据库文件用到了哪些目录

-- file path
select  filetype,directory_path,
        count(*) as file_cnt,
        round(sum(bytes)/1024/1024/1024) as size_gb,
        round(sum(maxbytes)/1024/1024/1024) as maxsize_gb
from (
    SELECT 
        filetype,
        file_name,
        -- 从最后 '/' 开始提取文件名
        SUBSTR(file_name,INSTR(file_name, '/', -1) + 1) AS file_name_only,
        -- 提取路径
        SUBSTR(file_name,1,INSTR(file_name, '/', -1) - 1) AS directory_path,
        BYTES,
        MAXBYTES
    FROM (select 'datafile' as filetype,ddf.FILE_NAME,ddf.FILE_ID,ddf.BYTES,decode(ddf.MAXBYTES,0,ddf.BYTES,ddf.MAXBYTES) as MAXBYTES from dba_data_files ddf union all
          select 'tempfile' as filetype,dtf.FILE_NAME,dtf.FILE_ID,dtf.BYTES,decode(dtf.MAXBYTES,0,dtf.BYTES,dtf.MAXBYTES) as MAXBYTES from dba_temp_files dtf union all
          select 'logfile_'||lf.TYPE as filetype,lf.MEMBER as FILE_NAME,1 as file_id,l.BYTES,l.BYTES as MAXBYTES  from v$log l,v$logfile lf where l.GROUP#=lf.GROUP#  union all
          select 'logfile_'||lf.TYPE as filetype,lf.MEMBER as FILE_NAME,1 as file_id,l.BYTES,l.BYTES as MAXBYTES  from v$standby_log l,v$logfile lf where l.GROUP#=lf.GROUP#  
         )
    order by filetype,FILE_ID
    )
group by filetype,directory_path
order by filetype,directory_path
;

查看能收缩多少

-- reduce size
SELECT
    df.tablespace_name,
    df.file_id,
    df.file_name,
    df.AUTOEXTENSIBLE,
    df.bytes / 1024 / 1024 / 1024 AS current_size_gb,
    e.max_block * 8192 /1024/ 1024 / 1024 AS current_used_gb,  -- 实际用到的位置
    ROUND((df.bytes - e.max_block * 8192) / 1024 / 1024 / 1024, 2) AS can_shrink_gb,
    -- 建议收缩到大小（实际使用 + 1G 安全余量）
    CEIL(e.max_block * 8192 /1024 / 1024 / 1024) + 1 AS resize_to_gb,
    'alter database datafile '''||df.FILE_NAME||''' resize '||(CEIL(e.max_block * 8 / 1024 / 1024) + 1) || 'G;' as ddl_resize
FROM  dba_data_files df,
      ( SELECT file_id, MAX(block_id + blocks - 1) max_block
        FROM dba_extents
        GROUP BY file_id
      ) e
WHERE 1=1
  and df.file_id = e.file_id
  and df.tablespace_name NOT IN ('SYSTEM','SYSAUX','UNDOTBS1')
ORDER BY
    can_shrink_gb DESC;

收缩前需要检查下回收站

select  a.*
from dba_recyclebin a
where 1=1
;

select 'PURGE TABLE "'||a.owner||'"."'||a.object_name||'";' as ddl_purge, a.*
from dba_recyclebin a
where 1=1
and a.type='TABLE'
order by a.owner,a.original_name
;