Oracle通用日誌表設計

-- Create sequence
create sequence SEQ_COMMON_LOG
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

 

-- Create table
create table TU_COMMON_LOG
(
  COL_SEQUENCE           NUMBER(20) not null,
  COL_DATE               DATE default sysdate not null,
  COL_SYSTEM             VARCHAR2(100),
  COL_PACKAGE            VARCHAR2(100),
  COL_FUNCTION_PROCEDURE VARCHAR2(100),
  COL_MESSAGE            VARCHAR2(4000),
  COL_IS_EXCEPTION       VARCHAR2(1) default 'N'
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table TU_COMMON_LOG
  is '通用日誌表';
-- Add comments to the columns
comment on column TU_COMMON_LOG.COL_SEQUENCE
  is '記錄SEQUENCE(從SEQ_COMMON_LOG擷取)';
comment on column TU_COMMON_LOG.COL_DATE
  is '記錄產生時間';
comment on column TU_COMMON_LOG.COL_SYSTEM
  is '所屬系統';
comment on column TU_COMMON_LOG.COL_PACKAGE
  is '日誌所在包';
comment on column TU_COMMON_LOG.COL_FUNCTION_PROCEDURE
  is '日誌所在函數或預存程序';
comment on column TU_COMMON_LOG.COL_MESSAGE
  is '日誌詳細資料';
comment on column TU_COMMON_LOG.COL_IS_EXCEPTION
  is '日誌是否是異常資訊 Y代表異常資訊 N代表非異常資訊';