Oracle PL/SQL Tutorial/System Tables Data Dictionary/user segments

Get invalid table objects

SQL> column object_name format a30
SQL> column tablespace_name format a30
SQL> column object_type format a12
SQL> column status format a1
SQL> column status format a10
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                    STATUS     TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
FUNCTION     COMPUTE_DISCOUNTS
             DELETEEMPLOYEE                 *
             EXITFUNC
             F_GETDIFF                      *
             GETAREA
INDEX        BIN$fKX+qNArQt+8GLZvNQJlrQ==$0            SYSTEM

OBJECT_TYPE  OBJECT_NAME                    STATUS     TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
PACKAGE      PKG                            *
             PKG_TEST1
PACKAGE BODY PKG_TEST1
PROCEDURE    ADD_NEW_EMPLOYEE               *
 clear columns;
OBJECT_TYPE  OBJECT_NAME                    STATUS     TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
PROCEDURE    AUTHORS_SEL                    *
             AUTHOR_FIRST_NAME_UPD          *
             AUTHOR_SEL                     *
             BIND_TEST                      *
             CHANGEPRICE
             COMPILE_WARNING                *
             DELETEMYEMPLOYEE               *
OBJECT_TYPE  OBJECT_NAME                    STATUS     TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
PROCEDURE    EMPLOYEE_COUNT                 *
             EMP_CHANGE_S                   *
             HELLO
             HELLOFLE                       *
             HIKE_PRICES
             INSERT_NUMBERS                 *
             MEASURE_USAGE                  *
OBJECT_TYPE  OBJECT_NAME                    STATUS     TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
PROCEDURE    MULTIPLE_CURSORS_PROC
             MYPROCEDURE                    *
             MY_FIRST_PROC
             NAMED_BLOCK                    *
             P
             P_HELLO
             P_HELLOTO
OBJECT_TYPE  OBJECT_NAME                    STATUS     TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
TABLE        BIN$dgfyftgCSZSBnu0LqVJzrQ==$0            USERS
             BIN$zbJqSAxnQA2up7MHjGauHw==$0            USERS
             EMPLOYEE_TAB
             EMP_DEPT                                  SYSTEM
             PEOPLE                                    SYSTEM

OBJECT_TYPE  OBJECT_NAME                    STATUS     TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
TRIGGER      EMP_NAME_CHANGE                *
             LOG_SHUTDOWN                   *
             LOG_STARTUP                    *
TYPE         ADDRESSTYPE
             ADDRESS_OBJ
             AOBJ
OBJECT_TYPE  OBJECT_NAME                    STATUS     TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
TYPE         EMPLOYEETYPE
             PERSONTYPE
             PRODUCTTYPE
TYPE BODY    AOBJ
             EMPLOYEETYPE

OBJECT_TYPE  OBJECT_NAME                    STATUS     TABLESPACE_NAME
------------ ------------------------------ ---------- ------------------------------
VIEW         EMP_DATA                       *
             NEWYORKEMP                     *

49 rows selected.
SQL>

select * from DBA_SEGMENTS where Segment_Type = "ROLLBACK"

SQL>
SQL>
SQL>
SQL> select * from DBA_SEGMENTS
  2   where Segment_Type = "ROLLBACK";
                                                              
OWNER         SEGMENT_NAME
------------  ---------------------------------------------------------------------------------
PARTITION_NAME                  SEGMENT_TYPE        TABLESPACE_NAME                 HEADER_FILE  HEADER_BLOCK
------------------------------  ------------------  ------------------------------  -----------  ------------
     BYTES      BLOCKS     EXTENTS  INITIAL_EXTENT  NEXT_EXTENT  MIN_EXTENTS  MAX_EXTENTS  PCT_INCREASE   FREELISTS
----------  ----------  ----------  --------------  -----------  -----------  -----------  ------------  ----------
FREELIST_GROUPS  RELATIVE_FNO  BUFFER_
---------------  ------------  -------
SYS           SYSTEM
                                ROLLBACK            SYSTEM                                    1             9
    393216          48           6          114688      1048576            1        32765             0           1
              1             1  DEFAULT
                  
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>