...
| No Format | ||||
|---|---|---|---|---|
| ||||
set linesize 1024 set colsize 30 SELECT U.USER_NAME USER_NAME -- 데이터베이스 사용자 , DECODE(TBL.IS_PARTITIONED, 'T', 'PARTITIONED', 'F', 'NON-PARTITIONED') PARTITIONED -- 파티션드 테이블이면 PARTITIONED, 논-파티션드 테이블이면 NON-PARTITIONED , TBL.TABLE_NAME TABLE_NAME -- 테이블 이름 , DECODE(TBL.IS_PARTITIONED, 'T', TBL.PARTITION_NAME, 'F', '-') PARTITIONED_TABLE -- 파티션드 테이블 이름 , TBS.NAME TABLESPACE_NAME -- 테이블스페이스 , TO_CHAR((D.MAX * TBS.PAGE_SIZE)/1024, '999,999,999') 'MAX(KB)' -- 테이블이 속한 테이블스페이스의 최대 크기 , TO_CHAR((TBS.EXTENT_PAGE_COUNT * TBS.PAGE_SIZE * SEG.EXTENT_TOTAL_COUNT)/1024, '999,999,999') 'ALLOC(KB)' -- 현재까지 할당받은 전체 크기 , TO_CHAR((((TBS.EXTENT_PAGE_COUNT * TBS.PAGE_SIZE * SEG.EXTENT_TOTAL_COUNT)/(D.MAX*TBS.PAGE_SIZE))*100), '99.99') 'USAGE(%)' -- 테이블스페이스 최대 크기 대비 사용률 FROM (SELECT TBL.USER_ID , DECODE(TBL.IS_PARTITIONED, 'F', TBL.TABLE_OID, 'T', PT.PARTITION_OID) TABLE_OID , TBL.TABLE_NAME , PT.PARTITION_NAME , DECODE(TBL.IS_PARTITIONED, 'F', TBL.TBS_ID, 'T', PT.TBS_ID) TBS_ID , TBL.IS_PARTITIONED FROM SYSTEM_.SYS_TABLES_ TBL LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ PT ON TBL.TABLE_ID = PT.TABLE_ID ) TBL , V$SEGMENT SEG , SYSTEM_.SYS_USERS_ U , V$TABLESPACES TBS , (SELECT SPACEID , SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX , DECODE(MAX(AUTOEXTEND),1,'ON','OFF') 'AUTOEXTEND' FROM V$DATAFILES GROUP BY SPACEID) D WHERE 1=1 AND SEG.SEGMENT_TYPE = 'TABLE' /* 'TABLE' : 테이블, 'INDEX' : 인덱스 */ AND SEG.TABLE_OID = TBL.TABLE_OID AND U.USER_ID = TBL.USER_ID AND D.SPACEID = TBL.TBS_ID AND TBS.ID = TBL.TBS_ID ORDER BY USER_NAME, PARTITIONED, TABLE_NAME, PARTITIONED_TABLE ; |
| Code Block | ||||
|---|---|---|---|---|
| ||||
USER_NAME PARTITIONED TABLESPACE_NAME TABLE_NAME PARTITIONED_TABLE MAX(KB) ALLOC(KB) USAGE(%)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS NON-PARTITIONED SYS_TBS_DISK_DATA CUSTOMER - 2,097,152 256 .01
SYS NON-PARTITIONED SYS_TBS_DISK_DATA DEPARTMENT - 2,097,152 256 .01
SYS NON-PARTITIONED SYS_TBS_DISK_DATA EMPLOYEE - 2,097,152 256 .01
SYS NON-PARTITIONED SYS_TBS_DISK_DATA GOODS - 2,097,152 256 .01
SYS NON-PARTITIONED USER_DATA ORDERS - 2,097,152 256 .01
SYS PARTITIONED PART_DATA PART_T1 P1 262,144 512 .20
SYS PARTITIONED PART_DATA PART_T1 P2 262,144 512 .20
SYS PARTITIONED PART_DATA PART_T1 P3 262,144 18,176 6.93
SYS PARTITIONED PART_DATA PART_T2 P201406 262,144 9,472 3.61
SYS PARTITIONED PART_DATA PART_T2 P201407 262,144 18,944 7.23
SYS PARTITIONED PART_DATA PART_T2 P201408 262,144 4,864 1.86
SYS PARTITIONED PART_DATA PART_T2 P201512 262,144 7,168 2.73
SYS PARTITIONED PART_DATA_DEF PART_T2 PMAX 262,144 8,192 3.13
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE2 DEF 2,097,152 256 .01
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE2 Q1_2014 2,097,152 1,536 .07
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE2 Q2_2014 2,097,152 768 .04
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE2 Q3_2014 2,097,152 2,304 .11
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE2 Q4_2014 2,097,152 256 .01
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE_SALES DEF 2,097,152 256 .01
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE_SALES Q1_2014 2,097,152 768 .04
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE_SALES Q2_2014 2,097,152 1,536 .07
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE_SALES Q3_2014 2,097,152 1,280 .06
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE_SALES Q4_2014 2,097,152 768 .04
23 rows selected. |
디스크 인덱스
...
| No Format | ||||
|---|---|---|---|---|
| ||||
set linesize 1024 set colsize 20 SELECT U.USER_NAME USER_NAME -- 데이터베이스 사용자 , I_LIST.TABLE_NAME -- 테이블 이름 , DECODE(I_LIST.PARTITION_NAME, NULL, 'NON-PARTITIONED', I_LIST.PARTITION_NAME) PARTITIONED_NAME -- 파티션드 테이블 이름, 논-파티션드 테이블이면 NON-PARTITIONED , I_LIST.INDEX_NAME INDEX_NAME -- 인덱스 이름 , DECODE(I_LIST.INDEX_PARTITION_NAME, NULL, 'NON-PARTITIONED', I_LIST.INDEX_PARTITION_NAME) PARTITIONED_INDEX -- 파티션드 인덱스 이름 , TBS.NAME TBS_NAME -- 인덱스가 속한 테이블스페이스 , TO_CHAR((D.MAX * TBS.PAGE_SIZE)/1024, '999,999,999') 'MAX(KB)' -- 테이블스페이스의 최대 크기 , TO_CHAR((TBS.EXTENT_PAGE_COUNT * TBS.PAGE_SIZE * SEG.EXTENT_TOTAL_COUNT)/1024, '999,999,999') 'ALLOC(KB)' -- 인덱스에서 할당 받은 전체 크기 , TO_CHAR((((TBS.EXTENT_PAGE_COUNT * TBS.PAGE_SIZE * SEG.EXTENT_TOTAL_COUNT)/(D.MAX*TBS.PAGE_SIZE))*100), '99.99') 'USAGE(%)' -- 테이블스페이스 최대 크기 대비 사용률 FROM (SELECT T.TABLE_NAME , PT.PARTITION_NAME , I.INDEX_NAME , PI.INDEX_PARTITION_NAME , DECODE(T.IS_PARTITIONED, 'F', I.TABLE_ID, 'T', PT.TABLE_ID) TABLE_ID , DECODE(T.IS_PARTITIONED, 'F', T.TABLE_OID, 'T', PT.PARTITION_OID) TABLE_OID , DECODE(I.IS_PARTITIONED, 'F', I.TBS_ID, 'T', PI.TBS_ID) TBS_ID , I.INDEX_ID , T.USER_ID FROM SYSTEM_.SYS_INDICES_ I LEFT OUTER JOIN SYSTEM_.SYS_INDEX_PARTITIONS_ PI ON PI.INDEX_ID = I.INDEX_ID LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ PT ON PT.PARTITION_ID = PI.TABLE_PARTITION_ID LEFT OUTER JOIN SYSTEM_.SYS_TABLES_ T ON T.TABLE_ID = I.TABLE_ID ) I_LIST , V$SEGMENT SEG , V$INDEX I , V$TABLESPACES TBS , SYSTEM_.SYS_USERS_ U , (SELECT SPACEID , SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX , DECODE(MAX(AUTOEXTEND),1,'ON','OFF') 'AUTOEXTEND' FROM V$DATAFILES GROUP BY SPACEID) D WHERE 1=1 AND SEG.TABLE_OID = I.TABLE_OID AND SEG.SEGMENT_PID = I.INDEX_SEG_PID AND SEG.SPACE_ID = I_LIST.TBS_ID AND I_LIST.INDEX_ID = I.INDEX_ID AND I_LIST.TABLE_OID = I.TABLE_OID AND I_LIST.TBS_ID = TBS.ID AND D.SPACEID = I_LIST.TBS_ID AND U.USER_ID = I_LIST.USER_ID ORDER BY I_LIST.TABLE_NAME, I_LIST.INDEX_NAME, I_LIST.PARTITION_NAME, I_LIST.INDEX_PARTITION_NAME ; |
...