See why stored procedures in Oracle are stuck for a long time

1: Check V$db_object_cache

SELECT * from V$db_object_cache WHERE name= ' cux_oe_order_rpt_pkg ' and locks!= ' 0 ';

Note: Cux_oe_order_rpt_pkg is the name of the stored procedure.

Discover locks=2

2: Find the SID value by object

Select/*+ rule*/SID from v$access WHERE object= ' cux_oe_order_rpt_pkg ';

Note: Cux_oe_order_rpt_pkg is the name of the stored procedure.

3: Check sid,serial#

SELECT sid,serial#,paddr from v$session WHERE sid= ' SID ' just found;

4, depending on the session ID (SID), the wait event for this session:

[SQL]View PlainCopy

1. SELECT * from v$session where sid=***;

The event field is waiting for events. After querying we find that this session waits for the event to be sql*net message from Dblink; two days before the logon_time of the viewing session. This time is much more than our estimated time.

5. View the SQL statement that this session is executing based on the session ID

[SQL]View PlainCopy

1. Select Sql_text from v$sqlarea where address= (select sql_address from v$session where sid=***);

The query found that the SQL statement that was being executed was inserted into table B by querying data for a table on the remote database via Dblink.

6. Connect to the remote database and query the object that is currently locked

[SQL]View PlainCopy

1. SELECT * from v$locked_object lo,
2. All_objects ao where lo.  Object_id= ao.object_id;

View after discovering that the remote database does not involve A, B is locked

7. View the session for remote data:

[SQL]View PlainCopy

1. SELECT * from v$session where terminal like '% machine name% ' and program=' Oracle.exe '

Using Dblink to connect to the remote database, the session on the remote database program should be Oracle.exe

The query found that two remote libraries sometimes had no related sessions at all, and sometimes related sessions, but their wait events were sql*net message from the Client remote library waiting for local Oracle to send him a request.

Dblink remote libraries such as local libraries, remote libraries await client messages. It seems that this stored procedure is not going to be done.

It is unclear what caused the problem.

The way to deal with this is to kill the conversation.

http://blog.csdn.net/fupei/article/details/7325190

Refer to the above article for specific steps

See why stored procedures in Oracle are stuck for a long time