'테이블스페이스'에 해당되는 글 2건
- 2009.09.22 오라클 테이블스페이스 정리하는 방법 갈켜주세요.
- 2009.09.22 테이블 스페이스 조회
그 이후 답변은 없으시네요 그래도 감사합니다......round(EXTEND_BYTES /1024 /1024 /1024,1) ||'GB' AS "총할당량(byte)",
USED_BYTES AS "총사용량(byte)",
ROUND(((USED_BYTES)/EXTEND_BYTES)*100,2) || '%' AS "사용률",
FREE_BYTES AS "여유공간(byte)",
ROUND((1-((USED_BYTES)/EXTEND_BYTES))*100,2) || '%' AS "여유공간률"
FROM (SELECT T1.TABLESPACE_NAME,
T1.EXTEND_BYTES,
T1.EXTEND_BYTES - T2.FREE_BYTES USED_BYTES,
T2.FREE_BYTES
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES) EXTEND_BYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME ) T1,
(SELECT TABLESPACE_NAME,
SUM(BYTES) FREE_BYTES
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) T2
WHERE T2.TABLESPACE_NAME = T1.TABLESPACE_NAME )
[출처] 테이블스페이스 사용량 조회|작성자 xoonho
[ALTIBASE] 테이블 스페이스 사용량 확인
 ┫Hybrid DBMS
2009/09/17 10:56
|
1. 메모리 테이블
SELECT C.USER_NAME||'.'||B.TABLE_NAME TABLENM,
A.MEM_PAGE_CNT, A.FIXED_ALLOC_MEM+A.VAR_ALLOC_MEM ALLOC_SIZE,
A.FIXED_USED_MEM+A.VAR_USED_MEM USED_SIZE,
ROUND((A.FIXED_USED_MEM+A.VAR_USED_MEM)/(A.FIXED_ALLOC_MEM+A.VAR_ALLOC_MEM)*100,2) EFFICIENCY
FROM V$MEMTBL_INFO A, SYSTEM_.SYS_TABLES_ B, SYSTEM_.SYS_USERS_ C
WHERE A.TABLE_OID = B.TABLE_OID
AND B.USER_ID = C.USER_ID
AND B.TABLE_TYPE = 'T'
AND C.USER_ID != 1
ORDER BY 2 DESC;
TABLENM MEM_PAGE_CNT ALLOC_SIZE USED_SIZE EFFICIENCY
-----------------------------------------------------------------------------------------------------------------------------
MCYBTB.CUSTOMER 38290 1254686720 1252207936 99.8
MCYBTB.SMS200906 23367 765689856 762698688 99.61
MCYBTB.USERCONTACTLOG_200906 17391 569868288 569311248 99.9
MCYBTB.ALIMI 15998 524222464 522170368 99.61
MCYBTB.UAPROFILE_INFO 6532 214040576 213512760 99.75
MCYBTB.FIXED_AMOUNT 2746 89980928 89739072 99.73
MCYBTB.SMS_QUEUE 346 12288000 12132232 98.73
MCYBTB.A_TTT 39 1277952 1199928 93.89
MCYBTB.TEST_EMP_TBL 1 32768 280 0.85
MCYBTB.DAILY_TIME_USER_COUNT 1 32768 0 0
SYS.TEST001
1 32768 336
1.03
MCYBTB.SMS_BUFFER_SMS 1 32768 0 0
MCYBTB.CUST_TTT 1 32768 0 0
13 rows selected.
2. 디스크 테이블
SELECT C.USER_NAME||'.'||B.TABLE_NAME TABLENM,
A.DISK_PAGE_CNT,
A.DISK_PAGE_CNT * 8 * 1024 ALLOC_SIZE,
ROUND(A.DISK_PAGE_CNT/(A.DISK_TOTAL_PAGE_CNT)*100,2) EFFICIENCY
FROM V$DISKTBL_INFO A, SYSTEM_.SYS_TABLES_ B, SYSTEM_.SYS_USERS_ C
WHERE A.TABLE_OID = B.TABLE_OID
AND B.USER_ID = C.USER_ID
AND B.TABLE_TYPE = 'T'
AND B.TABLE_NAME NOT LIKE 'SYS_%'
AND C.USER_ID != 1
ORDER BY 1;
TABLENM DISK_PAGE_CNT ALLOC_SIZE EFFICIENCY
-------------------------------------------------------------------------------------------------------
No rows selected.
[출처] [ALTIBASE] 테이블 스페이스 사용량 확인|작성자 이카의꿈
2005/02/25 18:01
SELECT SEGMENT_TYPE
, SEGMENT_NAME
, TABLESPACE_NAME
, BYTES
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE'
ORDER BY SEGMENT_TYPE,SEGMENT_NAME;
테이블스페이스별 사용량 확인
SELECT U.TABLESPACE_NAME "GHSDTS"
, U.BYTES / 1024000 "크기(MB)"
, (U.BYTES - SUM(NVL(F.BYTES,0))) / 1024000 "사용됨(MB)"
, (SUM(NVL(F.BYTES,0))) / 1024000 "남음(MB)"
, TRUNC((SUM(NVL(F.BYTES,0)) / U.BYTES) * 100,2) "남은 %"
FROM DBA_FREE_SPACE F, DBA_DATA_FILES U
WHERE F.FILE_ID(+) = U.FILE_ID
GROUP BY U.TABLESPACE_NAME, U.FILE_NAME, U.BYTES
ORDER BY U.TABLESPACE_NAME;
DB
http://blog.naver.com/jabusunin/20010331510
col ts_name form a20 head 'Tablespace'
col pieces form 9990 head 'Pcs'
col ts_size form 999,990 head 'SizeMb'
col largestpc form 999,990 head 'LrgMB'
col totalfree form 999,990 head 'FreeMb'
col pct_free form 990 head '%Free'
col whatsused form 999,990 head 'Used'
col pct_used form 990 head '%Used'
col problem head 'Prob??'
spool TS_used.log
nvl(largest_chunk,0) largestpc, nvl(total_free,0) totalfree,
nvl(round((total_free/ts_size)*100,2),0) pct_free,
ts_size-total_free whatsused,
nvl(100-round((total_free/ts_size)*100,2),100) pct_used,
decode(nvl(100-round((total_free/ts_size)*100,0),100),
85,'+',86,'+',87,'+',88,'+',89,'++',90,'++',91,'++',
92,'++',93,'++',94,'+++',95,'+++',96,'+++',97,'++++',
98,'+++++',99,'+++++',100,'+++++','') problem
from (select dfs.tablespace_name,count(*) pieces,
round(max(dfs.bytes)/1024/1024,2) largest_chunk,
round(sum(dfs.bytes)/1024/1024,2) total_free
from dba_free_space dfs group by tablespace_name) q1,
(select tablespace_name other_tname,
round(sum(ddf2.bytes)/1024/1024,2) ts_size
from dba_data_files ddf2 group by tablespace_name) q2
where q2.other_tname = q1.tablespace_name(+)
order by nvl(100-round((total_free/ts_size)*100,0),100) desc;
