一、查询段类别:
SELECT T.TABLESPACE_NAME, SEGMENT_TYPE, COUNT(1)
FROM USER_SEGMENTS T
WHERE 1 = 1
GROUP BY T.TABLESPACE_NAME, T.SEGMENT_TYPE
ORDER BY T.TABLESPACE_NAME, T.SEGMENT_TYPE;
二、查看数据库表空间存储对象的大小
SELECT *
FROM (SELECT SEGMENT_NAME,
T.SEGMENT_TYPE,
SUM(BYTES) / 1024 / 1024 SEGMENT_SIZE
FROM USER_SEGMENTS T
WHERE 1 = 1
GROUP BY T.SEGMENT_NAME, T.SEGMENT_TYPE) T
WHERE 1 = 1
ORDER BY SEGMENT_SIZE DESC;
三、查看表空间占的总大小:
SELECT SUM(BYTES) / 1024 / 1024 SEGMENT_SIZE FROM USER_SEGMENTS T;
四、实际表占用空间大小,包括表所属对象:INDEX、LOBINDEX、LOBSEGMENT
SELECT *
FROM (SELECT SEGMENT_NAME,
SEGMENT_TYPE,
SUM(BYTES) / 1024 / 1024 SEGMENT_SIZE
FROM (SELECT T.TABLESPACE_NAME,
T.SEGMENT_NAME,
T.SEGMENT_TYPE,
T.BYTES
FROM USER_SEGMENTS T
WHERE 1 = 1
AND T.SEGMENT_TYPE NOT IN
('INDEX', 'LOBINDEX', 'LOBSEGMENT')
UNION ALL
SELECT T.TABLESPACE_NAME,
UL.TABLE_NAME AS SEGMENT_NAME,
'TABLE' AS SEGMENT_TYPE,
T.BYTES
FROM USER_SEGMENTS T
INNER JOIN USER_LOBS UL
ON T.SEGMENT_NAME = UL.SEGMENT_NAME
WHERE 1 = 1
AND T.SEGMENT_TYPE = 'LOBSEGMENT'
UNION ALL
SELECT T.TABLESPACE_NAME,
UI.TABLE_NAME AS SEGMENT_NAME,
'TABLE' AS SEGMENT_TYPE,
T.BYTES
FROM USER_SEGMENTS T
INNER JOIN USER_INDEXES UI
ON T.SEGMENT_NAME = UI.INDEX_NAME
WHERE 1 = 1
AND T.SEGMENT_TYPE IN ('INDEX', 'LOBINDEX')
AND 1 = 1) T
WHERE 1 = 1
GROUP BY SEGMENT_NAME, SEGMENT_TYPE) T
WHERE 1 = 1
ORDER BY SEGMENT_SIZE DESC;
SELECT T.TABLESPACE_NAME, SEGMENT_TYPE, COUNT(1)
FROM USER_SEGMENTS T
WHERE 1 = 1
GROUP BY T.TABLESPACE_NAME, T.SEGMENT_TYPE
ORDER BY T.TABLESPACE_NAME, T.SEGMENT_TYPE;
二、查看数据库表空间存储对象的大小
SELECT *
FROM (SELECT SEGMENT_NAME,
T.SEGMENT_TYPE,
SUM(BYTES) / 1024 / 1024 SEGMENT_SIZE
FROM USER_SEGMENTS T
WHERE 1 = 1
GROUP BY T.SEGMENT_NAME, T.SEGMENT_TYPE) T
WHERE 1 = 1
ORDER BY SEGMENT_SIZE DESC;
三、查看表空间占的总大小:
SELECT SUM(BYTES) / 1024 / 1024 SEGMENT_SIZE FROM USER_SEGMENTS T;
四、实际表占用空间大小,包括表所属对象:INDEX、LOBINDEX、LOBSEGMENT
SELECT *
FROM (SELECT SEGMENT_NAME,
SEGMENT_TYPE,
SUM(BYTES) / 1024 / 1024 SEGMENT_SIZE
FROM (SELECT T.TABLESPACE_NAME,
T.SEGMENT_NAME,
T.SEGMENT_TYPE,
T.BYTES
FROM USER_SEGMENTS T
WHERE 1 = 1
AND T.SEGMENT_TYPE NOT IN
('INDEX', 'LOBINDEX', 'LOBSEGMENT')
UNION ALL
SELECT T.TABLESPACE_NAME,
UL.TABLE_NAME AS SEGMENT_NAME,
'TABLE' AS SEGMENT_TYPE,
T.BYTES
FROM USER_SEGMENTS T
INNER JOIN USER_LOBS UL
ON T.SEGMENT_NAME = UL.SEGMENT_NAME
WHERE 1 = 1
AND T.SEGMENT_TYPE = 'LOBSEGMENT'
UNION ALL
SELECT T.TABLESPACE_NAME,
UI.TABLE_NAME AS SEGMENT_NAME,
'TABLE' AS SEGMENT_TYPE,
T.BYTES
FROM USER_SEGMENTS T
INNER JOIN USER_INDEXES UI
ON T.SEGMENT_NAME = UI.INDEX_NAME
WHERE 1 = 1
AND T.SEGMENT_TYPE IN ('INDEX', 'LOBINDEX')
AND 1 = 1) T
WHERE 1 = 1
GROUP BY SEGMENT_NAME, SEGMENT_TYPE) T
WHERE 1 = 1
ORDER BY SEGMENT_SIZE DESC;