oracle表空间相关统计查询

た入场券 2021-12-16 15:59 338阅读 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