Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

SQL> show user

User is "scott"

SQL> create or replace procedure proc_test_special

2 as

3 begin

4 null;

5 end;

6 /

Procedure created

SQL> desc obj$;

Object obj$ does not exist.

SQL> conn sys/s@orcl as syssdba

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as SYS

SQL> grant dba to scott;

Grant succeeded

SQL> conn scott/system@orcl

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

SQL> desc obj$;

Object obj$ does not exist.

desc obj$;

小結：

1,此底層字典僅sysdba可以查詢

SQL> conn sys/system@orcl as sysdba

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as SYS

SQL> desc obj$;

Name Type Nullable Default Comments

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

OBJ# NUMBER

DATAOBJ# NUMBER Y

OWNER# NUMBER

NAME VARCHAR2(30)

NAMESPACE NUMBER

SUBNAME VARCHAR2(30) Y

TYPE# NUMBER

CTIME DATE

MTIME DATE

STIME DATE

STATUS NUMBER

REMOTEOWNER VARCHAR2(30) Y

LINKNAME VARCHAR2(128) Y

FLAGS NUMBER Y

OID$ RAW(16) Y

SPARE1 NUMBER Y

SPARE2 NUMBER Y

SPARE3 NUMBER Y

SPARE4 VARCHAR2(1000) Y

SPARE5 VARCHAR2(1000) Y

SPARE6 DATE Y

SQL> select OBJ#,status,name from obj$ ob where ob.name='PROC_TEST_SPECIAL';

OBJ# STATUS NAME

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

132739 1 PROC_TEST_SPECIAL

SQL> conn scott/system@orcl

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

SQL> drop procedure PROC_TEST_SPECIAL;

Procedure dropped

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 OBJ#,status,name from obj$ ob where ob.name='PROC_TEST_SPECIAL';

OBJ# STATUS NAME

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

132739 1 PROC_TEST_SPECIAL

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 do.OWNER,do.object_name,do.object_id,status from dba_objects do where do.object_name='PROC_TEST_SPECIAL';

OWNER OBJECT_NAME OBJECT_ID STATUS

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

SQL> desc obj$;

Name Type Nullable Default Comments

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

OBJ# NUMBER

DATAOBJ# NUMBER Y

OWNER# NUMBER

NAME VARCHAR2(30)

NAMESPACE NUMBER

SUBNAME VARCHAR2(30) Y

TYPE# NUMBER

CTIME DATE

MTIME DATE

STIME DATE

STATUS NUMBER

REMOTEOWNER VARCHAR2(30) Y

LINKNAME VARCHAR2(128) Y

FLAGS NUMBER Y

OID$ RAW(16) Y

SPARE1 NUMBER Y

SPARE2 NUMBER Y

SPARE3 NUMBER Y

SPARE4 VARCHAR2(1000) Y

SPARE5 VARCHAR2(1000) Y

SPARE6 DATE Y

SQL> select * from obj$ where name='PROC_TEST_SPECIAL';

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

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

SQL> SHOW USER

User is "SYS"

SQL> /

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$ where name='PROC_TEST_SPECIAL';

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

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

SQL>

SQL> select OBJ#,status,name from obj$ ob where ob.name='PROC_TEST_SPECIAL';

OBJ# STATUS NAME

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

SQL>

小結：如果馬上刪除儲存過程，在底層字典表沒有馬上刪除，還可以看到相關的資訊，所以還有恢復的可能性

oracle底層字典表obj$及source$與儲存過程procedure系列一

相關文章