10g提供了很好的查询表空间使用率的方法,不需要再去生成复杂的SQL,在DBA权限下使用:
SQL> Select * from sys.DBA_TABLESPACE_USAGE_METRICS;
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
TS_ORABPEL 3137056 47640820 6.58480689
TS_ORABPEL_LOB 1663552 8982524 18.5198726
TS_ORABPEL_MOVE 3176104 31074302 10.2209987
TS_PRODUCT 167952 12582906 1.33476321
UNDOTBS1 6736 2621440 .256958008
USERS 1688 4452094 .037914743
.......
即可
表空间是否达到阈值,可以查询
SQL> SELECT * FROM dba_outstanding_alerts;
看到数据库的严重告警
[color=red][b]Oracle 查询表大小以及表空间使用率 [/b][/color]
查看表物理文件大小:select segment_name,bytes/1024/1024 from user_segments/dba_segments;
查看表空间使用情况的SQL语句:
SELECT a.tablespace_name "表空间名",total 表空间大小,free 表空间剩余大小,
(total-free) 表空间使用大小,
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
SQL> Select * from sys.DBA_TABLESPACE_USAGE_METRICS;
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
TS_ORABPEL 3137056 47640820 6.58480689
TS_ORABPEL_LOB 1663552 8982524 18.5198726
TS_ORABPEL_MOVE 3176104 31074302 10.2209987
TS_PRODUCT 167952 12582906 1.33476321
UNDOTBS1 6736 2621440 .256958008
USERS 1688 4452094 .037914743
.......
即可
表空间是否达到阈值,可以查询
SQL> SELECT * FROM dba_outstanding_alerts;
看到数据库的严重告警
[color=red][b]Oracle 查询表大小以及表空间使用率 [/b][/color]
查看表物理文件大小:select segment_name,bytes/1024/1024 from user_segments/dba_segments;
查看表空间使用情况的SQL语句:
SELECT a.tablespace_name "表空间名",total 表空间大小,free 表空间剩余大小,
(total-free) 表空间使用大小,
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