查看表空间的名称及大小:
SQL> select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
2 from dba_tablespaces t, dba_data_files d
3 where t.tablespace_name = d.tablespace_name
4 group by t.tablespace_name;
查看表空间使用情况
SQL> SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
2 ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
3 FROM
4 (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
5 FROM DBA_DATA_FILES
6 GROUP BY TABLESPACE_NAME) D,
7 (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
8 FROM DBA_FREE_SPACE
9 GROUP BY TABLESPACE_NAME) F
10 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
11 UNION ALL --if have tempfile
12 SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
13 USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
14 NVL(FREE_SPACE,0) "FREE_SPACE(M)"
15 FROM
16 (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
17 FROM DBA_TEMP_FILES
18 GROUP BY TABLESPACE_NAME) D,
19 (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
20 ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
21 FROM V$TEMP_SPACE_HEADER
22 GROUP BY TABLESPACE_NAME) F
23 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
24
SQL>/
查看表空间使用情况
最新推荐文章于 2024-05-29 14:20:04 发布