Query all INVALID objects from user_objects table
SQL>
SQL> column object_name format a30
SQL> column tablespace_name format a30
SQL> column object_type format a20
SQL> column status format a1
SQL>
SQL> break on object_type skip 1
SQL>
SQL> select object_type, object_name,
2 decode( status, 'INVALID', '*', '' ) status,
3 decode( object_type,
4 'TABLE',
5 (select tablespace_name
6 from user_tables
7 where table_name = object_name),
8 'TABLE PARTITION',
9 (select tablespace_name
10 from user_tab_partitions
11 where partition_name = subobject_name),
12 'INDEX',
13 (select tablespace_name
14 from user_indexes
15 where index_name = object_name),
16 'INDEX PARTITION',
17 (select tablespace_name
18 from user_ind_partitions
19 where partition_name = subobject_name),
20 'LOB',
21 (select tablespace_name
22 from user_segments
23 where segment_name = object_name),
24 null ) tablespace_name
25 from user_objects a
26 order by object_type, object_name
27 /
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
EVALUATION CONTEXT AQ$_EMP_CHANGES_V
FUNCTION ADD_MON
DONTCOUNTSP
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
FUNCTION EXITFUNC
FACTORIAL
GETEMPBLDGNAME *
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
FUNCTION MY_TO_DATE
PIVOT
RAISE
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
FUNCTION REMOVE_CONSTANTS
VIRTUAL_TABLE
INDEX COMPILE_SCHEMA_TMP_PK
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
INDEX SYS_C005784
SYSTEM
SYS_C005787
SYSTEM
SYS_C005826
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
SYSTEM
INDEX SYS_C005827
SYSTEM
SYS_IOT_TOP_15816
SYSTEM
SYS_IOT_TOP_16247
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
SYSTEM
INDEX SYS_IOT_TOP_16249
SYSTEM
SYS_IOT_TOP_16251
SYSTEM
SYS_IOT_TOP_16254
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
SYSTEM
LOB SYS_LOB0000016238C00034$$
SYSTEM
PACKAGE DEBUG
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PACKAGE EMP_DEPT_PROCS *
PEOPLE *
STATE_PACKAGE
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PACKAGE BODY DEBUG *
EMP_DEPT_PROCS *
PEOPLE *
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PROCEDURE ASSERT
ASSIGNEMPTOBLDG *
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PROCEDURE COMPANY_LISTING *
COMPILE_SCHEMA *
DEBUG_TIMER *
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PROCEDURE DO_COMMIT *
EMP_CHANGE_S *
ERASE *
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PROCEDURE FILE_DUMP
GET_NEXT_OBJECT_TO_COMPILE *
HELLO
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PROCEDURE MAKE_MYTABLE
MEASURE_USAGE *
P
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PROCEDURE PRINT_TABLE
P_ADD_EMPS *
P_ADD_ORDERS *
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PROCEDURE P_ADD_PROD *
SHOW_IOT_SPACE
SHOW_SPACE
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
PROCEDURE WRITE_NAME *
QUEUE AQ$_EMP_CHANGES_E
EMP_DEPT_CHANGES
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
RULE SET EMP_DEPT_CHANGES_N
EMP_DEPT_CHANGES_R
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
SEQUENCE AQ$_EMP_CHANGES_N
TABLE AQ$_EMP_CHANGES_G
AQ$_EMP_CHANGES_H
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TABLE AQ$_EMP_CHANGES_I
AQ$_EMP_CHANGES_S
SYSTEM
AQ$_EMP_CHANGES_T
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TABLE COMPILE_SCHEMA_TMP
DEPT_AND_EMP
SYSTEM
DONE
SYSTEM
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TABLE EMPS_NT
EMP_CHANGES
SYSTEM
EMP_REG
SYSTEM
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TABLE MYTABLE_SESSION
SESS_EVENT
SQL_AREA_TMP
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TABLE SYS_IOT_OVER_16251
SYSTEM
TEMP_EMP
UPPER_ENAME
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TRIGGER EMP_NAME_CHANGE *
IO_BIFER_DEPT_EMP_VIEW *
LOGON_TRIGGER
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TRIGGER LOG_SHUTDOWN *
LOG_STARTUP *
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TYPE ADDRESS
ELEMENTTYPE
EMPLOYEES_LIST
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TYPE EMPLOYEE_TYPE
EMP_CHG
EMP_TAB_TYPE
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TYPE EMP_TYPE
NEW_PERSON
PERSON
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TYPE RESULTTYPE
ROWTYPE
SHAPE
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TYPE SUPER_TYPE
VC2TAB
VIRTUAL_TABLE_TYPE
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
TYPE BODY ADDRESS *
NEW_PERSON
VIEW AQ$EMP_CHANGES
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
VIEW AQ$EMP_CHANGES_R
AQ$EMP_CHANGES_S
AQ$_EMP_CHANGES_F
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
VIEW DEPARTMENT_10 *
DEPT_EMP_VIEW *
EMP_PUBLIC_DATA *
OBJECT_TYPE OBJECT_NAME S
-------------------- ------------------------------ -
TABLESPACE_NAME
------------------------------
VIEW VIEW_T *
102 rows selected.
SQL> column status format a10
SQL>
Related examples in the same category