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

Материал из SQL эксперт
Перейти к: навигация, поиск

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>