sql 代码
- 1:此脚本不能查临时表空间
- SELECT V1.TABLESPACE_NAME"表空间名",
- ROUND(NVL(V1.SPACE,0)) "表空间大小(M)",
- ROUND(NVL(V1.SPACE,0)-NVL(V2.SPACE,0)) " 已经使用空间(M)",
- ROUND(NVL(V2.SPACE,0)) "可用空间(M)",
- ROUND((NVL(V1.SPACE,0)-NVL(V2.SPACE,0))/NVL(V1.SPACE,0)*100,2) "已用百分比"
- FROM (SELECTTABLESPACE_NAME,SUM(BYTES)/1024/1024 SPACE
- FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) V1,
- (SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 SPACE
- FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) V2
- WHERE V1.TABLESPACE_NAME =V2.TABLESPACE_NAME(+)
- ORDER BY V1.TABLESPACE_NAME;
- 查看当前用户每个表占用空间的大小:
- Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group BySegment_Name
- 2:查看表空间SQL,这个脚本可以查Temp表空间。
- select t.*
- from (SELECT D.TABLESPACE_NAME,
- SPACE "SUM_SPACE(M)",
- BLOCKS SUM_BLOCKS,
- SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
- ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
- FREE_SPACE "FREE_SPACE(M)"
- FROM (SELECT TABLESPACE_NAME,
- ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
- SUM(BLOCKS) BLOCKS
- FROM DBA_DATA_FILES
- GROUP BY TABLESPACE_NAME) D,
- (SELECT TABLESPACE_NAME,
- ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
- FROM DBA_FREE_SPACE
- GROUP BY TABLESPACE_NAME) F
- WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
- UNION ALL --if have tempfile
- SELECT D.TABLESPACE_NAME,
- SPACE "SUM_SPACE(M)",
- BLOCKS SUM_BLOCKS,
- USED_SPACE "USED_SPACE(M)",
- ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
- SPACE - USED_SPACE "FREE_SPACE(M)"
- FROM (SELECT TABLESPACE_NAME,
- ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
- SUM(BLOCKS) BLOCKS
- FROM DBA_TEMP_FILES
- GROUP BY TABLESPACE_NAME) D,
- (SELECT TABLESPACE,
- ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
- FROM V$SORT_USAGE
- GROUP BY TABLESPACE) F
- WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
- order by "USED_RATE(%)" desc;