Oracle PL/SQL/System Tables Views/user segments
Check if an object is valid by querying user_segments table
<source lang="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 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>
</source>
Getting a Database List
<source lang="sql">
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>
</source>
select segment_name, segment_type from user_segments
<source lang="sql">
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>
</source>
