oracle 带日志的表,Oracle通用日志表设计

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代表非异常信息’;