FAQ
Hi,

I had a quick question for a intro DB class that I am teaching, and
using Oracle.

My students some times run into this problem --

They perform a DDL statement, say drop table in one session. They then
lose the internet connection or something happens. So somehow it ends
up locking the table.

Then restart their client and try to perform the same DDL operation --
it comes back with a ORA-00054 error message, and they cannot
continue. I think they wait for one hour or so before the lock is
released.

My question is -- how do I specify a duration for the lock? probably I
can specify it for the entire database for all users for all their DDL
operations?
or is there a way of specifying for each DDL (or can even be DML)
statements, the duration to hold the lock..?

Note: students who are getting the ORA-00054 operation, I do not want
to give them SYS privileges.
Just to inform you, if things work normally, the drop table should
finish in a couple of seconds.

Remember I said this is for a class. So some students end up dropping
the table and creating it again multiple times (because I give the
scripts to create tables and populating data).

again, I was wondering if there is some way as an administrator, I can
set up so that the locks will not persist for a long time, and I do
not get email from students saying -- their create table is giving
them ORA-00054 error..
can I change the lock duration?? I was looking up --
http://www.morganslibrary.com/reference/startup_parms.html -- I could
not seem to find what parameter I can set up??

Or is there a script that I can ask students to use for dropping a
table, so that it locks only for a short duration..

thanks, murali.

Search Discussions

  • Taral Desai at Mar 23, 2011 at 4:17 pm
    *
    *
    alter sesion set ddl_lock_timeout
    On Tue, Mar 22, 2011 at 8:26 PM, Mani wrote:

    ORA-00054
    --
    Thanks & Regards,
    Taral Desai

    --
    http://www.freelists.org/webpage/oracle-l
  • Thomas Day at Mar 24, 2011 at 1:11 pm
    Live and learn. New with Oracle 11. However, this will still result in an
    ORA-00054, only quicker. I'm not sure that it will resolve the situation
    since the original (unconnected) server session isn't the one with the
    ddl_lock issue. It's problem is that it can't connect to its client. You
    still need to kill that session on the server.
    On Wed, Mar 23, 2011 at 12:17 PM, Taral Desai wrote:

    *
    *
    alter sesion set ddl_lock_timeout
    On Tue, Mar 22, 2011 at 8:26 PM, Mani wrote:

    ORA-00054



    --
    Thanks & Regards,
    Taral Desai
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 23, '11 at 1:26a
activeMar 24, '11 at 1:11p
posts3
users3
websiteoracle.com

3 users in discussion

Thomas Day: 1 post Mani: 1 post Taral Desai: 1 post

People

Translate

site design / logo © 2022 Grokbase