Oracle中的Package/Procedure/Function存放在哪里?

有同学问Oracle 的package、Procedure、Function 这些PL/SQL程序单元分别存放在哪里?

针对这个问题我们可以通过对create package、Procedure、Function 做trace分析来了解其细节,如:

 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
-----------------------------------------------
www.askmac.cn & www.askmac.cn

SQL> oradebug setmypid;
Statement processed.

SQL> oradebug event 10046 trace name context forever,level 8;
Statement processed.

SQL> create or replace procedure maclean
2 as
3 begin
4 null
5 ;
6 end;
7 /

SQL> oradebug tracefile_name;
/s01/admin/G10R21/udump/g10r21_ora_3503.trc

 

我们来分析以上trace的内容:

 

[oracle@vrh8 ~]$ grep "insert"  /s01/admin/G10R21/udump/g10r21_ora_3503.trc

insert into procedure$(obj#,audit$,options) values (:1,:2,:3)
insert into source$(obj#,line,source) values (:1,:2,:3)
insert into idl_sb4$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into idl_ub1$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into idl_char$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into idl_ub2$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into idl_sb4$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into idl_ub1$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4,:5,:6)
insert into settings$(obj#, param, value) values (:1, :2, :3)
insert into warning_settings$(obj#, warning_num, global_mod, property) values (:1, :2, :3, :4)
insert into procedureinfo$(obj#,procedure#,overload#,procedurename,properties,itypeobj#) values (:1,:2,:3,:4,:5,:6)
insert into argument$( obj#,procedure$,procedure#,overload#,position#,sequence#,level#)
insert into procedureplsql$(obj#,procedure#,entrypoint#) values (:1,:2,:3)
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
insert into obj$(owner#,name,namespace,obj#,type#,

 

创建一个非常简单的PL/SQL Procedure牵涉到的数据字典基表却不少,包括了:

  • procedure$
  • source$
  • idl_sb4$
  • idl_ub1$
  • idl_char$
  • idl_ub2$
  • idl_sb4$
  • idl_ub1$
  • settings$
  • warning_settings$
  • procedureinfo$
  • access$
  • obj$

 

注意以上这些数据字典基表都存放在SYSTEM表空间上,丢失SYSTEM表空间也就意味着你可能找不回这些程序了(如果你没有其他备份的话),即使使用DUL工具也无法挽回。

 

我来简单介绍一下这些数据字典基表( 11g以前可以在sql.bsq文件中找到以下信息, 11g以后在$ORACLE_HOME/rdbms/admin/dplsql.bsq中):

 

 

procedure$ 基表(普通堆表)用于存放Package程序包和独立的procedure存储过程以及function 函数信息,每一条记录对应一个procedure或function(procedure$ contains information about packages and standalone procedures, functions. There is one row for a top-level object.)

 

 

procedureinfo$基表(普通堆表)用于存放独立的或程序包中procedure或function的详细信息,包括Name名字和属性。

这些属性包括:Aggregate、Pipelined、Parallel、deterministic等:

 

  properties    number not null,                     /* procedure properties */
                /* 0x00001 =     1 = HIDDEN (internally generated) procedure */
                /* 0x00002 =     2 = C implementation (in spec)              */
                /* 0x00004 =     4 = Java implementation (in spec)           */
                /* 0x00008 =     8 = Aggregate function                      */
                /* 0x00010 =    16 = Pipelined function                      */
                /* 0x00020 =    32 = Parallel enabled                        */
                /* 0x00040 =    64 = Retrun Self as result (SQLJ)            */
                /* 0x00080 =   128 = Constructor function (SQLJ)             */
                /* 0x00100 =   256 = deterministic                           */
                /* 0x00200 =   512 = Pipelined func; interface impl          */
                /* 0x00400 =  1024 = Function with invokers rights           */
                /* 0x00800 =  2048 = Func with partitioned argument(s)       */
                /* 0x01000 =  4096 = Func with clustered argument(s)         */
                /* 0x02000 =  8192 = Func with ordered i/p argument(s)       */
                /* 0x04000 =  16384 = Partitioned arg: Hash partitioning     */
                /* 0x08000 = 32768 = Partitioned arg: Range partitioning     */
                /* 0x10000 = 65536 = Partitioned using any partitioning      */
  /* The following field is relevant only for aggregate and pipelined        */
  /*  functions that are implemented using an implementation type            */

 

source$用于存放PL/SQL程序的源代码, 这些代码包括已经被Oracle Wrap加密的代码,一般来说如Oracle EBS这样的应用这张表会非常地大:

 

 

idl_ub1$、idl_char$ 这种表名以idl_打头的字典基表用以存放编译好的PL/SQL程序代码,包括三种种类:

  • DIANA
    • Heap 2 (Diana) : Contains the DIANA (Parse tree metadata) for a PL/SQL object
  • Portable pcode
    • Heap 3 (Pcode): Stores the pseudocode for a PL/SQL object
  • machine-dependent code
    • Heap 4 (Mcode): Machine-dependent pseudocode for a PL/SQL object

 

 

argument$基表记录了存储过程或函数的调用时的参数信息, 如我们所常用的DBMS_METADATA.GET_DDL函数就有7个argument参数

 

select object_name,object_id,object_type from dba_objects where object_name='DBMS_METADATA';

OBJECT_NAME           OBJECT_ID OBJECT_TYPE
-------------------- ---------- -------------------
DBMS_METADATA              6097 PACKAGE
DBMS_METADATA              7105 PACKAGE BODY
DBMS_METADATA              6098 SYNONYM

SQL> select procedure$,sequence#,argument from sys.argument$ where procedure$='GET_DDL' and obj#=6097;

PROCEDURE$                      SEQUENCE# ARGUMENT
------------------------------ ---------- ------------------------------
GET_DDL                                 1
GET_DDL                                 2 OBJECT_TYPE
GET_DDL                                 3 NAME
GET_DDL                                 4 SCHEMA
GET_DDL                                 5 VERSION
GET_DDL                                 6 MODEL
GET_DDL                                 7 TRANSFORM

7 rows selected.

 

 


Posted

in

by

Tags:

Comments

5 responses to “Oracle中的Package/Procedure/Function存放在哪里?”

  1. 北在南方 Avatar

    顶一下,问一个不相干的问题,如果修改oracle的监听端口,有什么要求吗?
    比如 必须大于 1000
    如果 小于 1000 比如 443 ,其他和oracle不相干的请求会对oracle 监听有什么影响?

    1. maclean Avatar
      maclean

      你好 ,

      Unix-like的系统上只有root用户可以使用1024以下的 TCP 或 UDP端口,所以Oracle用户或其他 DBA组的普通用户是无法启动监听在如443 这样的端口上的。

      如:
      [oracle@vrh8 admin]$ cat listener.ora

      LSN_MACLEAN =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vrh8)(PORT = 888))
      )

      [oracle@vrh8 admin]$ lsnrctl start LSN_MACLEAN

      LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 10-NOV-2011 00:50:29

      Copyright (c) 1991, 2005, Oracle. All rights reserved.

      Starting /s01/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait…

      TNSLSNR for Linux: Version 10.2.0.1.0 – Production
      System parameter file is /s01/oracle/product/10.2.0/db_1/network/admin/listener.ora
      Log messages written to /s01/oracle/product/10.2.0/db_1/network/log/lsn_maclean.log
      Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vrh8)(PORT=888)))
      TNS-12546: TNS:permission denied
      TNS-12560: TNS:protocol adapter error
      TNS-00516: Permission denied
      Linux Error: 13: Permission denied

      Listener failed to start. See the error message(s) above..

      会出现Linux Error: 13: Permission denied

      In Linux, and other UNIX-like systems, you have to be root (have superuser privileges) in order to listen to TCP or UDP ports below 1024 (the well-known ports).

  2. marvelyu Avatar

    ITPUB上的提问?

  3. Shang Avatar
    Shang

    请教一下,包中全局变量不是存储在某个系统表中?
    外部过程创建时对包中全局变量的引用
    create or replace procedure p1
    as
    begin
    pack1.v1 := 1;
    end;
    如果不在系统中存储这个变量,那怎么找到它?难道出现引用后就将包实例加载到session中来吗?

Leave a Reply to marvelyu Cancel reply

Your email address will not be published. Required fields are marked *