How to: Determine if a Package that is About to be Compiled is Being Used Currently (文档 ID 1054939.6

本文提供了一个用于检查Oracle数据库中特定包是否正在被使用的脚本。该脚本可以帮助确定是否可以安全地编译一个包，避免因包正在使用而导致的编译失败。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

 APPLIES TO: Oracle Server - Standard Edition - Version 9.2.0.8 to 11.2.0.3 [Release 9.2 to 11.2]
 
 Oracle Server - Enterprise Edition - Version 9.2.0.8 to 11.2.0.3 [Release 9.2 to 11.2]
 
 Information in this document applies to any platform.
 
 GOAL This document explain how to determine whether a Package that you are about to compile is being used. If it was in use, this would prevent compilation from completing.
 FIXThis article contains a script that checks to see whether a package is currenly in use.

   This script is provided for educational purposes only. It is NOT supported by Oracle Support Services.  The script has been tested and appears to work as intended.  However, you should always test any script before relying on it.  
  
 PROOFREAD THIS SCRIPT BEFORE USING IT!  Due to differences in the way text  editors, email packages and operating systems handle text formatting  (i.e. spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it.  Check over the script to ensure that errors of this type are corrected.
 
The procedure  can be used to check whether any user is using the package. If the procedure shows ANY row, then the package cannot be compiled at this time. Substitute the parameter '%' with the actual package name that you are trying to compile.
Package compilation will hang waiting on 'Library Cache Lock' and 'Library Cache Pin' if some users are executing any Procedure/Function defined in the same package.
 
 In order to compile/Parse a Package/Procedure/Function/View, a user needs to obtain 'Library Cache Lock' and 'Library Cache Pin' latches for the . The latches ensure that
 no one is using the object during compilation/parsing. If we did not do this then the pacakge could be replace while it is being executed with unpredicatable results.
 
 Example of Use 
  SQL> connect / as sysdba
SQL> serveroutput on
SQL> exec who_is_using('my_funct%');

(1-198) - WEBUSER

PL/SQL procedure successfully completed.

SQL>
 
  In this example, 'my_funct%' is the name of the stored program that we wish to compile. The parameter accepts the same values as you would use in a LIKE clause of a query.

 (1-198)  displays  the INSTANCE_ID SID columns of gv$session.
 WEBUSER is the user currently using the Stored Program.
 Source Code:The following procedure MUST be created in the SYS schema or connected as internal.
 
  who_is_using.plb

   
            附件 
          
    downloadable plb file(3.07 KB)

   
            相关内容 
          
              产品 
            
       Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Standard Edition > Generic RDBMS > Database Level Performance Issues (not SQL Tuning)
Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)