Oracle表大小和表空间使用情况查询

---

1. 表空间使用量查询

1. 表空间查询：

SELECT a.tablespace_name "表空间名",
total / (1024 * 1024) "表空间大小(M)",
free / (1024 * 1024) "表空间剩余大小(M)",
(total - free) / (1024 * 1024) "表空间使用大小(M)",
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, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name 

2. 临时表temp查询

• 10g查看temp表空间使用率：

SELECT temp_used.tablespace_name,
       total - used as "Free",
       total as "Total",
       round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
  FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
          FROM GV_$TEMP_SPACE_HEADER
         GROUP BY tablespace_name) temp_used,
       (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
          FROM dba_temp_files
         GROUP BY tablespace_name) temp_total
         WHERE temp_used.tablespace_name = temp_total.tablespace_name;

• 11g查看temp表空间使用率

select tablespace_name,
       round(tablespace_size / 1024 / 1024 / 1024, 2) as total,
       round(free_space / 1024 / 1024 / 1024, 2) as free,
       round((tablespace_size - free_space) / 1024 / 1024 / 1024, 2) used_size,
       round(nvl(free_space, 0) * 100 / tablespace_size, 3)||'%' pct_free
  from dba_temp_free_space;

2. 数据表使用量查询

2.1 按用户查询

每张表都是作为“段”来存储的，可以通过user_segments视图查看其相应信息。
段（segments）的定义：如果创建一个堆组织表，则该表就是一个段。

user_segments 包含的关键字段及含义：
SEGMENT_NAME 段名，列出所有表名，索引名等
SEGMENT_TYPE 段类型，如table, index等
TABLE_SAPCE 表空间名字
BYTES 字节，分配给段的物理空间大小

例：查询某用户的数据使用情况

--在该用户下执行以下sql语句
select b.segment_name "段名", b.SEGMENT_TYPE "段类型", b.BYTES "段大小G",TABLESPACE_NAME "所属表空间"
  from （select segment_name,
       SEGMENT_TYPE,TABLESPACE_NAME,
       round(sum(BYTES) / 1024 / 1024 / 1024, 2) BYTES
  from user_segments a
 group by a.segment_name, SEGMENT_TYPE,TABLESPACE_NAME）b
 where b.BYTES >= 0.5
 order by BYTES desc

扩展：若对象是索引，列出索引所属的表

with b as (
select segment_name,
       SEGMENT_TYPE,
       TABLESPACE_NAME,
       round(sum(BYTES) / 1024 / 1024 / 1024, 2) BYTES
  from user_segments a
 group by a.segment_name,
          SEGMENT_TYPE,
          TABLESPACE_NAME）,
     c as (select distinct index_name, table_name from user_ind_columns)
select b.segment_name  "段名",
       b.SEGMENT_TYPE  "段类型",
       c.table_name   "表名",
       b.BYTES         "段大小G",
       TABLESPACE_NAME "所属表空间"
  from b
  left join c
    on b.segment_name = c.index_name
 where b.BYTES >= 0.5
 order by b.BYTES desc

2.2 按表空间查询

dba_segments 包含的关键字段及含义：
SEGMENT_NAME 段名，列出所有表名，索引名等
SEGMENT_TYPE 段类型，如table, index等
TABLE_SAPCE 表空间名字
BYTES 字节，分配给段的物理空间大小

--需要dba权限
select b.segment_name "段名", b.SEGMENT_TYPE "段类型", b.BYTES "段大小G",TABLESPACE_NAME "所属表空间"
  from （select segment_name,
       SEGMENT_TYPE,TABLESPACE_NAME,
       round(sum(BYTES) / 1024 / 1024 / 1024, 2) BYTES
  from dba_segments a
  where TABLESPACE_NAME='IPNET'
 group by a.segment_name, SEGMENT_TYPE,TABLESPACE_NAME）b
 where b.BYTES >= 0.5
 order by BYTES desc