Rem
Rem $Header: whdev/2.0/owb/shiphome/owb/patches/bug9955095/fix9955095.sql /main/2 2011/06/02 03:05:28 yuhhuang Exp $
Rem
Rem fix9955095.sql
Rem
Rem Copyright (c) 2010, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem

set serveroutput on size 999999
set line 300

ACCEPT tndex DEFAULT 'USERS' PROMPT 'Tablespace for creating indexes :[USERS] '

@../../rtasst/wb_rt_execute_pv.sql;
@../../rtasst/roles_object_rights.sql;

CREATE OR REPLACE FORCE VIEW UAB_RT_EXEC_PROC_RUN_COUNTS_T (TOP_LEVEL_AUDIT_EXECUTION_ID, TOP_ID_TYPE, NUMBER_ERRORS) AS 
SELECT
e.top_level_audit_execution_id AS top_level_audit_execution_id,
'X' as top_id_type,
nvl(x.err,0) AS number_errors
FROM owb$wb_rt_audit_executions e,
     (SELECT e.audit_execution_id AS exec_id,
             decode(SUM(a.rta_errors), null, 0, SUM(a.rta_errors)) as err 
      FROM owb$wb_rt_audit_executions e, OWB$WB_RT_AUDIT a
      WHERE e.audit_execution_id = a.rte_id
      AND e.workspace_id = a.workspace_id
      AND (a.workspace_id = wb_workspace_management.get_workspace_id or a.workspace_id = 0 or e.workspace_id is null) 
      AND wb_workspace_management.has_system_privilege_int('CONTROL_CENTER_VIEW') != 0
      GROUP BY e.audit_execution_id) x
WHERE
  e.audit_execution_id = x.exec_id (+)
union all
select TOP_LEVEL_AUDIT_EXECUTION_ID, TOP_ID_TYPE, NUMBER_ERRORS from hab_rt_exec_proc_run_counts
WITH READ ONLY
;

GRANT SELECT ON UAB_RT_EXEC_PROC_RUN_COUNTS_T TO OWB_USER;

GRANT SELECT ON WB_RT_AUDIT_EXECUTIONS TO OWB_USER;
GRANT SELECT ON WB_RT_AUDIT TO OWB_USER;
GRANT SELECT ON WB_RT_DEF_OBJECT_TYPES TO OWB_USER;
GRANT SELECT ON WB_RT_DEF_EXECUTION_OPERATORS TO OWB_USER;
GRANT SELECT ON WB_RT_AUDIT_STRUCT TO OWB_USER;
GRANT SELECT ON WB_RT_AUDIT_DETAIL TO OWB_USER;
GRANT SELECT ON WB_RT_DEF_STORE_TYPES TO OWB_USER; 
COMMIT;

CREATE INDEX AUDIT_STATUS_INDEX ON OWB$WB_RT_AUDIT_EXECUTIONS (AUDIT_STATUS ASC) TABLESPACE &tndex;
COMMIT;

CREATE INDEX WB_RT_IDX_EX_ID_TOSU ON OWB$WB_RT_AUDIT_EXECUTIONS (AUDIT_EXECUTION_ID ASC, TASK_OBJECT_STORE_UOID ASC) TABLESPACE &tndex;
COMMIT;

CREATE INDEX WB_RT_IDX_EX_W_A_T ON OWB$WB_RT_AUDIT_EXECUTIONS (WORKSPACE_ID ASC, AUDIT_EXECUTION_ID ASC, TASK_OBJECT_STORE_UOID ASC) TABLESPACE &tndex;
COMMIT;

CREATE INDEX WB_RT_IDX_EX_W_A_E ON OWB$WB_RT_AUDIT_EXECUTIONS (WORKSPACE_ID ASC, AUDIT_EXECUTION_ID ASC, EXECUTION_STORE_UOID ASC) TABLESPACE &tndex;
COMMIT;

CREATE INDEX WB_RT_IDX_EX_W_A_EU ON OWB$WB_RT_AUDIT_EXECUTIONS (WORKSPACE_ID ASC, AUDIT_EXECUTION_ID ASC, EXECUTION_OBJECT_STORE_UOID ASC) TABLESPACE &tndex;
COMMIT;

CREATE INDEX WB_RT_IDX_EX_W_E_T ON OWB$WB_RT_AUDIT_EXECUTIONS (WORKSPACE_ID ASC, EXECUTION_NAME ASC, TASK_OBJECT_NAME ASC) TABLESPACE &tndex;
COMMIT;

CREATE INDEX WB_RT_IDX_EX_W_P_E ON OWB$WB_RT_AUDIT_EXECUTIONS (WORKSPACE_ID ASC, PARENT_AUDIT_EXECUTION_ID ASC, EXECUTION_OBJECT_TYPE_ID ASC) TABLESPACE &tndex;
COMMIT;

CREATE INDEX WB_RT_IDX_EX_E_O_I ON OWB$WB_RT_AUDIT_EXECUTIONS (EXECUTION_OBJECT_TYPE_ID ASC) TABLESPACE &tndex;
COMMIT;

CREATE INDEX WB_RT_IDX_EX_E_ALL ON OWB$WB_RT_AUDIT_EXECUTIONS (EXECUTION_OBJECT_TYPE_ID ASC, WORKSPACE_ID ASC, AUDIT_EXECUTION_ID ASC, EXECUTION_OBJECT_UOID ASC, EXECUTION_OBJECT_NAME ASC, EXECUTION_OBJECT_STORE_NAME ASC, EXECUTION_OBJECT_STORE_UOID ASC, AUDIT_STATUS ASC, RETURN_RESULT ASC, EXTERNAL_AUDIT_ID ASC, CREATION_DATE ASC, RETURN_RESULT_NUMBER ASC) TABLESPACE &tndex;
COMMIT;

CREATE INDEX WB_RT_IDX_W_EOSU_AS_EOU ON OWB$WB_RT_AUDIT_EXECUTIONS (WORKSPACE_ID ASC, EXECUTION_OBJECT_STORE_UOID ASC, AUDIT_STATUS ASC, EXECUTION_OBJECT_UOID ASC) TABLESPACE &tndex;
COMMIT;

CREATE INDEX WB_RT_AUDIT_STRUCT_W_RPOU ON OWB$WB_RT_AUDIT_STRUCT (WORKSPACE_ID ASC, RTT_PARENT_OPERATOR_UOID ASC) TABLESPACE &tndex;
COMMIT;