Supplemental Logging 설정에 따른 테이블 Logging

Supplemental Logging 설정에 따른 테이블 Logging 

결론 :
1. Database PK, UK 에 대한 Supplemental Logging 설정 환경에서 PK,UK 가 없는 테이블 생성 하면

   , 전체 칼럼에 대한 Logging 이 설정됨
2. (1) 상황에서 PK 추가하면, PK 칼럼에 대한 Logging 설정됨 

 

의문 (나중에 확인 해보자 ) : 
1. logging 설정이 
   table(LOGMNR$ALWAYS_SUPLOG_COLUMNS('SCOTT','LOGGING_TEST_01'))  조회 되지만,
   dba_log_groups, dba_log_group_columns 에선 확인 불가 

 

 

CREATE TABLE LOGGING_TEST_01 ( A1 NUMBER, A2 VARCHAR2(10), A3 DATE ) ;

SELECT * FROM table(LOGMNR$ALWAYS_SUPLOG_COLUMNS('SCOTT','LOGGING_TEST_01'))   ;
-- 결과 없음 


SELECT g.owner, g.log_group_name, g.table_name, g.log_group_type,
LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.POSITION) column_list
FROM dba_log_groups g,
     dba_log_group_columns c
WHERE g.owner = c.owner(+)
AND   g.log_group_name = c.log_group_name(+)
AND   g.table_name = c.table_name(+)
AND   g.owner ='SCOTT' 
AND   g.TABLE_NAME ='LOGGING_TEST_01'
GROUP BY g.owner, g.log_group_name, g.table_name, g.log_group_type
ORDER BY g.owner, g.log_group_name, g.table_name, g.log_group_type
-- 결과 없음

SELECT NAME, LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, FORCE_LOGGING
, SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL
FROM V$DATABASE ;

ORACLE19 NOARCHIVELOG NO NO NO NO NO NO

-- logging 설정 
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA ;
Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.

SQL>

SELECT NAME, LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, FORCE_LOGGING
, SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL
FROM V$DATABASE ;

ORACLE19 NOARCHIVELOG YES NO NO NO NO NO
SUPPLEMENTAL_LOG_DATA_MIN = YES 

CREATE TABLE LOGGING_TEST_02 ( A1 NUMBER, A2 VARCHAR2(10), A3 DATE ) ;
SELECT * FROM table(LOGMNR$ALWAYS_SUPLOG_COLUMNS('SCOTT','LOGGING_TEST_02'))   ;
-- 결과 없음 
SELECT g.owner, g.log_group_name, g.table_name, g.log_group_type,
LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.POSITION) column_list
FROM dba_log_groups g,
     dba_log_group_columns c
WHERE g.owner = c.owner(+)
AND   g.log_group_name = c.log_group_name(+)
AND   g.table_name = c.table_name(+)
AND   g.owner ='SCOTT' 
AND   g.TABLE_NAME ='LOGGING_TEST_02'
GROUP BY g.owner, g.log_group_name, g.table_name, g.log_group_type
ORDER BY g.owner, g.log_group_name, g.table_name, g.log_group_type 
-- 결과 없음 

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA ( PRIMARY KEY) COLUMNS ;
Database altered.

SELECT NAME, LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, FORCE_LOGGING
, SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL
FROM V$DATABASE ;

ORACLE19 NOARCHIVELOG YES YES NO NO NO NO
SUPPLEMENTAL_LOG_DATA_MIN = YES 
SUPPLEMENTAL_LOG_DATA_PK  = YES 

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA ( UNIQUE ) COLUMNS ;
Database altered.

SELECT NAME, LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, FORCE_LOGGING
, SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL
FROM V$DATABASE ;

ORACLE19 NOARCHIVELOG YES YES YES NO NO NO
SUPPLEMENTAL_LOG_DATA_MIN = YES 
SUPPLEMENTAL_LOG_DATA_PK  = YES 
SUPPLEMENTAL_LOG_DATA_UI  = YES 

CREATE TABLE LOGGING_TEST_03 ( A1 NUMBER, A2 VARCHAR2(10), A3 DATE ) ;
SELECT * FROM table(LOGMNR$ALWAYS_SUPLOG_COLUMNS('SCOTT','LOGGING_TEST_03'))   ;
OWNER             TABLE_NAME         COLUMN_NAME   INTCOL      SEGCOL      USERCOL    
----------------- ------------------ ------------- ----------- ----------- -----------
SCOTT             LOGGING_TEST_03    A1                      1           1           1
SCOTT             LOGGING_TEST_03    A2                      2           2           2
SCOTT             LOGGING_TEST_03    A3                      3           3           3
-- PK, UK SUPPLEMENTAL 설정 상태에서, PK,UK 없는 테이블 생성 시, 테이블 전체 칼럼에 대한 Logging 


SELECT g.owner, g.log_group_name, g.table_name, g.log_group_type,
LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.POSITION) column_list
FROM dba_log_groups g,
     dba_log_group_columns c
WHERE g.owner = c.owner(+)
AND   g.log_group_name = c.log_group_name(+)
AND   g.table_name = c.table_name(+)
AND   g.owner ='SCOTT' 
AND   g.TABLE_NAME ='LOGGING_TEST_03'
GROUP BY g.owner, g.log_group_name, g.table_name, g.log_group_type
ORDER BY g.owner, g.log_group_name, g.table_name, g.log_group_type
-- 결과 없음 

-- PK 추가
ALTER TABLE LOGGING_TEST_03 ADD CONSTRAINT LOGGING_TEST_03_PK PRIMARY KEY ( A1 ) ;

SQL> SELECT * FROM table(LOGMNR$ALWAYS_SUPLOG_COLUMNS('SCOTT','LOGGING_TEST_03'));

OWNER    TABLE_NAME       COLUMN_NAME    INTCOL      SEGCOL      USERCOL    
-------- ---------------- -------------- ----------- ----------- -----------
SCOTT    LOGGING_TEST_03  A1                       1           1           1

1 rows selected.

SQL> SELECT g.owner, g.log_group_name, g.table_name, g.log_group_type,
LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.POSITION) column_list
FROM dba_log_groups g,
     dba_log_group_columns c
WHERE g.owner = c.owner(+)
AND   g.log_group_name = c.log_group_name(+)
AND   g.table_name = c.table_name(+)
AND   g.owner ='SCOTT' 
AND   g.TABLE_NAME ='LOGGING_TEST_03'
GROUP BY g.owner, g.log_group_name, g.table_name, g.log_group_type
ORDER BY g.owner, g.log_group_name, g.table_name, g.log_group_type;
-- 결과 없음