Home >Database >Mysql Tutorial >Oracle各种空间大小及占用大小
Oracle各种空间大小及占用大小
- WBOYOriginal
- 2016-06-07 17:04:171563browse
1. 表占用空间大小计算方法 Select S.SEGMENT_NAME ,DECODE(SUM(BYTES), NULL, 0, SUM(BYTES) / 1024 / 1024) Mbytes From
1. 表占用空间大小计算方法
Select S.SEGMENT_NAME ,DECODE(SUM(BYTES), NULL, 0, SUM(BYTES) / 1024 / 1024) Mbytes
From DBA_SEGMENTS S
Where S.SEGMENT_NAME = 'MTL_MATERIAL_TRANSACTIONS' --表名
And S.SEGMENT_TYPE = 'TABLE'
Group By S.SEGMENT_NAME
2. 索引占用空间大小计算方法
Select S.SEGMENT_NAME ,DECODE(SUM(BYTES), NULL, 0, SUM(BYTES) / 1024 / 1024) Mbytes
From DBA_SEGMENTS S
Where S.SEGMENT_NAME = 'MTL_MATERIAL_TRANSACTIONS_N1' --索引
And S.SEGMENT_TYPE = 'INDEX'
Group By S.SEGMENT_NAME
3. 分区表占用空间大小计算方法
Select S.SEGMENT_NAME ,DECODE(SUM(BYTES), NULL, 0, SUM(BYTES) / 1024 / 1024) Mbytes
From DBA_SEGMENTS S
Where S.SEGMENT_TYPE = 'TABLE PARTITION'
Group By S.SEGMENT_NAME
4. 表空间使用情况
SELECT A.TABLESPACE_NAME 表空间名称,
Round(A.BYTES, 2) "总空间(G)",
Round(B.BYTES, 2) "未用空间(G)",
ROUND(((A.BYTES - B.BYTES) / A.BYTES) * 100, 2) "使用率%"
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / (1024 * 1024 * 1024) BYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES) / (1024 * 1024 * 1024) BYTES
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;
