오라클 테이블스페이스 용량 조회, oracle tablespace 용량 조회
출처 - http://kalipso.tistory.com/80
출처 - https://kldp.org/node/34801
출처 - http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle
1. 테이블스페이스 정보 조회
SELECT * FROM DBA_TABLESPACES;
2. 테이블스페이스별 용량 확인 쿼리문(MB 단위)
select substr(a.tablespace_name,1,30) tablespace,
round(sum(a.total1)/1024/1024,1) "TotalMB",
round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",
round(sum(a.sum1)/1024/1024,1) "FreeMB",
round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%"
from
(select tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt
from dba_free_space
group by tablespace_name
union
select tablespace_name,sum(bytes) total1,0,0,0
from dba_data_files
group by tablespace_name) a
group by a.tablespace_name
order by tablespace;
조회결과를 다음과 같이 살펴볼 수 있다.
TABLESPACE명 | 총용량(TotalMB) | 사용용량(UsedMB) | 여유용량(FreeMB) | 사용율(Used%) |
SYSAUX | 1024 | 514.4 | 509.6 | 50.23 |
SYSTEM | 1024 | 390 | 634 | 38.09 |
TS_SEND_DATA | 70960 | 59890.6 | 11069.4 | 84.4 |
TS_SEND_TEMP | 1024 | 0.1 | 1023.9 | 0.01 |
UNDOTBS1 | 4518 | 55.7 | 4462.3 | 1.23 |
USERS | 100 | 0.1 | 99.9 | 0.1 |
3. 테이블스페이스별 현황 확인 쿼리문(MB 단위)
SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024 AS MBytes, RESULT/1024 AS USE_MBytes FROM
(
SELECT E.TABLESPACE_NAME,E.FILE_NAME,E.BYTES, (E.BYTES-SUM(F.BYTES)) RESULT
FROM DBA_DATA_FILES E, DBA_FREE_SPACE F
WHERE E.FILE_ID = F.FILE_ID
GROUP BY E.TABLESPACE_NAME, E.FILE_NAME, E.BYTES
) A;
TABLESPACE_NAME | FILE_NAME | MBYTES | USE_MBYTES |
SYSTEM | C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF | 1464320 | 1458816 |
SYSAUX | C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF | 471040 | 450048 |
USERS | C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF | 5120 | 3328 |
TEST | C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEST | 20733952 | 18843264 |
EXAMPLE | C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE01.DBF | 102400 | 79552 |
UNDOTBS1 | C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF | 6917120 | 17856 |
ORCL | C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ORCL | 1048576 | 1600 |
4. 테이블스페이스별, 파일별 현황 확인 쿼리문(바이트 단위)
SELECT A.TABLESPACE_NAME "테이블스페이스명",
A.FILE_NAME "파일경로",
(A.BYTES - B.FREE) "사용공간",
B.FREE "여유 공간",
A.BYTES "총크기",
TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간"
FROM
(
SELECT FILE_ID,
TABLESPACE_NAME,
FILE_NAME,
SUBSTR(FILE_NAME,1,200) FILE_NM,
SUM(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,SUBSTR(FILE_NAME,1,200)
) A,
(
SELECT TABLESPACE_NAME,
FILE_ID,
SUM(NVL(BYTES,0)) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME,FILE_ID
) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.FILE_ID = B.FILE_ID;
테이블스페이스명 | 파일경로 | 사용공간 | 여유공간 | 총크기 | 여유공간 |
TS_TEST_DATA | /oradata/TEST/ts_test_data02.dbf | 16148332544 | 5326503936 | 21474836480 | 24.80% |
TS_TEST_DATA | /oradata/TEST/ts_test_data03.dbf | 16073621504 | 5401214976 | 21474836480 | 25.15% |
TS_TEST_DATA | /oradata/TEST/ts_test_data.dbf | 30577852416 | 879427584 | 31457280000 | 2.80% |
SYSAUX | /oradata/TEST/sysaux01.dbf | 539361280 | 534380544 | 1073741824 | 49.77% |
USERS | /oradata/TEST/users01.dbf | 65536 | 104792064 | 104857600 | 99.94% |
SYSTEM | /oradata/TEST/system01.dbf | 408944640 | 664797184 | 1073741824 | 61.91% |
UNDOTBS1 | /oradata/TEST/undotbs01.dbf | 53149696 | 4684316672 | 4737466368 | 98.88% |
TS_TEST_TEMP | /oradata/TEST/ts_test_temp.dbf | 65536 | 1073676288 | 1073741824 | 99.99% |
5. 테이블 용량 조회
SELECT
owner, table_name, TRUNC(sum(bytes)/1024/1024/1024) GB
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type in ('TABLE','TABLE PARTITION')
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type in ('INDEX','INDEX PARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type IN ('LOBSEGMENT','LOB PARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
---WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */
ORDER BY SUM(bytes) desc
'Database > Oracle Tip' 카테고리의 다른 글
[오라클] SQL*Plus 사용법 정리 (0) | 2020.08.04 |
---|---|
[오라클] 테이블스페이스와 데이터파일 (0) | 2020.08.04 |
[오라클] Partition Table(파티션 테이블) (0) | 2020.08.04 |
[Oracle] Tablespace(테이블스페이스)란? (0) | 2020.08.04 |
[오라클] 테이블별 용량 확인 방법 (0) | 2019.07.29 |
오라클 최근 생성된 테이블, 뷰 확인 (0) | 2019.06.17 |
자식키가 있는 상태에서 부모키 값 수정 (0) | 2019.05.08 |
[ORACLE] 테이블과 데이터 복사하기 (0) | 2019.04.01 |