Oracle PL/SQL/System Tables Views/user segments
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>
Getting a Database List
SQL>
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;
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
I_CDEF1 SYSTEM
I_CDEF2 SYSTEM
I_CDEF4 SYSTEM
I_COBJ# SYSTEM
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX I_COL2 SYSTEM
I_COL3 SYSTEM
I_CON1 SYSTEM
I_CON2 SYSTEM
I_FILE#_BLOCK# SYSTEM
I_FILE1 SYSTEM
I_FILE2 SYSTEM
I_IND1 SYSTEM
I_OBJ# SYSTEM
I_OBJ1 SYSTEM
I_OBJ2 SYSTEM
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX I_OBJ3 SYSTEM
I_PROXY_DATA$ SYSTEM
I_PROXY_ROLE_DATA$_1 SYSTEM
I_TAB1 SYSTEM
I_TS# SYSTEM
I_TS1 SYSTEM
I_UNDO1 SYSTEM
I_UNDO2 SYSTEM
I_USER1 SYSTEM
TABLE BOOTSTRAP$ SYSTEM
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
TABLE CCOL$
CDEF$
CLU$
COL$
CON$ SYSTEM
FET$
FILE$ SYSTEM
ICOL$
IND$
OBJ$ SYSTEM
PROXY_DATA$ SYSTEM
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
TABLE PROXY_ROLE_DATA$ SYSTEM
SEG$
TAB$
TS$
UET$
UNDO$ SYSTEM
USER$
49 rows selected.
SQL>
SQL> column status format a10
SQL>
SQL>
select segment_name, segment_type from user_segments
SQL>
SQL> select segment_name, segment_type from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
DEPT$AUDIT
TABLE
P1
TABLE
MYSTATS
TABLE
IT
TABLE
DEMO
TABLE
CUST_NO_KEY_TABLE
TABLE
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
PASSWORD_AUDIT
TABLE
CUSTLOG
TABLE
EMP_DELTAS
TABLE
INVENTORY_TBL
TABLE
P
TABLE
MYTABLE1
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
TABLE
SYSTEM_STATS
TABLE
HD_CLUSTER
CLUSTER
SYS_C005096
INDEX
MYSTATS
INDEX
SYS_C005376
INDEX
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
SYS_C005536
INDEX
SYS_C005404
INDEX
SYS_IOT_TOP_16135
INDEX
SYS_C006948
INDEX
HD_CLUSTER_INDEX
INDEX
SYS_C0010267
INDEX
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
SYS_C009993
INDEX
SYSTEM_STATS
INDEX
25 rows selected.
SQL>
SQL> create table t ( x int primary key, y clob, z blob );
Table created.
SQL>
SQL> select segment_name, segment_type from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
DEPT$AUDIT
TABLE
P1
TABLE
MYSTATS
TABLE
IT
TABLE
DEMO
TABLE
CUST_NO_KEY_TABLE
TABLE
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
PASSWORD_AUDIT
TABLE
CUSTLOG
TABLE
EMP_DELTAS
TABLE
INVENTORY_TBL
TABLE
P
TABLE
MYTABLE1
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
TABLE
T
TABLE
SYSTEM_STATS
TABLE
HD_CLUSTER
CLUSTER
SYS_C005096
INDEX
MYSTATS
INDEX
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
SYS_C005376
INDEX
SYS_C005536
INDEX
SYS_C005404
INDEX
SYS_IOT_TOP_16135
INDEX
SYS_C006948
INDEX
HD_CLUSTER_INDEX
INDEX
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
SYS_C0010267
INDEX
SYS_C009993
INDEX
SYS_IL0000021357C00002$$
LOBINDEX
SYS_IL0000021357C00003$$
LOBINDEX
SYS_C0010294
INDEX
SYSTEM_STATS
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
INDEX
SYS_LOB0000021357C00002$$
LOBSEGMENT
SYS_LOB0000021357C00003$$
LOBSEGMENT
31 rows selected.
SQL>
SQL> drop table t;
Table dropped.
SQL>