Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / System Tables Data Dictionary

 

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>