Mega Code Archive

 

Categories / Oracle PLSQL / System Tables Views

 

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>