Oct 10, 2016

on 2 comments

Compile invalid objects hangs forever


Using adadmin to compile invalid objects. Process never ended and so it was killed. Run again and it kept happening.  What to do next ?

Another similar situation is like:

Finished running $ORACLE_HOME\rdbms\admin\utlirp.sql, and then run utlrp.sql to recompile all objects.  The session got killed in the middle for whatever reason, and tried to run it again. 

Under this situation, you cannot stop the jobs (it says it's not running), and drop the jobs (it says it's running !!).  Bounce the database doesn't help.

A couple days ago a DBA approached to me and asked for help.  Fortunately I had this one before.  Not sure it's a bug or not and my solution is as follows (probably not the best approach but I'm sure it works):

Connect to the database as sysdba

Use this query to find out the JOB_NAME responsible for recompile the objects. Usually it's called "UTL_RECOMP_SALVE_#".
SELECT * FROM DBA_SCHEDULER_RUNNING_JOBS

Use this query to find out the OBJ# of those jobs:
SELECT * FROM SCHEDULER$_JOB

Change the status to 1 for those jobs :
UPDATE SCHEDULER$_JOB SET JOB_STATUS = 1 WHERE OBJ# in (12345,12346)

And then you should able to drop the jobs by
EXEC DBMS_SCHEDULER.DROP_JOB('UTL_RECOMP_SLAVE_2',  force => TRUE);
EXEC DBMS_SCHEDULER.DROP_JOB('UTL_RECOMP_SLAVE_3',  force => TRUE);

Then you should able to continue the adadmin -> compile apps schema or the utlrp.sql script.

2 comments:

educational blogs said...

I am so happy after read your blog. It’s a very useful blog for us.

Java Corporate training for employees in Nigeria

Anonymous said...

Thank you. I'd been trying to solve that problem for three days before I found your post.