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>
小结:如果马上删除存储过程,在底层字典表没有马上删除,还可以看到相关的信息,所以还有恢复的可能性
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-749379/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-749379/