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

Topic: how to compile a failed package with Oracle

Author: Ji Mao flew to the sky

Description: are you there? Can you help me recompile a package? We can't compile here!

Deal with thinking:

(1) packet head failure or inclusion failure?

(2) is there an impeded process that affects recompilation?

(3) if there are obstacles, can you try to kill Process and have a conversation with the application staff?

1. Check whether the packet header and package are invalid?

From the output of the following SQL statement, the packet header has not failed, and the packet body has failed.

BEGIN pkg_test.ap_get_baTran (200720180515); END

Click (here) to collapse or open

SQL > SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (to_char ('7b4dpss4k3hc5'), NULL))

PLAN_TABLE_OUTPUT

SQL_ID 7b4dpss4k3hc5, child number 0

BEGIN pkg_test.ap_get_baTran (200720180515); END

NOTE: cannot fetch plan for SQL_ID: 7b4dpss4k3hc5, CHILD_NUMBER: 0

Please verify value of SQL_ID and CHILD_NUMBER

It could also be that the plan is no longer in cursor cache (check v$sql_plan) 7. Kill conversation

After communicating with the application staff, you can kill the conversation as much as you want, as well as PROCESS (this needs to be killed locally)

Click (here) to collapse or open

Alter system kill session '124817711'

8. Check again to see if there is a lock that monopolizes the compiled package

Check for 1719 ZJDB PKG_TEST Body Exclusive None and there is still a process with an exclusive DDL lock. According to the above way, it is wrong to kill 1719 of the conversation! Because this session is the recompiled statement being executed by the SYS user. It's impossible to kill the recompiled statement, so let's see what else we can do!

Click (here) to collapse or open

SQL > select * from dba_ddl_locks where name='PKG_REPORT'

SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU

--

9710 ZJDB PKG_TEST Table/Procedure/Type Null None

948 ZJDB PKG_TEST Table/Procedure/Type Null None

10155 ZJDB PKG_TEST Table/Procedure/Type Null None

6814 ZJDB PKG_TEST Table/Procedure/Type Null None

2851 ZJDB PKG_TEST Table/Procedure/Type Null None

21183 ZJDB PKG_TEST Table/Procedure/Type Null None

14698 ZJDB PKG_TEST Table/Procedure/Type Null None

16963 ZJDB PKG_TEST Table/Procedure/Type Null None

23503 ZJDB PKG_TEST Table/Procedure/Type Null None

2650 ZJDB PKG_TEST Table/Procedure/Type Null None

20671 ZJDB PKG_TEST Table/Procedure/Type Null None

1719 ZJDB PKG_TEST Body Exclusive None

9710 ZJDB PKG_TEST Body Null None

20671 ZJDB PKG_TEST Body Null None

6814 ZJDB PKG_TEST Body Null None

16963 ZJDB PKG_TEST Body Null None

21183 ZJDB PKG_TEST Body Null None

948 ZJDB PKG_TEST Body Null None

2851 ZJDB PKG_TEST Body Null None

10155 ZJDB PKG_TEST Body Null None

23503 ZJDB PKG_TEST Body Null None

2650 ZJDB PKG_TEST Body Null None

14698 ZJDB PKG_TEST Body Null None

23 rows selected.

9. Find out the relevant information about the ddl lock corresponding to ZJDB.PKG_TEST

Find out the session, machine name, user name and status of the DDL lock SID corresponding to ZJDB.PKG_TEST that matches the session SID.

Click (here) to collapse or open

SQL > select b.saddr, b.sid, b.serializer Select b.sid From dba_ddl_locks b.processing b.statusdir b.username, b.MACHINE from v$session b where b.sid in (Select b.sid From dba_ddl_locks a, v$session b Where a.session_id = b.SID and a.name = 'PKG_TEST' and owner='ZJDB')

Order by b.username desc

SADDR SID SERIAL# PROCESS STATUS USERNAME MACHINE

070000A02BB0E7E0 1719 30765 37358470 ACTIVE SYS RACZJ-DB1

070000A010BFF958 10155 46885 33227250 ACTIVE ZJDB RACZJ-DB1

070000A00CB32110 9710 9641 27525876 ACTIVE ZJDB RACZJ-DB1

070000A023BC4438 2032 9479 31195338 ACTIVE ZJDB RACZJ-DB1

070000A0061E8210 23503 30787 55313168 ACTIVE ZJDB RACZJ-DB1

070000A0279C9920 948 44781 13631706 ACTIVE ZJDB RACZJ-DB1

070000A02B9B4FC0 893 27923 54263886 ACTIVE ZJDB RACZJ-DB1

070000A02BCA68C8 2650 48113 29033334 ACTIVE ZJDB RACZJ-DB1

070000A00DE3DE58 21183 59867 43516348 ACTIVE ZJDB RACZJ-DB1

070000A015A40440 18410 50345 3998656 ACTIVE ZJDB RACZJ-DB1

070000A009727FB0 16963 33555 66847560 ACTIVE ZJDB RACZJ-DB1

070000A023D20C00 2851 17117 10224286 ACTIVE ZJDB RACZJ-DB1

070000A02FB62590 1776 28699 46334786 ACTIVE ZJDB RACZJ-DB1

070000A015E08488 20671 38781 53150014 ACTIVE ZJDB RACZJ-DB1

070000A011386AF0 14698 60901 37421588 ACTIVE ZJDB RACZJ-DB1

070000A005CC69A8 20416 37977 30147116 ACTIVE ZJDB RACZJ-DB1

070000A001EE7AC0 21745 60937 8192734 ACTIVE ZJDB RACZJ-DB1

17 rows selected.10. Kill process at the operating system level

Take sid=10155 as an example, kill the process of the system, the operation of killing process, usually kill process on their host!

Click (here) to collapse or open

SQL > select sid, SERIAL#,process,status from v$session where sid=10155

SID SERIAL# PROCESS STATUS

10155 30765 37358425 ACTIVE

$ps-ef | grep 37358425

$kill-9 37358425-kill session at the operating system level

$ps-ef | grep 3735845212. Kill session at database level

Click (here) to collapse or open

Alter system kill session '10155, 46885'

Alter system kill session '9710pc9641'

Alter system kill session '2032, 9479'

Alter system kill session '23503, 30787'

Alter system kill session '948,44781'

Alter system kill session '893 people 27923'

Alter system kill session '2650,48113'

Alter system kill session '21183 # 59867'

Alter system kill session '18410 50345'

Alter system kill session '16963, 33555'

Alter system kill session '2851 Magi 17117'

Alter system kill session '1776, 28699'

Alter system kill session '20671 and 38781'

Alter system kill session '14698 # 60901'

Alter system kill session '20416 37977'

Alter system kill session '21745. 60937. Check whether the command to create the package is successful

Click (here) to collapse or open

SQL > alter package ZJDB.PKG_TEST compile body

Package body altered.14. Related links

Http://www.itpub.net/forum.php?mod=viewthread&tid=1761963

Https://blog.csdn.net/u011146687/article/details/72808565

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.