Oracle PL/SQL Tutorial/System Tables Data Dictionary/user segments
Get invalid table objects
<source lang="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> 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></source>
select * from DBA_SEGMENTS where Segment_Type = "ROLLBACK"
<source lang="sql">
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></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>