Thanks for the question, KHALID.
Asked: June 30, 2020 - 3:57 am UTC
Last updated: July 05, 2020 - 9:07 am UTC
Version: 19.6.0.0.0
Viewed 1000+ times
You Asked
Hi AskTom team,
Originally I had a very nice question for you, but I forgot, but this one is equally important to me. My question is about why does my recompile "SQL> @?/rdbms/admin/utlrp.sql" take forever to return? there is no real users on my database yet, except that I am running utlrp as SYSDBA. I am importing several schemas at a time from another db, and every now and then I re-compile all. The previous ones in the afternoon returned promptly, but not this time.
Right now it is 11:42 PM, 06/29 ok? and I issued that command at: <hold-your-breath-please...> COMP_TIMESTAMP UTLRP_BGN 2020-06-29 22:51:00
What is "PL/SQL lock timer" event?
and is there anything that I can do return results faster? for now I will just leave it running and look at it in the morning.
Would you look at my query against v$session and let me know what can be done?
( And oh by the way my query just returned, but my question to you still remains because I have seen this before, and likely to see this again.)
-- Query against v$session and results below
SELECT username, sql_id, prev_sql_id, module, logon_time, last_call_et, event, wait_time_micro/1000000 secs
FROM
v$session where status='ACTIVE' and username is not NULL and username != USER ;
-- I have seperated it out one column at a time to make it readable, the last (3rd) row is mine (the sqlplus command)
SYS
dynvx9wypcbtv
b0hxh61dtxc25
DBMS_SCHEDULER
06/29/2020 22:51:27
2786
single-task message
83.501024
SYS
dzbggb6bmyfdx
null
06/29/2020 22:23:28
4464
OFS idle
2.952462
SYS
3cmwr02yfw7ud
7nppm0f50r36n
sqlplus@itad121.it-dev-db.aws.umd.edu (TNS V1-V3
06/29/2020 22:28:01
2813
PL/SQL lock timer
0.400026
Thanks thanks thanks
and Connor said...
utlrp.sql runs in parallel by spawning off child processes to do its work. The parent then waits until all the children are complete and loop and sleeps on a lock during that time, so you'll see PL/SQL lock time.
If it "never" comes back, it normally means somehow the parent has lost touch with one or more of the children. Typically you can just kill off the session and give it another run. No damage in doing that. You can also query dba_objects to see if there are still any invalid objects.
Hope this helps.
Is this answer out of date? If it is, please let us know via a Comment