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.
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.
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; |