Re: How to avoid or handle the ORA-0054s

>Hemant,
>Another simple method to achieve your purpose:
>
>DROPME:gonza> lock table testddl in exclusive mode;
>-- Waiting until all concurrent DML has been done.
>
>Table(s) Locked.
>
>DROPME:gonza> alter index testddl_i1 rebuild;
>
>Index altered.
>
>PS Replace "alter ... rebuild;" with "drop index ... ; lock table ...;
>create index ..." if you want.

this *may* have problem:

DDL = commit + ddl + commit;

for example:

session 1:
SQL> create table t as select * from all_objects where rownum < 11;

Table created.

SQL> delete from t where rownum = 1;

1 row deleted.

SQL> session 2:

SQL> lock table t in exclusive mode;

• session 2 was blocked by session 1

session 1:
SQL> commit;

Commit complete.

SQL> session 2 :
Table(s) Locked.

SQL> session 1:
SQL> delete from t where rownum = 1;
--session 1 was blocked by session 2

session 2:
SQL> truncate table t;
truncate table t
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL>

• session 2 lock table then truncate ,but failed.

session 1:
1 row deleted.

SQL>

• when session 2 truncate failed ,session 1 deleted successfully.

Best regards

msn: biti_rainy_at_hotmail.com
a dba from alibaba(china)

• from the mail-----

>
>Jurijs
>+371 9268222 (+2 GMT)
>============================================
>Thank you for teaching me.
>http://otn.oracle.com/ocm/jvelikanovs.html
>
>
>Jurijs
>+371 9268222 (+2 GMT)
>============================================
>Thank you for teaching me.
>http://otn.oracle.com/ocm/jvelikanovs.html
>
>
>
>
>
>
>Hemant K Chitale <hkchital_at_singnet.com.sg>
>Sent by: oracle-l-bounce_at_freelists.org
>01.01.2005 05:05
>Please respond to hkchital
>
> To: oracle-l_at_freelists.org
> cc:
> Subject: How to avoid or handle the ORA-0054s
>
>
>
>I have a need to regularly Recreate certain Indexes . {see note below on
>WHY !}
>This is scripted. However, the script sometimes errors on the DROP with
>ORA-0054
>and, of course, the CREATE doesn't go through.
>We are trying to put a loop to check the spooled output of the script and
>rerun
>it if the DROP fails.
>
>However, I was wondering if anyone has implemented a technique to handle
>ORA-0054s
>and automated the retry of the DDL.
>
>Why I can't use a REBUILD is because it is a corrupt index.
>{and surely, the REBUILD does use a WAIT when it switches the indexes.
>Why doesn't Oracle allow us to write a DROP ... WAIT ?}
>
>
>
>NOTE : Why the Recreate Indexes :
>These are 6 BitMap Join Indexes. A bug in 9.2.0.4 causes occasional
>ORA-600s
>when querying the table. The solution is to Recreate the Indexes. I had
>emailed
>this list on 03-Dec on ORA-600 [12700] errors with these BMJIs.
>
>Although 9.2.0.5 is indicated to have a fix, I see some references to
>other
>BMJI issues in 9.2.0.5 and we haven't yet gone to 9.2.0.5 for this
>particular database.
>
>Hemant K Chitale
>http://web.singnet.com.sg/~hkchital
>
>
>
>
>--
>http://www.freelists.org/webpage/oracle-l
>
>
>
>--
>http://www.freelists.org/webpage/oracle-l
>
>.

--
http://www.freelists.org/webpage/oracle-l