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>
Related examples in the same category