Shulou(Shulou.com)06/01 Report--

This article mainly introduces the Oracle ORA-00054 error how to do, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian with you to understand.

When you delete an intermediate table during database maintenance, you encounter the following error:

Sys@DW > drop table dwods.member_DELTA

Drop table dwods.member_DELTA

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

It is found that the table is locked due to the execution of dml. The following error processing ideas and procedures are given, depending on the circumstances:

1 View the information of locked users in the database:

Sys@DW > select t2.usernameauthorship t2.sidrecoveryt2.serialrecording authorityt2.logonrecording time

2 from v$locked_object T1 recording session T2

3 where t1.session_id=t2.sid order by t2.logon_time

USERNAME SID SERIAL# LOGON_TIME

DWODS 1520 42477 2011-11-17 18:00:40

DWODS 1594 7385 2011-11-17 18:41:27

The dwods is locked because the transaction was initiated at 18:41, so check the information with a sid of 1594

2 query the sql information and operate according to the actual situation

Sys@DW > select sql_text from v$session a minute vandalism qltextbook withdrawing newlines b

2 where DECODE (a.sql_hash_value, 0, prev_hash_value, sql_hash_value) = b.hash_value

3 and a.sid=&sid order by piece

Enter value for sid: 1594

Old 3: and a.sid=&sid order by piece

New 3: and a.sid=1594 order by piece

SQL_TEXT

Insert / * + append + * / into DWODS.MEMBER_delta (ACTION

ADDRESS

.

32 rows selected.

It is the statement initiated to check the user's information for confirmation.

Sys@DW > @ user_info

Enter value for sid: 1594

Old 12: where a.sid = & sid

New 12: where a.sid = 1594

USERNAME SID SERIAL# OS Process Logon time OSUSER PROGRAM STATUS

DWODS 1594 7385 3309 17 shock 11 etl sqlplus@dw1 18:41:27 etl sqlplus@dw1 (TNS V1-V3) ACTIVE

1 row selected.

3 Select kill drop process

We know here that this session can be killed, so kill this session.

Sys@DW > alter system kill session '1594 7385'

System altered.

4 confirm:

Confirm in the database

Sys@DW > @ user_info

Enter value for sid: 1594

Old 12: where a.sid = & sid

New 12: where a.sid = 1594

No rows selected

Sys@DW > select t2.usernameauthorship t2.sidrecoveryt2.serialrecording authorityt2.logonrecording time

2 from v$locked_object T1 recording session T2

3 where t1.session_id=t2.sid order by t2.logon_time

USERNAME SID SERIAL# LOGON_TIME

--

DWODS 1520 42477 2011-11-17 18:00:40

DWODS 1520 42477 2011-11-17 18:00:40

2 rows selected.

Confirm at the os layer that the process has been killed.

Oracle@dw1:/home/oracle > ps-ef | grep 3309

Oracle 22565 18543 0 18:59 pts/5 00:00:00 grep 3309

Delete the table again:

Sys@DW > drop table dwods.member_DELTA

Table dropped.

-attached:

User_info . The contents of the sql script:

Select

A.username

A.sid

A.serial#

B.spid "OS Process"

To_char (a. Logoncake time.mlxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx (to_char) "Logon time"

A.osuser

A.program

A.status

From v$session a, v$process b

Where a.sid = & sid

And a.paddr = b.addr

/

Thank you for reading this article carefully. I hope the article "what to do if there is an ORA-00054 error in Oracle" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.