OraFAQ Forum: Server Administration » ORA-04021:

Home » RDBMS Server » Server Administration » ORA-04021: (oracle 10.2.0.2.0,Unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-04021: [message #508992]

Wed, 25 May 2011 09:02

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Hi,

When i try to compile a package, im getting below error.

ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object

i gave below query and found the sid of object.

select * from v$access where object='THINKING_PKG'

From v$session view,i found sid and serial# and at same time i queried
dba_jobs and saw one jobs is running at this time.

Now how to compile this object.Can somebody explain me on this and give solution to this.

Thanks

Report message to a moderator

Re: ORA-04021: [message #508993 is a reply to message #508992]

Wed, 25 May 2011 09:06

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Wait the other session ends.

Regards
Michel

Report message to a moderator

Re: ORA-04021: [message #508994 is a reply to message #508992]

Wed, 25 May 2011 09:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

04021, 00000, "timeout occurred while waiting to lock object %s%s%s%s%s"
// *Cause: While waiting to lock a library object, a timeout occurred.
// *Action: Retry the operation later

Report message to a moderator

Re: ORA-04021: [message #508998 is a reply to message #508993]

Wed, 25 May 2011 09:12

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

How to check other session ends ?

Report message to a moderator

Re: ORA-04021: [message #509000 is a reply to message #508998]

Wed, 25 May 2011 09:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>From v$session view,i found sid and serial#
#1) query V$SESSION
#2) when no rows returned, then exit
#3) wait a while
#4) goto #1

Report message to a moderator

Re: ORA-04021: [message #509003 is a reply to message #509000]

Wed, 25 May 2011 09:22

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

i queried v$session view and it returned 1 row.

Report message to a moderator

Re: ORA-04021: [message #509004 is a reply to message #509003]

Wed, 25 May 2011 09:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>dba_jobs and saw one jobs is running at this time.
who owns this job?
what is this job doing?

It is your DB so you are the one who needs to control what happens.
We can't do anything to change what your DB does or does not do.

Report message to a moderator

Re: ORA-04021: [message #509145 is a reply to message #509004]

Thu, 26 May 2011 05:14

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

This job is owned by Sysman. Is it not possible to fix sysman object ?

Thanks

Report message to a moderator

Re: ORA-04021: [message #509147 is a reply to message #509145]

Thu, 26 May 2011 05:15

cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester

Senior Member

What is sysman?
What does the job do?

Report message to a moderator

Re: ORA-04021: [message #509155 is a reply to message #509147]

Thu, 26 May 2011 05:43

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

SYSMAN
----------
By default during the installation of Oracle Enterprise Manager, one Super Administrator
account is created with the user name of SYSMAN. The SYSMAN account should be used
to perform infrequent system-wide, global configuration tasks such as setting up
the environment. Other administrator accounts can be created for daily
administration work. The SYSMAN account is:

Owner of the Management Repository schema
Default Enterprise Manager Super Administrator
User name used to log in to Enterprise Manager the first time

The job does the following

The procedure inside the package are used to start and stop EM jobs

The procedure is used for moving repository from one table space to another

PROCEDURE PARTITION_MAINTENANCE

The procedure sets the version, compatibility version and mode of operation
for a repository component

The procedure grants the SDK privilege to the specified user PROCEDURE
create_sdk_synonyms;

The following routines perform partition maintenance

set the reschedule execs on new jobtype flag in MGMT_PARAMETERS (Procedure)

check if central mode and set reschedule execs on new jobtype flag

removes procedure the reschedule execs on new jobtype flag

return function whether the reschedule execs on new jobtype flag is set or not

Thanks

Report message to a moderator

Re: ORA-04021: [message #509156 is a reply to message #509147]

Thu, 26 May 2011 05:45

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Its already One day old Question!
@ Op Do you have the issue still ?
Did you read Michel`s Response
Quote:

Wait the other session ends.

Did you check who is blocking the object ?

@ cookie
That might be a Internal maintenance Job(not created by Normal DB user) he is talking about.
OOPS Network problem I was late Smile

Smile

[Updated on: Thu, 26 May 2011 05:45]

Report message to a moderator

Re: ORA-04021: [message #509161 is a reply to message #509156]

Thu, 26 May 2011 06:00

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

1.Do you have the issue still ?
--yes
2.Did you read Michel`s Response
Quote:
Wait the other session ends.
--I waited for long time and then tried but the result is same.

3.Did you check who is blocking the object ?
yes.As you said it could be a maintenance job

4.S Network problem I was late Smile

I dont its a network problem.

Thanks

Report message to a moderator

Re: ORA-04021: [message #509164 is a reply to message #509161]

Thu, 26 May 2011 06:10

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

balaji14 wrote on Thu, 26 May 2011 16:30

3.Did you check who is blocking the object ?
yes.As you said it could be a maintenance job

4.S Network problem I was late Smile

I dont its a network problem.

Thanks

---> #3 As you verified ...Who is blocking ?
You said this happend when You tried to compile a package...
Did you try it now ?
query v$session_wait and v$session for the event 'library cache lock '

--->#4 That is not for you ...I said I am late( you posted your previous response just before 2 min than me).

Report message to a moderator

Re: ORA-04021: [message #509181 is a reply to message #509164]

Thu, 26 May 2011 07:10

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Yes i tried .But same error.

I guess this error could be due to job running parallely.

Report message to a moderator

Re: ORA-04021: [message #509183 is a reply to message #509181]

Thu, 26 May 2011 07:20

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Quote:

query v$session_wait and v$session for the event 'library cache lock '

have you tried this ?post the results..

Sriram

Report message to a moderator

Re: ORA-04021: [message #509186 is a reply to message #509183]

Thu, 26 May 2011 07:22

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

What is the exact query for this v$session am v$session_wait?

Thanks

Report message to a moderator

Re: ORA-04021: [message #509264 is a reply to message #509186]

Thu, 26 May 2011 13:23

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

babu.knb@gmail.com

I think this thread is going different way. When you say there is some SYSMAN JOBs running & your checking v$locked_object.

We would like to know why are you looking / comparing into SYSMAN jobs & THINKING_PKG

Do you have any relation between system & user object. As we already told you need to compare your objects into V$SESSION & V$SESSION_WAIT.

Report message to a moderator

Re: ORA-04021: [message #509325 is a reply to message #509264]

Fri, 27 May 2011 03:17

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

I gave the below query and it returned with 3 rows sql_hash_value,
event and count(1)

Parallely i executed the package in another session.

select b.sql_hash_value,a.event evn,count(1)
from v$session_wait a, v$session b
where b.status='ACTIVE'
and a.sid=b.sid
and b.username='BALA'
group by b.sql_hash_value,a.event
order by 3

Should i modify the query to find more details? and may i know
the reason to query v$session and v$session_wait view for the event
'library cache lock ?

Thanks

Report message to a moderator

Re: ORA-04021: [message #509326 is a reply to message #509325]

Fri, 27 May 2011 03:21

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use SQL*Plus and copy and paste your session.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: ORA-04021: [message #509327 is a reply to message #509326]

Fri, 27 May 2011 03:28

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

select b.sql_hash_value,a.event evn,count(1)
from v$session_wait a, v$session b
where b.status='ACTIVE'
and a.sid=b.sid
and b.username='BALA'
group by b.sql_hash_value,a.event
order by 3

SQL_HASH_VALUE EVN
-------------- -------------------------------------
COUNT(1)
----------
3913086194 SQL*Net message from dblink
1

76692386 SQL*Net message to client
1

Report message to a moderator

Re: ORA-04021: [message #509361 is a reply to message #509327]

Fri, 27 May 2011 06:27

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

To go at it from another angle:

SYSMAN is accessing the package you are trying to compile? Why? That would mean that eiter:

1) Sysman is accessing one of your user packages, which would be highly unlikely
2) You are trying to recompile a package that is part of the Enterprise Manager, which would make not much sense either.

One option would be to stop the Enterprise Manager service and then see if the access to that package is gone.

Report message to a moderator

Re: ORA-04021: [message #509385 is a reply to message #509361]

Fri, 27 May 2011 07:28

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Thomas,

"You are trying to recompile a package that is part of the Enterprise Manager,
which would make not much sense either."

How do you say that package is part of Enterprise Manager(SYSMAN)
Please explain.

"One option would be to stop the Enterprise Manager service and then see if the
access to that package is gone."

If that is the case, then how to stop the Enterprise Manager service.

Thanks

Report message to a moderator

Re: ORA-04021: [message #509391 is a reply to message #509385]

Fri, 27 May 2011 07:58

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Well, I'm not sure how I should explain that. That's kinda like explaining how you can say "those eggs are in that basket" when you see that eggs are in a basket. A package is part of an application when it was written for that application.

And how to start and stop the Enterprise Manager is all described in great length in the documentation, no sense in repeating it here over and over.

Report message to a moderator

Re: ORA-04021: [message #509441 is a reply to message #509391]

Fri, 27 May 2011 11:22

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

babu.knb@gmail.com

>>How do you say that package is part of Enterprise Manager(SYSMAN) Please explain.

So still you did not checked the package code? do you check the package belongs to which owner?

Babu

Report message to a moderator

Re: ORA-04021: [message #509444 is a reply to message #509385]

Fri, 27 May 2011 15:18

cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester

Senior Member

balaji14 wrote on Fri, 27 May 2011 13:28

Thomas,

"You are trying to recompile a package that is part of the Enterprise Manager,
which would make not much sense either."

How do you say that package is part of Enterprise Manager(SYSMAN)
Please explain.

We were under the assumption this package you're trying to recompile is one of yours, not an oracle supplied package. Is that not the case?

Report message to a moderator

Re: ORA-04021: [message #509499 is a reply to message #509444]

Sat, 28 May 2011 14:06

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Thank you all for your inputs.I got some idea as why i was unable to compile the package.

Report message to a moderator

Re: ORA-04021: [message #509519 is a reply to message #509444]

Sun, 29 May 2011 02:46

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Solution to compile the package:

1.Shutdown database
comment two oracle process in init parameter file
Job queue process
AQTM
2.startup database
perform compile package
3.shutdown database
4.uncomment the oracle process
5.startup database.

Another solution is to stop the oracle Enterprise Manager service and perform compilation.

Please correct if the above steps are incorrect.

Thanks

Report message to a moderator

Previous Topic:	Database Shutdown
Next Topic:	redo log buffer using

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 12:17:08 CST 2024