How can I stop Disco jobs (EUL5_BATCH_PACKAGExxx.RUN;) in dba_jobs?

DanCxATGxBDE

While applying EBS patch "HRMS Release Update Pack 9 for Release 12.1 Patch 21980909", we find that the patch application will always stall at about two hours into the adpatch run because it is trying to recreate the package HR_SECURITY.  This object is locked by a query being run by a discoverer related process.

The sql_text of the process holding the lock always looks similar to this:

    select sql_text

    from v$sqlarea

    where sql_id ='5y2zs1fg0d49x';

   

    SQL_TEXT
    -------------------------------------

    SELECT * FROM EUL5_B151109093718Q1V1

We can use "alter system kill session xxx", but I am concerned that this lacks elegance.  Also, if we kill this and do not restart the patch quickly enough then HR_SECURITY will be locked again by another, very similar Discoverer EUL process.  I suspect these are coming from dba_jobs which is loaded with several jobs for "EUL5_BATCH_PACKAGExxx.RUN" (where xxx is some number).  We can use exec dbms_job.broken() to stop these, but I have doubts that this is enough.  There is probably some nice way to do this from Discoverer, but I'm not familiar enough with this product to know what that navigation would be.

Can somehow who is familiar with Disco explain to me how to make it stop submitting jobs for awhile while I apply a patch?

-Dan

DanCxATGxBDE

Conclusion:

While applying EBS patch "HRMS Release Update Pack 9 for Release 12.1 Patch 21980909", we find that the patch application will always stall at about two hours into the adpatch run because it is trying to recreate the package HR_SECURITY.  The sql_text of the process holding the lock always looks similar to this:

    select sql_text

    from v$sqlarea

    where sql_id ='5y2zs1fg0d49x';

  

    SQL_TEXT

    -------------------------------------

    SELECT * FROM EUL5_B151109093718Q1V1

   

These are indeed Discoverer processes and are specifically "Discoverer Scheduled Workbooks".  EUL stands for "End User Layer" and is part of the Discoverer product. The EUL insulates Discoverer end users from the complexity usually associated with databases. It provides an intuitive, business-focused view of the database using terms that Discoverer end users are familiar with and can easily understand. This enables Discoverer end users to focus on business issues instead of data access issues.  The EUL is a collection of approximately 50 tables in the database.

Per Note:555612.1 -How and When Can You Delete Discoverer Scheduled Workbooks :

.

What objects are created when a workbook is scheduled ?

- A view is created containing the select statement to create the result tables (e.g.EUL5_B011206161506Q1V1)

- A table is created for each run with the results (e.g. EUL5_B011206161506Q1R1)

- A package is created with the run information (e.g. EUL5_BATCH_PACKAGE011206161506 )

- A job is created to call package at specified schedule intervals (e.g.EUL5_BATCH_PACKAGE011206161506.RUN)

.

All these objects have the same unique sequence number within the object name.

Note:429415.1 -How to Cancel or Delete a Scheduled Discoverer Report When Running

This is an outdated document, but does suggest that simpmly killing these sessions is harmful to Discoverer and gives a long list of steps on what should be updated to repair the damage.

Note:421696.1 -How To Disable All Discoverer Scheduled Workbooks Programatically

This note gives PLSQL with a simple cursor and for loop to go through the jobs table and declare all currently scheduled Discoverer

DECLARE CURSOR c1 IS

SELECT job

FROM all_jobs

WHERE what LIKE '%EUL5%';

BEGIN

  FOR i IN c1

  LOOP

    dbms_job.broken(i.job,   TRUE);

  END LOOP;

END;

It may be good enough to stop here with this as a solution since it appears to be a supported solution as per a published Oracle Support Document.

The Discoverer Administrator user can delete any scheduled workbook and/or result sets from Discoverer Administrator in Tools -> Manage Scheduled Workbooks. This is the more elegant solution.

-Dan

DanCxATGxBDE

Conclusion:

While applying EBS patch "HRMS Release Update Pack 9 for Release 12.1 Patch 21980909", we find that the patch application will always stall at about two hours into the adpatch run because it is trying to recreate the package HR_SECURITY.  The sql_text of the process holding the lock always looks similar to this:

    select sql_text

    from v$sqlarea

    where sql_id ='5y2zs1fg0d49x';

  

    SQL_TEXT

    -------------------------------------

    SELECT * FROM EUL5_B151109093718Q1V1

   

These are indeed Discoverer processes and are specifically "Discoverer Scheduled Workbooks".  EUL stands for "End User Layer" and is part of the Discoverer product. The EUL insulates Discoverer end users from the complexity usually associated with databases. It provides an intuitive, business-focused view of the database using terms that Discoverer end users are familiar with and can easily understand. This enables Discoverer end users to focus on business issues instead of data access issues.  The EUL is a collection of approximately 50 tables in the database.

Per Note:555612.1 -How and When Can You Delete Discoverer Scheduled Workbooks :

.

What objects are created when a workbook is scheduled ?

- A view is created containing the select statement to create the result tables (e.g.EUL5_B011206161506Q1V1)

- A table is created for each run with the results (e.g. EUL5_B011206161506Q1R1)

- A package is created with the run information (e.g. EUL5_BATCH_PACKAGE011206161506 )

- A job is created to call package at specified schedule intervals (e.g.EUL5_BATCH_PACKAGE011206161506.RUN)

.

All these objects have the same unique sequence number within the object name.

Note:429415.1 -How to Cancel or Delete a Scheduled Discoverer Report When Running

This is an outdated document, but does suggest that simpmly killing these sessions is harmful to Discoverer and gives a long list of steps on what should be updated to repair the damage.

Note:421696.1 -How To Disable All Discoverer Scheduled Workbooks Programatically

This note gives PLSQL with a simple cursor and for loop to go through the jobs table and declare all currently scheduled Discoverer

DECLARE CURSOR c1 IS

SELECT job

FROM all_jobs

WHERE what LIKE '%EUL5%';

BEGIN

  FOR i IN c1

  LOOP

    dbms_job.broken(i.job,   TRUE);

  END LOOP;

END;

It may be good enough to stop here with this as a solution since it appears to be a supported solution as per a published Oracle Support Document.

The Discoverer Administrator user can delete any scheduled workbook and/or result sets from Discoverer Administrator in Tools -> Manage Scheduled Workbooks. This is the more elegant solution.

-Dan

DanCxATGxBDE

Wow!  Thanks, Dan!  That was brilliant!