oracle底层字典表obj$及source$与存储过程procedure系列二

最新推荐文章于 2022-09-29 18:44:32 发布

clg10051

最新推荐文章于 2022-09-29 18:44:32 发布

阅读量633

点赞数

文章标签：数据库

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as SYS

SQL> conn scott/system@orcl

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

SQL> create or replace procedure proc_obj

2 as

3 begin

4 null;

5 end;

6 /

Procedure created

SQL> conn sys/system@orcl as sysdba

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as SYS

SQL> select * from obj$ ob where ob.name='PROC_OBJ';

OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME MTIME STIME STATUS REMOTEOWNER LINKNAME FLAGS OID$ SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6

---------- ---------- ---------- ------------------------------ ---------- ------------------------------ ---------- ----------- ----------- ----------- ---------- ------------------------------ -------------------------------------------------------------------------------- ---------- -------------------------------- ---------- ---------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------

132740 84 PROC_OBJ 1 7 2012/11/16 2012/11/16 2012/11/16 1 0 6 65535 84

SQL> desc source$;

Name Type Nullable Default Comments

------ -------------- -------- ------- --------

OBJ# NUMBER

LINE NUMBER

SOURCE VARCHAR2(4000) Y

SQL> select * from source$ where obj#=132740;

OBJ# LINE SOURCE

---------- ---------- --------------------------------------------------------------------------------

132740 1 procedure proc_obj

132740 2 as

132740 3 begin

132740 4 null;

132740 5 end;

132740 6

6 rows selected

SQL> update source$ set source=replace(source,'proc_obj','proc_obj_new') where obj#=132740 and line=1;

1 row updated

SQL> commit;

Commit complete

SQL> select * from source$ where obj#=132740;

OBJ# LINE SOURCE

---------- ---------- --------------------------------------------------------------------------------

132740 1 procedure proc_obj_new

132740 2 as

132740 3 begin

132740 4 null;

132740 5 end;

132740 6

6 rows selected

SQL> update obj$ ob set ob.name='proc_obj_new' where obj#=132740;

1 row updated

SQL> commit;

Commit complete

SQL> select * from obj$ ob where ob.name='PROC_OBJ';

OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME MTIME STIME STATUS REMOTEOWNER LINKNAME FLAGS OID$ SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6

SQL> select * from obj$ ob where ob.name='PROC_OBJ_NEW';

OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME MTIME STIME STATUS REMOTEOWNER LINKNAME FLAGS OID$ SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6

SQL> select * from obj$ ob where ob.name like '%PROC_OBJ%'

2 ;

OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME MTIME STIME STATUS REMOTEOWNER LINKNAME FLAGS OID$ SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6

SQL> select * from obj$ ob where ob.name like '%proc_obj%'

2 ;

OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME MTIME STIME STATUS REMOTEOWNER LINKNAME FLAGS OID$ SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6

132740 84 proc_obj_new 1 7 2012/11/16 2012/11/16 2012/11/16 1 0 6 65535 84

SQL> select * from obj$ ob where ob.name='proc_obj';

OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME MTIME STIME STATUS REMOTEOWNER LINKNAME FLAGS OID$ SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6

SQL> select * from obj$ ob where ob.name='proc_obj_new';

OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME MTIME STIME STATUS REMOTEOWNER LINKNAME FLAGS OID$ SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6

132740 84 proc_obj_new 1 7 2012/11/16 2012/11/16 2012/11/16 1 0 6 65535 84

SQL> desc dba_objects;

Name Type Nullable Default Comments

-------------- ------------- -------- ------- ----------------------------------------------------------------------------

OWNER VARCHAR2(30) Y Username of the owner of the object

OBJECT_NAME VARCHAR2(128) Y Name of the object

SUBOBJECT_NAME VARCHAR2(30) Y Name of the sub-object (for example, partititon)

OBJECT_ID NUMBER Y Object number of the object

DATA_OBJECT_ID NUMBER Y Object number of the segment which contains the object

OBJECT_TYPE VARCHAR2(19) Y Type of the object

CREATED DATE Y Timestamp for the creation of the object

LAST_DDL_TIME DATE Y Timestamp for the last DDL change (including GRANT and REVOKE) to the object

TIMESTAMP VARCHAR2(19) Y Timestamp for the specification of the object

STATUS VARCHAR2(7) Y Status of the object

TEMPORARY VARCHAR2(1) Y Can the current session only see data that it place in this object itself?

GENERATED VARCHAR2(1) Y Was the name of this object system generated?

SECONDARY VARCHAR2(1) Y Is this a secondary object created as part of icreate for domain indexes?

NAMESPACE NUMBER Y Namespace for the object

EDITION_NAME VARCHAR2(30) Y Name of the edition in which the object is actual

SQL> select * from dba_objects where object_id=132740;

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME

SCOTT proc_obj_new 132740 PROCEDURE 2012/11/16 2012/11/16 19 2012-11-16:19:05:16 VALID N N N 1

SQL> conn scott/system@orcl

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

SQL>

小结：

　　　通过底层字典表obj$与source$可以更新存储过程的名称及存储过程的脚本

　　　进一层可以引申，批量进行快速更新出错的存储过程脚本

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-749380/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-749380/