Get all invalid user objects in user_objects table : user_objects « System Tables Data Dictionary « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. System Tables Data Dictionary
  3. user_objects
SQL>
SQL>
SQL> SELECT    object_type, object_name, status, created, last_ddl_time
  2  FROM      user_objects
  3  WHERE     object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE',
  4                            'PACKAGE BODY', 'TRIGGER')
  5  AND       status = 'INVALID'
  6  and       rownum < 50;

OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PROCEDURE           DELETE_CUST               INVALID 17-JUL-08
24-JUL-08

PROCEDURE           WAIT                      INVALID 16-JUL-08
24-JUL-08

PACKAGE             WORKPLACE_TYPE_TS         INVALID 16-JUL-08
24-JUL-08


OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PACKAGE BODY        WORKPLACE_TYPE_TS         INVALID 16-JUL-08
24-JUL-08

PACKAGE             WORKER_TYPE_TS            INVALID 16-JUL-08
24-JUL-08

PACKAGE             GENDER_TS                 INVALID 16-JUL-08
24-JUL-08


OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PACKAGE BODY        GENDER_TS                 INVALID 16-JUL-08
24-JUL-08

PACKAGE             WORKER_TS                 INVALID 16-JUL-08
24-JUL-08

PACKAGE BODY        WORKER_TS                 INVALID 16-JUL-08
24-JUL-08


OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PACKAGE BODY        WORKER_TYPE_TS            INVALID 16-JUL-08
24-JUL-08

PROCEDURE           P_RUN_INSERT              INVALID 17-JUL-08
24-JUL-08

PROCEDURE           ROW_AT_A_TIME_TEST        INVALID 17-JUL-08
24-JUL-08


OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PROCEDURE           BULK_TEST                 INVALID 17-JUL-08
24-JUL-08

PACKAGE             CLASSPACKAGE              INVALID 28-JUN-08
24-JUL-08

PACKAGE BODY        CLASSPACKAGE              INVALID 28-JUN-08
24-JUL-08


OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PROCEDURE           FCREATE                   INVALID 14-JUL-08
24-JUL-08

PROCEDURE           CLOSECUR                  INVALID 14-JUL-08
24-JUL-08

PROCEDURE           SAVESTRINGTOFILE          INVALID 14-JUL-08
24-JUL-08


OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PROCEDURE           PUT_BOOLEAN               INVALID 14-JUL-08
24-JUL-08

PROCEDURE           CREINDX                   INVALID 14-JUL-08
24-JUL-08

PROCEDURE           GET_LINE                  INVALID 14-JUL-08
24-JUL-08


OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
FUNCTION            RECEIVE_MESSAGE           INVALID 14-JUL-08
24-JUL-08

FUNCTION            TRACEIT                   INVALID 14-JUL-08
24-JUL-08

PROCEDURE           SEND_MESSAGE              INVALID 14-JUL-08
24-JUL-08


OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PACKAGE             ONECUR                    INVALID 14-JUL-08
24-JUL-08

PROCEDURE           SHOWEMPS                  INVALID 14-JUL-08
24-JUL-08

PROCEDURE           PROCA                     INVALID 14-JUL-08
24-JUL-08


OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PROCEDURE           PROCB                     INVALID 14-JUL-08
24-JUL-08

PACKAGE BODY        MYDATE                    INVALID 14-JUL-08
24-JUL-08

PROCEDURE           SHOW_FILE                 INVALID 14-JUL-08
24-JUL-08


OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
FUNCTION            FOPEN                     INVALID 14-JUL-08
24-JUL-08

FUNCTION            EMP_COUNT                 INVALID 14-JUL-08
24-JUL-08

PROCEDURE           ORG_PROC                  INVALID 15-JUL-08
24-JUL-08


OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
FUNCTION            F_CURSOR_EXP              INVALID 15-JUL-08
24-JUL-08

PROCEDURE           P_PRINT_REPORT            INVALID 15-JUL-08
24-JUL-08

FUNCTION            F_CURSOR_EXP_COMPLEX      INVALID 15-JUL-08
24-JUL-08


OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
FUNCTION            GETALLHIERARCHIES         INVALID 15-JUL-08
24-JUL-08

PACKAGE BODY        ORGMASTER                 INVALID 15-JUL-08
24-JUL-08

FUNCTION            F_GET_FORMATTED_ORG_NAME  INVALID 15-JUL-08
24-JUL-08


OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PACKAGE BODY        RFPKG                     INVALID 15-JUL-08
24-JUL-08

PACKAGE BODY        RFPKG2                    INVALID 15-JUL-08
24-JUL-08

PACKAGE             NOCOPYPKG                 INVALID 15-JUL-08
24-JUL-08


OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PACKAGE BODY        NOCOPYPKG                 INVALID 15-JUL-08
24-JUL-08

PROCEDURE           UPDATE_DYN_GLOBAL_BULK2   INVALID 16-JUL-08
24-JUL-08

PACKAGE BODY        ORGMASTER2                INVALID 15-JUL-08
24-JUL-08


OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PROCEDURE           UPDATE_DYN_GLOBAL_BULK    INVALID 16-JUL-08
24-JUL-08

PACKAGE             SRPKG2                    INVALID 15-JUL-08
24-JUL-08

PACKAGE BODY        SRPKG2                    INVALID 15-JUL-08
24-JUL-08


OBJECT_TYPE         OBJECT_NAME               STATUS  CREATED
------------------- ------------------------- ------- --------------------
LAST_DDL_
---------
PROCEDURE           LOG_ERROR                 INVALID 15-JUL-08
25-JUL-08


49 rows selected.

SQL>








30.90.user_objects
30.90.1.Check user_objects for object name
30.90.2.Check the package status in user_objects
30.90.3.Check function/procedure status
30.90.4.Check the status for all stored procedures is by using the Oracle data dictionary view USER_OBJECTS
30.90.5.Get invalid table objects by joining user_segments and user_objects
30.90.6.Get all invalid user objects in user_objects table
30.90.7.Create drop command by query the user_objects table
30.90.8.Create grant permission command by querying user_objects table
30.90.9.Query user_objects for all packages, procedures and functions
30.90.10.Query user_objects for user-defined data type
30.90.11.Recompile all invalid package
30.90.12.Query user_objects for all java classes
30.90.13.Query user_objects for all PL/SQL code
30.90.14.Get Object id from user_objects
30.90.15.Join sys.col$ and user_objects
30.90.16.Query user_objects for object status
30.90.17.Query user_objects for your functions and procedures
30.90.18.User and its average object id
30.90.19.Show the procedure is marked invalid **
30.90.20.Behavior of dependent objects.
30.90.21.Finding, Validating, and Describing Packages
30.90.22.If procedure is valid