要查询Oracle数据库的存储容量，可以通过以下步骤和SQL语句获取相关信息：

一、查看数据库总存储容量

1.数据文件 + 临时文件

SELECT 
  ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS "Total Size (GB)"
FROM 
  (SELECT bytes FROM dba_data_files 
   UNION ALL 
   SELECT bytes FROM dba_temp_files);
 

2.包含控制文件、日志文件等（需访问动态视图）

SELECT 
  (SELECT SUM(bytes) FROM dba_data_files) 
  + (SELECT SUM(bytes) FROM dba_temp_files) 
  + (SELECT SUM(blocks * block_size) FROM v$controlfile) 
  + (SELECT SUM(bytes) FROM v$log) 
  + (SELECT SUM(bytes) FROM v$archived_log WHERE deleted = 'NO') AS total_bytes
FROM dual;
 

二、按表空间查看存储使用情况

1.普通表空间使用情况

SELECT 
  a.tablespace_name,
  ROUND(a.total_size, 2) "Total Size (GB)",
  ROUND(a.total_size - b.free_size, 2) "Used Size (GB)",
  ROUND(b.free_size, 2) "Free Size (GB)",
  ROUND((a.total_size - b.free_size) / a.total_size * 100, 2) "Used %"
FROM 
  (SELECT tablespace_name, SUM(bytes)/1024/1024/1024 AS total_size 
   FROM dba_data_files 
   GROUP BY tablespace_name) a,
  (SELECT tablespace_name, SUM(bytes)/1024/1024/1024 AS free_size 
   FROM dba_free_space 
   GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY "Used %" DESC;
 

2.临时表空间使用情况

SELECT 
  tablespace_name,
  ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) "Total Size (GB)",
  ROUND(SUM(bytes - free_space) / 1024 / 1024 / 1024, 2) "Used Size (GB)"
FROM 
  (SELECT tablespace_name, bytes, 
          (SELECT SUM(bytes) FROM dba_free_space 
           WHERE tablespace_name = f.tablespace_name) AS free_space
   FROM dba_temp_files f)
GROUP BY tablespace_name;
 

三、查看数据文件详细信息

SELECT 
  file_name,
  tablespace_name,
  ROUND(bytes / 1024 / 1024 / 1024, 2) "Size (GB)",
  ROUND((bytes - NVL((SELECT SUM(bytes) FROM dba_free_space 
                     WHERE file_id = f.file_id), 0)) / 1024 / 1024 / 1024, 2) "Used (GB)",
  ROUND(NVL((SELECT SUM(bytes) FROM dba_free_space 
            WHERE file_id = f.file_id), 0) / 1024 / 1024 / 1024, 2) "Free (GB)"
FROM dba_data_files f;
 

四、查看对象占用空间（表/索引）

SELECT 
  owner,
  segment_name,
  segment_type,
  ROUND(bytes / 1024 / 1024 / 1024, 2) "Size (GB)"
FROM dba_segments
ORDER BY bytes DESC;
 

五、其他存储组件

1.在线重做日志

SELECT 
  group#, 
  ROUND(bytes / 1024 / 1024, 2) "Size (MB)",
  members 
FROM v$log;
 

2.归档日志

SELECT 
  ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) "Total Archived Size (GB)"
FROM v$archived_log 
WHERE deleted = 'NO';
 

注意事项

1. 权限要求：执行上述查询需要 DBA 权限或 SELECT_CATALOG_ROLE。
2. 单位转换：根据实际需要调整单位（如 1024 换为 1000 或改用 MB）。
3. ASM存储：若使用ASM，需查询 v$asm_diskgroup 获取磁盘组信息：

SELECT

name,

total_mb / 1024 "Total (GB)",

free_mb / 1024 "Free (GB)"

FROM v$asm_diskgroup;

4. 版本差异：不同Oracle版本可能存在视图字段差异，建议参考官方文档。

通过以上方法，可以全面了解Oracle数据库的存储容量分布及使用情况。