Oracle补充日志及日志记录规则(二)

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

数据库已更改。

SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
NO NO IMPLICIT

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

数据库已更改。

SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
NO NO NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

数据库已更改。

SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
NO NO IMPLICIT

IMPLICIT - Minimal supplemental logging is enabled because all or a combination of primary key, unique key, and foreign key supplemental logging is enabled.
YES - Minimal supplemental logging is enabled through an ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement.

SUPPLEMENTAL_LOG_DATA_MIN=YES和IMPLICIT都表示数据库启动了最小化的补充日志。YES表示通过ALTER DATABASE ADD SUPPLEMENTAL LOG DATA语句启动的最小化补充日志;如果启动了对主键、唯一键、外键、ALL的补充日志，默认情况下都会打开最小化的补充日志，IMPLICIT表示的是通过启动对主键、唯一键、外键或者ALL的支持而打开的最小化补充日志。

SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
NO NO NO

下面是ALTER DATABASE ADD SUPPLEMENTAL LOG DATA的语法：

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE,FOREIGN KEY) COLUMNS;

数据库已更改。

多种类型的补充日志可以一起设置。

SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
YES NO IMPLICIT

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE,FOREIGN KEY) COLUMNS;

数据库已更改。

SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
NO NO NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

数据库已更改。

SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_D