我们有时候需要计算表的大小，找出最大的表。下面这个脚本就是做这个查询的：

set lines 1000 pages 50000
col owner for a5
col tname for a30
-- the following script will display the top objects/tables in size order desc
WITH 
t AS ( 
SELECT owner, table_name,
       CASE 
          WHEN partitioned = 'YES' THEN 'Partitioned Table'
          WHEN iot_type IS NOT NULL THEN 'IOT Table'
          WHEN cluster_name IS NOT NULL THEN 'Clustered Table'
          ELSE 'Normal Table'
       END AS type
FROM dba_tables
-- WHERE tablespace_name=''   -- you can add some filter here
),
seg AS (
SELECT t.owner AS owner, t.table_name AS tname, t.type AS type, s.bytes AS tbytes, 0 AS ibytes, 0 AS lbytes
FROM t JOIN dba_segments s ON (t.owner=s.owner AND t.table_name = s.segment_name)
UNION ALL
SELECT t.owner AS owner, t.table_name AS tname, t.type AS type, 0 AS tbytes, s.bytes AS ibytes, 0 AS lbytes
FROM t JOIN dba_indexes i ON (t.owner=i.table_owner AND t.table_name=i.table_name)
JOIN dba_segments s ON (i.owner=s.owner AND i.index_name = s.segment_name)
UNION ALL
SELECT t.owner AS owner, t.table_name AS tname, t.type AS type, 0 AS tbytes, 0 AS ibytes, s.bytes AS lbytes
FROM t JOIN dba_lobs b ON (t.owner=b.owner AND t.table_name=b.table_name)
JOIN dba_segments s ON (b.owner=s.owner AND (b.segment_name = s.segment_name OR b.index_name = s.segment_name))
),
toplist AS (
SELECT owner, tname, type, 
       ROUND(SUM(tbytes)/1024/1024) AS table_size_mb,
       ROUND(SUM(ibytes)/1024/1024) AS index_size_mb,
       ROUND(SUM(lbytes)/1024/1024) AS lob_size_mb,
       ROUND((SUM(tbytes)+SUM(ibytes)+SUM(lbytes))/1024/1024) AS total_size_mb
FROM seg
GROUP BY owner, tname, type
)
SELECT owner, tname, type, table_size_mb, index_size_mb, lob_size_mb, total_size_mb
FROM toplist
WHERE total_size_mb >=1024
ORDER BY total_size_mb;

在上面的脚本中，其中的子查询是找出你感兴趣的表的列表的。你可以根据自己的需要进行修改，就是修改下面的WHERE子句里面的过滤条件：

WITH 
t AS ( 
SELECT owner, table_name,
       CASE 
          WHEN partitioned = 'YES' THEN 'Partitioned Table'
          WHEN iot_type IS NOT NULL THEN 'IOT Table'
          WHEN cluster_name IS NOT NULL THEN 'Clustered Table'
          ELSE 'Normal Table'
       END AS type
FROM dba_tables
-- WHERE tablespace_name=''   -- you can add some filter here
)