Oracle：如何查找表使用的存储空间？

通过OEM 10g，

1. 像往常一样连接到数据库
2. 点击Schema标签
3. 在数据库对象内的Tables链接
4. input模式名称和对象名称（可选），然后单击Go
5. 使用单选buttonselect要查看的表格，然后单击Edit （不要单击表格名称链接）
6. 点击Segments选项卡（并等待…）
7. 您将看到表格数据的大小和使用的索引。

好的，这在技术上回答了你的问题。 但更好的方法是：

1. 使用SQLPLUSlogin
2. 从文森特运行脚本。

我喜欢将脚本保存为t.sql作为快速参考

 COLUMN size_mb FORMAT '999,999,990.0' COLUMN num_rows FORMAT '999,999,990' COLUMN fmt_short FORMAT A24 COLUMN owner FORMAT A16 COLUMN table_name LIKE fmt_short COLUMN tablespace_name LIKE fmt_short SET LINESIZE 200 SET AUTOTRACE OFF COMPUTE SUM OF size_mb ON REPORT BREAK ON REPORT SELECT lower( owner ) AS owner ,lower(table_name) AS table_name ,tablespace_name ,num_rows ,blocks*8/1024 AS size_mb ,pct_free ,compression ,logging FROM all_tables WHERE owner LIKE UPPER('&1') OR owner = USER ORDER BY 1,2; CLEAR COMPUTES CLEAR BREAKS 

表使用的空间是所有扩展使用的空间：

 SELECT SUM(bytes), SUM(bytes)/1024/1024 MB FROM dba_extents WHERE owner = :owner AND segment_name = :table_name; SUM(BYTES) MB ---------- ---------- 3066429440 2924,375 

您确定使用all_tables视图的原始方法包含LOB扩展？ 我想不会。 这是我觉得有用的一个：

 with da as ( SELECT owner, segment_name, SUM(bytes)/1024/1024 size_mb FROM dba_extents group by rollup(owner, segment_name) ) select owner, segment_name, size_mb, round(size_mb/total_mb*100) from da cross join ( select size_mb as total_mb from da t where owner is null and segment_name is null ) order by size_mb desc 

它向我展示了什么是最大的空间。

经过一些谷歌search和实验，我已经build立了以下查询，这提供了我的脑海里最准确的结果。 对于oracle用户，它通过表和段types（TABLE *，INDEX *，LOB *）提供了free / used / total空间。 您可以轻松扩展它以提供更多的统计信息，如块计数。

典型输出是：

 table ; segment type ; used (mb) ; unused (mb) ; total (mb) user ; INDEX ; 0,78; 0,00; 0,78 user ; LOBINDEX ; 0,15; 0,00; 0,15 user ; LOBSEGMENT ; 3,48; 1,19; 4,67 user ; TABLE ; 12,11; 2,74; 14,85 address ; INDEX ; 0,12; 0,00; 0,12 (...) 

脚本：

 DECLARE input_owner NVARCHAR2(128) := 'MY_ORACLE_OWNER'; segment_size_blocks NUMBER; segment_size_bytes NUMBER; used_blocks NUMBER; used_bytes NUMBER; expired_blocks NUMBER; expired_bytes NUMBER; unexpired_blocks NUMBER; unexpired_bytes NUMBER; total_blocks NUMBER; total_bytes NUMBER; unused_blocks NUMBER; unused_bytes NUMBER; last_ext_file_id NUMBER; last_ext_blk_id NUMBER; last_used_blk NUMBER; result_table NVARCHAR2(128); result_segment_type NVARCHAR2(128); result_used_mb NUMBER; result_unused_mb NUMBER; result_total_mb NUMBER; CURSOR cur IS SELECT s.segment_name AS segment_name, s.owner AS segment_owner, s.partition_name AS partition_name, s.segment_type AS segment_type, CASE WHEN s.segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') THEN s.segment_name WHEN s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION') THEN (SELECT i.table_name FROM dba_indexes i WHERE s.segment_name = i.index_name AND s.owner = i.owner) WHEN s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION') THEN (SELECT l.table_name FROM dba_lobs l WHERE s.segment_name = l.segment_name AND s.owner = l.owner) WHEN s.segment_type IN ('LOBINDEX') THEN (SELECT l.table_name FROM dba_lobs l WHERE s.segment_name = l.index_name AND s.owner = l.owner) ELSE 'Unknown' END AS table_name, s.bytes AS segment_bytes FROM dba_segments s WHERE owner = input_owner ORDER BY table_name, segment_type; BEGIN dbms_output.put_line('table ; segment type ; used (mb) ; unused (mb) ; total (mb)'); FOR ro IN cur LOOP result_table := ro.table_name; result_segment_type := ro.segment_type; IF ro.segment_type IN ('TABLE', 'INDEX') THEN dbms_space.unused_space( segment_owner => ro.segment_owner, segment_name => ro.segment_name, segment_type => ro.segment_type, total_blocks => total_blocks, total_bytes => total_bytes, unused_blocks => unused_blocks, unused_bytes => unused_bytes, last_used_extent_file_id => last_ext_file_id, last_used_extent_block_id => last_ext_blk_id, last_used_block => last_used_blk); result_used_mb := (total_bytes - unused_bytes) / 1024 / 1024; result_unused_mb := unused_bytes / 1024 / 1024; result_total_mb := total_bytes / 1024 / 1024; ELSIF ro.segment_type IN ('LOBSEGMENT') THEN dbms_space.space_usage( segment_owner => ro.segment_owner, segment_name => ro.segment_name, segment_type => 'LOB', partition_name => ro.partition_name, segment_size_blocks => segment_size_blocks, segment_size_bytes => segment_size_bytes, used_blocks => used_blocks, used_bytes => used_bytes, expired_blocks => expired_blocks, expired_bytes => expired_bytes, unexpired_blocks => unexpired_blocks, unexpired_bytes => unexpired_bytes ); result_used_mb := used_bytes / 1024 / 1024; result_unused_mb := (segment_size_bytes - used_bytes) / 1024 / 1024; result_total_mb := segment_size_bytes / 1024 / 1024; ELSE -- TODO ?? result_used_mb := ro.segment_bytes / 1024 / 1024; result_unused_mb := 0; result_total_mb := result_used_mb + result_unused_mb; END IF; dbms_output.put_line( RPAD(result_table, 30) || '; ' || RPAD(result_segment_type, 20)|| '; ' || TO_CHAR(result_used_mb / 1024 / 1024, '999999999990D00')|| '; ' || TO_CHAR(result_unused_mb / 1024 / 1024, '999999999990D00')|| '; ' || TO_CHAR(result_total_mb / 1024 / 1024, '999999999990D00')); END LOOP; END;