Mega Code Archive

 

Categories / Oracle PLSQL / System Tables Views

 

Check if an object is valid by querying user_segments table

SQL> SQL> column object_name format a30 SQL> column tablespace_name format a30 SQL> column object_type format a12 SQL> column status format a1 SQL> break on object_type skip 1 SQL> SQL> select object_type, object_name,   2         decode(status,'INVALID','*','') status,   3         tablespace_name   4    from user_objects a, user_segments b   5   where a.object_name = b.segment_name (+)   6     and a.object_type = b.segment_type (+)   7     and rownum < 50   8   order by object_type, object_name   9  / OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME ------------ ------------------------------ - ------------------------------ CLUSTER      C_COBJ#                          SYSTEM              C_FILE#_BLOCK#                   SYSTEM              C_OBJ#                           SYSTEM              C_TS#                            SYSTEM              C_USER#                          SYSTEM INDEX        I_CCOL1                          SYSTEM OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME ------------ ------------------------------ - ------------------------------ INDEX        I_CDEF1                          SYSTEM              I_CDEF2                          SYSTEM              I_CDEF4                          SYSTEM              I_COBJ#                          SYSTEM              I_COL2                           SYSTEM              I_COL3                           SYSTEM              I_CON1                           SYSTEM OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME ------------ ------------------------------ - ------------------------------ INDEX        I_CON2                           SYSTEM              I_FILE#_BLOCK#                   SYSTEM              I_FILE1                          SYSTEM              I_FILE2                          SYSTEM              I_IND1                           SYSTEM              I_OBJ#                           SYSTEM              I_OBJ1                           SYSTEM OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME ------------ ------------------------------ - ------------------------------ INDEX        I_OBJ2                           SYSTEM              I_OBJ3                           SYSTEM              I_PROXY_DATA$                    SYSTEM              I_PROXY_ROLE_DATA$_1             SYSTEM              I_TAB1                           SYSTEM              I_TS#                            SYSTEM              I_TS1                            SYSTEM OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME ------------ ------------------------------ - ------------------------------ INDEX        I_UNDO1                          SYSTEM              I_UNDO2                          SYSTEM              I_USER1                          SYSTEM TABLE        BOOTSTRAP$                       SYSTEM              CCOL$              CDEF$ OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME ------------ ------------------------------ - ------------------------------ TABLE        CLU$              COL$              CON$                             SYSTEM              FET$              FILE$                            SYSTEM              ICOL$              IND$ OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME ------------ ------------------------------ - ------------------------------ TABLE        OBJ$                             SYSTEM              PROXY_DATA$                      SYSTEM              PROXY_ROLE_DATA$                 SYSTEM              SEG$              TAB$              TS$              UET$ OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME ------------ ------------------------------ - ------------------------------ TABLE        UNDO$                            SYSTEM              USER$ 49 rows selected. SQL> column status format a10 SQL>