create table進階學習系列(七)

create table進階學習系列(七)
  本篇學習create table子項:supplemental_logging_props
 
supplemental_logging_props子項組成如下: 
 
  { supplemental_log_grp_clause
   | supplemental_id_key_clause
  }
 
supplemental_log_grp_clause子句
  GROUP log_group
  (column [ NO LOG ]
           [, column [ NO LOG ] ]...)
  [ ALWAYS ]
 
 
supplemental_id_key_clause子句
DATA
({ ALL
 | PRIMARY KEY
 | UNIQUE
 | FOREIGN KEY
 }
   [, { ALL
      | PRIMARY KEY
      | UNIQUE
      | FOREIGN KEY
      }
  ]...
)
COLUMNS

先測試  supplemental_log_grp_clause子句

ALL_LOG_GROUPS
  --此檢視查詢當前資料庫的日誌組定義的相關資訊
 ALL_LOG_GROUPS describes the log group definitions on the tables accessible to the current user.
 
SQL> r
  1* select log_group_name,table_name,column_name,position,logging_property from
 dba_log_group_columns

LOG_GROUP_NAME       TABLE_NAME           COLUMN_NAM   POSITION LOGGING_PROP
-------------------- -------------------- ---------- ---------- ------------
ENC$_LOG_GRP         ENC$                 OBJ#                1 LOG
ENC$_LOG_GRP         ENC$                 OWNER#              2 LOG

---如下為supplemental_logging_props語義
supplemental_logging_props

 ---記錄額外日誌為了支援基於日誌的工具
The supplemental_logging_props clause lets you instruct the database to put additional data into the log stream to support log-based tools.
 
supplemental_log_grp_clause Use this clause to create a named log group.
 ?
The NO LOG clause lets you omit from the redo log one or more columns that would otherwise be included in the redo for the named log group. You must specify at least one fixed-length column without NO LOG in the named log group.
 
?
If you specify ALWAYS, then during an update, the database includes in the redo all columns in the log group. This is called an unconditional log group (sometimes called an "always log group"), because Oracle Database supplementally logs all the columns in the log group when the associated row is modified. If you omit ALWAYS, then the database supplementally logs all the columns in the log group only if any column in the log group is modified. This is called a conditional log group.
 

You can query the appropriate USER_, ALL_, or DBA_LOG_GROUP_COLUMNS data dictionary view to determine whether any supplemental logging has already been specified.

---第一種語法建立一直報錯,
SQL> create table t_constraint_table(a int) group lgrp always;
create table t_constraint_table(a int) group lgrp always
                                       *
ERROR at line 1:
ORA-00922: missing or invalid option

--查詢網路,採用alter table執行成功
SQL> alter table t_log add supplemental log group log_grp(a) always;

Table altered.

SQL>

---上述新增的資訊已經體現出來,log_grp日誌組,它用於表t_log
SQL> select log_group_name,table_name,column_name,position,logging_property from
  2   dba_log_group_columns
  3  ;
 
LOG_GROUP_NAME                 TABLE_NAME                     COLUMN_NAME                                                                        POSITION LOGGING_PROPERTY
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ---------- ----------------
ENC$_LOG_GRP                   ENC$                           OBJ#                                                                                      1 LOG
ENC$_LOG_GRP                   ENC$                           OWNER#                                                                                    2 LOG
LOG_GRP                        T_LOG                          A                                                                                         1 LOG
 
--日誌組資訊
SQL> select * from dba_log_groups;
 
OWNER                          LOG_GROUP_NAME                 TABLE_NAME                     LOG_GROUP_TYPE      ALWAYS      GENERATED
------------------------------ ------------------------------ ------------------------------ ------------------- ----------- --------------
SYS                            ENC$_LOG_GRP                   ENC$                           USER LOG GROUP      ALWAYS      USER NAME
SCOTT                          LOG_GRP                        T_LOG                          USER LOG GROUP      ALWAYS      USER NAME
 
--刪除表的附加日誌記錄功能 
SQL> alter table t_log drop supplemental log group log_grp;
 
Table altered 

---必須要在日誌組中指定一個表列開啟日誌記錄功能,否則報錯
SQL> alter table t_log add supplemental log group log_grp (a no log) always;
 
alter table t_log add supplemental log group log_grp (a no log) always
 
ORA-32592: all columns in log group can not be no log columns
 
SQL> alter table t_log add supplemental log group log_grp (a,b no log) always;
 
Table altered

---第二種語法 supplemental_id_key_clause子句
SQL> alter table t_log add supplemental log data (all) columns;
 
Table altered

SQL> alter table t_log drop supplemental log data(all) columns;
 
Table altered
 
SQL> alter table t_log add supplemental log data (primary key) columns;
 
Table altered
 
SQL> alter table t_log drop supplemental log data(all) columns;
 
alter table t_log drop supplemental log data(all) columns
 
ORA-32587: Cannot drop nonexistent all column supplemental logging
 
SQL> alter table t_log drop supplemental log data(primary key) columns;
 
Table altered
 
SQL> alter table t_log add supplemental log data (unique) columns;
 
Table altered
 
SQL> alter table t_log drop supplemental log data(unique) columns;
 
Table altered
 
SQL> alter table t_log add supplemental log data (foreign) columns;
 
alter table t_log add supplemental log data (foreign) columns
 
ORA-00905: missing keyword
 
SQL> alter table t_log add supplemental log data (foreign key) columns;
 
Table altered
 
SQL> alter table t_log drop supplemental log data(foreign key) columns;
 
Table altered
 
SQL> alter table t_log add supplemental log data (all primary key) columns;
 
alter table t_log add supplemental log data (all primary key) columns
 
ORA-00907: missing right parenthesis
 
SQL> alter table t_log add supplemental log data (all,primary key) columns;
 
Table altered
 
SQL> alter table t_log drop supplemental log data (all,primary key) columns;
 
Table altered

---第二種語法的語義
--可指定all,primary key,unique,foreign key的單項或組合或全部
supplemental_id_key_clause Use this clause to specify that all or a combination of the primary key, unique key, and foreign key columns should be supplementally logged. Oracle Database will generate either an unconditional log group or a conditional log group. With an unconditional log group, the database supplementally logs all the columns in the log group when the associated row is modified. With a conditional log group, the database supplementally logs all the columns in the log group only if any column in the log group is modified.

---如下為各選項含義

---all columns為無條件式,由系統生成的日誌組;
If you specify ALL COLUMNS, then the database includes in the redo log all the fixed-length maximum size columns of that row. Such a redo log is a system-generated unconditional log group.
 
---primary key,用於附有主鍵的表;一旦表更新,則記錄表主鍵所有列相關資訊
If you specify PRIMARY KEY COLUMNS, then for all tables with a primary key, the database places into the redo log all columns of the primary key whenever an update

----資料庫評估記錄哪些列的原則如下
is performed. Oracle Database evaluates which columns to supplementally log as follows:

--首先選取主鍵約束所屬列(即引約束處於驗證或標記不rely,且未標記為disabled或initially deferred)
First the database chooses columns of the primary key constraint, if the constraint is validated or marked RELY and is not marked as DISABLED or INITIALLY DEFERRED.
 
--如主鍵列不存在,資料庫會選取最小的非空值且為唯一索引的列;
If no primary key columns exist, then the database looks for the smallest UNIQUE index with at least one NOT NULL column and uses the columns in that index.
 
--如上述兩條件皆不滿足,資料庫記錄表中所有的標量列
If no such index exists, then the database supplementally logs all scalar columns of the table.
 

---unique columns,面向唯一鍵或點陣圖索引的列,此為有條件式的系統產生的日誌組
If you specify UNIQUE COLUMNS, then for all tables with a unique key or a bitmap index, if any of the unique key or bitmap index columns are modified, the database
places into the redo log all other columns belonging to the unique key or bitmap index. Such a log group is a system-generated conditional log group.
 
--foreign key,面向外來鍵所屬表.如外來鍵列修改,資料庫則記錄屬於此外來鍵的其它所有列的資訊;
If you specify FOREIGN KEY COLUMNS, then for all tables with a foreign key, if any foreign key columns are modified, the database places into the redo log all
other columns belonging to the foreign key. Such a redo log is a system-generated conditional log group.
 
---如你指定多次此子句,資料庫會建立多個獨立的日誌組
If you specify this clause multiple times, then the database creates a separate log group for each specification. You can query the appropriate USER_, ALL_,
or DBA_LOG_GROUPS data dictionary view to determine whether any supplemental logging data has already been specified.