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

  • From: "biti_rainy" <biti_rainy@xxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 5 Jan 2005 20:26:17 +0800

  


>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@xxxxxxxxxxx
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@xxxxxxxxxxxxxx>
>Sent by: oracle-l-bounce@xxxxxxxxxxxxx
>01.01.2005 05:05
>Please respond to hkchital
> 
>        To:     oracle-l@xxxxxxxxxxxxx
>        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
>
>
>
> 
>--
>//www.freelists.org/webpage/oracle-l
>
>
>
>--
>//www.freelists.org/webpage/oracle-l
>
>.


        


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

Other related posts: