LOB 들어간 테이블 용량 조회(SEGMENTS)

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : LOB 들어간 테이블 용량 조회(SEGMENTS)

용량중복됨

LOB 들어간 테이블 용량 조회(SEGMENTS)

SQL>
SET PAGES 1000
SET LINES 200
COL OWNER FOR A20
COL TABLE_NAME FOR A30
SELECT A.OWNER, A.TABLE_NAME, SUM(B.BYTES)/1048576 MB
FROM DBA_TAB_COLUMNS A, DBA_SEGMENTS B
WHERE A.TABLE_NAME = B.SEGMENT_NAME
AND A.DATA_TYPE LIKE '%LOB'
GROUP BY A.OWNER, A.TABLE_NAME
ORDER BY 1,2;
 
OWNER             TABLE_NAME                 MB
-------------------- ------------------------------ ----------
APEX_040200         APEX$_WS_FILES             .0625
APEX_040200         APEX$_WS_NOTES             .0625
APEX_040200         APEX$_WS_ROWS              .125
APEX_040200         APEX$_WS_WEBPG_SECTIONS          .375
APEX_040200         WWV_FLOW_ADVISOR_CHECKS         .0625
APEX_040200         WWV_FLOW_AUTHENTICATIONS          .125
.
.
177 rows selected.

위 쿼리는 컬럼 갯수에따라 sum이 더해져서 정확하지 않음

LOB 들어간 테이블 용량 조회(SEGMENTS)

SQL> 
SET PAGES 1000
SET LINES 200
COL OWNER FOR A20
COL TABLE_NAME FOR A30
SELECT DISTINCT A.OWNER, A.TABLE_NAME, B.BYTES/1048576 MB
FROM DBA_TAB_COLUMNS A, DBA_SEGMENTS B
WHERE A.TABLE_NAME = B.SEGMENT_NAME
AND A.DATA_TYPE LIKE '%LOB'
ORDER BY 1,2;
 
OWNER             TABLE_NAME                 MB
-------------------- ------------------------------ ----------
OUTLN             OL$HINTS                 .0625
SYS             AQ$_MEM_MC              .0625
SYS             ATTRIBUTE_TRANSFORMATIONS$      .0625
SYS             AUD$                    .5

LOB size 까지 확인

SQL>
SET PAGES 1000
SET LINES 200
COL OWNER FOR A20
select owner, table_name, sum(table_size), sum(lob_size)
from (
select a.owner, a.segment_name as table_name, a.bytes/1024/1024 as table_size, 
(select sum(bytes)/1024/1024 from dba_segments where segment_name= b.segment_name) as lob_size
from dba_segments a, (select owner, segment_name, table_name from dba_lobs) b
where a.owner not in ('SYSMAN','SYSTEM','SYS')
and a.segment_name = b.table_name 
and a.owner = b.owner) 
group by owner, table_name;
 
OWNER             TABLE_NAME             SUM(TABLE_SIZE) SUM(LOB_SIZE)
-------------------- ------------------------------ --------------- -------------
OUTLN             OL$HINTS                      .0625        .0625

LOB 들어간 테이블 컬럼 조회

SQL>
COL COLUMN_NAME FOR A20
COL DATA_TYPE FOR A20
SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE 
FROM DBA_TAB_COLUMNS
WHERE DATA_TYPE LIKE '%LOB'
ORDER BY 1,2,3;
 
OWNER             TABLE_NAME             COLUMN_NAME      DATA_TYPE
-------------------- ------------------------------ -------------------- --------------------
SYS             WRI$_OPTSTAT_HISTHEAD_HISTORY  EXPRESSION         CLOB
SYS             WRI$_OPTSTAT_SYNOPSIS_HEAD$    SPARE2         CLOB
SYS             WRI$_SQLSET_MASK            OTHER         CLOB
SYS             WRI$_SQLSET_PLAN_LINES        OTHER_XML         CLOB
SYS             WRI$_SQLSET_WORKSPACE        CONTROL_OPTIONS     CLOB
.
.
986 rows selected.

참조 :