DBA_OBJECTS描述数据库中的所有对象。它的列与“ALL_OBJECTS”中的列相同。

ALL_OBJECTS描述当前用户可以访问的所有对象。

USER_OBJECTS描述当前用户拥有的所有对象。此视图不显示所有者列。

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the object 对象的拥有者
OBJECT_NAME	VARCHAR2(128)	NOT NULL	Name of the object 对象的名字
SUBOBJECT_NAME	VARCHAR2(30)		Name of the subobject (for example, partition) 子对象的名称，例如，子分区
OBJECT_ID	NUMBER	NOT NULL	Dictionary object number of the object 对象在数据字典中创建的唯一标识符
DATA_OBJECT_ID	NUMBER		Dictionary object number of the segment that contains the object Note: OBJECT_ID and DATA_OBJECT_ID display data dictionary metadata. Do not confuse these numbers with the unique 16-byte object identifier (object ID) that Oracle Database assigns to row objects in object tables in the system. 包含该对象的段的字典对象号注意:OBJECT_ID和DATA_OBJECT_ID显示数据字典元数据。不要将这些数字与Oracle数据库分配给系统中对象表中的行对象的惟一16字节对象标识符(对象ID)相混淆。 object_id：只要是数据库的对象，就一定会在数据字典里有一个object_id data_object_id：对象实际存储的物理的 segment id，若一个对象没有被实际存储在segment，那么他的data_object_id就是空的，但是object_id还是存在
OBJECT_TYPE	VARCHAR2(19)		Type of the object (such as TABLE , INDEX ) 对象的类型。如：表、索引。。。
CREATED	DATE	NOT NULL	Timestamp for the creation of the object 对象创建的时间
LAST_DDL_TIME	DATE	NOT NULL	Timestamp for the last modification of the object resulting from a DDL statement (including grants and revokes) 从当前时间计算，上一次做DDL操作的时间，包括（grant 和revoke）
TIMESTAMP	VARCHAR2(19)		Timestamp for the specification of the object (character data) 是指最后一次更新对象“描述”的时间，它被用来检查远程依赖关系 (character data)
STATUS	VARCHAR2(7)		Status of the object: VALID INVALID N/A 对象的状态：有效的，失效的，或者不存在的
TEMPORARY	VARCHAR2(1)		Indicates whether the object is temporary (the current session can see only data that it placed in this object itself) (Y) or not (N) 对象是否是临时对象，比如是否是临时表，若是临时表，则只能在当前会话操作
GENERATED	VARCHAR2(1)		Indicates whether the name of this object was system-generated (Y) or not (N) 对象是否是被系统创建
SECONDARY	VARCHAR2(1)		Indicates whether this is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y) or not (N) 是否是由ODCIIndexCreate创建的secondary对象
NAMESPACE	NUMBER	NOT NULL	Namespace for the object 对象的命名空间
EDITION_NAME	VARCHAR2(30)		Name of the edition in which the object is actual 对象的命名版本

如果出现：

ORA-4043 "object %s does not exist"

ORA-4045 "errors during recompilation/revalidation of %s.%s"

ORA-4063 "%s has errors"

ORA-6508 "PL/SQL: could not find program unit being called%s"

则说明存在无效对象

查看无效对象

SELECT OWNER

,OBJECT_NAME

,OBJECT_TYPE

,STATUS

FROM DBA_OBJECTS

WHERE (OWNER, OBJECT_NAME) IN

( SELECT OWNER

,OBJECT_NAME

FROM DBA_OBJECTS

WHERE STATUS = 'INVALID' )

ORDER BY OBJECT_NAME;

编译过程如下：

1)单个对象的编译

alter <object_type> <object_name> compile;

-- or

alter <object_type> <object_name> compile body;

也可以指定用户，进行 SQL 拼接语句

SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME ||

' COMPILE;'

FROM DBA_OBJECTS

WHERE OWNER = OWNERNAME

AND STATUS = 'INVALID'

AND

OBJECT_TYPE IN ( 'PACKAGE' , 'FUNCTION' , 'PROCEDURE' , 'TRIGGER' ,

'JAVA SOURCE' , 'JAVA CLASS' , '' )

2)编译全库失效对象（一般用于进行数据库升级后）

SQL> connect / sysdba

SQL> spool utlrp.log

SQL> @?/rdbms/admin/utlrp

SQL> select owner,object_name,object_type,status from dba_objects where status = 'INVALID';

参考：How to Diagnose Invalid or Missing Data Dictionary (SYS) Objects (文档 ID 554520.1)

ITPub博客

dba_objects视图

最新文章