Link to home
Start Free TrialLog in
Avatar of joe_echavarria
joe_echavarriaFlag for Dominican Republic

asked on

Blocking session and procedures running.

Hi,

  Every time i bounce the database this blocking sessions occurs and it never ends.   When checking the contents of the SQL ID Text, is about one procedure running.

  I want to know how to stop this from running automatically ?   and how to stop it from starting every time i start the database ? , also how to stop it now.

  This is a 2TB database, is a big one.

CREATE OR REPLACE PROCEDURE SMARTSOLVE.pilgrim_sync_all_indexes IS

CURSOR c1 IS
SELECT pnd_index_name,
       COUNT(*) num_rows
  FROM ctxsys.ctx_user_pending
GROUP BY pnd_index_name;

dStartTime    DATE;
nErrorCount   NUMBER;

BEGIN
   FOR r1 IN c1 LOOP

      dStartTime := SYSDATE;
      pilgrim_synchronize_index (r1.pnd_index_name);

   END LOOP;
END;


CREATE OR REPLACE PROCEDURE SMARTSOLVE.pilgrim_synchronize_index
              (pIndexName IN VARCHAR2) IS
--Sync the index. To be run as CTXSYS.
BEGIN

   CTX_DDL.SYNC_INDEX(pIndexName);

END;
SQL-TEXT-01.jpg
SQL-TEXT-02.jpg
blocking_session01.jpg
Avatar of joe_echavarria
joe_echavarria
Flag of Dominican Republic image

ASKER

How to know if it is a job ? , or where to find any logs of the job ?
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am agree with you.    I know it has to run.

What happens is that it should end at some point.   It never ends.

Also i want to know how is being kicking off to run.

It appears in the dba_jobs also in the dba_jobs_running, but not in the dba_scheduler_jobs.

Is there any configuration to start a job when the database first start ?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
select * from ctxsys.ctx_user_pending    , returns no rows at all.

If the database "remembers" the jobs that were running previous the shutdown, maybe this could be the case.

I have even killed  the job, and then it appears running again.

The software vendor has no answer yet.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Well, is not a bad thing, it is a strange behavior on the first glance.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial