oracle表空间相关统计查询 た 入场券 2021-12-16 15:59 291阅读 0赞 部分转自 https://www.cnblogs.com/xwdreamer/p/3511047.html \--查询表空间使用情况 SELECT UPPER(F.TABLESPACE\_NAME) "表空间名", D.TOT\_GROOTTE\_MB "表空间大小(M)", D.TOT\_GROOTTE\_MB - F.TOTAL\_BYTES "已使用空间(M)", TO\_CHAR(ROUND((D.TOT\_GROOTTE\_MB - F.TOTAL\_BYTES) / D.TOT\_GROOTTE\_MB \* 100, 2), '990.99') || '%' "使用比", F.TOTAL\_BYTES "空闲空间(M)", F.MAX\_BYTES "最大块(M)" FROM (SELECT TABLESPACE\_NAME, ROUND(SUM(BYTES) / (1024 \* 1024), 2) TOTAL\_BYTES, ROUND(MAX(BYTES) / (1024 \* 1024), 2) MAX\_BYTES FROM SYS.DBA\_FREE\_SPACE GROUP BY TABLESPACE\_NAME) F, (SELECT DD.TABLESPACE\_NAME, ROUND(SUM(DD.BYTES) / (1024 \* 1024), 2) TOT\_GROOTTE\_MB FROM SYS.DBA\_DATA\_FILES DD GROUP BY DD.TABLESPACE\_NAME) D WHERE D.TABLESPACE\_NAME = F.TABLESPACE\_NAME ORDER BY 4 DESC, 5 ASC; \--查询表空间的free space SELECT TABLESPACE\_NAME, COUNT(\*) AS EXTENDS, ROUND(SUM(BYTES) / 1024 / 1024, 2) AS MB, SUM(BLOCKS) AS BLOCKS FROM DBA\_FREE\_SPACE GROUP BY TABLESPACE\_NAME ORDER BY 3 ASC; \--查询表空间的总容量 SELECT TABLESPACE\_NAME, SUM(BYTES) / 1024 / 1024 AS MB FROM DBA\_DATA\_FILES GROUP BY TABLESPACE\_NAME ORDER BY 2 DESC; \--查询表空间使用率 SELECT TOTAL.TABLESPACE\_NAME, ROUND(TOTAL.MB, 2) AS TOTAL\_MB, ROUND(TOTAL.MB - FREE.MB, 2) AS USED\_MB, ROUND((1 - FREE.MB / TOTAL.MB) \* 100, 2) || '%' AS USED\_PCT FROM (SELECT TABLESPACE\_NAME, SUM(BYTES) / 1024 / 1024 AS MB FROM DBA\_FREE\_SPACE GROUP BY TABLESPACE\_NAME) FREE, (SELECT TABLESPACE\_NAME, SUM(BYTES) / 1024 / 1024 AS MB FROM DBA\_DATA\_FILES GROUP BY TABLESPACE\_NAME) TOTAL WHERE FREE.TABLESPACE\_NAME = TOTAL.TABLESPACE\_NAME ORDER BY 4 DESC; 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 ORDER BY 5 DESC ; SELECT TABLESPACE\_NAME "表空间", TO\_CHAR(ROUND(BYTES / 1024, 2), '99990.00') || '' "实有", TO\_CHAR(ROUND(FREE / 1024, 2), '99990.00') || 'G' "现有", TO\_CHAR(ROUND((BYTES - FREE) / 1024, 2), '99990.00') || 'G' "使用", TO\_CHAR(ROUND(10000 \* USED / BYTES) / 100, '99990.00') || '%' "使用比例" FROM (SELECT A.TABLESPACE\_NAME TABLESPACE\_NAME, FLOOR(A.BYTES / (1024 \* 1024)) BYTES, FLOOR(B.FREE / (1024 \* 1024)) FREE, FLOOR((A.BYTES - B.FREE) / (1024 \* 1024)) USED FROM (SELECT TABLESPACE\_NAME TABLESPACE\_NAME, SUM(BYTES) BYTES FROM DBA\_DATA\_FILES GROUP BY TABLESPACE\_NAME) A, (SELECT TABLESPACE\_NAME TABLESPACE\_NAME, SUM(BYTES) FREE FROM DBA\_FREE\_SPACE GROUP BY TABLESPACE\_NAME) B WHERE A.TABLESPACE\_NAME = B.TABLESPACE\_NAME) \--WHERE TABLESPACE\_NAME LIKE 'CDR%' --这一句用于指定表空间名称 ORDER BY FLOOR(10000 \* USED / BYTES) DESC; SELECT TABLESPACE\_NAME, MAX\_GB, USED\_GB, ROUND(100 \* USED\_GB / MAX\_GB) PCT\_USED FROM (SELECT A.TABLESPACE\_NAME TABLESPACE\_NAME, ROUND((A.BYTES\_ALLOC - NVL(B.BYTES\_FREE, 0)) / POWER(2, 30), 2) USED\_GB, ROUND(A.MAXBYTES / POWER(2, 30), 2) MAX\_GB FROM (SELECT F.TABLESPACE\_NAME, SUM(F.BYTES) BYTES\_ALLOC, SUM(DECODE(F.AUTOEXTENSIBLE, 'YES', F.MAXBYTES, 'NO', F.BYTES)) MAXBYTES FROM DBA\_DATA\_FILES F GROUP BY TABLESPACE\_NAME) A, (SELECT F.TABLESPACE\_NAME, SUM(F.BYTES) BYTES\_FREE FROM DBA\_FREE\_SPACE F GROUP BY TABLESPACE\_NAME) B WHERE A.TABLESPACE\_NAME = B.TABLESPACE\_NAME(+) UNION ALL SELECT H.TABLESPACE\_NAME TABLESPACE\_NAME, ROUND(SUM(NVL(P.BYTES\_USED, 0)) / POWER(2, 30), 2) USED\_GB, ROUND(SUM(DECODE(F.AUTOEXTENSIBLE, 'YES', F.MAXBYTES, 'NO', F.BYTES)) / POWER(2, 30), 2) MAX\_GB FROM V$TEMP\_SPACE\_HEADER H, V$TEMP\_EXTENT\_POOL P, DBA\_TEMP\_FILES F WHERE P.FILE\_ID(+) = H.FILE\_ID AND P.TABLESPACE\_NAME(+) = H.TABLESPACE\_NAME AND F.FILE\_ID = H.FILE\_ID AND F.TABLESPACE\_NAME = H.TABLESPACE\_NAME GROUP BY H.TABLESPACE\_NAME) ORDER BY 4; \--带获取数据文件及路径 SELECT B.FILE\_NAME 物理文件名, B.TABLESPACE\_NAME 表空间名称, B.BYTES / 1024 / 1024 总大小M, (B.BYTES - SUM(NVL(A.BYTES, 0))) / 1024 / 1024 已使用M, SUBSTR((B.BYTES - SUM(NVL(A.BYTES, 0))) / (B.BYTES) \* 100, 1, 5) 使用率 FROM DBA\_FREE\_SPACE A, DBA\_DATA\_FILES B WHERE A.FILE\_ID = B.FILE\_ID GROUP BY B.TABLESPACE\_NAME, B.FILE\_NAME, B.BYTES ORDER BY B.TABLESPACE\_NAME; SELECT A.TABLESPACE\_NAME TABLESPACE\_NAME, TOTAL / 1048576 TOTAL\_M, FREE / 1048576 FREE\_M, (TOTAL - FREE) / 1048576 USED\_M, ROUND((TOTAL - FREE) / TOTAL, 4) \* 100 "USED%", AUTOEXTENSIBLE AUTOEXTEM FROM (SELECT TABLESPACE\_NAME, SUM(BYTES) FREE FROM DBA\_FREE\_SPACE GROUP BY TABLESPACE\_NAME) A, (SELECT TABLESPACE\_NAME, SUM(BYTES) TOTAL, MAX(AUTOEXTENSIBLE) AUTOEXTENSIBLE FROM DBA\_DATA\_FILES GROUP BY TABLESPACE\_NAME) B WHERE A.TABLESPACE\_NAME = B.TABLESPACE\_NAME ORDER BY 6, 5 DESC; \--查看表空间的名字及文件所在位置 SELECT TABLESPACE\_NAME, FILE\_ID, FILE\_NAME, ROUND(BYTES / (1024 \* 1024), 0) TOTAL\_SPACE FROM DBA\_DATA\_FILES ORDER BY TABLESPACE\_NAME; \--查询表空间使用情况 SELECT A.TABLESPACE\_NAME AS "表空间名", A.BYTES / 1024 / 1024 AS "表空间大小(M)", (A.BYTES - B.BYTES) / 1024 / 1024 AS "已使用空间(M)", B.BYTES / 1024 / 1024 "空闲空间(M)", ROUND(((A.BYTES - B.BYTES) / A.BYTES) \* 100, 2) "使用比" FROM (SELECT TABLESPACE\_NAME, SUM(BYTES) BYTES FROM DBA\_DATA\_FILES GROUP BY TABLESPACE\_NAME) A, (SELECT TABLESPACE\_NAME, SUM(BYTES) BYTES, MAX(BYTES) LARGEST FROM DBA\_FREE\_SPACE GROUP BY TABLESPACE\_NAME) B WHERE A.TABLESPACE\_NAME = B.TABLESPACE\_NAME ORDER BY ((A.BYTES - B.BYTES) / A.BYTES) DESC; SELECT A.A1 表空间名称, C.C2 类型, C.C3 区管理, B.B2 / 1024 / 1024 表空间大小M, (B.B2 - A.A2) / 1024 / 1024 已使用M, SUBSTR((B.B2 - A.A2) / B.B2 \* 100, 1, 5) 利用率 FROM (SELECT TABLESPACE\_NAME A1, SUM(NVL(BYTES, 0)) A2 FROM DBA\_FREE\_SPACE GROUP BY TABLESPACE\_NAME) A, (SELECT TABLESPACE\_NAME B1, SUM(BYTES) B2 FROM DBA\_DATA\_FILES GROUP BY TABLESPACE\_NAME) B, (SELECT TABLESPACE\_NAME C1, CONTENTS C2, EXTENT\_MANAGEMENT C3 FROM DBA\_TABLESPACES) C WHERE A.A1 = B.B1 AND C.C1 = B.B1 ORDER BY 6 DESC; \---所有用户表使用大小的前三十名 SELECT \* FROM (SELECT SEGMENT\_NAME, BYTES FROM DBA\_SEGMENTS WHERE OWNER = USER ORDER BY BYTES DESC) WHERE ROWNUM <= 30; \--有两种含义的表大小。一种是分配给一个表的物理空间数量,而不管空间是否被使用。可以这样查询获得字节数: SELECT SEGMENT\_NAME, BYTES FROM USER\_SEGMENTS WHERE SEGMENT\_TYPE = 'TABLE'; \--or SELECT SEGMENT\_NAME, SUM(BYTES) / 1024 / 1024 FROM USER\_EXTENTS GROUP BY SEGMENT\_NAME; \--另一种表实际使用的空间 ANALYZE TABLE EMP COMPUTE STATISTICS; SELECT NUM\_ROWS \* AVG\_ROW\_LEN FROM USER\_TABLES WHERE TABLE\_NAME = 'T\_TEST'; SELECT NUM\_ROWS \* AVG\_ROW\_LEN, T.\* FROM DBA\_TABLES T WHERE TABLE\_NAME = 'T\_TEST'; 转载于:https://www.cnblogs.com/ritchy/p/10917393.html
还没有评论,来说两句吧...