Getting a Database List : user_segments « System Tables Views « Oracle PL / 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>

   
  








Related examples in the same category

1.Check if an object is valid by querying user_segments table
2.select segment_name, segment_type from user_segments