初始化

/********************************************

功能说明： 生产库初始化脚本
创建人： 
创建日期： 

********************************************/
---accept sid char prompt 'sid is:'
---define password=paic1234

spool prd_database_initial_01.log
conn sys/&password@&sid as sysdba

----------------------------------------------------------------
-- (1)关闭11g大小写敏感的参数设置;
-- (2)设置数据库默认表空间属性为smallfile;
-- (3)设置optimizer_secure_view_merging=false;
-- (4)关闭默认打开的审计项
-- (5)修改default profile
----------------------------------------------------------------
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
alter system set deferred_segment_creation=false scope=both;
--alter system set sec_case_sensitive_logon = false scope=both;
alter system set db_writer_processes = 4 scope=spfile;
alter system set archive_lag_target=900 scope=both;
alter system set fast_start_parallel_rollback=false scope=both;
alter system set fast_start_mttr_target=300 scope=both;
alter system set undo_retention=10800 scope=both ;
alter system set cell_offload_processing=false scope=both;
alter system set db_securefile='ALWAYS' scope=both ;
alter system set filesystemio_options='SETALL' scope=spfile;
alter system set log_checkpoints_to_alert=true scope=both;
alter system set parallel_min_servers =0 scope=both;
alter system set pga_aggregate_limit =0 scope=both;
alter system set PARALLEL_DEGREE_LIMIT=4 scope=both ;

alter system set "_cursor_obsolete_threshold"=1024 scope=spfile;
alter system set "_optimizer_use_feedback" = FALSE scope=both;
alter system set "_optimizer_gather_feedback" = FALSE scope=both;
alter system set "_CURSOR_RELOAD_FAILURE_THRESHOLD"=4 scope=both;

alter system set optimizer_secure_view_merging=false scope=spfile;
---禁用FTS达到阀值后使用DPR.禁用adaptive log file sync---------
alter system set "_serial_direct_read"=never scope=both ;
alter system set "_use_adaptive_log_file_sync"=false scope=both ;
alter system set "_kttext_warning"=1 scope=both ;
alter system set "_use_single_log_writer"=TRUE scope=spfile;
---disable LOGON delay-----
alter system set "_sys_logon_delay"=0 scope=spfile ;

alter system set audit_trail=DB scope=spfile;
alter system set parallel_execution_message_size=32768 scope=spfile;
alter system set audit_sys_operations=false scope=spfile;
alter system set temp_undo_enabled=true scope=spfile;

---for GOLDENGATE
alter system set ENABLE_GOLDENGATE_REPLICATION=true scope=both;
---for RAC
alter system set "_asm_hbeatiowait"=120 scope=spfile sid='*';
alter system set "_gc_policy_time"=0 scope=spfile sid='*' /* close DRM */;
alter system set parallel_force_local=true scope=both sid='*';

alter profile default limit failed_login_attempts unlimited;
alter profile default limit password_grace_time unlimited;
alter profile default limit password_lock_time unlimited;
alter profile default limit password_life_time unlimited;
noaudit alter system;
noaudit create external job;
noaudit create session;
noaudit role;
noaudit profile;
noaudit grant any role;
noaudit system grant;
noaudit create any procedure;
noaudit alter any procedure;
noaudit drop any procedure;
noaudit alter profile;
noaudit drop profile;
noaudit grant any privilege;
noaudit create any library;
noaudit exempt access policy;
noaudit grant any object privilege;
noaudit create any job;
noaudit system audit;
noaudit directory;

noaudit policy ORA_LOGON_FAILURES ;
noaudit policy ORA_SECURECONFIG ;
----------------------------------------------------------------
-- 修改统计信息收集策略
-- 关闭直方图收集策略
exec dbms_stats.set_global_prefs(pname=>'METHOD_OPT', pvalue=>'FOR ALL COLUMNS SIZE 1');
-- 打开publish
exec dbms_stats.set_global_prefs(pname=>'PUBLISH', pvalue=>'TRUE');
-- 修改统计信息默认保留时间为1年
exec dbms_stats.alter_stats_history_retention(365);
-- 修改AWR的保留时间(三个月，即90天）和采样频率（15分钟）
exec dbms_workload_repository.modify_snapshot_settings(retention=>129600,interval=>15);
-- 修改SMB保留的空间占比
exec dbms_spm.configure('space_budget_percent',30);
-- 停止11g数据库的自动任务auto space advisor,sql tuning advisor
exec dbms_auto_task_admin.disable(client_name=>'auto space advisor',operation=>NULL,window_name=>NULL);
exec dbms_auto_task_admin.disable(client_name=>'sql tuning advisor',operation=>NULL,window_name=>NULL);

-- 收集dictionary和fixed object的统计信息
exec dbms_stats.gather_dictionary_stats;
alter system set aq_tm_processes = 1 scope = both;
exec dbms_stats.gather_fixed_objects_stats;
alter system set aq_tm_processes = 0 scope = both;
--reset aq_tm_processes
alter system reset aq_tm_processes scope = spfile;
-- 默认使用smallfile
alter database set default smallfile tablespace;
-- 调整supplemental_log
alter database add supplemental log data;
--alter database add supplemental log data (primary key,unique index) columns; ---cancel by liuyong746 20170622--

-- 打开数据库的force logging
alter database force logging;

-- 关闭sqlplan baseline自动捕获
alter system set optimizer_capture_sql_plan_baselines = false scope = both;

-- 关闭resource_manager
--设定参数关闭RESOURCE_MANAGER
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '' SCOPE=BOTH;

--- 将scheduler 的resource plan 指定成一个空的plan
execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

exec dbms_scheduler.set_attribute('SYS.MONDAY_WINDOW','RESOURCE_PLAN','');
exec dbms_scheduler.set_attribute('SYS.TUESDAY_WINDOW','RESOURCE_PLAN','');
exec dbms_scheduler.set_attribute('SYS.WEDNESDAY_WINDOW','RESOURCE_PLAN','');
exec dbms_scheduler.set_attribute('SYS.THURSDAY_WINDOW','RESOURCE_PLAN','');
exec dbms_scheduler.set_attribute('SYS.FRIDAY_WINDOW','RESOURCE_PLAN','');
exec dbms_scheduler.set_attribute('SYS.SATURDAY_WINDOW','RESOURCE_PLAN','');
exec dbms_scheduler.set_attribute('SYS.SUNDAY_WINDOW','RESOURCE_PLAN','');

set serverout on
begin
---关闭SYS用户的分区表INCREMENTAL增量统计信息收集,规避12c INCREMENTAL统计信息收集导致的redo突增的问题----add 2018.1.18-----
---关闭sys.WRI$_OPTSTAT_SYNOPSIS$表分区Incremental统计信息收集
for cur in (
select tt.owner , tt.table_name from dba_tables tt where tt.partitioned = 'YES' and tt.owner = 'SYS' ) loop
dbms_stats.set_table_prefs(cur.owner ,cur.table_name,'INCREMENTAL','FALSE' );
end loop ;
dbms_output.put_line('关闭SYS用户的分区表INCREMENTAL增量统计信息收集.OK');
end ;
/

/********************************************

功能说明： 创建密码限制文件
参数说明：
创建人：
创建日期： 
修改人：
修改日期：
修改说明：

********************************************/

--@D:\w_dba\oraclesec\pa_pw_verify_function.sql
----------------------------------------------------------------
-- 密码规则如下：
-- 密码不能与用户名相同
-- 密码长度至少8位
-- 密码必须包含至少一位数字，至少一个字母
-- 密码和原密码从第一个字符开始比较，至少一共有5个字符不同
-----------------------------------------------------------------
-- 该function只能有sys创建
-----------------------------------------------------------------

CREATE OR REPLACE FUNCTION pa_pw_verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
digitarray varchar2(20);
chararray varchar2(52);

BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

-- Check if the password is same as the username
IF upper(password) = upper(username) THEN
raise_application_error(-20001, 'Password same as user');
END IF;

-- Check for the minimum length of the password
IF length(password) < 8 THEN
raise_application_error(-20002, 'Password length less than 8');
END IF;

-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
raise_application_error(-20002, 'Password too simple');
END IF;

-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one digit and one character');
END IF;
-- 2. Check for the character

ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one digit and one character');
END IF;

-- Check if the password differs from the previous password by at least
-- 5 letters
IF old_password = '' THEN
raise_application_error(-20004, 'Old password is null');
END IF;
-- Everything is fine; return TRUE ;
differ := length(old_password) - length(password);

IF abs(differ) < 5 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;
differ := abs(differ);
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;
IF differ < 5 THEN
raise_application_error(-20004, 'Password should differ by at least 5 characters');
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/

------------------------
--CREATE PROFILE pa_pw_profile LIMIT
--FAILED_LOGIN_ATTEMPTS 5
--PASSWORD_GRACE_TIME 1
--PASSWORD_LIFE_TIME 60
--PASSWORD_REUSE_TIME UNLIMITED
--PASSWORD_REUSE_MAX 10
--PASSWORD_LOCK_TIME 1/24
--PASSWORD_VERIFY_FUNCTION pa_pw_verify_function;

/********************************************

功能说明： 创建 profile限制文件
参数说明：
创建人：
创建日期： 
修改人：
修改日期：
修改说明：

********************************************/

--@d:\w_dba\oraclesec\pw_profile.sql
CREATE PROFILE pa_pw_profile LIMIT
/*连续登录3次失败，则用户被锁*/ FAILED_LOGIN_ATTEMPTS 3
/*如果用户连续3次登录失败，账号被锁，指定被锁的时间长度*/ PASSWORD_LOCK_TIME 999
/*提示密码过期，仍能用原密码登录的天数*/ PASSWORD_GRACE_TIME 7
/*密码有效的天数，即需要用户至少60天修改一次密码*/ PASSWORD_LIFE_TIME 60
/*需要多少天后，该密码可以被重用，这里天数不受限制*/ PASSWORD_REUSE_TIME UNLIMITED
/*当前密码被重用之前，最少需要修改10次密码 */ PASSWORD_REUSE_MAX 10
/*使用的密码规则验证的函数*/ PASSWORD_VERIFY_FUNCTION pa_pw_verify_function
;

alter profile pa_pw_profile limit idle_time 10;

alter profile default limit failed_login_attempts unlimited;

/************************************************unified audit policy begin*******************************************/
conn sys/&password@&sid as sysdba

CREATE AUDIT POLICY PA_CONNECT_POLICY ACTIONS LOGON,LOGOFF;

CREATE AUDIT POLICY PA_DML_POLICY
ACTIONS insert ,update ,delete
WHEN 'SYS_CONTEXT (''USERENV'', ''DATABASE_ROLE'') ''PHYSICAL STANDBY''
AND SYS_CONTEXT (''USERENV'', ''BG_JOB_ID'') IS NULL
AND SYS_CONTEXT (''USERENV'', ''FG_JOB_ID'') =''0'''
EVALUATE PER SESSION ;

create AUDIT policy PA_select_POLICY ACTIONS select ;

create AUDIT policy pa_system_policy
privileges ALTER DATABASE,
CREATE ANY TABLE ,ALTER ANY TABLE ,DROP ANY TABLE,
CREATE USER,ALTER USER,DROP USER,
CREATE PUBLIC DATABASE LINK, ALTER PUBLIC DATABASE LINK, DROP PUBLIC DATABASE LINK,
CREATE DATABASE LINK, ALTER DATABASE LINK,
CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM ;

audit policy pa_system_policy ;
/************************************************dbmgr begin*******************************************/
/*创建dbmgr用户*/
create user dbmgr identified by paic1234
default tablespace users
temporary tablespace temp
quota unlimited on users
profile pa_pw_profile;

audit policy PA_DML_POLICY by dbmgr;
audit policy PA_CONNECT_POLICY by dbmgr;

grant CREATE SESSION to dbmgr;
grant SELECT ANY TABLE to dbmgr;
grant dba to dbmgr;
GRANT SELECT ANY DICTIONARY to dbmgr;
grant ADMINISTER DATABASE TRIGGER to dbmgr ;

grant select on v_$session to dbmgr;
grant select on v_$statname to dbmgr;
grant select on v_$sesstat to dbmgr;
grant select on dba_users to dbmgr;

grant create user to dbmgr;
grant drop user to dbmgr;
grant create any trigger to dbmgr;
grant alter user to dbmgr;
grant alter system to dbmgr;

grant exp_full_database To dbmgr with admin Option ;
grant select on Dba_Role_Privs to dbmgr ;
grant restricted session to dbmgr with admin option ;

conn dbmgr/&password@&sid

/********************************************

功能说明： dbmgr脚本
参数说明：
创建人： 
创建日期： 
修改人： 
修改日期： 
修改说明： 根据cow实名需求进行修改

********************************************/
--创建实名用户类型表，并初始化数据
create table factuser_type (
user_type varchar2(30) not null primary key );
insert into factuser_type values ('实名用户');
insert into factuser_type values ('cow实名');
insert into factuser_type values ('开发公共实名');
commit;

--创建新的实名用户管理表
--DBA的position为”DBA“
create table factuser_list (
oa_name varchar2(30) primary key,
cn_name varchar2(50) not null,
status varchar2(30) default 'OPEN' not null,
oa_email varchar2(45) ,
user_type varchar2(30) default '实名用户' not null ,
user_level number default 0 not null ,
hr_no varchar2(60) default '请补入员工代码' not null,
dept varchar2(100) default '请补入部门' not null,
position varchar2(100) default '请补入岗位' not null,
create_date date,
end_date date,
permit_start_time date,
permit_end_time date ,
constraint ck_factuser_list_status check (status in ('OPEN','DROPPED','LOCKED','LOCKED-离司转岗')),
constraint ck_factuser_list_level check (user_level in (0,1))
);

alter table factuser_list add (
constraint fk_factuser_list_user_type foreign key (user_type) references factuser_type(user_type));

alter table factuser_list modify ( create_date date not null);

comment on column factuser_list.oa_name is '实名用户名称';
comment on column factuser_list.cn_name is '实名用户使用者';
comment on column factuser_list.oa_email is '实名用户使用者E-MAIL';
comment on column factuser_list.user_type is '实名用户类型';
comment on column factuser_list.user_level is '实名用户级别';
comment on column factuser_list.hr_no is '实名用户员工代码';
comment on column factuser_list.dept is '实名用户部门';
comment on column factuser_list.position is '实名用户岗位';
comment on column factuser_list.create_date is '实名用户创建时间';
comment on column factuser_list.end_date is '实名用户有效日期';
COMMENT ON COLUMN DBMGR.FACTUSER_LIST.permit_start_time IS '通行证开始时间';
COMMENT ON COLUMN DBMGR.FACTUSER_LIST.permit_end_time IS '通行证结束时间';

--创建用户管理操作序列
CREATE SEQUENCE SEQ_FACTUSER_OPERATION_ID MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1;

--创建用户管理操作日志表
create table FACTUSER_OPERATION_LOG
(
ACTION_ID NUMBER NOT NULL,
ACTION_OPERATOR VARCHAR2(30) NOT NULL,
ACTION_SOURCE VARCHAR2(30) NOT NULL,
ACTION_DATE DATE NOT NULL,
ACTION_USERNAME VARCHAR2(30) NOT NULL,
ACTION_SQL VARCHAR2(1000) NOT NULL,
ACTION_STATUS VARCHAR2(30) NOT NULL,
ACTION_MESSAGE VARCHAR2(256) NOT NULL,
ACTION_SID NUMBER,
ACTION_SERIAL# NUMBER,
ACTION_LOGON_TIME DATE,
ACTION_MACHINE VARCHAR2(64),
ACTION_OSUSER VARCHAR2(30),
ACTION_PROGRAM VARCHAR2(48),
ACTION_TERMINAL VARCHAR2(30),
ACTION_MODULE VARCHAR2(48)
);
ALTER TABLE factUSER_OPERATION_LOG ADD CONSTRAINT PK_USER_OPERATION_LOG PRIMARY KEY (ACTION_ID);

comment on column factUSER_OPERATION_LOG.ACTION_ID is '用户操作序列标识';
comment on column factUSER_OPERATION_LOG.ACTION_OPERATOR is '用户操作执行者';
comment on column factUSER_OPERATION_LOG.ACTION_SOURCE is '用户操作来源';
comment on column factUSER_OPERATION_LOG.ACTION_DATE is '用户操作日期';
comment on column factUSER_OPERATION_LOG.ACTION_USERNAME is '用户操作对象名';
comment on column factUSER_OPERATION_LOG.ACTION_SQL is '用户操作语句';
comment on column factUSER_OPERATION_LOG.ACTION_STATUS is '用户操作状态';
comment on column factUSER_OPERATION_LOG.ACTION_MESSAGE is '用户操作信息';
comment on column factUSER_OPERATION_LOG.ACTION_SID is '用户操作SESSION信息';
comment on column factUSER_OPERATION_LOG.ACTION_SERIAL# is '用户操作SESSION信息';
comment on column factUSER_OPERATION_LOG.ACTION_LOGON_TIME is '用户操作SESSION信息';
comment on column factUSER_OPERATION_LOG.ACTION_MACHINE is '用户操作SESSION信息';
comment on column factUSER_OPERATION_LOG.ACTION_OSUSER is '用户操作SESSION信息';
comment on column factUSER_OPERATION_LOG.ACTION_PROGRAM is '用户操作SESSION信息';
comment on column factUSER_OPERATION_LOG.ACTION_TERMINAL is '用户操作SESSION信息';
comment on column factUSER_OPERATION_LOG.ACTION_MODULE is '用户操作SESSION信息';

-----创建用户最后一次登录信息表
create table mon_logon_lastinfo
(
username varchar2(30) not null,
last_logon_time date
);
alter table mon_logon_lastinfo
add constraint pk_mon_logon_lastinfo_username primary key (username);

/********************************************

功能说明： dbmgr脚本
参数说明：
创建人： 
创建日期： 
修改人： 
修改日期： 2007-8-21
修改说明： 为了兼容10g版本数据库，修改factuser_create_prc和factuser_drop_prc
修改人： 
修改日期： 2008-1-10
修改说明： 根据cow实名需求进行修改
修改人： 
修改日期： 2019-11-27
修改说明： devsup限制登录放行存储过程
********************************************/
CREATE OR REPLACE procedure DBMGR.proc_devuser_permit(
p_username varchar2 ,
p_start_date date ,
p_end_date date )
is
cursor cur_session is select sid,serial#,logon_time,machine,osuser,program,terminal,module
FROM v$session
WHERE audsid = USERENV( 'sessionid' );
vt_log factuser_operation_log%rowtype;

v_cnt number;
v_sql varchar2(300);

begin
select count(*) into v_cnt
from factuser_list
where oa_name = upper(ltrim(rtrim(p_username))) and oa_name like 'DEVSUP%' and status='OPEN';
if v_cnt = 1 then

if ceil(p_end_date - p_start_date) = 0 then

v_sql := 'update DBMGR.factuser_list set (permit_start_time, permit_end_time) = (select '||p_start_date||', '||p_end_date||' from dual) where oa_name=upper(ltrim(rtrim('||p_username||')))';

select seq_factuser_operation_id.nextval into vt_log.action_id from dual;
select user,sysdate,'proc_devuser_permit' into vt_log.action_operator,vt_log.action_date,vt_log.action_source from dual;
vt_log.action_sql := v_sql;
vt_log.action_username := p_username;
open cur_session;
fetch cur_session into vt_log.action_sid,vt_log.action_serial#,vt_log.action_logon_time,vt_log.action_machine,vt_log.action_osuser,vt_log.action_program,vt_log.action_terminal,vt_log.action_module;
close cur_session;

begin
--execute immediate v_sql;
update factuser_list set (permit_start_time, permit_end_time) = (select p_start_date, p_end_date from dual)
where oa_name=upper(ltrim(rtrim(p_username)));

vt_log.action_status := 'Success';
vt_log.action_message := 'Dev User account permit accept. ';

commit;
exception when others then
rollback;
vt_log.action_status := 'Failure';
vt_log.action_message := 'Error to unlock dev user account :'||substr(sqlerrm,1,220);
end;
--writing log
dbms_output.put_line(vt_log.action_status||' : '||vt_log.action_message);
FACTUSER_OPERATION_log_prc(vt_log,0);
else
raise_application_error(-20002, 'Permit refused ! The validity of permit can`t more than 7 days! ');
end if;
else
RAISE_APPLICATION_ERROR(-20001,'This user is not exist');
dbms_output.put_line('Can not find input dev username from factuser_list. ');
end if;
end proc_devuser_permit;
/

create or replace PROCEDURE FACTUSER_OPERATION_log_prc(
P_LOG FACTUSER_OPERATION_LOG%ROWTYPE,
P_TYPE NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
begin
if P_TYPE = 0 THEN
INSERT INTO FACTUSER_OPERATION_LOG VALUES (
P_LOG.ACTION_ID ,
P_LOG.ACTION_OPERATOR ,
P_LOG.ACTION_SOURCE ,
P_LOG.ACTION_DATE ,
P_LOG.ACTION_USERNAME ,
P_LOG.ACTION_SQL ,
P_LOG.ACTION_STATUS ,
P_LOG.ACTION_MESSAGE ,
P_LOG.ACTION_SID ,
P_LOG.ACTION_SERIAL# ,
P_LOG.ACTION_LOGON_TIME,
P_LOG.ACTION_MACHINE ,
P_LOG.ACTION_OSUSER ,
P_LOG.ACTION_PROGRAM ,
P_LOG.ACTION_TERMINAL ,
P_LOG.ACTION_MODULE
);
else
UPDATE FACTUSER_OPERATION_LOG SET ACTION_OPERATOR = DECODE(P_LOG.ACTION_OPERATOR,NULL,ACTION_OPERATOR,P_LOG.ACTION_OPERATOR),
ACTION_SOURCE = DECODE(P_LOG.ACTION_SOURCE,NULL,ACTION_SOURCE,P_LOG.ACTION_SOURCE),
ACTION_DATE = DECODE(P_LOG.ACTION_DATE,NULL,ACTION_DATE,P_LOG.ACTION_DATE),
ACTION_USERNAME = DECODE(P_LOG.ACTION_USERNAME,NULL,ACTION_USERNAME,P_LOG.ACTION_USERNAME),
ACTION_SQL = DECODE(P_LOG.ACTION_SQL,NULL,ACTION_SQL,P_LOG.ACTION_SQL),
ACTION_STATUS = DECODE(P_LOG.ACTION_STATUS,NULL,ACTION_STATUS,P_LOG.ACTION_STATUS),
ACTION_MESSAGE = DECODE(P_LOG.ACTION_MESSAGE,NULL,ACTION_MESSAGE,P_LOG.ACTION_MESSAGE),
ACTION_SID = DECODE(P_LOG.ACTION_SID,NULL,ACTION_SID,P_LOG.ACTION_SID),
ACTION_SERIAL# = DECODE(P_LOG.ACTION_SERIAL#,NULL,ACTION_SERIAL#,P_LOG.ACTION_SERIAL#),
ACTION_LOGON_TIME = DECODE(P_LOG.ACTION_LOGON_TIME,NULL,ACTION_LOGON_TIME,P_LOG.ACTION_LOGON_TIME),
ACTION_MACHINE = DECODE(P_LOG.ACTION_MACHINE,NULL,ACTION_MACHINE,P_LOG.ACTION_MACHINE),
ACTION_OSUSER = DECODE(P_LOG.ACTION_OSUSER,NULL,ACTION_OSUSER,P_LOG.ACTION_OSUSER),
ACTION_PROGRAM = DECODE(P_LOG.ACTION_PROGRAM,NULL,ACTION_PROGRAM,P_LOG.ACTION_PROGRAM),
ACTION_TERMINAL = DECODE(P_LOG.ACTION_TERMINAL,NULL,ACTION_TERMINAL,P_LOG.ACTION_TERMINAL),
ACTION_MODULE = DECODE(P_LOG.ACTION_MODULE,NULL,ACTION_MODULE,P_LOG.ACTION_MODULE)
WHERE ACTION_ID = P_LOG.ACTION_ID;

end IF;
commit;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Writing log detail error: ' || sqlerrm);
END FACTUSER_OPERATION_log_prc;
/

--------------------------
-- create factuser procedure
-- for dbas
--------------------------
create or replace procedure factuser_create_prc(
p_username varchar2 ,
p_cnname varchar2 ,
p_password varchar2 ,
p_deft_ts varchar2 ,
p_temp_ts varchar2 ,
p_profile varchar2 ,
p_deft_ts_qts number default -1, --MB
p_oa_email varchar2 default null,
p_user_type varchar2 default '实名用户',
p_user_level number default 0,
p_hr_no varchar2 ,
p_dept varchar2 ,
p_position varchar2 ,
p_end_date date default null )
is
cursor cur_session is select sid,serial#,logon_time,machine,osuser,program,terminal,module
FROM v$session
WHERE audsid = USERENV( 'sessionid' );
vt_log factuser_operation_log%rowtype;
v_deft_ts_qts varchar2(20) :='unlimited';
v_sql varchar2(4000);
v_temp number;
begin
select count(*) into v_temp from factuser_list a where a.oa_name = upper(p_username) and status'DROPPED';
if v_temp = 1 then
dbms_output.put_line('Error: 输入的新建实名用户名称：'||p_username||' 在factuser_list表中已经存在!');
return;
end if;

if upper(p_profile) = 'DEFAULT' then
dbms_output.put_line('Error: 实名用户不能使用默认的profile=>DEFAULT，请指定其他profile名称');
return;
end if;

select count(*) into v_temp from factuser_type where user_type=lower(p_user_type);
if v_temp=0 then
dbms_output.put_line('Error: 创建实名用户，p_user_type必须为表factuser_type中列出的类型');
return;
end if;
if p_user_level not in (0,1) then
dbms_output.put_line('Error: p_user_level必须是0(普通实名用户)或1(服务台实名用户管理员)');
return;
end if;

if p_deft_ts_qts -1 then
v_deft_ts_qts := to_char(p_deft_ts_qts) ||'m';
end if;

v_sql :='create user '||p_username||' identified by '||p_password||' password expire default tablespace '|| p_deft_ts ||
' temporary tablespace '|| p_temp_ts || ' quota ' || v_deft_ts_qts || ' on '|| p_deft_ts ||
' profile '|| p_profile ;
dbms_output.put_line(v_sql);

--log information init
select seq_factuser_operation_id.nextval into vt_log.action_id from dual;
select user,sysdate,'factuser_create_prc' into vt_log.action_operator,vt_log.action_date,vt_log.action_source from dual;
vt_log.action_username := upper(p_username);
vt_log.action_sql := v_sql;
open cur_session;
fetch cur_session into vt_log.action_sid,vt_log.action_serial#,vt_log.action_logon_time,vt_log.action_machine,vt_log.action_osuser,vt_log.action_program,vt_log.action_terminal,vt_log.action_module;
close cur_session;

--running creating user
begin
select count(*) into v_temp from factuser_list a where a.oa_name = upper(p_username) and status='DROPPED';
execute immediate v_sql;
if v_temp=0 then
insert into factuser_list(oa_name, cn_name, oa_email, user_type, user_level, dept, position, hr_no, create_date,end_date)
values (upper(p_username),p_cnname,p_oa_email,lower(p_user_type),p_user_level,p_dept,p_position,p_hr_no,sysdate,p_end_date);
else
update factuser_list
set cn_name = p_cnname,
oa_email = p_oa_email,
user_type = lower(p_user_type),
user_level = p_user_level,
dept = p_dept,
position = p_position,
hr_no = p_hr_no,
create_date= sysdate,
end_date = p_end_date,
status ='OPEN'
where oa_name = upper(p_username) and status='DROPPED';
end if;
vt_log.action_status := 'Success';
vt_log.action_message := 'User created successfully. ';
commit;
FACTUSER_OPERATION_log_prc(vt_log,0);

exception when others then
rollback;
vt_log.action_status := 'Failure';
vt_log.action_message := 'Error to create user:'||substr(sqlerrm,1,220);
--writing log
dbms_output.put_line(vt_log.action_status||' : '||vt_log.action_message);
FACTUSER_OPERATION_log_prc(vt_log,0);
return;
end;
end factuser_create_prc;
/

--------------------------
-- drop factuser procedure
-- for dbas
--------------------------

create or replace procedure factuser_drop_prc(
p_username varchar2,
p_cascade boolean default true )
is
cursor cur_session is select sid,serial#,logon_time,machine,osuser,program,terminal,module
FROM v$session
WHERE audsid = USERENV( 'sessionid' );
vt_log factuser_operation_log%rowtype;
v_temp number;
v_sql varchar2(250);
begin
if p_cascade then
v_sql :='drop user '||p_username ||' cascade';
else
v_sql :='drop user '||p_username ;
end if;

select count(*) into v_temp from dba_users a where a.username = upper(p_username);

--if the user exist
if v_temp > 0 then
select count(*) into v_temp from factuser_list
where oa_name = upper(p_username) and lower(user_type) in (select user_type from factuser_type);
--if the user is fact user
if v_temp > 0 then
--running drop user
begin
execute immediate v_sql;
update factuser_list
set status='DROPPED',
end_date=sysdate
where oa_name=upper(p_username);
vt_log.action_status := 'Success';
vt_log.action_message := 'User drop successfully. ';
commit;
exception when others then
rollback;
vt_log.action_status := 'Failure';
vt_log.action_message := 'Error to drop factuser :'||substr(sqlerrm,1,220);
end;
--if the user is not recorded in per_user_list
else
vt_log.action_status := 'Failure';
vt_log.action_message := 'Error to drop factuser: '||p_username||' 用户无法在factuser_list中找到!';
end if;
--if the user does not exist
else
vt_log.action_status := 'Failure';
vt_log.action_message := 'Error to drop factuser: '||p_username||' 用户在数据库中不存在!';
end if;

--writing log
--log information init
select seq_factuser_operation_id.nextval into vt_log.action_id from dual;
select user,sysdate,'factuser_drop_prc' into vt_log.action_operator,vt_log.action_date,vt_log.action_source from dual;
vt_log.action_username := upper(p_username);
vt_log.action_sql := v_sql;
open cur_session;
fetch cur_session into vt_log.action_sid,vt_log.action_serial#,vt_log.action_logon_time,vt_log.action_machine,vt_log.action_osuser,vt_log.action_program,vt_log.action_terminal,vt_log.action_module;
close cur_session;

dbms_output.put_line(vt_log.action_status||' : '||vt_log.action_message);
FACTUSER_OPERATION_log_prc(vt_log,0);

end factuser_drop_prc;
/

--/*Kill session不授给任何用户，现在库中已有的授权即将收回。
----------------------------
---- kill factuser's session
----
----------------------------
--create or replace procedure factuser_kill_prc (
-- p_username in varchar2,
-- p_sid IN number,
-- p_serial# IN number)
-- is
-- cursor cur_session is select sid,serial#,logon_time,machine,osuser,program,terminal,module
-- FROM v$session
-- WHERE audsid = USERENV( 'sessionid' );
-- vt_log factuser_operation_log%rowtype;
-- v_cnt number;
-- v_sql varchar2(1000);
-- begin
-- select count(*) into v_cnt
-- from v$session a,factuser_list b
-- where a.username is not null
-- and a.username=upper(p_username)
-- and a.sid=p_sid
-- and a.serial#=p_serial#
-- and a.username=upper(ltrim(rtrim(b.oa_name)))
-- and b.position'DBA';
--
-- if v_cnt = 1 then
--
-- vt_log.action_username :=upper(p_username);
--
-- v_sql := 'alter system kill session ' ||''''||p_sid||','||p_serial#||'''';
--
-- select seq_factuser_operation_id.nextval into vt_log.action_id from dual;
-- select user,sysdate,'factuser_kill_prc' into vt_log.action_operator,vt_log.action_date,vt_log.action_source from dual;
-- vt_log.action_sql := v_sql;
-- open cur_session;
-- fetch cur_session into vt_log.action_sid,vt_log.action_serial#,vt_log.action_logon_time,vt_log.action_machine,vt_log.action_osuser,vt_log.action_program,vt_log.action_terminal,vt_log.action_module;
-- close cur_session;
--
-- begin
-- execute immediate v_sql;
-- vt_log.action_status := 'Success';
-- vt_log.action_message := 'Session killed successfully. ';
-- exception when others then
-- rollback;
-- vt_log.action_status := 'Failure';
-- vt_log.action_message := 'Error to kill session :'||substr(sqlerrm,1,220);
-- end;
-- --writing log
-- dbms_output.put_line(vt_log.action_status||' : '||vt_log.action_message);
-- FACTUSER_OPERATION_log_prc(vt_log,0);
--
-- else
-- dbms_output.put_line('No session found or cannot kill DBA''s session.');
-- end if;
-- end factuser_kill_prc;
--/
--
--*/

--------------------------
-- lock factuser's account
-- for 运营服务台和离司人员自动清理程序调用，以及DBA离司转岗人员清理
--------------------------
create or replace procedure factuser_lock_prc(
p_username in varchar2,
p_is_dimission in boolean )
is
cursor cur_session is select sid,serial#,logon_time,machine,osuser,program,terminal,module
FROM v$session
WHERE audsid = USERENV( 'sessionid' );
vt_log factuser_operation_log%rowtype;
v_cnt number;
v_sql varchar2(1000);
begin
select count(*) into v_cnt
from factuser_list
where oa_name = upper(ltrim(rtrim(p_username))) and lower(user_type) in (select user_type from factuser_type);
if v_cnt = 1 then

v_sql := 'alter user '||p_username||' account lock';

select seq_factuser_operation_id.nextval into vt_log.action_id from dual;
select user,sysdate,'factuser_lock_prc' into vt_log.action_operator,vt_log.action_date,vt_log.action_source from dual;
vt_log.action_sql := v_sql;
vt_log.action_username := upper(p_username);
open cur_session;
fetch cur_session into vt_log.action_sid,vt_log.action_serial#,vt_log.action_logon_time,vt_log.action_machine,vt_log.action_osuser,vt_log.action_program,vt_log.action_terminal,vt_log.action_module;
close cur_session;

begin
execute immediate v_sql;
if p_is_dimission then
update factuser_list
set status='LOCKED-离司转岗'
where oa_name=upper(ltrim(rtrim(p_username)));
else
update factuser_list
set status='LOCKED'
where oa_name=upper(ltrim(rtrim(p_username)));
end if;
vt_log.action_status := 'Success';
vt_log.action_message := 'User account locked successfully. ';
commit;
exception when others then
rollback;
vt_log.action_status := 'Failure';
vt_log.action_message := 'Error to lock user account :'||substr(sqlerrm,1,220);
end;
--writing log
dbms_output.put_line(vt_log.action_status||' : '||vt_log.action_message);
FACTUSER_OPERATION_log_prc(vt_log,0);
else
dbms_output.put_line('Can not find input username in factuser_list. ');
end if;
end factuser_lock_prc;
/

--------------------------
-- initialize factuser's password
-- for 运营服务台
--------------------------
create or replace procedure factuser_pwd_prc(
p_username IN varchar,
p_pwd IN varchar)
is
cursor cur_session is select sid,serial#,logon_time,machine,osuser,program,terminal,module
FROM v$session
WHERE audsid = USERENV( 'sessionid' );
vt_log factuser_operation_log%rowtype;
v_pername_cnt number;
v_dbname_cnt number;
v_sql varchar2(1000);
begin
v_pername_cnt := 0;
v_dbname_cnt := 0;
select count(*) into v_dbname_cnt
from dba_users
where username = upper(ltrim(rtrim(p_username)));
if v_dbname_cnt = 0 then
RAISE_APPLICATION_ERROR(-20001,'This user is not exist');
else
select count(*) into v_pername_cnt
from factuser_list
where oa_name = upper(ltrim(rtrim(p_username))) and position'DBA'
and lower(user_type) in (select user_type from factuser_type);
if v_pername_cnt = 0 then
RAISE_APPLICATION_ERROR(-20002,'This user is not in factuser_list or this is DBA''s account.');
else

v_sql := 'alter user '||p_username||' identified by '||p_pwd||' password expire';

select seq_factuser_operation_id.nextval into vt_log.action_id from dual;
select user,sysdate,'factuser_pwd_prc' into vt_log.action_operator,vt_log.action_date,vt_log.action_source from dual;
vt_log.action_sql := v_sql;
vt_log.action_username := upper(p_username);
open cur_session;
fetch cur_session into vt_log.action_sid,vt_log.action_serial#,vt_log.action_logon_time,vt_log.action_machine,vt_log.action_osuser,vt_log.action_program,vt_log.action_terminal,vt_log.action_module;
close cur_session;

begin
execute immediate v_sql;
vt_log.action_status := 'Success';
vt_log.action_message := 'User password changed successfully. ';
exception when others then
rollback;
vt_log.action_status := 'Failure';
vt_log.action_message := 'Error to change user password :'||substr(sqlerrm,1,200);
end;
--writing log
dbms_output.put_line(vt_log.action_status||' : '||vt_log.action_message);
FACTUSER_OPERATION_log_prc(vt_log,0);

end if;
end if;
end factuser_pwd_prc;
/

--------------------------
-- unlock factuser's account
-- for DBA
-- LOCKED-离司转岗的帐号不能unlock
--------------------------
create or replace procedure factuser_unlock_prc(
p_username varchar2 ,
p_end_date date default null)
is
cursor cur_session is select sid,serial#,logon_time,machine,osuser,program,terminal,module
FROM v$session
WHERE audsid = USERENV( 'sessionid' );
vt_log factuser_operation_log%rowtype;

v_cnt number;
v_sql varchar2(100);

begin
select count(*) into v_cnt
from factuser_list
where oa_name = upper(ltrim(rtrim(p_username))) and status'LOCKED-离司转岗';
if v_cnt = 1 then

v_sql := 'alter user '||p_username||' account unlock';

select seq_factuser_operation_id.nextval into vt_log.action_id from dual;
select user,sysdate,'unlock_man_prc' into vt_log.action_operator,vt_log.action_date,vt_log.action_source from dual;
vt_log.action_sql := v_sql;
vt_log.action_username := p_username;
open cur_session;
fetch cur_session into vt_log.action_sid,vt_log.action_serial#,vt_log.action_logon_time,vt_log.action_machine,vt_log.action_osuser,vt_log.action_program,vt_log.action_terminal,vt_log.action_module;
close cur_session;

begin
execute immediate v_sql;
if p_end_date is not null then
update factuser_list set end_date=p_end_date
where oa_name=upper(ltrim(rtrim(p_username)));
end if;
update factuser_list set status='OPEN'
where oa_name=upper(ltrim(rtrim(p_username)));
vt_log.action_status := 'Success';
vt_log.action_message := 'User account unlocked successfully. ';
commit;
exception when others then
rollback;
vt_log.action_status := 'Failure';
vt_log.action_message := 'Error to unlock user account :'||substr(sqlerrm,1,220);
end;
--writing log
dbms_output.put_line(vt_log.action_status||' : '||vt_log.action_message);
FACTUSER_OPERATION_log_prc(vt_log,0);

else
RAISE_APPLICATION_ERROR(-20001,'This user is not exist');
dbms_output.put_line('Can not find input username from factuser_list or cannot unlock dimission account or cannot unlock DBA''s account. ');
end if;
end factuser_unlock_prc;
/

/********************************************

功能说明： dbmgr脚本
参数说明：
创建人： 
创建日期： 2007-4-20
修改人：
修改日期：
修改说明：

********************************************/

---DBA具有的权限：
--procedure:
create public synonym FACTUSER_OPERATION_log_prc for dbmgr.FACTUSER_OPERATION_log_prc ;
create public synonym factuser_create_prc for dbmgr.factuser_create_prc ;
create public synonym factuser_drop_prc for dbmgr.factuser_drop_prc ;
create public synonym factuser_lock_prc for dbmgr.factuser_lock_prc ;
create public synonym factuser_pwd_prc for dbmgr.factuser_pwd_prc ;
create public synonym factuser_unlock_prc for dbmgr.factuser_unlock_prc ;

-----table:
create public synonym factuser_list for dbmgr.factuser_list ;
create public synonym FACTUSER_OPERATION_LOG for dbmgr.FACTUSER_OPERATION_LOG ;

create user factusermgr identified by values '9D813CD10CD53183' default tablespace users TEMPORARY TABLESPACE temp;

audit policy PA_CONNECT_POLICY by factusermgr;

grant execute on dbmgr.factuser_pwd_prc to factusermgr;
grant execute on dbmgr.factuser_unlock_prc to factusermgr;
grant select on dbmgr.factuser_list to factusermgr;
grant create session to factusermgr;

/************************************************dbmgr end*******************************************/

/*****************************ovsee begin******************************************/
conn sys/&password@&sid as sysdba
create user ovsee identified by paic1234
default tablespace users
temporary tablespace temp
quota 20m on users;

grant SELECT_CATALOG_ROLE to ovsee;
grant ALTER SESSION to ovsee;
grant CREATE LIBRARY to ovsee;
grant CREATE PROCEDURE to ovsee;
grant CREATE SEQUENCE to ovsee;
grant CREATE SESSION to ovsee;
grant CREATE TABLE to ovsee;
grant CREATE VIEW to ovsee;
grant select on sys.dba_data_files to ovsee;
grant select on sys.dba_segments to ovsee;
grant select on sys.dba_free_space to ovsee;
grant select on sys.dba_extents to ovsee;
grant select on v_$INSTANCE to ovsee;
grant select on v_$session_wait to ovsee;
grant select on v_$lock to ovsee;
grant select on v_$session_event to ovsee;
grant select on v_$session to ovsee;
grant select on v_$filestat to ovsee;
grant select on v_$sysstat to ovsee;
grant select on v_$locked_object to ovsee;
grant select on v_$process to ovsee;
grant select on v_$rollname to ovsee;
grant select on v_$sesstat to ovsee;
grant select on v_$mystat to ovsee;
grant select on v_$STATNAME to ovsee;
grant select on v_$datafile to ovsee;

---授予ovsee使用toad工具查看awr报告的权限
grant execute on SYS.DBMS_WORKLOAD_REPOSITORY to ovsee;

conn ovsee/&password@&sid
/********************************************

功能说明： 创建表DG_UNRECOVERABLE_CHANGE 和GET_TBL_INFO_ERR
参数说明：
创建人：
创建日期： 2006-9-13
修改人：
修改日期：
修改说明：

********************************************/

create table DG_UNRECOVERABLE_CHANGE
(
FILE# NUMBER,
NAME VARCHAR2(513),
UNRECOVERABLE_CHANGE# NUMBER,
UNRECOVERABLE_TIME DATE
)
tablespace users;

/********************************************

功能说明：
参数说明：
创建人：
创建日期： 2006-9-13
修改人：
修改日期：
修改说明：

********************************************/

create or replace procedure dg_check_unrecoverable_change is
-- Find out any record in v$datafile has different unrecoverable change number from the saved records
Cursor c_datafile is
select df.file#, df.name, df.UNRECOVERABLE_CHANGE#, df.UNRECOVERABLE_TIME
from v$datafile df, dg_unrecoverable_change uc
where df.file# = uc.file# and
df.UNRECOVERABLE_CHANGE# uc.UNRECOVERABLE_CHANGE#;

D_START_DATE DATE;
Begin
SELECT SYSDATE INTO D_START_DATE FROM DUAL;

DBMS_OUTPUT.PUT_LINE('Start unrecoverable change checking '
|| to_char(D_START_DATE, 'DD/MM/YYYY HH24:MI:SS'));
-- For each entry with different unrecoverable change
FOR r_df in c_datafile LOOP
DBMS_OUTPUT.PUT_LINE('Unrecoverable Change : File#:' || r_df.FILE# || ' Datafile:' ||r_df.NAME ||
' Unrecoverable : '||r_df.UNRECOVERABLE_CHANGE# ||'-' || r_df.UNRECOVERABLE_TIME);
END LOOP;

-- Refresh the dg_unrecoverable_change table
delete from dg_unrecoverable_change;
insert into dg_unrecoverable_change select
FILE#, name, UNRECOVERABLE_CHANGE#, UNRECOVERABLE_TIME
from v$datafile;

COMMIT;

END;
/

/*************************************ovsee end *********************************************/

/*************************************dbmon begin *********************************************/

conn sys/&password@&sid as sysdba
/********************************************

功能说明： dbmon脚本,用于创建用户dbmon并授予相关权限
参数说明：
创建人：
创建日期：
修改人： 
修改日期： 2007-11-06
修改说明： 取消授角色connect，resource，改为直接授予系统权限

修改人： 樊志轩
修改日期： 2007-11-26
修改说明： 增加DML和登录审计，修改默认表空间

********************************************/

CREATE USER DBMON IDENTIFIED BY paic1234
default tablespace users
temporary tablespace temp
quota unlimited on users;

audit policy PA_DML_POLICY by dbmon;
audit policy PA_CONNECT_POLICY by dbmon;

--grant connect,resource to dbmon;
grant create session,alter session to dbmon;
grant create table to dbmon;
grant create sequence to dbmon;
grant create trigger to dbmon;
grant create procedure to dbmon;
grant create view to dbmon;

grant alter system to dbmon;
grant select_catalog_role to dbmon;
grant ADMINISTER DATABASE TRIGGER to dbmon;

grant select on dba_segments to dbmon;
grant select on dba_free_space to dbmon;
grant select on dba_data_files to dbmon;
grant select on dba_temp_files to dbmon;
grant select on v_$temp_space_header to dbmon;
grant select on dba_tablespaces to dbmon;

grant select on dbmgr.factuser_list to dbmon;
grant select on dbmgr.factuser_operation_log to dbmon;
grant select on x_$ksppi to dbmon;
grant select on x_$ksppcv to dbmon;

conn dbmon/&password@&sid
/********************************************

功能说明： dbmon脚本
参数说明：
创建人：
创建日期： 2006-9-13
修改人：
修改日期：
修改说明：

********************************************/

--停库原因定义
---DROP TABLE down_reason_def;
CREATE TABLE down_reason_def (reason_code VARCHAR2(2),
reason_desc VARCHAR2(300)
);
INSERT INTO down_reason_def values ('10','计划内停库(小型机维护)');
INSERT INTO down_reason_def values ('11','计划内停库(数据库维护)');
INSERT INTO down_reason_def values ('12','计划内停库(项目型冷备)');
INSERT INTO down_reason_def values ('20','故障停库(小型机故障)');
INSERT INTO down_reason_def values ('21','故障停库(数据库故障)');
INSERT INTO down_reason_def values ('22','故障停库(应用引起的故障)');
INSERT INTO down_reason_def values ('30','日常冷备停库');
INSERT INTO down_reason_def values ('0','其他');
COMMIT;

--建立数据库启动和关闭日志表：
---DROP TABLE db_uptime;
CREATE TABLE db_uptime (action_id NUMBER NOT NULL,
action_type VARCHAR2(20) NOT NULL,
action_date DATE ,
action_flag VARCHAR2(1) NOT NULL,
reason_code VARCHAR2(2) ,
reason_detail VARCHAR2(4000) ,
prev_uptime DATE ,
next_uptime DATE ,
fill_text VARCHAR2(200)
);
alter table db_uptime add constraint pk_db_uptime primary key (action_id,action_type);

COMMENT ON COLUMN db_uptime.action_id IS 'Database启动和关闭记录对标识';
COMMENT ON COLUMN db_uptime.action_type IS 'STARTUP 或 SHUTDOWN';
COMMENT ON COLUMN db_uptime.action_date IS '启动或关闭的具体时间';
COMMENT ON COLUMN db_uptime.action_flag IS '正常(N)或异常(A)启动或关闭';
COMMENT ON COLUMN db_uptime.reason_code IS '启动或关闭的原因代码';
COMMENT ON COLUMN db_uptime.reason_detail IS '启动或关闭的具体详细原因';
COMMENT ON COLUMN db_uptime.prev_uptime IS '异常停库前的启动时间';
COMMENT ON COLUMN db_uptime.next_uptime IS '异常停库后的启动时间';
COMMENT ON COLUMN db_uptime.fill_text IS '需要DBA执行的脚本';

--建立报表标识sequence：
---DROP SEQUENCE SEQ_STAT_ID ;
CREATE SEQUENCE SEQ_STAT_ID MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1;

--建立每月统计报表临时表：
---DROP TABLE db_rep_stat;
CREATE TABLE db_rep_stat (db_sid VARCHAR2(30),
report_id NUMBER ,
report_time DATE ,
report_month VARCHAR2(10),
down_type VARCHAR2(30),
reason_code VARCHAR2(2),
down_reason VARCHAR2(30) ,
down_detail VARCHAR2(4000) ,
down_time VARCHAR2(30),
up_time VARCHAR2(30),
duration NUMBER ,
duration_unit VARCHAR2(10)
);
COMMENT ON COLUMN db_rep_stat.db_sid IS '当前Database名称';
COMMENT ON COLUMN db_rep_stat.report_id IS '统计报表执行标识';
COMMENT ON COLUMN db_rep_stat.report_time IS '统计报表执行时间';
COMMENT ON COLUMN db_rep_stat.report_month IS '统计报表月份，如200309';
COMMENT ON COLUMN db_rep_stat.down_type IS '关库正常(N)还是异常(A)';
COMMENT ON COLUMN db_rep_stat.reason_code IS '关库的原因代码';
COMMENT ON COLUMN db_rep_stat.down_reason IS '关库的原因分类';
COMMENT ON COLUMN db_rep_stat.down_detail IS '关库的具体详细原因';
COMMENT ON COLUMN db_rep_stat.down_time IS '关库的具体时间';
COMMENT ON COLUMN db_rep_stat.up_time IS '关库后的启动时间';
COMMENT ON COLUMN db_rep_stat.duration IS '关库的持续时间';
COMMENT ON COLUMN db_rep_stat.duration_unit IS '关库的持续时间的单位';
COMMENT ON COLUMN db_rep_stat.duration_unit IS '关库的持续时间的单位';

---DROP SEQUENCE SEQ_ERROR_ID;
CREATE SEQUENCE SEQ_ERROR_ID MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1;

--建立错误日志表：
---DROP TABLE ERROR_LOG;
CREATE TABLE ERROR_LOG (error_id NUMBER ,
error_object VARCHAR2(61) ,
error_line NUMBER ,
error_code INTEGER ,
error_text VARCHAR2(4000),
created_on DATE ,
created_by VARCHAR2(100));
COMMENT ON COLUMN ERROR_LOG.error_id IS '报错的标识';
COMMENT ON COLUMN ERROR_LOG.error_object IS '报错的对象名称';
COMMENT ON COLUMN ERROR_LOG.error_line IS '报错的对象代码行号';
COMMENT ON COLUMN ERROR_LOG.error_code IS '报错的错误代码';
COMMENT ON COLUMN ERROR_LOG.error_text IS '报错的错误信息';
COMMENT ON COLUMN ERROR_LOG.created_on IS '报错的时间';
COMMENT ON COLUMN ERROR_LOG.created_by IS '报错的用户名';

--初始化当前的startup 时间记录：
declare
v_time date;
begin
select STARTUP_TIME into v_time from v$instance;
insert into db_uptime (action_id,action_type,action_date,action_flag) values(1 ,'STARTUP',v_time,'N');
commit;
end;
/

select * from db_uptime;

/********************************************

功能说明： dbmon脚本,用于创建trigger
参数说明：
创建人：
创建日期： 2006-9-13
修改人：
修改日期：
修改说明：

********************************************/

--启动的trigger
CREATE OR REPLACE TRIGGER tr_db_startup AFTER STARTUP ON DATABASE
DECLARE
v_action_id NUMBER := -1 ;
v_temp NUMBER ;
v_error_id error_log.error_id%TYPE;
v_error_message varchar2(2000);
BEGIN
begin
--获得最近一次启动和关闭的记录对标识
select max(action_id) into v_action_id from db_uptime;
if (v_action_id is null) or (v_action_id = -1) then --第一次记录
v_action_id := 0;
end if;
exception
when no_data_found then
v_action_id := 0;
when others then
return;
end;

--查看数据库最近一次是否正常关闭：
if v_action_id > 0 then
begin
select 1 into v_temp from db_uptime where action_id = v_action_id and action_type = 'SHUTDOWN';
exception
when no_data_found then --异常关闭，补上这条shutdown 的记录，但时间为空
insert into db_uptime (action_id,action_type,action_date,action_flag) values(v_action_id,'SHUTDOWN',NULL,'A');
when others then
return;
end;
end if;

--记录当前的正常启动时间：
insert into db_uptime (action_id,action_type,action_date,action_flag) values(v_action_id + 1 ,'STARTUP',sysdate,'N');
EXCEPTION
WHEN OTHERS THEN
v_error_message := SQLERRM;
INSERT INTO ERROR_LOG VALUES(SEQ_ERROR_ID.NEXTVAL,'TR_DB_STARTUP',0,0, v_error_message,SYSDATE,USER);
END;
/

--正常关闭的trigger
CREATE OR REPLACE TRIGGER tr_db_shutdown BEFORE SHUTDOWN ON DATABASE
DECLARE
v_action_id NUMBER := -1 ;
v_error_id error_log.error_id%TYPE;
v_error_message varchar2(2000);
BEGIN
--获得最近一次启动的记录对标识，以便进行配对
select max(action_id) into v_action_id from db_uptime where action_type = 'STARTUP';
if v_action_id > 0 then
insert into db_uptime (action_id,action_type,action_date,action_flag) values(v_action_id,'SHUTDOWN',sysdate,'N');
end if;
EXCEPTION
WHEN OTHERS THEN
v_error_message := SQLERRM;
INSERT INTO ERROR_LOG VALUES(SEQ_ERROR_ID.NEXTVAL,'TR_DB_SHUTDOWN',0,0, v_error_message,SYSDATE,USER);
END;
/

/********************************************

功能说明： dbmon脚本,用于创建包PKG_DB_STAT
参数说明：
创建人：
创建日期：
修改人：
修改日期：
修改说明：

********************************************/

CREATE OR REPLACE PACKAGE PKG_DB_STAT IS

V_DB_NAME db_rep_stat.db_sid%TYPE;
V_ERROR_MESSAGE VARCHAR2(512);

FUNCTION CHECK_ABNORMAL_DOWN RETURN NUMBER;

FUNCTION CHECK_DOWN_REASON(P_YEAR_MONTH VARCHAR2 DEFAULT '300001') RETURN NUMBER;

PROCEDURE FILL_ABNORMAL_TIME (P_ACTION_ID NUMBER,P_ABNORMAL_TIME DATE,P_REASON_CODE VARCHAR2,P_REASON_DETAIL VARCHAR2);

PROCEDURE FILL_DOWN_REASON (P_ACTION_ID NUMBER,P_REASON_CODE VARCHAR2,P_REASON_DETAIL VARCHAR2);

PROCEDURE FILL_COLDDOWN_REASON ;

PROCEDURE GET_MONTH_REPORT(P_YEAR_MONTH VARCHAR2);

PROCEDURE SAVE_ERROR(p_error_id NUMBER,
p_error_object VARCHAR2,
p_error_line NUMBER,
p_error_code INTEGER,
p_error_text VARCHAR2,
p_created_on DATE,
p_created_by VARCHAR2);

END PKG_DB_STAT;
/
CREATE OR REPLACE PACKAGE BODY PKG_DB_STAT IS

FUNCTION CHECK_ABNORMAL_DOWN RETURN NUMBER IS
CURSOR cur_abnormal IS SELECT action_id
FROM db_uptime
WHERE action_flag='A'
AND action_date IS NULL
AND ACTION_TYPE='SHUTDOWN';
v_prev_uptime db_uptime.prev_uptime%TYPE;
v_next_uptime db_uptime.next_uptime%TYPE;
v_fill_text db_uptime.fill_text%TYPE;
v_action_id db_uptime.action_id%TYPE;
v_error_id error_log.error_id%TYPE;
BEGIN
OPEN cur_abnormal;
FETCH cur_abnormal INTO v_action_id;
IF cur_abnormal%NOTFOUND THEN -- NO abnormal SHUTDOWN RECORD
CLOSE cur_abnormal;
RETURN 0;
END IF;
LOOP
EXIT WHEN cur_abnormal%NOTFOUND;
SELECT action_date INTO v_prev_uptime FROM db_uptime WHERE action_id = v_action_id AND action_type = 'STARTUP';
SELECT action_date INTO v_next_uptime FROM db_uptime WHERE (action_id = v_action_id + 1) AND action_type = 'STARTUP';
v_fill_text := 'EXEC PKG_DB_STAT.FILL_ABNORMAL_TIME('|| v_action_id || ',TO_DATE(

,''YYYYMMDD:HH24:MI:SS''),

,

));';
UPDATE db_uptime SET prev_uptime = v_prev_uptime,
next_uptime = v_next_uptime,
fill_text = v_fill_text
WHERE action_id = v_action_id
AND action_type = 'SHUTDOWN'
AND action_flag='A';
FETCH cur_abnormal INTO v_action_id;
END LOOP;
CLOSE cur_abnormal;
COMMIT;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
SELECT SEQ_ERROR_ID.NEXTVAL INTO v_error_id FROM DUAL;
SAVE_ERROR(v_error_id,'PKG_DB_STAT.CHECK_ABNORMAL_DOWN',0,SQLCODE, SQLERRM,SYSDATE,USER);
V_ERROR_MESSAGE := 'Error : please select the ERROR_ID: ' || v_error_id || ' for detail message!';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
RETURN v_error_id;
END CHECK_ABNORMAL_DOWN;

/**********************/
FUNCTION CHECK_DOWN_REASON(P_YEAR_MONTH VARCHAR2 DEFAULT '300001') RETURN NUMBER IS
CURSOR cur_reason(cv_year_month VARCHAR2) IS SELECT action_id
FROM db_uptime
WHERE reason_code IS NULL
AND action_date >=TO_DATE(cv_year_month,'YYYYMM') --异常停库的原因特别检查
AND reason_detail IS NULL
AND ACTION_TYPE='SHUTDOWN';
v_fill_text db_uptime.fill_text%TYPE;
v_action_id db_uptime.action_id%TYPE;
v_error_id error_log.error_id%TYPE;
v_year VARCHAR2(6);
v_month VARCHAR2(6);
v_year_month VARCHAR2(20);
BEGIN
IF P_YEAR_MONTH = '300001' THEN
select to_char(sysdate,'YYYYMMDD HH24:MI') into v_year_month from dual;
v_year_month := substr(v_year_month,1,6);
ELSE
v_year :=SUBSTR(p_year_month,1,4);
v_month :=SUBSTR(p_year_month,5,6);
IF (LENGTH(p_year_month) 6) OR (v_year IS NULL) OR (v_month IS NULL)
OR (TO_NUMBER(v_year) < 2003 )
OR (v_month NOT IN ('00','01','02','03','04','05','06','07','08','09','10','11','12')) THEN
V_ERROR_MESSAGE := 'Error : Invalid year and month : ' || p_year_month || '!';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
RETURN 1;
ELSE
v_year_month := P_YEAR_MONTH;
END IF;
END IF;
OPEN cur_reason(v_year_month);
FETCH cur_reason INTO v_action_id;
IF cur_reason%NOTFOUND THEN -- NO abnormal SHUTDOWN RECORD
CLOSE cur_reason;
RETURN 0;
END IF;
LOOP
EXIT WHEN cur_reason%NOTFOUND;
v_fill_text := 'EXEC PKG_DB_STAT.FILL_DOWN_REASON('|| v_action_id || ',

,

);';
UPDATE db_uptime SET fill_text = v_fill_text
WHERE action_id = v_action_id
AND action_type = 'SHUTDOWN';
FETCH cur_reason INTO v_action_id;
END LOOP;
CLOSE cur_reason;
COMMIT;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
SELECT SEQ_ERROR_ID.NEXTVAL INTO v_error_id FROM DUAL;
SAVE_ERROR(v_error_id,'PKG_DB_STAT.CHECK_DOWN_REASON',0,SQLCODE, SQLERRM,SYSDATE,USER);
V_ERROR_MESSAGE := 'Error : please select the ERROR_ID: ' || v_error_id || ' for detail message!';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
RETURN v_error_id;
END CHECK_DOWN_REASON;

PROCEDURE FILL_ABNORMAL_TIME (P_ACTION_ID NUMBER,P_ABNORMAL_TIME DATE,P_REASON_CODE VARCHAR2,P_REASON_DETAIL VARCHAR2) IS
CURSOR cur_fill(cv_action_id NUMBER) IS SELECT 1
FROM db_uptime
WHERE action_id = cv_action_id
AND action_type='SHUTDOWN'
AND action_flag='A' ;
v_prev_uptime db_uptime.prev_uptime%TYPE;
v_next_uptime db_uptime.next_uptime%TYPE;
v_temp NUMBER;
v_error_id error_log.error_id%TYPE;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
OPEN cur_fill(P_ACTION_ID);
FETCH cur_fill INTO v_temp;
IF cur_fill%NOTFOUND THEN -- NO abnormal SHUTDOWN RECORD
V_ERROR_MESSAGE := 'Error : Can not find database shutdown abnormal record of action id : ' || P_ACTION_ID || ' !';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
ELSE
BEGIN
select 1 into v_temp from down_reason_def where reason_code = P_REASON_CODE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK ;
V_ERROR_MESSAGE := 'Error : Undefind reason code: ' || P_REASON_CODE || ' !';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
RETURN;
END;

--判断人工补录的异常停库时间是否在前后两次 STARTUP 时间之间
SELECT action_date INTO v_prev_uptime FROM db_uptime WHERE action_id = p_action_id AND action_type = 'STARTUP';
SELECT action_date INTO v_next_uptime FROM db_uptime WHERE (action_id = p_action_id + 1) AND action_type = 'STARTUP';
IF (P_ABNORMAL_TIME = v_next_uptime) THEN
V_ERROR_MESSAGE := 'Error : Invalid time of database shutdown abnormal ! It should be between ' || to_char(v_prev_uptime,'YYYY-MM-DD HH24:MI:SS') || ' and ' || to_char(v_next_uptime,'YYYY-MM-DD HH24:MI:SS');
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
ELSE
UPDATE db_uptime SET action_date = P_ABNORMAL_TIME,
reason_code = P_REASON_CODE,
reason_detail = P_REASON_DETAIL,
fill_text = NULL
WHERE action_id = P_ACTION_ID AND action_type='SHUTDOWN';
COMMIT;
V_ERROR_MESSAGE := 'Success : The time and reason of database shutdown abnormal have been filled !';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
END IF;
END IF;
CLOSE cur_fill;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
SELECT SEQ_ERROR_ID.NEXTVAL INTO v_error_id FROM DUAL;
SAVE_ERROR(v_error_id,'PKG_DB_STAT.FILL_ABNORMAL_TIME',0,SQLCODE, SQLERRM,SYSDATE,USER);
V_ERROR_MESSAGE := 'Error : please select the ERROR_ID: ' || v_error_id || ' for detail message!';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
RETURN;
END FILL_ABNORMAL_TIME;

PROCEDURE FILL_DOWN_REASON (P_ACTION_ID NUMBER,P_REASON_CODE VARCHAR2,P_REASON_DETAIL VARCHAR2) IS
CURSOR cur_fill(cv_action_id NUMBER) IS SELECT 1
FROM db_uptime
WHERE action_id = cv_action_id
AND action_type='SHUTDOWN';
v_temp NUMBER;
v_error_id error_log.error_id%TYPE;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
OPEN cur_fill(P_ACTION_ID);
FETCH cur_fill INTO v_temp;
IF cur_fill%NOTFOUND THEN
V_ERROR_MESSAGE := 'Error : Can not find database shutdown record of action id : ' || P_ACTION_ID || ' !';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
RETURN;
ELSE
BEGIN
select 1 into v_temp from down_reason_def where reason_code = P_REASON_CODE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK ;
V_ERROR_MESSAGE := 'Error : Undefind reason code: ' || P_REASON_CODE || ' !';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
RETURN;
END;
UPDATE db_uptime SET reason_code = P_REASON_CODE,
reason_detail = P_REASON_DETAIL,
fill_text = NULL
WHERE action_id = P_ACTION_ID AND action_type='SHUTDOWN';
COMMIT;
V_ERROR_MESSAGE := 'Success : The reason of database shutdown have been filled !';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
END IF;
CLOSE cur_fill;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
SELECT SEQ_ERROR_ID.NEXTVAL INTO v_error_id FROM DUAL;
SAVE_ERROR(v_error_id,'PKG_DB_STAT.FILL_DOWN_REASON',0,SQLCODE, SQLERRM,SYSDATE,USER);
V_ERROR_MESSAGE := 'Error : please select the ERROR_ID: ' || v_error_id || ' for detail message!';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
RETURN;
END FILL_DOWN_REASON;

PROCEDURE FILL_COLDDOWN_REASON IS
CURSOR cur_coldfill(cv_action_id NUMBER) IS SELECT action_date
FROM db_uptime
WHERE action_id = cv_action_id
AND action_type='SHUTDOWN';

v_sysdate DATE;
v_action_id db_uptime.action_id%TYPE;
v_action_date db_uptime.action_date%TYPE;
v_reason_code db_uptime.reason_code%TYPE := '30';
v_reason_detail db_uptime.reason_detail%TYPE := '日常冷备份停库';
v_error_id error_log.error_id%TYPE;
BEGIN
SELECT SYSDATE INTO v_sysdate FROM DUAL;
SELECT MAX(action_id) INTO v_action_id FROM db_uptime;
OPEN cur_coldfill(v_action_id - 1);
FETCH cur_coldfill INTO v_action_date;
IF cur_coldfill%NOTFOUND THEN
V_ERROR_MESSAGE := 'Error : Can not find database shutdown record before action id : ' || v_action_id || ' !';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
RETURN;
ELSE
UPDATE db_uptime SET reason_code = v_reason_code,
reason_detail = v_reason_detail,
fill_text = NULL
WHERE action_id = (v_action_id - 1)
AND action_type='SHUTDOWN';
COMMIT;
V_ERROR_MESSAGE := 'Success : The reason of database coldbackup shutdown have been filled !';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
END IF;
CLOSE cur_coldfill;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
SELECT SEQ_ERROR_ID.NEXTVAL INTO v_error_id FROM DUAL;
SAVE_ERROR(v_error_id,'PKG_DB_STAT.FILL_COLDDOWN_REASON',0,SQLCODE, SQLERRM,SYSDATE,USER);
V_ERROR_MESSAGE := 'Error : please select the ERROR_ID: ' || v_error_id || ' for detail message!';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
RETURN ;
END FILL_COLDDOWN_REASON;

PROCEDURE GET_MONTH_REPORT(P_YEAR_MONTH VARCHAR2) IS
CURSOR cur_shutdown(cv_rpt_start_date DATE,cv_rpt_end_date DATE)
IS SELECT action_id,action_type,action_date,action_flag,reason_code,reason_detail,prev_uptime,next_uptime
FROM db_uptime
WHERE action_type = 'SHUTDOWN'
AND action_date >= cv_rpt_start_date
AND action_date cv_rpt_start_date
AND action_date IS NULL
AND action_FLAG = 'A'
ORDER BY 1 ;

CURSOR cur_startup(cv_action_id NUMBER)
IS SELECT action_date
FROM db_uptime
WHERE action_id = cv_action_id
AND action_type = 'STARTUP';

v_rpt_start_date DATE;
v_rpt_end_date DATE;
v_year VARCHAR2(4);
v_month VARCHAR2(2);
v_action_id db_uptime.action_id%type;
v_action_type db_uptime.action_type%type;
v_action_date db_uptime.action_date%type;
v_action_flag db_uptime.action_flag%type;
v_reason_code db_uptime.reason_code%type;
v_temp NUMBER;
v_prev_uptime db_uptime.prev_uptime%TYPE;
v_next_uptime db_uptime.next_uptime%TYPE;
-- REPORT
v_stat_id db_rep_stat.report_id%TYPE;
v_down_type db_rep_stat.down_type%TYPE;
v_down_reason db_rep_stat.down_reason%TYPE;
v_down_detail db_rep_stat.down_detail%TYPE;
v_down_time db_rep_stat.down_time%TYPE;
v_up_time db_rep_stat.up_time%TYPE;
v_duration db_rep_stat.duration%TYPE;
v_duration_unit db_rep_stat.duration_unit%TYPE;

v_prev_report_id db_rep_stat.report_id%TYPE := 0 ;
v_error_id error_log.error_id%TYPE;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
v_year :=SUBSTR(p_year_month,1,4);
v_month :=SUBSTR(p_year_month,5,6);
IF (LENGTH(p_year_month) 6) OR (v_year IS NULL) OR (v_month IS NULL)
OR (TO_NUMBER(v_year) < 2003 )
OR (v_month NOT IN ('00','01','02','03','04','05','06','07','08','09','10','11','12')) THEN
V_ERROR_MESSAGE := 'Error : Invalid year and month : ' || p_year_month || '!';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
RETURN ;
END IF;
IF v_month = '00' THEN -- 年度统计
v_rpt_start_date := TO_DATE(v_year || '0101 00:00:00','YYYYMMDD HH24:MI:SS');
v_rpt_end_date := TO_DATE(v_year || '1231 23:59:59','YYYYMMDD HH24:MI:SS');
ELSE -- 月度统计的开始日期都是 1
v_rpt_start_date := TO_DATE(P_YEAR_MONTH || '01 00:00:00','YYYYMMDD HH24:MI:SS');
END IF;
IF v_month = '02' THEN --2 月
IF (MOD(v_year,400)=0)
OR (MOD(v_year,4)=0 AND MOD(v_year,100) 0) THEN -- 闰年 2 月
v_rpt_end_date := TO_DATE(P_YEAR_MONTH || '29 23:59:59','YYYYMMDD HH24:MI:SS');
ELSE
v_rpt_end_date := TO_DATE(P_YEAR_MONTH || '28 23:59:59','YYYYMMDD HH24:MI:SS');
END IF;
END IF;
IF v_month IN ('01','03','05','07','08','10','12') THEN -- 大月结束日
v_rpt_end_date := TO_DATE(P_YEAR_MONTH || '31 23:59:59','YYYYMMDD HH24:MI:SS');
END IF;
IF v_month IN ('04','06','09','11') THEN -- 小月结束日
v_rpt_end_date := TO_DATE(P_YEAR_MONTH || '30 23:59:59','YYYYMMDD HH24:MI:SS');
END IF;

--检查是否存在还没有补填异常日期的异常停库记录
v_temp := CHECK_ABNORMAL_DOWN;
SELECT SEQ_STAT_ID.NEXTVAL INTO v_stat_id FROM DUAL;
SELECT SUBSTR(TRIM(global_name),1,DECODE(INSTR(TRIM(global_name),'.'),0,LENGTH(TRIM(global_name)),INSTR(TRIM(global_name),'.')-1))
INTO v_db_name
FROM global_name;

OPEN cur_shutdown(v_rpt_start_date,v_rpt_end_date);
FETCH cur_shutdown INTO v_action_id,v_action_type,v_action_date,v_action_flag,v_reason_code,v_down_detail,v_prev_uptime,v_next_uptime;
IF cur_shutdown%NOTFOUND THEN
V_ERROR_MESSAGE := 'Error : Can not find shutdown record of '||trim(v_db_name)||',please check the DB_REP_STAT table!';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
RETURN;
END IF;
LOOP
IF v_action_flag = 'N' THEN
v_down_type := '正常关闭';
END IF;
IF v_action_flag = 'A' THEN
v_down_type := '异常关闭';
END IF;
IF v_action_date IS NULL THEN -- 异常关闭记录，时间未知，就填入前后STARTUP 时间做参考
v_down_time := 'UNKNOWN'; --v_prev_uptime
v_up_time := TO_CHAR(v_next_uptime,'YYYY-MM-DD HH24:MI:SS');
v_duration := -1;
v_duration_unit := NULL;
ELSE -- 正常关闭记录，时间已知
v_down_time := TO_CHAR(v_action_date,'YYYY-MM-DD HH24:MI:SS');
OPEN cur_startup(v_action_id + 1);
FETCH cur_startup INTO v_next_uptime;
CLOSE cur_startup;
v_up_time := TO_CHAR(v_next_uptime,'YYYY-MM-DD HH24:MI:SS');
v_duration := ROUND(86400*(v_next_uptime - v_action_date));
v_duration := trunc(v_duration / 60, 3) ;
v_duration_unit := '分钟';
/*
IF (v_duration >=0 AND v_duration < 60 ) THEN
v_duration_unit := '秒';
ELSIF (v_duration >=60 AND v_duration < 3600 ) THEN
v_duration := trunc(v_duration / 60, 3) ;
v_duration_unit := '分钟';
ELSIF (v_duration >=3600 AND v_duration < 86400 ) THEN
v_duration := trunc(v_duration / 3660,4) ;
v_duration_unit := '小时';
ELSIF (v_duration >=86400 ) THEN
v_duration := trunc(v_duration / 86400 ,5) ;
v_duration_unit := '天';
END IF;
*/
END IF;
BEGIN
v_down_reason := NULL;
SELECT reason_desc INTO v_down_reason FROM down_reason_def WHERE reason_code = v_reason_code;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
INSERT INTO db_rep_stat (report_id,
report_time,
db_sid,
report_month,
down_type,
reason_code,
down_reason,
down_detail,
down_time,
up_time,
duration,
duration_unit)
VALUES (v_stat_id,
SYSDATE,
v_db_name,
P_YEAR_MONTH,
v_down_type,
v_reason_code,
v_down_reason,
v_down_detail,
v_down_time,
v_up_time,
v_duration,
v_duration_unit);
FETCH cur_shutdown INTO v_action_id,v_action_type,v_action_date,v_action_flag,v_reason_code,v_down_detail,v_prev_uptime,v_next_uptime;
EXIT WHEN cur_shutdown%NOTFOUND;
END LOOP;
CLOSE cur_shutdown;
COMMIT;
V_ERROR_MESSAGE := 'Success : The report(ID = ' ||v_stat_id|| ') of '||trim(v_db_name)||' have been generated !';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
SELECT SEQ_ERROR_ID.NEXTVAL INTO v_error_id FROM DUAL;
SAVE_ERROR(v_error_id,'PKG_DB_STAT.GET_MONTH_REPORT',0,SQLCODE, SQLERRM,SYSDATE,USER);
V_ERROR_MESSAGE := 'Error : please select the ERROR_ID: ' || v_error_id || ' for detail message!';
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
RETURN ;
END GET_MONTH_REPORT;

PROCEDURE SAVE_ERROR(p_error_id NUMBER,
p_error_object VARCHAR2,
p_error_line NUMBER,
p_error_code INTEGER,
p_error_text VARCHAR2,
p_created_on DATE,
p_created_by VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ERROR_LOG (error_id,error_object,error_line,error_code,error_text,created_on,created_by)
VALUES (p_error_id,p_error_object,p_error_line,p_error_code,p_error_text,p_created_on,p_created_by);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END SAVE_ERROR;

END PKG_DB_STAT;
/

create or replace view dbmon.v_ts_stat as
select a.tablespace_name,
a.allocated_sum,
a.files_count,
nvl(a.free_sum, 0) free_sum,
nvl(d.free_max_ext, 0) free_max_ext,
nvl(a.free_count, 0) free_count,
round(nvl(a.free_sum, 0) / a.allocated_sum * 100, 4) free_percent,
nvl(c.segment_sum, 0) segment_sum,
nvl(c.max_segment_next_ext, 0) max_segment_next_ext,
nvl(c.segment_count, 0) segment_count
from (select tablespace_name,
sum(allocated_sum) allocated_sum,
sum(files_count) files_count,
sum(free_sum) free_sum,
sum(free_count) free_count
from (select tablespace_name,
(case AUTOEXTENSIBLE
when 'YES' then
sum(file_max_size)
else
sum(file_size)
end) as allocated_sum,
count(*) files_count,
(case AUTOEXTENSIBLE
when 'YES' then
sum(file_max_size - file_size + free_sum_tmp)
else
sum(free_sum_tmp)
end) as free_sum,
sum(free_count) free_count
from (select ddf.tablespace_name,
sum(nvl(dfs.bytes,0)) free_sum_tmp,
-- max(bytes) free_max_ext,
count(*) free_count,
--ddf.file_id,
ddf.bytes file_size,
ddf.maxbytes file_max_size,
ddf.autoextensible
from dba_free_space dfs, dba_data_files ddf
where dfs.file_id(+) = ddf.file_id
group by ddf.tablespace_name,
ddf.file_id,
ddf.autoextensible,
ddf.bytes,
ddf.maxbytes)
group by tablespace_name, autoextensible)
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) segment_sum,
nvl(max(next_extent), 0) max_segment_next_ext,
count(*) segment_count
from dba_segments
group by tablespace_name) c,
(select tablespace_name,
case
when ext_cnt < 5 then
(ext_cnt - 5) * 1024 * 1024
else
(select max(bytes)
from dba_free_space
where tablespace_name = b.tablespace_name)
end as free_max_ext
from (select tablespace_name,
sum(floor(bytes /
(select max(next_extent)
from dba_segments
where tablespace_name = a.tablespace_name))) as ext_cnt
from dba_free_space a
group by tablespace_name) b) d
where a.tablespace_name = c.tablespace_name(+)
and a.tablespace_name = d.tablespace_name(+)
union all
select a.tablespace_name,
a.allocated_sum,
a.files_count,
nvl(b.free_sum, 0) free_sum,
0 free_max_ext,
0 free_count,
round(nvl(b.free_sum, 0) / a.allocated_sum * 100, 4) free_percent,
0 segment_sum,
0 max_segment_next_ext,
0 segment_count
from (select tablespace_name,
sum(bytes) allocated_sum,
count(*) files_count
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_free) free_sum
from v$temp_space_header
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+);

/*************************** dbmon end **************************************/

/*************************** dbqua begin **************************************/
conn sys/&password@&sid as sysdba

/********************************************
功能说明： 创建用户
参数说明：
创建人： 
创建日期： 2006-3-30

修改人：
修改日期：
修改说明：
********************************************/

create user dbqintf
identified by values '96F1666AEDAB76D1'
default tablespace users
temporary tablespace temp
quota unlimited on users;

audit policy PA_CONNECT_POLICY by dbqintf;

grant create session to dbqintf;
grant SELECT_CATALOG_ROLE to dbqintf;

--grant select,delete on dbqua.DBQ$SNAPID_TIME to dbqintf;
--grant select,delete on dbqua.DBQ$TS_BASE to dbqintf;
--grant select,delete on dbqua.DBQ$TS_INFO to dbqintf;
--grant select,delete on dbqua.DBQ$MEGATAB_INFO to dbqintf;
--grant select,delete on dbqua.DBQ$ARCH_INFO to dbqintf;
--grant select,delete on dbq$snapid_item to dbqintf;
--grant select,delete on DBQ$ARCHIVE_DETAIL to dbqintf;

/********************************************
功能说明： 创建dbqua用户
参数说明：
创建人： 甘露
创建日期： 2006-3-28

修改人：
修改日期：
修改说明：
********************************************/

set lines 100
set pages 0
set feedback off

create user dbqua
identified by paic1234
default tablespace users
temporary tablespace temp
quota unlimited on users;

audit policy PA_CONNECT_POLICY by dbqua;

grant connect ,resource to dbqua;
grant create procedure to dbqua;
grant create public synonym to dbqua;
grant drop public synonym to dbqua;
grant select any table to dbqua;
grant create job to dbqua /* add for 19c */ ;

spool off

--以下sql请用sys用户执行
grant select any dictionary to dbqua;

spool prd_database_initial_02.log

conn dbqua/&password@&sid
/****************************************************************************************/
/* */
/* 名称：DB质量评测项目--创建序列号脚本 */
/* 用途: 创建序列号，用于标识dbq项目唯一数据记录的ID */
/* 注意： */
/* */
/* 修改历史: */
/* Ver Date Author Description */
/* --------- ---------- --------------- -------------------------------------------*/
/* 1.0 2005-07-24 肖大韧 1. 创建 */
/* 1.1 2006-03-28 甘露 1.添加 snapid_item表 */
/* */
/* */
/****************************************************************************************/

--dbq$snapshot_id用于生产dbq中的ID
create sequence dbq$snapshot_id start with 1 increment by 1 nomaxvalue nocache;
create public synonym dbq$snapshot_id for dbq$snapshot_id;
--保存id和对应的生成时间
create table dbq$snapid_time (
snap_id number ,
created date ,
constraint pk_snpidtim_id primary key (snap_id))
;
comment on table dbq$snapid_time is 'id及其生成时间对照表';
comment on column dbq$snapid_time.snap_id is 'id号';
comment on column dbq$snapid_time.created is 'id号生成时间';

create public synonym dbq$snapid_time for dbq$snapid_time;
grant select,delete on dbq$snapid_time to dbqintf;

--保存id和对应的操作
create table dbq$snapid_item (
snap_id number ,
snap_item varchar2(20) ,
constraint pk_snpiditm_id_item primary key (snap_id,snap_item))
;
comment on table dbq$snapid_item is 'id及操作对照表';
comment on column dbq$snapid_item.snap_id is 'id号';
comment on column dbq$snapid_item.snap_item is 'id号对应的操作';

create public synonym dbq$snapid_item for dbq$snapid_item;
grant select,delete on dbq$snapid_item to dbqintf;

create table dbq$config (
snap_item varchar2(20),
run_flag varchar2(10) default 'N',
constraint pk_dbq_config_item primary key (snap_item))
;

comment on table dbq$config is '操作配置表，指明某种操作是否运行';
comment on column dbq$config.snap_item is '操作类型';
comment on column dbq$config.run_flag is '是否运行该操作，Y执行，N不执行';

create public synonym dbq$config for dbq$config;
grant all on dbq$config to dbqintf;

insert into dbq$config values ('ARCHIVE_LOG','Y');
insert into dbq$config values ('ARCH_INFO','N');
insert into dbq$config values ('TABLESPACE','Y');
insert into dbq$config values ('MEGATABLE','N');
commit;

/*********************************************************************/
/* */
/* 名称：DB质量评测项目--创建归档日志相关脚本 */
/* 用途: 创建归档日志相关表，用于存放反映归档日志变化的信息 */
/* 注意： */
/* */
/* 修改历史: */
/* 创建日期 姓名 - 2005-08-12 肖大韧 */
/*********************************************************************/

--dbq$arch_info 存放归档日志信息
create table dbq$arch_info (
snap_id number(9) ,
dbid number ,
instance_number number ,
min_time date constraint nn_dbqarchinfo_mintim not null,
max_time date constraint nn_dbqarchinfo_maxtim not null,
last_logs number ,
last_bytes number )
;
comment on table dbq$arch_info is '归档日志信息表';
comment on column dbq$arch_info.snap_id is 'dbq唯一标识id';
comment on column dbq$arch_info.dbid is '数据库id,取自v$database';
comment on column dbq$arch_info.instance_number is '实例号，取自v$instance';
comment on column dbq$arch_info.min_time is '最近生成archive log的开始时间';
comment on column dbq$arch_info.last_bytes is '最近生成archive log的结束时间';
comment on column dbq$arch_info.last_logs is '最近生成archive log数量';
comment on column dbq$arch_info.last_bytes is '最近生成archive log大小';

create public synonym dbq$arch_info for dbq$arch_info;
grant select,delete on dbq$arch_info to dbqintf;

/********************************************
功能说明： 创建归档日志表
参数说明：
创建人： 
创建日期： 2006-3-28

修改人：
修改日期：
修改说明：
********************************************/
--///////dbqua//////
create table DBQ$ARCHIVE_DETAIL(
snap_id number(9) ,
dbid number ,
instance_number number ,
HOST_NAME VARCHAR2(64),
SEQUENCE# NUMBER,
COMPLETION_TIME DATE ,
ARCHIVE_SIZE NUMBER
);

comment on table DBQ$ARCHIVE_DETAIL is 'archive log信息统计表';
comment on column dbq$archive_detail.snap_id is 'dbq唯一标识id';
comment on column dbq$archive_detail.dbid is '数据库id,取自v$database';
comment on column dbq$archive_detail.instance_number is '实例号，取自v$instance';
comment on column dbq$archive_detail.HOST_NAME is '主机名,取自v$instance';
comment on column dbq$archive_detail.SEQUENCE# is 'archive log序号，取自v$archived_log';
comment on column dbq$archive_detail.COMPLETION_TIME is 'archive log生成时间，取自v$archived_log';
comment on column dbq$archive_detail.ARCHIVE_SIZE is 'archive log大小，取自v$archived_log，blocks*block_size';

grant select,delete on DBQ$ARCHIVE_DETAIL to dbqintf;
create public synonym DBQ$ARCHIVE_DETAIL for DBQ$ARCHIVE_DETAIL;

/*********************************************************************/
/* */
/* 名称：DB质量评测项目--创建大对象相关脚本 */
/* 用途: 创建大对象相关表，用于存放反映大对象特征和变化的信息 */
/* 注意： */
/* */
/* 修改历史: */
/* 创建日期 姓名 - 2005-07-24 肖大韧 */
/*********************************************************************/

--dbq$megatab_list 大表清单
create table dbq$megatab_list (
owner varchar2(30) ,
table_name varchar2(30) ,
bytes number ,
rec_flag varchar(1) ,
constraint pk_dbqmegalist_owntab primary key (owner,table_name))
;
comment on table dbq$megatab_list is '大表清单表';
comment on column dbq$megatab_list.owner is '表属主';
comment on column dbq$megatab_list.table_name is '表名';
comment on column dbq$megatab_list.bytes is '表大小';
comment on column dbq$megatab_list.rec_flag is '需要记录详细信息的标志';

create public synonym dbq$megatab_list for dbq$megatab_list;
grant select,delete on dbq$megatab_list to dbqintf;

--dbq$megatab_info 存放大表属性及其变化值
create table dbq$megatab_info (
snap_id number(9) ,
dbid number ,
instance_number number ,
owner varchar2(30) ,
table_name varchar2(30) ,
ts_name varchar2(30) ,
bytes number ,
blocks number ,
extents number ,
tab_cols number ,
tab_inds number ,
ind_maxcol number ,
ind_sumbytes number ,
ind_maxbytes number ,
ind_minbytes number ,
records number ,
constraint fk_dbqmegainfo_owntab foreign key (owner,table_name) references dbq$megatab_list (owner,table_name))
;
comment on table dbq$megatab_info is '大表属性表';
comment on column dbq$megatab_info.snap_id is 'dbq唯一标识id';
comment on column dbq$megatab_info.dbid is '数据库id,取自v$database';
comment on column dbq$megatab_info.instance_number is '实例号，取自v$instance';
comment on column dbq$megatab_info.owner is '表属主';
comment on column dbq$megatab_info.table_name is '表名';
comment on column dbq$megatab_info.ts_name is '表空间名';
comment on column dbq$megatab_info.bytes is '表大小';
comment on column dbq$megatab_info.blocks is '表包含block数量';
comment on column dbq$megatab_info.extents is '表包含extent数量';
comment on column dbq$megatab_info.tab_cols is '表字段数';
comment on column dbq$megatab_info.tab_inds is '表索引数量';
comment on column dbq$megatab_info.ind_maxcol is '表上一个索引包含的最大字段数';
comment on column dbq$megatab_info.ind_sumbytes is '表上索引占空间和';
comment on column dbq$megatab_info.ind_maxbytes is '表上最大索引大小';
comment on column dbq$megatab_info.ind_minbytes is '表上最小索引大小';
comment on column dbq$megatab_info.records is '表包含记录数';

create public synonym dbq$megatab_info for dbq$megatab_info;
create index ix_dbqmegainfo_owntab on dbq$megatab_info (owner,table_name);
grant select,delete on dbq$megatab_info to dbqintf;

--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('ACCTMAN','VOU_DTL','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEBASE','RESV_TBL','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','ADV_PREM','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','APP_BEN','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','APP_FORM','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','BENEFICIARY_INFO','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','CLIENT_INFO','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','DIV_DETAIL','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','FORM_DELIVER','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','LCS_COMMISSION_TRAIL','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','MONTHLY_SALARY_DETAIL','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','MONTHLY_SALARY_DETAIL_TMP','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','PERST_TRAIL','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','POL_AGT_UPDATE','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','POL_ALT','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','POL_ALT_DETAIL','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','POL_BEN','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','POL_INFO','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','POS_ACCEPT','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','POS_ACCEPT_DETAIL','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','POS_ACPT_TIME_RECORD','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','PREM_DUE','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','PREM_INFO','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','PROV_RCPT','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','STAT_PREM_DUE','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','UNDWRT_RESULT','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('LIFEDATA','VIREMENT_FROMBANK_HISTORY','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('REINSMAN','LFREINS_POL','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('REINSMAN','QUOTA_SHARE_PREM_INFO','Y');
--insert into dbq$megatab_list (owner,table_name,rec_flag) values ('VOUDATA','VALUE_VOUCHER_RECORD','Y');
update dbq$megatab_list l set l.bytes=
(select s.bytes from dba_segments s where l.owner=s.owner and l.table_name=s.segment_name)
;
--dbq$megatab_nols 提供不记录大表信息的条件
create table dbq$megatab_nols (
nols_type varchar2(3) ,
name1 varchar2(30) ,
name2 varchar2(30) )
;
comment on table dbq$megatab_nols is '大表信息排除条件表';
comment on column dbq$megatab_nols.nols_type is '排除类型';
comment on column dbq$megatab_nols.name1 is '条件1';
comment on column dbq$megatab_nols.name2 is '条件2';

create public synonym dbq$megatab_nols for dbq$megatab_nols;
grant select,delete on dbq$megatab_nols to dbqintf;

--insert into dbq$megatab_nols (nols_type,name1) values ('OWN','SYS' );
--insert into dbq$megatab_nols (nols_type,name1) values ('OWN','SYSTEM' );
--insert into dbq$megatab_nols (nols_type,name1) values ('OWN','ACTRESV' );
--insert into dbq$megatab_nols (nols_type,name1) values ('OWN','LIFELOG' );
--insert into dbq$megatab_nols (nols_type,name1) values ('OWN','LIFEREPT');
--insert into dbq$megatab_nols (nols_type,name1) values ('OWN','HBSDATA' );
--insert into dbq$megatab_nols (nols_type,name1) values ('OWN','MISSEL' );
--insert into dbq$megatab_nols (nols_type,name1) values ('OWN','LIFEMENU');
--insert into dbq$megatab_nols (nols_type,name1) values ('OWN','RESPMAN' );
--insert into dbq$megatab_nols (nols_type,name1) values ('OWN','CIF_DUMP');
--insert into dbq$megatab_nols (nols_type,name1) values ('TBS','ACTRESV' );
--insert into dbq$megatab_nols (nols_type,name1) values ('TBS','BRCHREPT');
--insert into dbq$megatab_nols (nols_type,name1) values ('TBS','CMDATA' );
--insert into dbq$megatab_nols (nols_type,name1) values ('TBS','DBADATA' );
--insert into dbq$megatab_nols (nols_type,name1) values ('TBS','DBATMP' );
--insert into dbq$megatab_nols (nols_type,name1) values ('TBS','HBSBASE' );
--insert into dbq$megatab_nols (nols_type,name1) values ('TBS','HBSDATA' );
--insert into dbq$megatab_nols (nols_type,name1) values ('TBS','HBSIDX' );
--insert into dbq$megatab_nols (nols_type,name1) values ('TBS','LIFELOG' );
--insert into dbq$megatab_nols (nols_type,name1) values ('TBS','PERFSTAT');
--insert into dbq$megatab_nols (nols_type,name1) values ('TBS','RBS' );
--insert into dbq$megatab_nols (nols_type,name1) values ('TBS','SYSTEM' );
--insert into dbq$megatab_nols (nols_type,name1) values ('TBS','TEMP' );
--insert into dbq$megatab_nols (nols_type,name1) values ('TBS','TOOLS' );
--insert into dbq$megatab_nols (nols_type,name1) values ('TBS','USERS' );

/*********************************************************************/
/* */
/* 名称：DB质量评测项目--创建表空间、数据文件相关脚本 */
/* 用途: 创建表空间、数据文件相关表，用于存放反映表空间、数据文件 */
/* 特征和变化的信息 */
/* 注意： */
/* */
/* 修改历史: */
/* 创建日期 姓名 - 2005-07-24 肖大韧 */
/* 创建日期 姓名 - 2006-09-27 增加extents字段 */
/*********************************************************************/

--dbq$ts_base 存放表空间基本信息
create table dbq$ts_base (
snap_id number(9) ,
dbid number ,
instance_number number ,
ts_name varchar2(30) constraint nn_dbqtsbase_tsname not null ,
initial_extent number ,
next_extent number ,
min_extents number ,
pct_increase number ,
status varchar2(9) ,
contents varchar2(9) ,
logging varchar2(9) ,
extent_management varchar2(10) ,
allocation_type varchar2(9) ,
snap_date date ,
max_extents number,
constraint pk_dbqtsbase_id primary key (snap_id,ts_name))
;
comment on table dbq$ts_base is '表空间基本信息表';
comment on column dbq$ts_base.snap_id is 'dbq唯一标识id';
comment on column dbq$ts_base.dbid is '数据库id,取自v$database';
comment on column dbq$ts_base.instance_number is '实例号，取自v$instance';
comment on column dbq$ts_base.ts_name is '表空间名';
comment on column dbq$ts_base.initial_extent is '表空间默认存储参数,intial';
comment on column dbq$ts_base.next_extent is '表空间默认存储参数,next';
comment on column dbq$ts_base.min_extents is '表空间默认存储参数,minextents';
comment on column dbq$ts_base.pct_increase is '表空间默认存储参数,pctincrease';
comment on column dbq$ts_base.status is '表空间状态';
comment on column dbq$ts_base.contents is '表空间内容类型,permanent or temporary';
comment on column dbq$ts_base.logging is '表空间默认logging属性';
comment on column dbq$ts_base.extent_management is '表空间管理方式,dictionary or local';
comment on column dbq$ts_base.allocation_type is '表空间extent分配类型';
comment on column dbq$ts_base.snap_date is '收集信息的时间';
comment on column dbq$ts_base.max_extents is '表空间默认存储参数,max_extents';

create public synonym dbq$ts_base for dbq$ts_base;
grant select,delete on dbq$ts_base to dbqintf;

--dbq$ts_info 存放表空间、数据文件属性信息以及动态值
create table dbq$ts_info (
snap_id number(9) ,
dbid number ,
instance_number number ,
ts_name varchar2(30) constraint nn_dbqtsinfo_tsname not null ,
file_amount number ,
ts_size number ,
ts_blocks number ,
free_bytes number ,
free_blocks number ,
table_size number ,
index_size number ,
snap_date date ,
free_extents number ,
table_extents number ,
index_extents number ,
free_bytes_true number ,
constraint pk_dbqtsinfo_id primary key (snap_id,ts_name))
;
comment on table dbq$ts_info is '表空间属性表';
comment on column dbq$ts_info.snap_id is 'dbq唯一标识id';
comment on column dbq$ts_info.dbid is '数据库id,取自v$database';
comment on column dbq$ts_info.instance_number is '示例号，取自v$instance';
comment on column dbq$ts_info.ts_name is '表空间名';
comment on column dbq$ts_info.file_amount is '表空间包含数据文件数量';
comment on column dbq$ts_info.ts_size is '表空间大小';
comment on column dbq$ts_info.ts_blocks is '表空间包含block数量';
comment on column dbq$ts_info.free_bytes is '表空间剩余空间大小';
comment on column dbq$ts_info.free_blocks is '表空间剩余block数量';
comment on column dbq$ts_info.table_size is '表空间中包含表型数据量';
comment on column dbq$ts_info.index_size is '表空间中包含索引型数据量';
comment on column dbq$ts_info.snap_date is '收集信息的时间';
comment on column dbq$ts_info.free_extents is 'free space碎片数量';
comment on column dbq$ts_info.table_extents is 'table的extent数量';
comment on column dbq$ts_info.index_extents is 'index的extent数量';
comment on column dbq$ts_info.free_bytes_true is '表空间的可用剩余空间，缺省为>1M的可用空间的和';

create public synonym dbq$ts_info for dbq$ts_info;
grant select,delete on dbq$ts_info to dbqintf;

/****************************************************************************************/
/* */
/* 名称：DB质量评测项目--Session信息统计脚本 */
/* 用途: 统计数据库的session信息 */
/* 注意： */
/* */
/* 修改历史: */
/* Ver Date Author Description */
/* --------- ---------- --------------- -------------------------------------------*/
/* 1.0 2006-11-09 甘露、梁海安 1. 创建 */
/* 1.1 2006-11-14 甘露 1. 删除drop table语句 */
/* */
/* */
/****************************************************************************************/

-- Create table
create table DBQUA.DBQ$SESSION_DETAIL_01
(
SNAP_ID NUMBER(9),
DBID NUMBER,
INSTANCE_NUMBER NUMBER,
HOST_NAME VARCHAR2(64),
SNAP_TIME DATE,
SID NUMBER,
SERIAL# NUMBER,
USERNAME VARCHAR2(30),
STATUS VARCHAR2(8),
OSUSER VARCHAR2(30),
MACHINE VARCHAR2(64),
PROGRAM VARCHAR2(48),
TYPE VARCHAR2(10),
LOGON_TIME DATE,
CONSTRAINT "PK_SESSION_DETAIL_01" PRIMARY KEY ("SNAP_ID", "SID")
);

-- Add comments to the table
comment on table DBQUA.DBQ$SESSION_DETAIL_01
is 'Session信息Detail表';
-- Add comments to the columns
comment on column DBQUA.DBQ$SESSION_DETAIL_01.SNAP_ID
is 'dbq唯一标识id';
comment on column DBQUA.DBQ$SESSION_DETAIL_01.DBID
is '数据库id,取自v$database';
comment on column DBQUA.DBQ$SESSION_DETAIL_01.INSTANCE_NUMBER
is '实例号，取自v$instance';
comment on column DBQUA.DBQ$SESSION_DETAIL_01.HOST_NAME
is '主机名,取自v$instance';
comment on column DBQUA.DBQ$SESSION_DETAIL_01.SNAP_TIME
is '抓取数据的时间';
comment on column DBQUA.DBQ$SESSION_DETAIL_01.SID
is 'v$session field';
comment on column DBQUA.DBQ$SESSION_DETAIL_01.SERIAL#
is 'v$session field';
comment on column DBQUA.DBQ$SESSION_DETAIL_01.USERNAME
is 'v$session field';
comment on column DBQUA.DBQ$SESSION_DETAIL_01.STATUS
is 'v$session field';
comment on column DBQUA.DBQ$SESSION_DETAIL_01.OSUSER
is 'v$session field';
comment on column DBQUA.DBQ$SESSION_DETAIL_01.MACHINE
is 'v$session field';
comment on column DBQUA.DBQ$SESSION_DETAIL_01.PROGRAM
is 'v$session field';
comment on column DBQUA.DBQ$SESSION_DETAIL_01.TYPE
is 'v$session field';
comment on column DBQUA.DBQ$SESSION_DETAIL_01.LOGON_TIME
is 'v$session field';

-- Create public synonym
---drop public synonym DBQ$SESSION_DETAIL;
create public synonym DBQ$SESSION_DETAIL for DBQUA.DBQ$SESSION_DETAIL_01;
grant select, delete on dbq$session_detail_01 to dbqintf;

-- Create table
create table DBQUA.DBQ$SESSION_DETAIL_02
(
SNAP_ID NUMBER(9),
DBID NUMBER,
INSTANCE_NUMBER NUMBER,
HOST_NAME VARCHAR2(64),
SNAP_TIME DATE,
SID NUMBER,
SERIAL# NUMBER,
USERNAME VARCHAR2(30),
STATUS VARCHAR2(8),
OSUSER VARCHAR2(30),
MACHINE VARCHAR2(64),
PROGRAM VARCHAR2(48),
TYPE VARCHAR2(10),
LOGON_TIME DATE,
CONSTRAINT "PK_SESSION_DETAIL_02" PRIMARY KEY ("SNAP_ID", "SID")
);

-- Add comments to the table
comment on table DBQUA.DBQ$SESSION_DETAIL_02
is 'Session信息Detail表';
-- Add comments to the columns
comment on column DBQUA.DBQ$SESSION_DETAIL_02.SNAP_ID
is 'dbq唯一标识id';
comment on column DBQUA.DBQ$SESSION_DETAIL_02.DBID
is '数据库id,取自v$database';
comment on column DBQUA.DBQ$SESSION_DETAIL_02.INSTANCE_NUMBER
is '实例号，取自v$instance';
comment on column DBQUA.DBQ$SESSION_DETAIL_02.HOST_NAME
is '主机名,取自v$instance';
comment on column DBQUA.DBQ$SESSION_DETAIL_02.SNAP_TIME
is '抓取数据的时间';
comment on column DBQUA.DBQ$SESSION_DETAIL_02.SID
is 'v$session field';
comment on column DBQUA.DBQ$SESSION_DETAIL_02.SERIAL#
is 'v$session field';
comment on column DBQUA.DBQ$SESSION_DETAIL_02.USERNAME
is 'v$session field';
comment on column DBQUA.DBQ$SESSION_DETAIL_02.STATUS
is 'v$session field';
comment on column DBQUA.DBQ$SESSION_DETAIL_02.OSUSER
is 'v$session field';
comment on column DBQUA.DBQ$SESSION_DETAIL_02.MACHINE
is 'v$session field';
comment on column DBQUA.DBQ$SESSION_DETAIL_02.PROGRAM
is 'v$session field';
comment on column DBQUA.DBQ$SESSION_DETAIL_02.TYPE
is 'v$session field';
comment on column DBQUA.DBQ$SESSION_DETAIL_02.LOGON_TIME
is 'v$session field';

grant select, delete on dbq$session_detail_02 to dbqintf;

--汇总表

--drop table dbqua.dbq$session_count cascade constraint;

create table DBQUA.DBQ$SESSION_COUNT
(
snap_id number,
snap_date date,
USERNAME VARCHAR2(30) ,
COUNT_ALL NUMBER,
COUNT_ACTIVE NUMBER
);

create public synonym dbq$session_count for DBQUA.dbq$session_count;

alter table DBQ$SESSION_COUNT
add constraint PK_DBQC$SESSCOUNT_UNAME_DATE primary key (snap_id, username);
comment on table DBQUA.DBQ$SESSION_COUNT is 'Session信息统计表';
comment on column DBQUA.DBQ$SESSION_COUNT.snap_date is '每次获取session信息的时间';
comment on column DBQUA.DBQ$SESSION_COUNT.username is 'v$session.username';
comment on column DBQUA.DBQ$SESSION_COUNT.COUNT_ALL is '每个时间点总的session数量';
comment on column DBQUA.DBQ$SESSION_COUNT.COUNT_ACTIVE is '每个时间点活动的session数量';

grant select, delete on DBQ$SESSION_COUNT to DBQINTF;

insert into dbq$config values ('SESSION','Y');

commit;

CREATE OR REPLACE package dbq$pkg as

/***********************************************************************************/
/* */
/* 名称：DB质量评测项目--创建主程序包脚本 */
/* 用途: 创建程序包，用于完成dbq主要功能 */
/* 注意： */
/* */
/* 修改历史: */
/* Ver Date Author Description */
/* ------ ---------- -------- ------------------------------------------- */
/* 1.0 2005-07-24 1. 创建 */
/* 1.1 2006-03-29 1. 添加 archive_info过程，用来取archivelog信息 */
/* 1.2 2006-04-07 1. 添加 session_info过程，用来取session信息 */
/* 1.3 2006-09-01 1. 添加 session过程，用来取session信息 */
/* 1.4 2006-09-27 1. 修改tbs过程 */
/* 1.5 2006-11-09 1. session统计完善 */
/* 修改session_info过程，删除session_count,get_snapid过程 */
/* */
/***********************************************************************************/

--run_flag 是否运行
function run_flag(p_item varchar2) return varchar2;
--init：获取数据库和实例的基本信息
procedure init(o_snap_id out number,
o_dbid out number,
o_name out varchar2,
o_instance_number out number,
o_instance_name out varchar2,
o_startup_time out date,
o_parallel out varchar2,
o_version out varchar2,
o_hostname out varchar2);
--ts_info：获取表空间、数据库文件信息
procedure ts_info(i_snap_id in number default null,
i_dbid in number default null,
i_instance_number in number default null);

--ts_info：获取表空间、数据库文件信息
procedure ts_base(i_snap_id in number default null,
i_dbid in number default null,
i_instance_number in number default null);

--arch_info：获取归档日志信息
procedure arch_info(i_snap_id in number default null,
i_dbid in number default null,
i_instance_number in number default null);

--arch_info：获取归档日志信息--ganlu rewrite
procedure archive_info(i_snap_id in number default null,
i_dbid in number default null,
i_instance_number in number default null,
i_hostname in varchar2 default null,
i_version in varchar2 default null);

--megatab_info：获取大表信息
procedure megatab_info(i_snap_id in number default null,
i_dbid in number default null,
i_instance_number in number default null);

-- session_info 获取session信息
procedure session_info(i_snap_id in number default null,
i_dbid in number default null,
i_instance_number in number default null,
i_hostname in varchar2 default null);

--为了保留一周的数据，每周切换一次同义词，删除老的数据
procedure switch_syn;
end dbq$pkg;
/

create or replace package body dbq$pkg as

function run_flag(p_item varchar2) return varchar2 as
v_result varchar2(10);
begin
select nvl(run_flag, 'N')
into v_result
from dbq$config
where snap_item = upper(p_item);
return v_result;
exception
when others then
return 'N';
end;

procedure init(o_snap_id out number,
o_dbid out number,
o_name out varchar2,
o_instance_number out number,
o_instance_name out varchar2,
o_startup_time out date,
o_parallel out varchar2,
o_version out varchar2,
o_hostname out varchar2) is
cursor get_db is
select dbid, name from v$database;
cursor get_instance is
select instance_number,
instance_name,
startup_time,
parallel,
version,
host_name
from v$instance;
begin
open get_db;
fetch get_db
into o_dbid, o_name;
close get_db;
open get_instance;
fetch get_instance
into o_instance_number, o_instance_name, o_startup_time, o_parallel, o_version, o_hostname;
close get_instance;
select dbq$snapshot_id.nextval
into o_snap_id
from dual
where rownum = 1;
insert into dbq$snapid_time values (o_snap_id, sysdate);
commit;
end init;

procedure megatab_info(i_snap_id in number default null,
i_dbid in number default null,
i_instance_number in number default null) is
begin
null;
end megatab_info;

procedure ts_base(i_snap_id in number default null,
i_dbid in number default null,
i_instance_number in number default null) is
v_snap_id number;
v_DBID number;
v_instance_number number;
v_name varchar2(9);
v_instance_name varchar2(16);
v_startup_time date;
v_parallel varchar2(3);
v_version varchar2(17);
v_hostname varchar2(64);
v_snap_date date;
begin
if run_flag('TABLESPACE') = 'Y' then

if (i_snap_id is null) then
dbq$pkg.init(v_snap_id,
v_DBID,
v_name,
v_instance_number,
v_instance_name,
v_startup_time,
v_parallel,
v_version,
v_hostname);
else
v_snap_id := i_snap_id;
v_DBID := i_dbid;
v_instance_number := i_instance_number;
end if;

select created
into v_snap_date
from DBQ$SNAPID_TIME
where snap_id = v_snap_id;

insert into dbq$ts_base
select /*+RULE*/v_snap_id,
v_DBID,
v_instance_number,
tablespace_name,
initial_extent,
next_extent,
min_extents,
pct_increase,
status,
contents,
logging,
extent_management,
allocation_type,
v_snap_date,
max_extents
from dba_tablespaces;

commit;
end if;
end ts_base;

procedure ts_info(i_snap_id in number default null,
i_dbid in number default null,
i_instance_number in number default null) is
v_snap_id number;
v_DBID number;
v_instance_number number;
v_name varchar2(9);
v_instance_name varchar2(16);
v_startup_time date;
v_parallel varchar2(3);
v_version varchar2(17);
v_hostname varchar2(64);
v_snap_date date;
begin
if run_flag('TABLESPACE') = 'Y' then

if (i_snap_id is null) then
dbq$pkg.init(v_snap_id,
v_DBID,
v_name,
v_instance_number,
v_instance_name,
v_startup_time,
v_parallel,
v_version,
v_hostname);
else
v_snap_id := i_snap_id;
v_DBID := i_dbid;
v_instance_number := i_instance_number;
end if;

select created
into v_snap_date
from DBQ$SNAPID_TIME
where snap_id = v_snap_id;

insert into dbq$ts_info
(snap_id,
dbid,
instance_number,
ts_name,
file_amount,
ts_size,
ts_blocks,
free_bytes,
free_blocks,
table_size,
index_size,
snap_date,
free_extents,
table_extents,
index_extents,
-- table_count,
-- index_count,
free_bytes_true)
select /*+RULE*/v_snap_id,
v_DBID,
v_instance_number,
fl."ts_name",
fl."file_amount",
fl."ts_size",
fl."ts_blocks",
nvl(fr."free_bytes", 0),
nvl(fr."free_blocks", 0),
nvl(tb."table_size", 0),
nvl(ix."index_size", 0),
v_snap_date,
nvl(fr."free_extents", 0),
nvl(tb."table_extents", 0),
nvl(ix."index_extents", 0),
-- null ,--table_count
-- null, -- index count
nvl(fr_true."free_bytes", 0)
from (select tablespace_name "ts_name",
count(file_name) "file_amount",
sum(bytes) "ts_size",
sum(blocks) "ts_blocks"
from dba_data_files
group by tablespace_name) fl,
(select tablespace_name "ts_name",
sum(bytes) "free_bytes",
sum(blocks) "free_blocks",
count(blocks) "free_extents"
from dba_free_space
group by tablespace_name) fr,
(select tablespace_name "ts_name",
sum(bytes) "table_size",
count(*) "table_extents"
from dba_extents
-- where segment_type in('TABLE','TABLE PARTITION','LOBSEGMENT')
where segment_type not in
('INDEX', 'INDEX PARTITION', 'LOBINDEX')
group by tablespace_name) tb,
(select tablespace_name "ts_name",
sum(bytes) "index_size",
count(*) "index_extents"
from dba_extents
where segment_type in
('INDEX', 'INDEX PARTITION', 'LOBINDEX')
group by tablespace_name) ix,
(select tablespace_name "ts_name", sum(bytes) "free_bytes"
from dba_free_space
where bytes > 1048576
group by tablespace_name) fr_true
where fl."ts_name" = fr."ts_name"(+)
and fl."ts_name" = tb."ts_name"(+)
and fl."ts_name" = ix."ts_name"(+)
and fl."ts_name" = fr_true."ts_name"(+);

commit;
end if;
end ts_info;

procedure arch_info(i_snap_id in number default null,
i_dbid in number default null,
i_instance_number in number default null) is
begin
null;
end arch_info;

procedure archive_info(i_snap_id in number default null,
i_dbid in number default null,
i_instance_number in number default null,
i_hostname in varchar2 default null,
i_version in varchar2 default null) is
v_snap_id number;
v_DB_NAME number;
v_instance_number number;
v_name varchar2(9);
v_instance_name varchar2(16);
v_startup_time date;
v_parallel varchar2(3);
v_version varchar2(17);
v_hostname varchar2(64);
v_maxtime date;
v_sql varchar2(2000);
--/////split line//////
c_snap_item varchar2(20) := 'ARCHIVE_LOG';

begin
if run_flag(c_snap_item) = 'Y' then
if (i_snap_id is null) then
dbq$pkg.init(v_snap_id,
v_DB_NAME,
v_name,
v_instance_number,
v_instance_name,
v_startup_time,
v_parallel,
v_version,
v_hostname);
else
v_snap_id := i_snap_id;
v_DB_NAME := i_dbid;
v_instance_number := i_instance_number;
v_hostname := i_hostname;
v_version := i_version;
end if;

select nvl(max(created), to_date('2000', 'yyyy'))
into v_maxtime
from dbq$snapid_item i, dbq$snapid_time t
where i.snap_id = t.snap_id
and i.snap_item = c_snap_item;
/*modify by libing at 20080225 将动态sql改为使用绑定变量
v_sql := 'insert into dbq$archive_detail ' || chr(10) || 'select ' ||
v_snap_id || ',' || chr(10) || v_DB_NAME || ',' || chr(10) ||
v_instance_number || ',' || chr(10) || '''' || v_hostname ||
''',' || chr(10) || ' SEQUENCE#,' || chr(10) ||
' COMPLETION_TIME,' || chr(10) ||
' blocks * block_size ARCHIVE_SIZE' || chr(10) ||
' from v$archived_log' || chr(10) ||
' where COMPLETION_TIME > to_date(''' ||
to_char(v_maxtime, 'yyyymmdd hh24:mi:ss') ||
''',''yyyymmdd hh24:mi:ss'')';

if substr(v_version, 1, 1) = '9' THEN
v_sql := v_sql || chr(10) || ' AND DEST_ID = 1';
END IF;
dbms_output.put_line(v_sql);
execute immediate v_sql;
*/
insert into dbq$archive_detail
select v_snap_id,
v_db_name,
v_instance_number,
v_hostname,
sequence#,
completion_time,
blocks * block_size archive_size
from v$archived_log
where COMPLETION_TIME > to_date(to_char(v_maxtime, 'yyyymmdd hh24:mi:ss'),
'yyyymmdd hh24:mi:ss')
and standby_dest='NO';

insert into dbq$snapid_item values (v_snap_id, c_snap_item);

commit;
end if;
end archive_info;

procedure session_info(i_snap_id in number default null,
i_dbid in number default null,
i_instance_number in number default null,
i_hostname in varchar2 default null) is
v_snap_id number;
v_DB_NAME number;
v_instance_number number;
v_name varchar2(9);
v_instance_name varchar2(16);
v_startup_time date;
v_parallel varchar2(3);
v_version varchar2(17);
v_hostname varchar2(64);
v_maxtime date;
v_sql varchar2(2000);
--/////split line//////
c_snap_item varchar2(20) := 'SESSION';

begin
if run_flag(c_snap_item) = 'Y' then
if (i_snap_id is null) then
dbq$pkg.init(v_snap_id,
v_DB_NAME,
v_name,
v_instance_number,
v_instance_name,
v_startup_time,
v_parallel,
v_version,
v_hostname);
else
v_snap_id := i_snap_id;
v_DB_NAME := i_dbid;
v_instance_number := i_instance_number;
v_hostname := i_hostname;
end if;
insert into dbq$session_detail
select /*+RULE*/v_snap_id,
v_DB_NAME,
v_instance_number,
v_hostname,
sysdate,
sid,
serial#,
username,
status,
osuser,
machine,
program,
type,
logon_time
from v$session;
commit;
-- calc count
insert into dbq$session_count
select snap_id,
snap_time,
nvl(username, '系统进程'),
count(*) "all session count",
sum(decode(status, 'ACTIVE', 1, 0)) "Active Session Count"
from dbq$session_detail
where snap_id = v_snap_id
group by snap_id, snap_time, nvl(username, '系统进程');
commit;
end if;
end Session_info;

------转换同义词，删除老的数据----------
procedure switch_syn is
v_table_name varchar2(64);
-- v_sql_01 varchar2(500);
v_sql_02 varchar2(500);
v_sql_03 varchar2(500);
v_sql_04 varchar2(500);
v_sql_05 varchar2(500);
cursor curr_syn_infor is
select TABLE_NAME
from all_synonyms
where TABLE_OWNER = 'DBQUA'
and SYNONYM_NAME = 'DBQ$SESSION_DETAIL';

begin
-- v_sql_01 := 'drop public synonym DBQ$SESSION_DETAIL';
v_sql_02 := 'create or replace public synonym DBQ$SESSION_DETAIL for dbqua.DBQ$SESSION_DETAIL_02';
v_sql_03 := 'create or replace public synonym DBQ$SESSION_DETAIL for dbqua.DBQ$SESSION_DETAIL_01';
v_sql_04 := 'truncate table DBQ$SESSION_DETAIL_01';
v_sql_05 := 'truncate table DBQ$SESSION_DETAIL_02';
open curr_syn_infor;
fetch curr_syn_infor
into v_table_name;
close curr_syn_infor;
if (v_table_name = 'DBQ$SESSION_DETAIL_01') then
execute immediate (v_sql_05);
-- dbq$pkg.session_count(v_current_snap_id);
-- execute immediate (v_sql_01);
execute immediate (v_sql_02);

else
execute immediate (v_sql_04);
-- dbq$pkg.session_count(v_current_snap_id, v_old_snap_id);
-- execute immediate (v_sql_01);
execute immediate (v_sql_03);

end if;
end switch_syn;

end dbq$pkg;
/

/********************************************
功能说明： 创建刷新程序，用于刷新获取dbq信息
参数说明：
创建人： 
创建日期： 2005-07-24

修改人： 
修改日期： 2006-3-28
修改说明： dbq$snp15m 15分钟执行一次

修改人： 
修改日期： 2006-11-14
修改说明： 增加dbq$snp7d过程
********************************************/

--run every 7day
create or replace procedure dbq$snp7d is
begin
dbq$pkg.switch_syn;
end;
/

-- run every 1 day
create or replace procedure dbq$snp1d is
v_snap_id number ;
v_dbid number ;
v_instance_number number ;
v_name varchar2(9) ;
v_instance_name varchar2(16) ;
v_startup_time date ;
v_parallel varchar2(3) ;
v_version varchar2(17) ;
v_hostname varchar2(64) ;
begin
dbq$pkg.init(v_snap_id,v_dbid,v_name,v_instance_number,
v_instance_name,v_startup_time,v_parallel,v_version,v_hostname);
dbq$pkg.ts_base(v_snap_id,v_dbid,v_instance_number);
dbq$pkg.arch_info(v_snap_id,v_dbid,v_instance_number);
dbq$pkg.megatab_info(v_snap_id,v_dbid,v_instance_number);
end;
/

-- run every 6h ,0,6,12,18
create or replace procedure dbq$snp6h is
v_snap_id number ;
v_dbid number ;
v_instance_number number ;
v_name varchar2(9) ;
v_instance_name varchar2(16) ;
v_startup_time date ;
v_parallel varchar2(3) ;
v_version varchar2(17) ;
v_hostname varchar2(64) ;
begin
dbq$pkg.init(v_snap_id,v_dbid,v_name,v_instance_number,
v_instance_name,v_startup_time,v_parallel,v_version,v_hostname);
dbq$pkg.ts_info(v_snap_id,v_dbid,v_instance_number);
end;
/

--run every 15 min
create or replace procedure dbq$snp15m is
v_snap_id number;
v_dbid number;
v_instance_number number;
v_name varchar2(9);
v_instance_name varchar2(16);
v_startup_time date;
v_parallel varchar2(3);
v_version varchar2(17);
v_hostname varchar2(64);
begin
dbq$pkg.init(v_snap_id,
v_dbid,
v_name,
v_instance_number,
v_instance_name,
v_startup_time,
v_parallel,
v_version,
v_hostname);
dbq$pkg.archive_info(v_snap_id,
v_dbid,
v_instance_number,
v_hostname,
v_version);

dbq$pkg.session_info(v_snap_id,
v_dbid,
v_instance_number,
v_hostname);
end;
/

/********************************************
功能说明： 创建job，通过定时执行刷新程序获取dbq信息
参数说明：
创建人： 肖大韧
创建日期： 2005-07-24

修改人：ganlu
修改日期：2006-11-09
修改说明：add run every 7day's job
********************************************/

DECLARE
cursor cur_job is
select JOB FROM USER_JOBS WHERE WHAT in( 'dbq$snp_7day;','dbq$snp7d;');
BEGIN
for rowjob in cur_job loop
DBMS_JOB.REMOVE(rowjob.job);
DBMS_OUTPUT.PUT_LINE('WHAT:' || rowjob.job);
end loop;
END;
/

-- run every 7 day
--0:35
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'dbq$snp7d;',trunc(sysdate)+1+1/48, 'trunc(sysdate)+7+35/24/60');
commit;
END;
/

DECLARE
cursor cur_job is
select JOB FROM USER_JOBS WHERE WHAT = 'dbq$snp1d;';
BEGIN
for rowjob in cur_job loop
DBMS_JOB.REMOVE(rowjob.job);
DBMS_OUTPUT.PUT_LINE('WHAT:' || rowjob.job);
end loop;
END;
/

-- run every 1 day
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'dbq$snp1d;',trunc(sysdate)+1+1/24, 'trunc(sysdate)+1+1/24');
commit;
END;
/

DECLARE
cursor cur_job is
select JOB FROM USER_JOBS WHERE WHAT = 'dbq$snp6h;';
BEGIN
for rowjob in cur_job loop
DBMS_JOB.REMOVE(rowjob.job);
DBMS_OUTPUT.PUT_LINE('WHAT:' || rowjob.job);
end loop;
END;
/

-- run every 6h
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'dbq$snp6h;',trunc(sysdate)+1, 'trunc(sysdate,''HH'')+1/4');
commit;
END;
/

/*
-- run every 15m
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'dbq$snp15m;', trunc(sysdate+1/24,'HH')+1/24/60, 'trunc(sysdate,''MI'')+1/24/4');
commit;
END;
/

DECLARE
cursor cur_job is
select JOB FROM USER_JOBS WHERE WHAT = 'dbq$snp15m;';
BEGIN
for rowjob in cur_job loop
DBMS_JOB.REMOVE(rowjob.job);
DBMS_OUTPUT.PUT_LINE('WHAT:' || rowjob.job);
end loop;
END;
/

VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'dbq$snp15m;',
to_date(to_char(trunc(sysdate, 'HH'), 'yyyymmdd hh24:') ||
trunc((trunc(sysdate, 'MI') -
trunc(sysdate, 'HH')) * 24 * 60 / 15) * 15,
'yyyymmdd hh24:mi') + 1 / 24 / 60,
'to_date(to_char(trunc(sysdate, ''HH''), ''yyyymmdd hh24:'') || trunc((trunc(sysdate, ''MI'') - trunc(sysdate, ''HH'')) * 24 * 60 / 15) * 15,''yyyymmdd hh24:mi'')+1/24/60+1/24/4 ');
commit;
END;
/
*/
/*******************************************dbqua end ************************************************/

/*******************************************dbconn begin ************************************************/
conn sys/&password@&sid as sysdba

create user dbconn identified by conndb505
default tablespace users
temporary tablespace temp;

grant create session to dbconn;

/*******************************************dbconn end ************************************************/

/*******************************************dmlbak begin ************************************************/
--建DML备份用户
create user dmlbak identified by paic1234
profile pa_pw_profile
default tablespace users
temporary tablespace temp
quota unlimited on users
password expire;

grant create session to dmlbak;
grant create table to dmlbak;
grant select any table to dmlbak;

create sequence dmlbak.sq_bktb minvalue 1 maxvalue 999 increment by 1 nocache cycle;

create table dmlbak.bktb_info (
backup_tname varchar2(30),
source_tname varchar2(30),
rs_version varchar2(64)
);

create or replace procedure dmlbak.pc_bktb_rename (
i_tname in varchar,
i_version in varchar)
is
v_tname varchar2(30);
v_sql varchar2(200);
v_cnt number;
begin
select count(*) into v_cnt from user_tables
where table_name=upper(ltrim(rtrim(i_tname)));
if v_cnt > 0 then
select upper('dmlbk'||
to_char(sysdate,'yyyymmddhh24miss')||
lpad(sq_bktb.nextval,3,0))
into v_tname from dual;
select 'rename '||i_tname||' to '||v_tname
into v_sql from dual;
dbms_output.put_line(v_sql);
execute immediate v_sql;
insert into bktb_info values (v_tname,
upper(ltrim(rtrim(i_tname))),
upper(ltrim(rtrim(i_version))));
commit;
end if;
end;
/

grant execute on dbms_logmnr_d to dbmgr;
grant execute on dbms_logmnr to dbmgr;

grant select on v_$session to dbmgr;
grant select on v_$statname to dbmgr;
grant select on v_$mystat to dbmgr;

conn dbmgr/&password@&sid
--2. dbmgr执行
--alter database add supplemental log data (primary key,unique index) columns; ---cancel by liuyong746 20170622--
execute dbms_logmnr_d.build (options=>dbms_logmnr_d.store_in_redo_logs);
execute dbms_logmnr_d.set_tablespace(new_tablespace=>'users');

create table dmlbak.dml_log (
scn number ,
cscn number ,
commit_timestamp date ,
seg_owner varchar2(32) ,
seg_name varchar2(256) ,
table_name varchar2(32) ,
seg_type number ,
seg_type_name varchar2(32) ,
table_space varchar2(32) ,
row_id varchar2(18) ,
session# number ,
serial# number ,
username varchar2(30) ,
session_info varchar2(4000) ,
operation varchar2(32) ,
sql_redo varchar2(4000) ,
sql_undo varchar2(4000) ,
info varchar2(32) )
tablespace users
;

create public synonym dml_log for dmlbak.dml_log;

---variable jobno number;
---BEGIN
--- DBMS_JOB.SUBMIT(:jobno,
--- 'dbms_logmnr_d.build (options=>dbms_logmnr_d.store_in_redo_logs);',
--- to_date(to_char(trunc(sysdate)+1,'yyyy-mm-dd')||' 08:00:00','yyyy-mm-dd hh24:mi:ss'),
--- 'trunc(sysdate)+1+8/24');
--- COMMIT;
---END;
---/
---print jobno;

/**************************************dmlbak end******************************************/

/*******************************************DBMONOPR begin************************************************/

conn sys/&password@&sid as sysdba

-- Create the user
create user DBMONOPR
identified by paic1234
default tablespace users
temporary tablespace TEMP
profile DEFAULT
quota unlimited on users;

grant select_catalog_role to DBMONOPR;
grant create database link to DBMONOPR;
grant create session to DBMONOPR;
grant create table to DBMONOPR;
/*******************************************DBMONOPR end************************************************/

/*******************************************deployop start ************************************************/
conn sys/&password@&sid as sysdba
/********************************************

功能说明： 创建deployop用户
参数说明：
创建人： 李兵
创建日期： 2008-03-31
修改人：
修改日期：
修改说明：

********************************************/

-- Create the user
create user DEPLOYOP
identified by paic1234
default tablespace users
temporary tablespace TEMP
profile DEFAULT
quota 100m on users;
-- Grant/Revoke object privileges
-- Grant/Revoke role privileges
grant select_catalog_role to DEPLOYOP;
-- Grant/Revoke system privileges
grant create session to DEPLOYOP;
grant CREATE PUBLIC SYNONYM to DEPLOYOP;
grant DROP PUBLIC SYNONYM to DEPLOYOP;

/********************************************

功能说明： SYS授权，创建编译公共同义词的过程
参数说明：
创建人： 
创建日期： 2008-03-31
修改人：
修改日期：
修改说明：

********************************************/

--sys用户执行：
grant SELECT_CATALOG_ROLE to dbmgr;
grant select on dba_users to dbmgr;
grant select on dba_sys_privs to dbmgr;
grant select on dba_objects to dbmgr;
grant ALTER ANY CLUSTER to dbmgr;
grant ALTER ANY DIMENSION to dbmgr;
grant ALTER ANY INDEX to dbmgr;
grant ALTER ANY INDEXTYPE to dbmgr;
grant ALTER ANY LIBRARY to dbmgr;
grant ALTER ANY OUTLINE to dbmgr;
grant ALTER ANY PROCEDURE to dbmgr;
grant ALTER ANY SEQUENCE to dbmgr;
grant ALTER ANY SNAPSHOT to dbmgr;
grant ALTER ANY TABLE to dbmgr;
grant ALTER ANY TRIGGER to dbmgr;
grant ALTER ANY TYPE to dbmgr;
--补充
grant select on sys.v_$instance to dbmgr;
grant select on sys.v_$session to dbmgr;
grant alter system to dbmgr;

create or replace procedure pa_alter_synonym
(v_owner in varchar2,
v_synonym in varchar2 )
is
begin
if ltrim(rtrim(upper(v_owner)))='PUBLIC' then
execute immediate 'alter public synonym '||ltrim(rtrim(v_synonym))||' compile';
else
execute immediate 'alter synonym '||ltrim(rtrim(v_owner))||'.'||ltrim(rtrim(v_synonym))||' compile';
end if;
exception when others then
dbms_output.put_line('Error of compiling :'||substr(sqlerrm,1,220));
end;
/

grant execute on sys.pa_alter_synonym to dbmgr;
grant execute on sys.pa_alter_synonym to deployop;

conn dbmgr/&password@&sid
/********************************************

功能说明： 创建DDL版本下发需要的工具过程
参数说明：
创建人： 
创建日期： 2008-03-31
修改人：
修改日期：
修改说明：

********************************************/

--1、prc_kill_session.sql
create table KILLUSER_LOG
(
KILLER VARCHAR2(30),
REC_TIME DATE,
KILLED_USER VARCHAR2(30),
MACHINE VARCHAR2(64),
OSUSER VARCHAR2(30)
);

create or replace procedure prc_kill_session
(v_username IN varchar2,v_sid IN number ,v_serial# IN number)
is
v_cnt number;
v_machine varchar2(64);
v_osuser varchar2(30);
v_sql varchar2(100);
begin
if upper(v_username) is null or upper(v_username) in ('SYS','SYSTEM','DBMGR') then
dbms_output.put_line('Your username is null or the user can not be killed!');
else
select count(*) into v_cnt
from v$session a
where a.username is not null
and a.username=upper(trim(v_username))
and a.sid=v_sid
and a.serial#=v_serial#;
if v_cnt = 1 then
select machine,osuser into v_machine,v_osuser from v$session a
where a.username is not null
and a.username=upper(trim(v_username))
and a.sid=v_sid
and a.serial#=v_serial#;
execute immediate 'alter system kill session '''||v_sid||','||v_serial#||'''';
insert into killuser_log (KILLER,REC_TIME,KILLED_USER,machine, osuser)
values (user, sysdate, v_username, v_machine,v_osuser);
commit;
else
raise_application_error(-20005,'No such session: username='||v_username||',sid='||v_sid||',serial#='||v_serial#);
end if;
end if;
end;
/

create public synonym prc_kill_session for dbmgr.prc_kill_session;
grant execute on dbmgr.prc_kill_session to deployop;
--grant execute on dbmgr.prc_kill_session to DBMONO03;
grant execute on dbmgr.prc_kill_session to DBMONOPR;

--2、prc_enb_restrict.sql 数据库受限脚本
create table restrict_allow_user(username varchar2(30) primary key);

insert into restrict_allow_user values('DEPLOYOP');
commit;

create or replace procedure prc_enb_restrict is
-- Version : v2.0(2017/08/30)

sql_rvk varchar2(60);
sql_grn varchar2(60);
sql_kill varchar2(60);
v_username varchar2(30);
v_cnt1 number(1);
v_flag varchar2(10) ;
v_flag2 varchar2(10) ;
v_k_username varchar2(30);
v_k_sid number;
v_k_serial number;

cursor cur_revoke is
select 'revoke '||privilege||' from '||grantee
from dba_sys_privs
where privilege like '%RESTRICTED%'
and grantee not in ('DBMGR','DBA','SYS')
and grantee not in ( select upper(trim(username)) from restrict_allow_user );

cursor cur_grant is
select 'grant restricted session to '||username,upper(username)
from restrict_allow_user
where upper(username) not in (
select grantee from dba_sys_privs where privilege like '%RESTRICTED%'
);

/*
cursor cur_kill is
select 'alter system kill session '''||sid||','||serial#||'''' from v$session
where username is not null
and username not in ('SYS','SYSTEM','DBMGR','DEPLOYOP')
and username not in (select upper(username) from restrict_allow_user) ;
*/
cursor cur_kill is
--select 'prc_kill_session('''||username||''','||sid||','||serial#||')' from v$session
select username,sid,serial# from v$session
where status'KILLED'
and username is not null
and username not in ('SYS','SYSTEM','DBMGR','DEPLOYOP','APPMGR','DBCONN','OVSEE','DBMON','I3_ORCL','VERITAS_I3')
and username not in (select upper(username) from restrict_allow_user) ;
begin
--revoke restricted session from users
open cur_revoke;
loop
fetch cur_revoke into sql_rvk;
exit when cur_revoke%NOTFOUND;
execute immediate sql_rvk;
end loop;
close cur_revoke;
dbms_output.put_line('Have revoke restricted privilege from some users!');

--restricted session
begin
execute immediate 'grant restricted session to APPMGR';
execute immediate 'grant restricted session to DBCONN';
execute immediate 'grant restricted session to OVSEE';
execute immediate 'grant restricted session to DBMON';
--execute immediate 'grant restricted session to I3_ORCL';
--execute immediate 'grant restricted session to VERITAS_I3';
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||':'||sqlerrm);
end;

open cur_grant;
loop
fetch cur_grant into sql_grn,v_username;
exit when cur_grant%NOTFOUND;

select count(*) into v_cnt1 from dba_users where username=v_username;
if v_cnt1=1 then
execute immediate sql_grn;
else
dbms_output.put_line('There has no this username:'||v_username);
end if;
end loop;
close cur_grant;
dbms_output.put_line('Have grant restricted privilege to some users!');

--enable database to restricted mode
select LOGINS into v_flag from v$instance;
if v_flag like 'RESTRICTED%' then
dbms_output.put_line('Now is restricted mode,do not change!');
else
execute immediate 'alter system enable restricted session';
select LOGINS into v_flag2 from v$instance;
dbms_output.put_line('You change this database to '||v_flag2) ;
end if;

--kill session
open cur_kill;
loop
fetch cur_kill into v_k_username,v_k_sid,v_k_serial;
exit when cur_kill%NOTFOUND;
--dbms_output.put_line(sql_kill);
prc_kill_session(v_k_username,v_k_sid,v_k_serial);
--execute immediate (sql_kill);
end loop;
close cur_kill;
dbms_output.put_line('Have kill some session!');

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||':'||sqlerrm);

end ;
/

create public synonym restrict_allow_user for dbmgr.restrict_allow_user;
grant select,insert,update,delete on dbmgr.restrict_allow_user to deployop;
create public synonym prc_enb_restrict for dbmgr.prc_enb_restrict;
grant execute on dbmgr.prc_enb_restrict to deployop;

--3、prc_dis_restrict 数据库取消受限脚本
create or replace procedure prc_dis_restrict is
v_flag varchar2(10) ;
v_flag2 varchar2(10) ;
begin
select LOGINS into v_flag from v$instance;
if v_flag like 'RESTRICTED%' then
execute immediate 'alter system disable restricted session ';
select LOGINS into v_flag2 from v$instance;
dbms_output.put_line('THE DB HAVE '||v_flag2) ;
else
dbms_output.put_line('THE DB ALREADY BE OPEN') ;
end if;
end ;
/

create public synonym prc_dis_restrict for dbmgr.prc_dis_restrict;
grant execute on dbmgr.prc_dis_restrict to deployop;

--4、prc_alt_invalid.sql
create or replace procedure prc_alt_invalid
(v_sql in varchar2 ) is

begin
if lower(v_sql) like '%alter%' and lower(v_sql) like '%compile%' then
execute immediate v_sql;
else
raise_application_error(-20005,'Invalid compile statement:'||v_sql);
end if;

exception when others then
dbms_output.put_line('Error of compiling :'||substr(sqlerrm,1,220));

end;
/

create public synonym prc_alt_invalid for dbmgr.prc_alt_invalid;
grant execute on dbmgr.prc_alt_invalid to deployop;

--5、prc_grn_rvk_restrict.sql
create or replace procedure prc_grn_rvk_restrict(v_oper in varchar2,v_username in varchar2 ) is
v_cnt1 number(1);

begin
select count(*) into v_cnt1 from dba_users where username=upper(v_username);
if v_cnt1=0 then
raise_application_error(-20005,'Username is null or invalid username!');
end if;

if lower(v_oper)='grant' then
execute immediate 'grant restricted session to '||v_username;
elsif lower(v_oper)='revoke' then
execute immediate 'revoke restricted session from '||v_username;
else
dbms_output.put_line('Invalid operation grant or revoke:'||v_oper);
end if;

end;
/

create public synonym prc_grn_rvk_restrict for dbmgr.prc_grn_rvk_restrict;
grant execute on dbmgr.prc_grn_rvk_restrict to deployop;

--6、为deployop用户创建四个table，供部署组进行失效对象监控使用
create table deployop.invalid_object_list_last_time as select owner,object_type,object_name,status,sysdate checkdate from dba_objects ,dual where status='INVALID' order by owner,object_type,object_name;
create table deployop.invalid_object_list_auto_now as select owner,object_type,object_name,status,sysdate checkdate from dba_objects ,dual where status='INVALID' order by owner,object_type,object_name;
create table deployop.invalid_object_list_hand_now as select owner,object_type,object_name,status,sysdate checkdate from dba_objects ,dual where status='INVALID' order by owner,object_type,object_name;
create table deployop.invalid_object_list_history as select owner,object_type,object_name,status,sysdate checkdate from dba_objects ,dual where status='INVALID' order by owner,object_type,object_name;

/*******************************************deployop end ************************************************/

/*******************************************appmgr begin************************************************/

conn sys/&password@&sid as sysdba
create user appmgr identified by paic1234
default tablespace users
temporary tablespace temp
quota unlimited on users
profile pa_pw_profile;

/* appmgr下建有清理日志表和临时表的JOB,不做审计
audit insert table, delete table, update table
by appmgr
by session
whenever successful;
audit connect by appmgr;
*/

grant create session to appmgr;
grant create table to appmgr;
grant create trigger to appmgr;
grant create public synonym to appmgr;
grant drop public synonym to appmgr;
grant create procedure to appmgr;
grant create sequence to appmgr;
grant create role to appmgr;

grant select any table to appmgr;
grant drop any table to appmgr;
grant delete any table to appmgr;
grant lock any table to appmgr;
grant create job to appmgr/* add for >= 19c */;

--以下语句须在sys用户下执行
grant select on dba_segments to appmgr;
grant select on dba_tablespaces to appmgr;
grant select on dba_tables to appmgr;

grant select on v_$session to appmgr;
grant select on v_$instance to appmgr;
grant select on v_$mystat to appmgr;

grant execute on sys.dbms_job to appmgr;
grant select on sys.dba_jobs_running to appmgr;
grant select on sys.dba_jobs to appmgr;

EXEC Dbms_Java.Grant_Permission('APPMGR', 'java.io.FilePermission', '/opt/OV/bin/OpC/opcmsg', 'read ,execute');
EXEC dbms_java.grant_permission('APPMGR','java.lang.RuntimePermission','*','writeFileDescriptor' );

conn appmgr/paic1234@&sid
create table sensitive_info_shield_rule
(shield_item varchar2(100),
shield_sql varchar2(200),
constraint pk_sens_rule primary key(shield_item)
);
comment on table sensitive_info_shield_rule is '敏感信息屏蔽规则表';
comment on column sensitive_info_shield_rule.shield_item is '屏蔽项';
comment on column sensitive_info_shield_rule.shield_sql is '屏蔽语句';
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('姓拼音屏蔽','''PingAn''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('备注屏蔽','''中国平安''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('地址屏蔽','''中国平安''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('姓名屏蔽','''PA''||ltrim(substrb( ltrim(col),3,lengthb(col)))');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('电话屏蔽','substr(col,1,length(trim(col))-6)||''123456''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('邮件屏蔽','substr(col,1,instr(col,''@'')-1)||''@pa182.com.cn''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('证件号码屏蔽(护照/军人证)','substr(col,1,length(trim(col))-2)||''00''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('证件号码屏蔽(非护照军人证)','''1234''||substr(col,5,length(ltrim(col)))');
insert into SENSITIVE_INFO_SHIELD_RULE (SHIELD_ITEM, SHIELD_SQL)
values ('帐号屏蔽', 'substr(col,1,length(col)-6)||''123456''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('身份证屏蔽','''1234''||substr(col,5,length(ltrim(col)))');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('护照屏蔽','substr(col,1,length(trim(col))-2)||''00''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('驾照屏蔽','''1234''||substr(col,5,length(ltrim(col)))');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('军人证屏蔽','substr(col,1,length(trim(col))-2)||''00''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('联系电话屏蔽','substr(col,1,length(trim(col))-6)||''123456''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('Email屏蔽','substr(col,1,instr(col,''@'')-1)||''@pa182.com.cn''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('传真屏蔽','substr(col,1,length(trim(col))-6)||''123456''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('家庭地址屏蔽','''中国平安''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('联系地址屏蔽','''中国平安''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('工作地址屏蔽','''中国平安''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('出生地屏蔽','''中国平安''');

insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('团体名称屏蔽','substr(col,1,2)||''中国平安''||substr(col,7,length(col)-2)');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('团体英文名称屏蔽','substr(col,1,2)||''ping''||substr(col,7,length(col)-2)');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('团体联系人|负责人屏蔽','''平安''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('联系人电话屏蔽','substr(col,1,length(trim(col))-6)||''123456''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('联系人email屏蔽','substr(col,1,instr(col,''@'')-1)||''@pa182.com.cn''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('联系人地址屏蔽','''中国平安''');

insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('税务号屏蔽','substr(col,1,length(trim(col))-2)||''00''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('工商登记号屏蔽','substr(col,1,length(trim(col))-2)||''00''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('组织机构代码屏蔽','substr(col,1,length(trim(col))-2)||''00''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('股票代码屏蔽','substr(col,1,length(trim(col))-2)||''00''');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('企业主页屏蔽','decode(col,null,null,''www.paic.com.cn'')');
insert into sensitive_info_shield_rule(shield_item,shield_sql)
values('经营许可证屏蔽','substr(col,1,length(trim(col))-2)||''00''');
create table sensitive_info_shield
(
sys_name varchar2(30) not null,
owner varchar2(30) not null,
table_name varchar2(30) not null,
column_name varchar2(30) not null,
description varchar2(200),
shield_item varchar2(100) not null,
row_count number not null,
updateby_col varchar2(30),
updateby_clause varchar2(200),
script varchar2(4000),
constraint pk_sens_info primary key(sys_name,owner,table_name,column_name),
constraint fk_sens_rule foreign key (shield_item) references sensitive_info_shield_rule(shield_item)
);

comment on table sensitive_info_shield is '敏感信息屏蔽表';
comment on column sensitive_info_shield.sys_name is '系统名称';
comment on column sensitive_info_shield.owner is '属主';
comment on column sensitive_info_shield.table_name is '表名';
comment on column sensitive_info_shield.column_name is '字段名';
comment on column sensitive_info_shield.description is '字段描述';
comment on column sensitive_info_shield.shield_item is '屏蔽项';
comment on column sensitive_info_shield.row_count is '表记录数';
comment on column sensitive_info_shield.updateby_col is '更新用字段';
comment on column sensitive_info_shield.updateby_clause is '更新用字段表达式';
comment on column sensitive_info_shield.script is '屏蔽脚本';

create table t_error_log(file_name varchar2(100) not null,sql_code varchar2(100),sql_error varchar2(500),created_date date not null);
comment on table t_error_log is '敏感信息屏蔽异常日志表';
comment on column t_error_log.file_name is '脚本文件名';
comment on column t_error_log.sql_code is '异常代码';
comment on column t_error_log.sql_error is '异常信息';
comment on column t_error_log.created_date is '创建时间';

create public synonym sensitive_info_shield_rule for appmgr.sensitive_info_shield_rule;
create public synonym sensitive_info_shield for appmgr.sensitive_info_shield;
create public synonym t_error_log for appmgr.t_error_log;

create role r_sensitivity_data ;
create role r_sensitivity_qry;
grant r_sensitivity_data to deployop;
grant r_sensitivity_data to dbmon;

grant select,update,insert,delete on appmgr.sensitive_info_shield_rule to r_sensitivity_data;
grant select,update,insert,delete on appmgr.sensitive_info_shield to r_sensitivity_data;
grant select,update,insert,delete on appmgr.t_error_log to r_sensitivity_data;

grant select on appmgr.sensitive_info_shield_rule to r_sensitivity_data;
grant select on appmgr.sensitive_info_shield to r_sensitivity_data;
grant select on appmgr.t_error_log to r_sensitivity_qry;

create table sub_app_code_tbl
( sub_app_code varchar2(4),
description varchar2(30) constraint nn_sub_app_code_tbl_desc not null,
constraint pk_sub_app_code_tbl_sub_code primary key (sub_app_code)
);
comment on table sub_app_code_tbl is '应用子系统代码基表';
comment on column sub_app_code_tbl.sub_app_code is '子系统代码';
comment on column sub_app_code_tbl.description is '描述';

create table tmplog_table_type_tbl
(table_type_code varchar2(4),
description varchar2(400) constraint nn_tmplog_table_type_tbl_desc not null,
constraint pk_tmplog_table_type_tbl_code primary key (table_type_code)
);
comment on table tmplog_table_type_tbl is '临时表和日志表类型基表';
comment on column tmplog_table_type_tbl.table_type_code is '表类型代码';
comment on column tmplog_table_type_tbl.description is '描述';

create table CLEARPROGRAM
(
PROGRAM_ID VARCHAR2(2),
DESCRIPTION VARCHAR2(100),
constraint pk_CLEARPROGRAM_PROGRAM_ID primary key (PROGRAM_ID)
);
comment on table CLEARPROGRAM is '清理程序';
comment on column CLEARPROGRAM.PROGRAM_ID is '清理程序ID';
comment on column CLEARPROGRAM.DESCRIPTION is '描述';

create table tmplog_owner_tbl
(
owner varchar2(30),
constraint pk_tmplog_owner primary key (owner)
);
comment on table tmplog_owner_tbl is '可清理属主信息表';
comment on column tmplog_owner_tbl.owner is '可清理属主';

create table TMPLOGLIST_INFO(
table_owner VARCHAR2(30),
TABLE_NAME VARCHAR2(30),
CREATE_date DATE default sysdate,
table_type_code VARCHAR(4),
PURPOSE VARCHAR2(400),
APPLICATION VARCHAR2(50),
sub_app_code VARCHAR2(4),
CLEAR_STRATEGY VARCHAR2(400),
exec_condition varchar2(200),
CLEAR_FREQUENCY NUMBER,
PROGRAM_ID VARCHAR2(2),
LAST_CLEAR_date DATE,
UPPER_LIMIT NUMBER default 1073741824,
ALERT_LEVEL VARCHAR2(10) default 'minor',
AUTOCLEAR_flag VARCHAR2(2),
UPPER_EXTENTS NUMBER default 1000,
REMARK VARCHAR2(1200),
constraint pk_TMPLOGLIST_INFO_owntblname primary key (table_owner,table_name),
constraint fk_tmploglist_info_tblowner foreign key (table_owner) references tmplog_owner_tbl(owner),
constraint fk_TMPLOGLIST_INFO_subappcode foreign key (sub_app_code) references sub_app_code_tbl(sub_app_code),
constraint fk_TMPLOGLIST_INFO_tablecode foreign key (table_type_code) references tmplog_table_type_tbl (table_type_code),
constraint fk_TMPLOGLIST_INFO_PROGRAMID foreign key (PROGRAM_ID) references CLEARPROGRAM (PROGRAM_ID),
constraint ck_TMPLOGLIST_INFO_alertlev check (alert_level in ('warning','minor','major','critical')),
constraint ck_tmploglist_info_upplimit check (upper_limit -20000,
msg => '配置表信息中的属主和表名不能修改!');
end;
/

create index ix_TMPLOGLIST_INFO_lastdate on TMPLOGLIST_INFO(LAST_CLEAR_date);
create index ix_TMPLOGLIST_INFO_table_name on TMPLOGLIST_INFO(table_name);
create index ix_TMPLOGLIST_INFO_subappcode on TMPLOGLIST_INFO(sub_app_code);
create index ix_CLEARDETAIL_LOG_tbl_ownname on CLEARDETAIL_LOG(table_OWNER,table_name);
create index ix_CLEARDETAIL_LOG_STARTTIME on CLEARDETAIL_LOG(START_TIME);
create index ix_CLEARDETAIL_LOG_clear_seq on CLEARDETAIL_LOG(clear_seq);

create sequence seq_tmplog_cleanclear
increment by 1
start with 1
maxvalue 99999999999999
nocache
cycle;

create public synonym tmploglist_info for appmgr.tmploglist_info;
create public synonym sub_app_code_tbl for appmgr.sub_app_code_tbl;
create public synonym tmplog_table_type_tbl for appmgr.tmplog_table_type_tbl;
create public synonym clearprogram for appmgr.clearprogram;
create public synonym tmplog_owner_tbl for appmgr.tmplog_owner_tbl;
create public synonym tmplogmonitor_log for appmgr.tmplogmonitor_log;
create public synonym cleardetail_log for appmgr.cleardetail_log;

CREATE OR REPLACE PACKAGE tmplog_clear IS
--日志表和临时表自动清理

--清理主程序
PROCEDURE clear_main;

--truncate清理
PROCEDURE autoclear_truncate(p_owner_name IN VARCHAR2,
p_tab_name IN VARCHAR2,
p_flag OUT VARCHAR2,
p_errmsg OUT VARCHAR2);
--delete清理
PROCEDURE autoclear_del(p_owner_name IN VARCHAR2,
p_tab_name IN VARCHAR2,
p_condition IN VARCHAR2,
p_flag OUT VARCHAR2,
p_errmsg OUT VARCHAR2);
--写清理日志
PROCEDURE add_clearlog(p_clear_seq NUMBER,
p_owner_name VARCHAR2,
p_tab_name VARCHAR2,
p_begin_time DATE,
p_end_time DATE,
p_exec_result VARCHAR2,
p_err_message VARCHAR2,
p_sid VARCHAR2,
p_alert_level VARCHAR2,
p_application VARCHAR2,
p_sub_app_name VARCHAR2,
p_error_type VARCHAR2); --guolei010 20071015 增加错误类型

--获取表的存储信息
PROCEDURE monitor_table(p_owner_name IN VARCHAR2,
p_tab_name IN VARCHAR2,
p_extents OUT NUMBER,
p_bytes OUT NUMBER,
p_tbs_name OUT VARCHAR2,
p_extent_management OUT VARCHAR2,
p_flag OUT VARCHAR2,
p_errmsg OUT VARCHAR2);

--将表的存储信息记入日志表
PROCEDURE add_monitor_log(p_monitor_date date,
p_sid varchar2,
p_owner_name varchar2,
p_tab_name varchar2,
p_extents number,
p_bytes number,
p_tbs_name varchar2,
p_extent_management varchar2,
p_flag out varchar2,
p_errmsg out varchar2);

--判断表是否异常
procedure is_normal(p_sid in varchar2,
p_owner_name in varchar2,
p_tab_name in varchar2,
p_flag out varchar2,
p_errmsg out varchar2);

--判断是否需要清理
procedure need_clear(p_sid in varchar2,
p_owner_name in varchar2,
p_tab_name in varchar2,
p_flag out varchar2,
p_errmsg out varchar2);

END tmplog_clear;
/

CREATE OR REPLACE PACKAGE BODY tmplog_clear IS
--日志表和临时表自动清理

--upper_extent number := 1000; --guolei010 20071010 不再使用常量
rows_per_delete number := 5000;

PROCEDURE clear_main IS
--清理主程序
v_owner tmploglist_info.table_owner%TYPE;
v_table_name tmploglist_info.table_name%TYPE;
v_exec_condition tmploglist_info.exec_condition%TYPE;
v_clear_frequency tmploglist_info.clear_frequency%TYPE;
v_program_id tmploglist_info.program_id%TYPE;
v_last_clear_date tmploglist_info.last_clear_date%TYPE;
v_upper_limit tmploglist_info.upper_limit%TYPE;
v_alert_level tmploglist_info.alert_level%TYPE;
v_autoclear_flag tmploglist_info.autoclear_flag%TYPE;
v_application tmploglist_info.application%TYPE;
v_sub_app_code tmploglist_info.sub_app_code%TYPE;
v_sub_app_name sub_app_code_tbl.description%TYPE;
v_clear_seq cleardetail_log.clear_seq%TYPE;
v_flag cleardetail_log.exec_result%TYPE;
v_errmsg cleardetail_log.error_message%TYPE;
v_begin_time cleardetail_log.start_time%TYPE;
v_end_time cleardetail_log.end_time%TYPE;
v_sid cleardetail_log.DATABASE%TYPE;
v_error_type cleardetail_log.error_type%TYPE; --guolei010 增加错误类型 20071015

CURSOR tmplog_list IS
SELECT table_owner,
table_name,
exec_condition,
clear_frequency,
program_id,
last_clear_date,
upper_limit,
alert_level,
autoclear_flag,
application,
sub_app_code
FROM tmploglist_info;

BEGIN

SELECT instance_name INTO v_sid FROM v$instance;
select seq_tmplog_cleanclear.nextval into v_clear_seq from dual;

--取配置信息表中所有的日志表和临时表
OPEN tmplog_list;
LOOP
FETCH tmplog_list
INTO v_owner, v_table_name, v_exec_condition, v_clear_frequency, v_program_id, v_last_clear_date, v_upper_limit, v_alert_level, v_autoclear_flag, v_application,v_sub_app_code;
EXIT WHEN tmplog_list%NOTFOUND;

select description into v_sub_app_name from sub_app_code_tbl where sub_app_code=v_sub_app_code;

--判断是否可以自动清理
need_clear(v_sid, v_owner, v_table_name, v_flag, v_errmsg);

--如果可以自动清理
IF (v_flag = 'Y') THEN
IF (v_program_id = '00') OR
(v_program_id = '01' AND v_exec_condition IS NOT NULL) THEN
SELECT SYSDATE INTO v_begin_time FROM dual;
IF v_program_id = '00' THEN
--采取t runcate方式清理
autoclear_truncate(v_owner, v_table_name, v_flag, v_errmsg);
ELSIF v_program_id = '01' THEN
--采取delete方式清理
autoclear_del(v_owner,
v_table_name,
v_exec_condition,
v_flag,
v_errmsg);
END IF;
SELECT SYSDATE INTO v_end_time FROM dual;

--如果清理成功，更新清理完成时间
IF (v_flag = 'Y' AND v_errmsg IS NULL) THEN
UPDATE tmploglist_info
SET last_clear_date = v_end_time
WHERE table_owner = v_owner
AND table_name = v_table_name;
END IF;
IF (v_flag = 'Y') THEN
add_clearlog(v_clear_seq,
v_owner,
v_table_name,
v_begin_time,
v_end_time,
v_flag,
v_errmsg,
v_sid,
v_alert_level,
v_application,
v_sub_app_name,
NULL);
ELSIF (v_flag = 'N') THEN
v_error_type:='0'; --guolei010 增加错误类型 20071015
--记录清理日志
add_clearlog(v_clear_seq,
v_owner,
v_table_name,
v_begin_time,
v_end_time,
v_flag,
v_errmsg,
v_sid,
v_alert_level,
v_application,
v_sub_app_name,
v_error_type);
END IF;

--按delete方式清理，但没有清理条件，则不自动清理，需修改配置信息表
ELSIF (v_program_id = '01' AND v_exec_condition IS NULL) THEN
v_error_type:='2'; --guolei010 增加错误类型 20071015
add_clearlog(v_clear_seq,
v_owner,
v_table_name,
sysdate,
sysdate,
'N', --guolei010 修改为'N'
'没有where条件,建议采用truncate清理方式!',
v_sid,
v_alert_level,
v_application,
v_sub_app_name,
v_error_type);
END IF;
ELSIF (v_errmsg = '该表不存在或是全局临时表') THEN
v_error_type:='2';
add_clearlog(v_clear_seq,
v_owner,
v_table_name,
sysdate,
sysdate,
v_flag,
v_errmsg,
v_sid,
v_alert_level,
v_application,
v_sub_app_name,
v_error_type);

END IF;

--判断表大小是否异常
is_normal(v_sid, v_owner, v_table_name, v_flag, v_errmsg);
if (v_flag 'Y' and v_errmsg'已报异常') then
--如果异常，记录异常信息
v_error_type:='1'; --guolei010 增加错误类型 20071015
add_clearlog(v_clear_seq,
v_owner,
v_table_name,
sysdate,
sysdate,
v_flag,
v_errmsg,
v_sid,
v_alert_level,
v_application,
v_sub_app_name,
v_error_type);
end if;

COMMIT;

END LOOP;
CLOSE tmplog_list;

END clear_main;

--truncate清理
PROCEDURE autoclear_truncate(p_owner_name IN VARCHAR2,
p_tab_name IN VARCHAR2,
p_flag OUT VARCHAR2,
p_errmsg OUT VARCHAR2) IS
v_sql VARCHAR2(400);
BEGIN
v_sql := 'truncate table ' || p_owner_name || '.' || p_tab_name || '';
EXECUTE IMMEDIATE v_sql;
p_flag := 'Y';
p_errmsg := NULL;
EXCEPTION
WHEN OTHERS THEN
p_flag := 'N';
p_errmsg := substrb(SQLERRM, 1, 400);
END autoclear_truncate;

--delete清理
PROCEDURE autoclear_del(p_owner_name IN VARCHAR2,
p_tab_name IN VARCHAR2,
p_condition IN VARCHAR2,
p_flag OUT VARCHAR2,
p_errmsg OUT VARCHAR2) IS
v_sql VARCHAR2(1000);
BEGIN
v_sql := 'lock table '||p_owner_name||'.'||p_tab_name||' in exclusive mode nowait';
EXECUTE IMMEDIATE v_sql;
LOOP
v_sql := 'delete from ' || p_owner_name || '.' || p_tab_name ||
' where rownum