统计oracel表空间和用户的数据文件占物理磁盘空间大小

select banner from v$version;


-----统计磁盘空间文件大小
SELECT --B.file_name "文件名",
       A.TABLESPACE_NAME "表空间名",
       TOTAL "表空间大小",
       FREE "表空间剩余大小",
       (TOTAL - FREE) "表空间使用大小",
       TOTAL / (1024 * 1024 * 1024) "表空间大小(G)",
       FREE / (1024 * 1024 * 1024) "表空间剩余大小(G)",
       (TOTAL - FREE) / (1024 * 1024 * 1024) "表空间使用大小(G)",
       ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "使用率 %"
FROM   (SELECT TABLESPACE_NAME,
               SUM(BYTES) FREE
        FROM   DBA_FREE_SPACE
        GROUP  BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME,
             --  file_name,
               SUM(BYTES) TOTAL
        FROM   DBA_DATA_FILES
        GROUP  BY TABLESPACE_NAME/*,file_name*/) B
WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME;

---查询一个表空间下有多少个用户
select owner ,tablespace_name   from dba_tables where tablespace_name='GFRMS_PUB_TBS'  group by owner,tablespace_name;

---- 查看用户占磁盘空间文件数据量大小
select  v.* ,ROUND(USER_TABLESPACE_SIZE_GB) GB 
from (
      SELECT owner ,SUM(BYTES)/1024/1024/1024 AS USER_TABLESPACE_SIZE_GB 
      FROM DBA_SEGMENTS 
      GROUP BY owner 
) v ;