OraFAQ Forum: General » DDL Not working

Home » Other » General » DDL Not working (oracle 10g, 10.2.0.5, windows server 2008)

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

DDL Not working [message #579793]

Fri, 15 March 2013 07:22

shahidjee7
Messages: 11
Registered: December 2010
Location: Pakistan

Junior Member

dear all,

DDL command not working and there is no error in alert logfile. when i compile any object session hanged and never come back. Is there any other object status invalid in sys schema? The only thing i did was i clicked on the compile depandencies of a package.

Report message to a moderator

Re: DDL Not working [message #579794 is a reply to message #579793]

Fri, 15 March 2013 07:28

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Did you do that (compilation) when users were active on the database? If so, that might be a problem; try to do that when nobody is working.

Report message to a moderator

Re: DDL Not working [message #579795 is a reply to message #579794]

Fri, 15 March 2013 07:40

shahidjee7
Messages: 11
Registered: December 2010
Location: Pakistan

Junior Member

i compile the objects in restrict mode but the same result.

Report message to a moderator

Re: DDL Not working [message #579798 is a reply to message #579795]

Fri, 15 March 2013 08:15

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

Senior Member
Account Moderator

Which DDL command?

Quote:

Is there any other object status invalid in sys schema?

Check it.

Quote:

The only thing i did was i clicked on the compile depandencies of a package.

Use SQL*Plus; are you sure this is not your tool that "hangs"?

Regards
Michel

[Updated on: Fri, 15 March 2013 08:17]

Report message to a moderator

Re: DDL Not working [message #579799 is a reply to message #579795]

Fri, 15 March 2013 08:16

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Do it in sqlplus, not some gui that hide all helpful information.
do the command in one session and then in another session if it is still hung, check for locks, etc.
Here is a sample small piece of code I use sometimes.

  SELECT (SELECT username
              FROM v$session
             WHERE SID = a.SID) blocker, a.SID, ' is blocking ',
           (SELECT username
              FROM v$session
             WHERE SID = b.SID) blockee, b.SID
      FROM v$lock a, v$lock b
     WHERE a.BLOCK = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2
/

Report message to a moderator

Re: DDL Not working [message #579806 is a reply to message #579799]

Fri, 15 March 2013 09:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>when i compile any object session hanged and never come back
we have procedure that does DML but does not COMMIT or ROLLBACK & procedure gets executed.
After this nobody can recompile this procedure & will just hang.
The same session that did the DML must COMMIT or ROLLBACK the changes before anyone is allowed to recompile this procedure.
Terminating or logging out all session, results is a ROLLBACK so that procedure can be compiled once again.

Report message to a moderator