Oracle PL/SQL/System Tables Views/user segments — различия между версиями

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

Текущая версия на 10:01, 26 мая 2010

Check if an object is valid by querying user_segments table

  
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> 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                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
CLUSTER      C_COBJ#                          SYSTEM
             C_FILE#_BLOCK#                   SYSTEM
             C_OBJ#                           SYSTEM
             C_TS#                            SYSTEM
             C_USER#                          SYSTEM
INDEX        I_CCOL1                          SYSTEM
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX        I_CDEF1                          SYSTEM
             I_CDEF2                          SYSTEM
             I_CDEF4                          SYSTEM
             I_COBJ#                          SYSTEM
             I_COL2                           SYSTEM
             I_COL3                           SYSTEM
             I_CON1                           SYSTEM
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX        I_CON2                           SYSTEM
             I_FILE#_BLOCK#                   SYSTEM
             I_FILE1                          SYSTEM
             I_FILE2                          SYSTEM
             I_IND1                           SYSTEM
             I_OBJ#                           SYSTEM
             I_OBJ1                           SYSTEM
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX        I_OBJ2                           SYSTEM
             I_OBJ3                           SYSTEM
             I_PROXY_DATA$                    SYSTEM
             I_PROXY_ROLE_DATA$_1             SYSTEM
             I_TAB1                           SYSTEM
             I_TS#                            SYSTEM
             I_TS1                            SYSTEM
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX        I_UNDO1                          SYSTEM
             I_UNDO2                          SYSTEM
             I_USER1                          SYSTEM
TABLE        BOOTSTRAP$                       SYSTEM
             CCOL$
             CDEF$
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
TABLE        CLU$
             COL$
             CON$                             SYSTEM
             FET$
             FILE$                            SYSTEM
             ICOL$
             IND$
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
TABLE        OBJ$                             SYSTEM
             PROXY_DATA$                      SYSTEM
             PROXY_ROLE_DATA$                 SYSTEM
             SEG$
             TAB$
             TS$
             UET$
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
TABLE        UNDO$                            SYSTEM
             USER$

49 rows selected.
SQL> column status format a10
SQL>



Getting a Database List

  
SQL>
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> 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;
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
CLUSTER      C_COBJ#                          SYSTEM
             C_FILE#_BLOCK#                   SYSTEM
             C_OBJ#                           SYSTEM
             C_TS#                            SYSTEM
             C_USER#                          SYSTEM
INDEX        I_CCOL1                          SYSTEM
             I_CDEF1                          SYSTEM
             I_CDEF2                          SYSTEM
             I_CDEF4                          SYSTEM
             I_COBJ#                          SYSTEM
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX        I_COL2                           SYSTEM
             I_COL3                           SYSTEM
             I_CON1                           SYSTEM
             I_CON2                           SYSTEM
             I_FILE#_BLOCK#                   SYSTEM
             I_FILE1                          SYSTEM
             I_FILE2                          SYSTEM
             I_IND1                           SYSTEM
             I_OBJ#                           SYSTEM
             I_OBJ1                           SYSTEM
             I_OBJ2                           SYSTEM
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX        I_OBJ3                           SYSTEM
             I_PROXY_DATA$                    SYSTEM
             I_PROXY_ROLE_DATA$_1             SYSTEM
             I_TAB1                           SYSTEM
             I_TS#                            SYSTEM
             I_TS1                            SYSTEM
             I_UNDO1                          SYSTEM
             I_UNDO2                          SYSTEM
             I_USER1                          SYSTEM
TABLE        BOOTSTRAP$                       SYSTEM
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
TABLE        CCOL$
             CDEF$
             CLU$
             COL$
             CON$                             SYSTEM
             FET$
             FILE$                            SYSTEM
             ICOL$
             IND$
             OBJ$                             SYSTEM
             PROXY_DATA$                      SYSTEM
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
TABLE        PROXY_ROLE_DATA$                 SYSTEM
             SEG$
             TAB$
             TS$
             UET$
             UNDO$                            SYSTEM
             USER$

49 rows selected.
SQL>
SQL> column status format a10
SQL>
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>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/System_Tables_Views/user_segments&oldid=2337»