Re: Help wanted - Hangs up when creating index

From: Cong TANG <lai-tang_at_mycity.at>
Date: Tue, 28 Dec 1999 21:35:00 +0100
Message-ID: <38691EF4.7E08B27C@mycity.at>

A lock acquired by a transaction can be released if the user carrying the transaction issues the COMMIT or ROLLBACK command. Termination of a session is another way to release lock, but you may want to talk to the user before doing this. You can query the sys.v$locked_object or sys.v_$locked_object to see all locked objects. Example,

SQL> select lo.oracle_username,

  2   s.sid,
  3   s.serial#,
  4   o.owner,
  5   o.object_name,
  6   o.object_type,

  7 lo.locked_mode
  8 from dba_objects o, v$session s, sys.v_$locked_object lo   9 where lo.session_id = s.sid
 10 and lo.object_id = o.object_id
 11 /

ORACLE_USERNAME SID SERIAL# OWNER OBJECT_NAME OBJECT_TYPE LOCKED_MODE

-------------------- --------- --------- ---------- --------------------
-------------- -----------
SCOTT                       13         3 SCOTT      EMP
TABLE                    5

Meaning of the locked_mode:

Locked_mode            Meaning
--------------            ---------------------
1                                NULL
2                                Row share
3                                Row exclusive
4                                Share
5                                Share/Row Exclusive
6                                Exclusive

Good luck

Cong Tang

Zhiqi Shen wrote:

> Hi Jonathan,
>
> Thank you for your help. But how to release the lock. I found sometimes it is
> waiting for library cache lock and sometimes it is waiting for latch free
> event. I don't know how to release them.
>
> Sometimes somebody simply interupted the oracle session left a unix process
> there which is still holding the locks and resources. How do you handle such
> problems?
>
> Any comments from other people?
>
> Regards
> Lane
>
> Jonathan Lewis wrote:
>
> > Have a look in the v$system_event view for
> > library cache locks against the table.
> >
> > --
> >
> > Jonathan Lewis
> > Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
> >
> > Lane Shen wrote in message <38632512.18EC2F70_at_direct.ca>...
> > >We are using Oracle8i 8.1.5 on HPUX with partition option. We have a SQL
> > >
> > >statement to create table using partition method to store the primary
> > >key. After we created the new database, everyting was working fine
> > >including the statement mentioned above. After the database was running
> > >for some days, the statement could not work any more. It kept running
> > >forever. We seperated the statement to two parts. One is to create table
> > >
> > >and one is to alter table adding primary key constraint. The table was
> > >created but the other one to add constraint hanged there. Then we don't
> > >add this constraint, instead we created unique index on it. It still
> > >hanged. We tried to create non-unique index. It was the same thing.
> > >
> > >Any body encountered such problem before? Any body can help?
> > >
> > >Thanks in advance
> > >Lane
> > >
> > >
> > >
Received on Tue Dec 28 1999 - 14:35:00 CST