18.1 How to delete unwanted Integration Services (seen in wf Monitor)
1 Take back up of the following tables
Create table
OPB_SESS_TASK_LOG_20151020 as select * from OPB_SESS_TASK_LOG ;
Create table
OPB_SWIDGINST_LOG_20151020 as select * from OPB_SWIDGINST_LOG ;
Create table
OPB_DTL_SWIDG_LOG_20151020 as select * from OPB_DTL_SWIDG_LOG ;
Create table
OPB_TASK_INST_RUN_20151020 as select * from OPB_TASK_INST_RUN ;
Create table
OPB_WFLOW_VAR_RUN_20151020 as select * from OPB_WFLOW_VAR_RUN ;
Create table
OPB_WFLOW_DEP_RUN_20151020 as select * from OPB_WFLOW_DEP_RUN ;
Create table
OPB_WFLOW_CACHE_20151020 as select * from OPB_WFLOW_CACHE ;
Create table
OPB_WFLOW_RUN_20151020 as select * from OPB_WFLOW_RUN ;
2. Perform the below delete statements to delete log
tables.
DELETE FROM OPB_SESS_TASK_LOG;
DELETE FROM OPB_SWIDGINST_LOG;
DELETE FROM OPB_DTL_SWIDG_LOG;
DELETE FROM OPB_TASK_INST_RUN;
DELETE FROM OPB_WFLOW_VAR_RUN;
DELETE FROM OPB_WFLOW_DEP_RUN;
DELETE FROM OPB_WFLOW_CACHE;
DELETE FROM OPB_WFLOW_RUN;
COMMIT;
3. Execute the below query for the list of integration
services list under the repository
SELECT * FROM OPB_SERVER_INFO;
4. Delete the duplicate/unwanted integration service
based on the least server id
(ex: 2 – Integration_ABC, 5 - Integration_ABC, perform
delete on 2)
Delete from OPB_SERVER_INFO where server_id in ( 2,5)
-- least server id as it is outdated
18.2 Total Count of workflows assigned to each Integration Services
SELECT SERVER_NAME,
COUNT(1) AS WF_COUNT
FROM
(SELECT SUBJECT_ID,
WORKFLOW_NAME,
SERVER_NAME,
TRUNC(START_TIME) AS START_TIME,
RANK() OVER (PARTITION BY SUBJECT_ID,
WORKFLOW_NAME ORDER BY START_TIME DESC ) RNK
FROM OPB_WFLOW_RUN
)
WHERE RNK=1
GROUP BY SERVER_NAME
--ORDER BY 1,2,3
SELECT (SELECT REPOSITORY_NAME FROM
OPB_REPOSIT_INFO) AS REPO,
TRUNC (START_TIME) AS START_TIME,
A.SERVER_NAME AS INTEGRATION_SERVICE,
COUNT (*) WF_COUNT
FROM OPB_WFLOW_RUN A
WHERE TRUNC (START_TIME) = ( SELECT MAX (TRUNC (AA.START_TIME))
FROM
OPB_WFLOW_RUN AA
WHERE
AA.WORKFLOW_NAME = A.WORKFLOW_NAME
GROUP BY
A.WORKFLOW_NAME)
GROUP BY
SERVER_NAME,TRUNC (START_TIME)
--ORDER BY 1, 2, 3
18.3 List of workflows running on each Integration Service
select * from (
select
(SELECT repository_name FROM OPB_REPOSIT_INFO) AS REPO,
subject_id, workflow_name,server_name, trunc(start_time) as
start_time,
rank() over (partition by subject_id, workflow_name order by
start_time desc ) rnk
from OPB_WFLOW_RUN
) where rnk=1
order by 1,2,3