9.3 USER_OBJECTS

The USER_OBJECTS view provides status information on objects you create. This includes tables, sequences, views, stored procedures, database links, and others. The following is a partial description of columns from this view. Use this view to determine if a stored procedure is valid, if you need to recompile it, or to determine its last compile timestamp.

Refer to Chapter 5 for a complete description of the differences between the USER , ALL, and DBA data dictionary views.

• USER_OBJECTS provides information only on those objects you have created in your account.

• ALL_OBJECTS provides information on objects you have created plus objects to which you have privileges.

• DBA_OBJECTS provides information on all objects in the database. You need the Oracle role DBA or SELECT_CATALOG_ROLE to access DBA views.

Because the scope of DBA views is everything in the database, you must have either the Oracle DBA role or the Oracle SELECT_CATALOG_ROLE role. The DBA role has high privileges. SELECT_CATALOG_ROLE is intended for users who need to query data dictionary views. Application developers should be given this role.

A procedure you create will have an entry in USER_OBJECTS. If BLAKE creates a procedure HELLO_BLAKE and grants execute on that procedure to you, then you can see this object when you query OWNER, OBJECT_NAME, and OBJECT_TYPE from ALL_OBJECTS.

 SQL> desc user_objects  Name                           Null?    Type  ------------------------------ -------- ---------------  OBJECT_NAME                             VARCHAR2(128)  OBJECT_TYPE                             VARCHAR2(18)  CREATED                                 DATE  LAST_DDL_TIME                           DATE  STATUS                                  VARCHAR2(7)  And other columns 

These are the columns relevant to object name, type, and status. There are a few other columns such as the OBJECT_ID of the object in the database. Relevant to this discussion, these columns have the following meaning.

OBJECT_NAME	This is the name in the CREATE OR REPLACE clause. This is not the host file. Running the script @MY_HELLO.SQL with a CREATE OR REPLACE PROCEDURE HELLO statement creates the object name HELLO. The data dictionary stores all attributes in upper case.
OBJECT_TYPE	For PL/SQL this is FUNCTION, PROCEDURE, PACKAGE, or PACKAGE BODY. There is never an underscore in PACKAGE BODY.
CREATED	This is the program creation date. To see the precise time of creation, change the default display for a DATE format: ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON HH24:MI'; If you drop a procedure, running the CREATE OR REPLACE script resets the CREATED date. This is equivalent to creating the object for the first time. Once created, you can recompile it many times. The recompile data is the LAST_DDL_TIME date.
LAST_DDL_TIME	You can recompile a stored procedure many times. The recompile might fail or it might succeed. Either way, the recompilation updates the column LAST_DDL_TIME.
STATUS	Objects can have a status of VALID or INVALID. A procedure that inserts into the STUDENTS table becomes invalid if you drop the STUDENTS table. All objects should be VALID.

The following SQL is a general report on all the PL/SQL program units you have compiled into your schema.

 -- Filename CHECK_PLSQL_OBJECTS column object_name format a20 column last_ddl_time heading last_ddl SELECT object_name        decode(object_type,'PROCEDURE','(P)',        'FUNCTION','(F)', 'PACKAGE','(Spec)',        'PACKAGE BODY','(Body)') object_name,         status, created, last_ddl_time FROM   user_objects WHERE  object_type in ('PROCEDURE', 'FUNCTION',        'PACKAGE','PACKAGE BODY'); 

This output includes each procedure, function, specification and body, the status, and last compile time. If there is one procedure, HELLO, the result is:

 OBJECT_NAME    STATUS  CREATED           LAST_DDL_TIME -------------- ------- ----------------- ----------------- HELLO(P)       VALID   14-jul-2003 16:18 14-jul-2003 16:18 

The SQL built-in function USER evaluates to your current Oracle session account. To see what packages other users have extended to you, excluding the data dictionary SYS packages, select all PACKAGE type objects from ALL_OBJECTS and exclude SYS and yourself:

 SELECT owner, object_name FROM all_objects WHERE object_type='PACKAGE' AND owner <> ' SYS' AND owner <> USER;