Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00054 (ORACLE, 10g, Linux Redhat 4.8)
ORA-00054 [message #570254] Wed, 07 November 2012 04:41 Go to next message
Devo
Messages: 23
Registered: November 2012
Location: France
Junior Member
Hi everybody,

I would like to resolve my problem about truncating table in oracle 10g, i have a table with more than 2 millions of data and i tr to delete data using delete statement but it take many time and i tried with truncate it's ok but when i want to insert data into the table it give me this error ORA-00054 ORA-00054: ressource occupée et acquisition avec NOWAIT (i'm frensh native speaker tht's why my database is in frensh)

thank's for help and im sorry for my bad english(i'm learning english Very Happy )

Re: ORA-00054 [message #570255 is a reply to message #570254] Wed, 07 November 2012 05:08 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Your table is already locked by some query. Like you have executed "select for update" and has yet not committed/rollback and again fired select query. Do a commit/rollback before executing your query.

http://www.orafaq.com/forum/t/178502/0/
Re: ORA-00054 [message #570256 is a reply to message #570255] Wed, 07 November 2012 05:15 Go to previous messageGo to next message
Devo
Messages: 23
Registered: November 2012
Location: France
Junior Member
should i commit after truncate statement ?
Re: ORA-00054 [message #570257 is a reply to message #570256] Wed, 07 November 2012 05:19 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

No need COMMIT..TRUNCATE IS DDL command automatically commit...
Re: ORA-00054 [message #570259 is a reply to message #570257] Wed, 07 November 2012 05:24 Go to previous messageGo to next message
Devo
Messages: 23
Registered: November 2012
Location: France
Junior Member
I have table called "Aricles" i select from this table some data and insert it in my "Traitement" table, after i have to do some preccessing in this "Traitement" table, after that i export data to excel. and in the next time when i want to do the same process it give me ORA-00054.
Re: ORA-00054 [message #570261 is a reply to message #570259] Wed, 07 November 2012 05:26 Go to previous messageGo to next message
Devo
Messages: 23
Registered: November 2012
Location: France
Junior Member
i do
1 - Insert into traitement (select ..... from article)
2 - do some update statement in article table
3 - export data from article to a Excel
----
After ending this operation i would like to reexecute the same process it give me : ORA-00054

Re: ORA-00054 [message #570262 is a reply to message #570261] Wed, 07 November 2012 05:31 Go to previous messageGo to next message
Littlefoot
Messages: 19817
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What tool(s) do you use to accomplish this task? Do you do that from the same session? Where is the TRUNCATE - I don't see it. If possible, reproduce it in SQL*Plus so that we could trace your steps.
Re: ORA-00054 [message #570263 is a reply to message #570262] Wed, 07 November 2012 05:34 Go to previous messageGo to next message
Devo
Messages: 23
Registered: November 2012
Location: France
Junior Member
befor Inserting data into Traitement table i execute truncate statement (to initialize the table because when i execute delete it take a lot of time)
Re: ORA-00054 [message #570264 is a reply to message #570263] Wed, 07 November 2012 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you have this error on TRUNCATE this means that someone else is currently using the table (for instance, has inserted some rows and did not commit).
You can see who is holding lock on the table querying V$LOCKED_OBJECT.

Regards
Michel
Re: ORA-00054 [message #570267 is a reply to message #570264] Wed, 07 November 2012 05:59 Go to previous messageGo to next message
Devo
Messages: 23
Registered: November 2012
Location: France
Junior Member
What's the query to execute with V$LOCKED_OBJECT ?
Re: ORA-00054 [message #570268 is a reply to message #570267] Wed, 07 November 2012 06:00 Go to previous messageGo to next message
Littlefoot
Messages: 19817
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SELECT * FROM ...
is the simplest one (though, you need to have privileges to query that view).

[Updated on: Wed, 07 November 2012 06:00]

Report message to a moderator

Re: ORA-00054 [message #570269 is a reply to message #570268] Wed, 07 November 2012 06:08 Go to previous messageGo to next message
Devo
Messages: 23
Registered: November 2012
Location: France
Junior Member
because when i execute select * from V$LOCKED_OBJECT it give me 00942. 00000 - "table or view does not exist" Sad
Re: ORA-00054 [message #570270 is a reply to message #570269] Wed, 07 November 2012 06:10 Go to previous messageGo to next message
Littlefoot
Messages: 19817
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Lack of privileges, as I said. Contact your DBA.
Re: ORA-00054 [message #570271 is a reply to message #570270] Wed, 07 November 2012 06:11 Go to previous messageGo to next message
Devo
Messages: 23
Registered: November 2012
Location: France
Junior Member
Ok, thank you

when i found who is lokked my table what should i do to resolve this error ?
Re: ORA-00054 [message #570272 is a reply to message #570271] Wed, 07 November 2012 06:13 Go to previous messageGo to next message
Littlefoot
Messages: 19817
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ask him whether he plans to release the lock. If not, kill him. The session, I mean, not the person.
Re: ORA-00054 [message #570273 is a reply to message #570272] Wed, 07 November 2012 06:17 Go to previous messageGo to next message
Devo
Messages: 23
Registered: November 2012
Location: France
Junior Member
Hahaha ok i will kil myself because i'm the DBA (it's just for studies not for work Smile but my project to get my bac+5 in engineering)
i should resolve the problem Sad
Re: ORA-00054 [message #570275 is a reply to message #570273] Wed, 07 November 2012 06:36 Go to previous messageGo to next message
Littlefoot
Messages: 19817
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
See an example: in the first session (left) I'm updating a table. In another session (right), I'm trying to truncate it, but I'm unable to:

./fa/10492/0/

The same goes with SELECT FOR UPDATE:

./fa/10493/0/

But, you can't lock yourself:

./fa/10494/0/

So, if it is only you working on it, I guess you must have two different sessions.
Re: ORA-00054 [message #570276 is a reply to message #570275] Wed, 07 November 2012 07:29 Go to previous messageGo to next message
Devo
Messages: 23
Registered: November 2012
Location: France
Junior Member
thank u very much for the example, i'm the onely one who work in the session
Re: ORA-00054 [message #570281 is a reply to message #570276] Wed, 07 November 2012 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But maybe you have an aborted session or your tool open several sessions, one per window for instance.

Regards
Michel
Re: ORA-00054 [message #570435 is a reply to message #570276] Fri, 09 November 2012 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Any feedback?

Regards
Michel
Re: ORA-00054 [message #570441 is a reply to message #570435] Fri, 09 November 2012 10:25 Go to previous messageGo to next message
Devo
Messages: 23
Registered: November 2012
Location: France
Junior Member
Hi,

i still have the smae problem i have to wait many minutes and after i can query the database
i duno how to kill this session, and what if i kill this session? what mean killing a session
Re: ORA-00054 [message #570446 is a reply to message #570441] Fri, 09 November 2012 11:56 Go to previous messageGo to next message
joy_division
Messages: 4546
Registered: February 2005
Location: East Coast USA
Senior Member
Devo wrote on Fri, 09 November 2012 11:25
Hi,

i still have the smae problem i have to wait many minutes and after i can query the database
i duno how to kill this session, and what if i kill this session? what mean killing a session


What problem? You have to wait a few minutes to query? The session is in a active transaction and then it completes and now you can query?
look up
alter system kill session...
after looking in v$sesion table. You said you are the DBA, so grant rights on the v$ tables to be able to query from them (and kill).
Re: ORA-00054 [message #570585 is a reply to message #570446] Mon, 12 November 2012 09:05 Go to previous messageGo to next message
Devo
Messages: 23
Registered: November 2012
Location: France
Junior Member
yes, i have to still few minutes to query,

i just need to understand how kill session work ?

Thank you
Re: ORA-00054 [message #570587 is a reply to message #570585] Mon, 12 November 2012 09:07 Go to previous messageGo to next message
BlackSwan
Messages: 23061
Registered: January 2009
Senior Member
>i just need to understand how kill session work ?

It works well.
What is there to understand?
The identified session is terminated.
Re: ORA-00054 [message #570592 is a reply to message #570587] Mon, 12 November 2012 09:42 Go to previous messageGo to next message
Devo
Messages: 23
Registered: November 2012
Location: France
Junior Member
no it's not that i need to understand how kill session work
Re: ORA-00054 [message #570593 is a reply to message #570592] Mon, 12 November 2012 09:45 Go to previous messageGo to next message
BlackSwan
Messages: 23061
Registered: January 2009
Senior Member
>no it's not that i need to understand how kill session work
Good luck with that.

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_2014.htm#i2053602
Re: ORA-00054 [message #570595 is a reply to message #570593] Mon, 12 November 2012 09:54 Go to previous messageGo to next message
Devo
Messages: 23
Registered: November 2012
Location: France
Junior Member
i need a brief reponse.

thank u
Re: ORA-00054 [message #570596 is a reply to message #570595] Mon, 12 November 2012 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 23061
Registered: January 2009
Senior Member
>i need a brief reponse.
RTFM is a brief as I can be.
Re: ORA-00054 [message #570597 is a reply to message #570596] Mon, 12 November 2012 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Laughing
Re: ORA-00054 [message #570616 is a reply to message #570597] Mon, 12 November 2012 15:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8007
Registered: November 2002
Location: California, USA
Senior Member
The following is an extension of one of Littlefoot's examples and shows how to get the session_id (sid) and serial# to use in the "alter system kill session (<sid>, <serial#>)" statement.

-- session 1:
SCOTT@orcl_11gR2> select * from emp where deptno = 10 for update;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7839 KING       PRESIDENT            17-NOV-81       5000
        10

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10


3 rows selected.

SCOTT@orcl_11gR2>


-- session 2:
SCOTT@orcl_11gR2> truncate table emp;
truncate table emp
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SCOTT@orcl_11gR2> select s.sid, s.serial#
  2  from   all_objects ao, v$locked_object lo, v$session s
  3  where  ao.object_name = 'EMP'
  4  and    ao.object_id = lo.object_id
  5  and    lo.session_id = s.sid;

       SID    SERIAL#
---------- ----------
       133        724

1 row selected.

SCOTT@orcl_11gR2> alter system kill session '133,724';

System altered.

SCOTT@orcl_11gR2> truncate table emp;

Table truncated.

SCOTT@orcl_11gR2>








Re: ORA-00054 [message #570624 is a reply to message #570616] Tue, 13 November 2012 02:42 Go to previous message
Devo
Messages: 23
Registered: November 2012
Location: France
Junior Member
Barbara Boehmer Thank you very much, it's verry clear.
Have nice day
Previous Topic: Help with this Analytical Query (Windowing Function) Question
Next Topic: Pivot
Goto Forum:
  


Current Time: Thu Nov 27 22:29:30 CST 2014

Total time taken to generate the page: 0.15670 seconds