oracle 备份package,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.askmaclean.com & www.askmaclean.com

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.