-- 테이블 변경 정도 체크 하기
-- 10% 이상 변경 발생시만 Logging 된다.
-- 전제 조건 : MONITORING=YES / STATISTICS_LEVEL is set to TYPICAL
-- 아래 질의 결과가 없다면, 강제로 모니터링 정보를 Flush 하면 된다고 한다.
-- execute DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
-- SYS 로만 수행 가능하다.

2011/12/26

Partition + Normal Table

SELECT SYSDATE AS CHECK_DT, A.OWNER, A.SEGMENT_NAME, A.PARTITION_NAME, A.BYTES/1024/1024 AS TSIZE, B.NUM_ROWS,
C.INSERTS, C.UPDATES, C.DELETES, ( C.INSERTS + C.UPDATES + C.DELETES) AS MODIFIED, (C.INSERTS + C.UPDATES + C.DELETES )/B.NUM_ROWS*100 ,C.TIMESTAMP
FROM DBA_SEGMENTS A, DBA_TAB_PARTITIONS B , DBA_TAB_MODIFICATIONS C
WHERE B.TABLE_OWNER IN ('ADM','CDM')
AND B.TABLE_NAME = A.SEGMENT_NAME
AND B.PARTITION_NAME = A.PARTITION_NAME
AND A.SEGMENT_NAME = C.TABLE_NAME
AND A.PARTITION_NAME= C.PARTITION_NAME
UNION ALL
SELECT SYSDATE AS CHECK_DT, A.OWNER, A.SEGMENT_NAME, A.PARTITION_NAME, A.BYTES/1024/1024 AS TSIZE, B.NUM_ROWS,
C.INSERTS, C.UPDATES, C.DELETES, ( C.INSERTS + C.UPDATES + C.DELETES) AS MODIFIED, (C.INSERTS + C.UPDATES + C.DELETES )/B.NUM_ROWS*100 ,C.TIMESTAMP
FROM DBA_SEGMENTS A, DBA_TABLES B , DBA_TAB_MODIFICATIONS C
WHERE B.OWNER IN ('ADM','CDM')
AND B.PARTITIONED = 'NO'

AND B.TABLE_NAME = A.SEGMENT_NAME
AND A.SEGMENT_NAME = C.TABLE_NAME ;

==> 데이타 변경이 없는 경우 TAB_TAB_MODIFICATION 값이 없어, 조회 결과에서 제외 된다.