--创建表（PUB_SYSLOG）
create table PUB_SYSLOG
(
logid VARCHAR2(36) not null,
requestuser VARCHAR2(20),
requesttime DATE not null,
requestip VARCHAR2(20) not null,
requesturl VARCHAR2(500),
requestmethod VARCHAR2(100),
requestparam VARCHAR2(4000),
requestresult VARCHAR2(4000),
logtype VARCHAR2(110),
requestmethodnote VARCHAR2(4000)
);
--创建表（PUB_SYSLOG_BACKUP）
create table PUB_SYSLOG_BACKUP
(
logid VARCHAR2(36) not null,
requestuser VARCHAR2(20),
requesttime DATE not null,
requestip VARCHAR2(20) not null,
requesturl VARCHAR2(500),
requestmethod VARCHAR2(100),
requestparam VARCHAR2(4000),
requestresult VARCHAR2(4000),
logtype VARCHAR2(110),
requestmethodnote VARCHAR2(4000)
);
-- Add comments to the table
comment on table PUB_SYSLOG_BACKUP
is '日志备份';
-- Create/Recreate primary, unique and foreign key constraints
alter table PUB_SYSLOG_BACKUP
add constraint PK_LOGBACK primary key (LOGID);
--创建视图（关联日志表和日志备份表）
create or replace view pub_syslogview as
select "LOGID","REQUESTUSER","REQUESTTIME","REQUESTIP","REQUESTURL","REQUESTMETHOD","REQUESTPARAM","REQUESTRESULT","LOGTYPE","REQUESTMETHODNOTE" from pub_syslog
union ALL
select "LOGID","REQUESTUSER","REQUESTTIME","REQUESTIP","REQUESTURL","REQUESTMETHOD","REQUESTPARAM","REQUESTRESULT","LOGTYPE","REQUESTMETHODNOTE" from pub_syslog_backup;
--创建同义词（基于视图的同义词）
create or replace synonym PUB_SYSLOG_BACK
for PUB_SYSLOGVIEW;
--备份系统日志（存储过程，定时调用）
create or replace procedure backup_pub_syslog is
begin
insert into pub_syslog_backup
(logid,
requestuser,
requesttime,
requestip,
requesturl,
requestmethod,
requestparam,
requestresult,
logtype,
requestmethodnote)
select slog.logid,
slog.requestuser,
slog.requesttime,
slog.requestip,
slog.requesturl,
slog.requestmethod,
slog.requestparam,
slog.requestresult,
slog.logtype,
slog.requestmethodnote
from pub_syslog slog;
delete pub_syslog;
end backup_pub_syslog;
--调用备份日志存储过程
call backup_pub_syslog();
commit;
--查询操作日志
select * from PUB_SYSLOG_BACK;