Query all INVALID objects from user_objects table : USER_OBJECTS « System Tables Views « Oracle PL / SQL






Query all INVALID objects from user_objects table

    
SQL>
SQL> column object_name format a30
SQL> column tablespace_name format a30
SQL> column object_type format a20
SQL> column status format a1
SQL>
SQL> break on object_type skip 1
SQL>
SQL> select object_type, object_name,
  2         decode( status, 'INVALID', '*', '' ) status,
  3         decode( object_type,
  4                 'TABLE',
  5                 (select tablespace_name
  6                    from user_tables
  7                   where table_name = object_name),
  8                 'TABLE PARTITION',
  9                 (select tablespace_name
 10                    from user_tab_partitions
 11                   where partition_name = subobject_name),
 12                 'INDEX',
 13                 (select tablespace_name
 14                    from user_indexes
 15                   where index_name = object_name),
 16                 'INDEX PARTITION',
 17                 (select tablespace_name
 18                    from user_ind_partitions
 19                   where partition_name = subobject_name),
 20                 'LOB',
 21                 (select tablespace_name
 22                    from user_segments
 23                   where segment_name = object_name),
 24                 null ) tablespace_name
 25    from user_objects a
 26   order by object_type, object_name
 27  /

OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
EVALUATION CONTEXT   AQ$_EMP_CHANGES_V



FUNCTION             ADD_MON


                     DONTCOUNTSP


OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------

FUNCTION             EXITFUNC


                     FACTORIAL


                     GETEMPBLDGNAME                 *


OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------

FUNCTION             MY_TO_DATE


                     PIVOT


                     RAISE


OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------

FUNCTION             REMOVE_CONSTANTS


                     VIRTUAL_TABLE



INDEX                COMPILE_SCHEMA_TMP_PK

OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------


INDEX                SYS_C005784
SYSTEM

                     SYS_C005787
SYSTEM

                     SYS_C005826

OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
SYSTEM

INDEX                SYS_C005827
SYSTEM

                     SYS_IOT_TOP_15816
SYSTEM

                     SYS_IOT_TOP_16247

OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
SYSTEM

INDEX                SYS_IOT_TOP_16249
SYSTEM

                     SYS_IOT_TOP_16251
SYSTEM

                     SYS_IOT_TOP_16254

OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
SYSTEM


LOB                  SYS_LOB0000016238C00034$$
SYSTEM


PACKAGE              DEBUG


OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------

PACKAGE              EMP_DEPT_PROCS                 *


                     PEOPLE                         *


                     STATE_PACKAGE


OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------


PACKAGE BODY         DEBUG                          *


                     EMP_DEPT_PROCS                 *


                     PEOPLE                         *

OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------



PROCEDURE            ASSERT


                     ASSIGNEMPTOBLDG                *



OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PROCEDURE            COMPANY_LISTING                *


                     COMPILE_SCHEMA                 *


                     DEBUG_TIMER                    *



OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PROCEDURE            DO_COMMIT                      *


                     EMP_CHANGE_S                   *


                     ERASE                          *



OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PROCEDURE            FILE_DUMP


                     GET_NEXT_OBJECT_TO_COMPILE     *


                     HELLO



OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PROCEDURE            MAKE_MYTABLE


                     MEASURE_USAGE                  *


                     P



OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PROCEDURE            PRINT_TABLE


                     P_ADD_EMPS                     *


                     P_ADD_ORDERS                   *



OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PROCEDURE            P_ADD_PROD                     *


                     SHOW_IOT_SPACE


                     SHOW_SPACE



OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PROCEDURE            WRITE_NAME                     *



QUEUE                AQ$_EMP_CHANGES_E


                     EMP_DEPT_CHANGES


OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------


RULE SET             EMP_DEPT_CHANGES_N


                     EMP_DEPT_CHANGES_R




OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
SEQUENCE             AQ$_EMP_CHANGES_N



TABLE                AQ$_EMP_CHANGES_G


                     AQ$_EMP_CHANGES_H


OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------

TABLE                AQ$_EMP_CHANGES_I


                     AQ$_EMP_CHANGES_S
SYSTEM

                     AQ$_EMP_CHANGES_T


OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------

TABLE                COMPILE_SCHEMA_TMP


                     DEPT_AND_EMP
SYSTEM

                     DONE
SYSTEM

OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------

TABLE                EMPS_NT


                     EMP_CHANGES
SYSTEM

                     EMP_REG
SYSTEM

OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------

TABLE                MYTABLE_SESSION


                     SESS_EVENT


                     SQL_AREA_TMP


OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------

TABLE                SYS_IOT_OVER_16251
SYSTEM

                     TEMP_EMP


                     UPPER_ENAME


OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------


TRIGGER              EMP_NAME_CHANGE                *


                     IO_BIFER_DEPT_EMP_VIEW         *


                     LOGON_TRIGGER

OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------


TRIGGER              LOG_SHUTDOWN                   *


                     LOG_STARTUP                    *




OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TYPE                 ADDRESS


                     ELEMENTTYPE


                     EMPLOYEES_LIST



OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TYPE                 EMPLOYEE_TYPE


                     EMP_CHG


                     EMP_TAB_TYPE



OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TYPE                 EMP_TYPE


                     NEW_PERSON


                     PERSON



OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TYPE                 RESULTTYPE


                     ROWTYPE


                     SHAPE



OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TYPE                 SUPER_TYPE


                     VC2TAB


                     VIRTUAL_TABLE_TYPE



OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------

TYPE BODY            ADDRESS                        *


                     NEW_PERSON



VIEW                 AQ$EMP_CHANGES

OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------


VIEW                 AQ$EMP_CHANGES_R


                     AQ$EMP_CHANGES_S


                     AQ$_EMP_CHANGES_F

OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------


VIEW                 DEPARTMENT_10                  *


                     DEPT_EMP_VIEW                  *


                     EMP_PUBLIC_DATA                *

OBJECT_TYPE          OBJECT_NAME                    S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------


VIEW                 VIEW_T                         *




102 rows selected.

SQL> column status format a10
SQL>

   
    
    
  








Related examples in the same category

1.Query user_objects table
2.Query USER_OBJECTS table by object name
3.Query object_type, object_name from user_objects
4.Query user_objects table for all procedure
5.list all stored procedures: 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY'
6.Query user_objects in PL/SQL
7.Query a view in user_objects table for a view just created
8.Query user_objects for invalid package body
9.Query user-objects for stored procedure
10.Query user_objects for trigger
11.Query user_objects for invalid view
12.Get code for all procedure, function and package from user_objects
13.Query user_objects table for stored procedure before and after recompile
14.If procedure is valid
15.A procedure with dependencies
16.Check package status
17.Check new created tables in user_objects
18.Finding, Validating, and Describing Packages
19.Get object id for created table
20.Show the procedure is marked invalid **
21.Show the status of a procedure
22.To find out what procedures and functions you have created, use the following SQL query: