http://sqle.ru/index.php?action=history&feed=atom&title=Oracle_PL%2FSQL%2FSystem_Tables_Views%2Fuser_segments
Oracle PL/SQL/System Tables Views/user segments - История изменений
2026-07-15T13:17:17Z
История изменений этой страницы в вики
MediaWiki 1.30.0
http://sqle.ru/index.php?title=Oracle_PL/SQL/System_Tables_Views/user_segments&diff=2336&oldid=prev
в 13:45, 26 мая 2010
2010-05-26T13:45:19Z
<p></p>
<table class="diff diff-contentalign-left" data-mw="interface">
<tr style="vertical-align: top;" lang="ru">
<td colspan="1" style="background-color: white; color:black; text-align: center;">← Предыдущая</td>
<td colspan="1" style="background-color: white; color:black; text-align: center;">Версия 13:45, 26 мая 2010</td>
</tr><tr><td colspan="2" style="text-align: center;" lang="ru"><div class="mw-diff-empty">(нет различий)</div>
</td></tr></table>
http://sqle.ru/index.php?title=Oracle_PL/SQL/System_Tables_Views/user_segments&diff=2337&oldid=prev
Admin: 1 версия
2010-05-26T10:01:23Z
<p>1 версия</p>
<p><b>Новая страница</b></p><div>==Check if an object is valid by querying user_segments table==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
<br />
SQL><br />
SQL> column object_name format a30<br />
SQL> column tablespace_name format a30<br />
SQL> column object_type format a12<br />
SQL> column status format a1<br />
SQL> break on object_type skip 1<br />
SQL><br />
SQL> select object_type, object_name,<br />
2 decode(status,"INVALID","*","") status,<br />
3 tablespace_name<br />
4 from user_objects a, user_segments b<br />
5 where a.object_name = b.segment_name (+)<br />
6 and a.object_type = b.segment_type (+)<br />
7 and rownum < 50<br />
8 order by object_type, object_name<br />
9 /<br />
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME<br />
------------ ------------------------------ - ------------------------------<br />
CLUSTER C_COBJ# SYSTEM<br />
C_FILE#_BLOCK# SYSTEM<br />
C_OBJ# SYSTEM<br />
C_TS# SYSTEM<br />
C_USER# SYSTEM<br />
INDEX I_CCOL1 SYSTEM<br />
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME<br />
------------ ------------------------------ - ------------------------------<br />
INDEX I_CDEF1 SYSTEM<br />
I_CDEF2 SYSTEM<br />
I_CDEF4 SYSTEM<br />
I_COBJ# SYSTEM<br />
I_COL2 SYSTEM<br />
I_COL3 SYSTEM<br />
I_CON1 SYSTEM<br />
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME<br />
------------ ------------------------------ - ------------------------------<br />
INDEX I_CON2 SYSTEM<br />
I_FILE#_BLOCK# SYSTEM<br />
I_FILE1 SYSTEM<br />
I_FILE2 SYSTEM<br />
I_IND1 SYSTEM<br />
I_OBJ# SYSTEM<br />
I_OBJ1 SYSTEM<br />
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME<br />
------------ ------------------------------ - ------------------------------<br />
INDEX I_OBJ2 SYSTEM<br />
I_OBJ3 SYSTEM<br />
I_PROXY_DATA$ SYSTEM<br />
I_PROXY_ROLE_DATA$_1 SYSTEM<br />
I_TAB1 SYSTEM<br />
I_TS# SYSTEM<br />
I_TS1 SYSTEM<br />
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME<br />
------------ ------------------------------ - ------------------------------<br />
INDEX I_UNDO1 SYSTEM<br />
I_UNDO2 SYSTEM<br />
I_USER1 SYSTEM<br />
TABLE BOOTSTRAP$ SYSTEM<br />
CCOL$<br />
CDEF$<br />
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME<br />
------------ ------------------------------ - ------------------------------<br />
TABLE CLU$<br />
COL$<br />
CON$ SYSTEM<br />
FET$<br />
FILE$ SYSTEM<br />
ICOL$<br />
IND$<br />
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME<br />
------------ ------------------------------ - ------------------------------<br />
TABLE OBJ$ SYSTEM<br />
PROXY_DATA$ SYSTEM<br />
PROXY_ROLE_DATA$ SYSTEM<br />
SEG$<br />
TAB$<br />
TS$<br />
UET$<br />
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME<br />
------------ ------------------------------ - ------------------------------<br />
TABLE UNDO$ SYSTEM<br />
USER$<br />
<br />
49 rows selected.<br />
SQL> column status format a10<br />
SQL><br />
<br />
</source><br />
<br />
<br />
<br />
<br />
==Getting a Database List==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
<br />
SQL><br />
SQL><br />
SQL> column object_name format a30<br />
SQL> column tablespace_name format a30<br />
SQL> column object_type format a12<br />
SQL> column status format a1<br />
SQL> break on object_type skip 1<br />
SQL><br />
SQL> select object_type, object_name,<br />
2 decode(status,"INVALID","*","") status,<br />
3 tablespace_name<br />
4 from user_objects a, user_segments b<br />
5 where a.object_name = b.segment_name (+)<br />
6 and a.object_type = b.segment_type (+)<br />
7 and rownum < 50<br />
8 order by object_type, object_name;<br />
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME<br />
------------ ------------------------------ - ------------------------------<br />
CLUSTER C_COBJ# SYSTEM<br />
C_FILE#_BLOCK# SYSTEM<br />
C_OBJ# SYSTEM<br />
C_TS# SYSTEM<br />
C_USER# SYSTEM<br />
INDEX I_CCOL1 SYSTEM<br />
I_CDEF1 SYSTEM<br />
I_CDEF2 SYSTEM<br />
I_CDEF4 SYSTEM<br />
I_COBJ# SYSTEM<br />
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME<br />
------------ ------------------------------ - ------------------------------<br />
INDEX I_COL2 SYSTEM<br />
I_COL3 SYSTEM<br />
I_CON1 SYSTEM<br />
I_CON2 SYSTEM<br />
I_FILE#_BLOCK# SYSTEM<br />
I_FILE1 SYSTEM<br />
I_FILE2 SYSTEM<br />
I_IND1 SYSTEM<br />
I_OBJ# SYSTEM<br />
I_OBJ1 SYSTEM<br />
I_OBJ2 SYSTEM<br />
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME<br />
------------ ------------------------------ - ------------------------------<br />
INDEX I_OBJ3 SYSTEM<br />
I_PROXY_DATA$ SYSTEM<br />
I_PROXY_ROLE_DATA$_1 SYSTEM<br />
I_TAB1 SYSTEM<br />
I_TS# SYSTEM<br />
I_TS1 SYSTEM<br />
I_UNDO1 SYSTEM<br />
I_UNDO2 SYSTEM<br />
I_USER1 SYSTEM<br />
TABLE BOOTSTRAP$ SYSTEM<br />
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME<br />
------------ ------------------------------ - ------------------------------<br />
TABLE CCOL$<br />
CDEF$<br />
CLU$<br />
COL$<br />
CON$ SYSTEM<br />
FET$<br />
FILE$ SYSTEM<br />
ICOL$<br />
IND$<br />
OBJ$ SYSTEM<br />
PROXY_DATA$ SYSTEM<br />
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME<br />
------------ ------------------------------ - ------------------------------<br />
TABLE PROXY_ROLE_DATA$ SYSTEM<br />
SEG$<br />
TAB$<br />
TS$<br />
UET$<br />
UNDO$ SYSTEM<br />
USER$<br />
<br />
49 rows selected.<br />
SQL><br />
SQL> column status format a10<br />
SQL><br />
SQL><br />
<br />
</source><br />
<br />
<br />
<br />
<br />
==select segment_name, segment_type from user_segments==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
<br />
SQL><br />
SQL> select segment_name, segment_type from user_segments;<br />
SEGMENT_NAME<br />
--------------------------------------------------------------------------------<br />
SEGMENT_TYPE<br />
------------------<br />
DEPT$AUDIT<br />
TABLE<br />
P1<br />
TABLE<br />
MYSTATS<br />
TABLE<br />
IT<br />
TABLE<br />
DEMO<br />
TABLE<br />
CUST_NO_KEY_TABLE<br />
TABLE<br />
SEGMENT_NAME<br />
--------------------------------------------------------------------------------<br />
SEGMENT_TYPE<br />
------------------<br />
PASSWORD_AUDIT<br />
TABLE<br />
CUSTLOG<br />
TABLE<br />
EMP_DELTAS<br />
TABLE<br />
INVENTORY_TBL<br />
TABLE<br />
P<br />
TABLE<br />
MYTABLE1<br />
SEGMENT_NAME<br />
--------------------------------------------------------------------------------<br />
SEGMENT_TYPE<br />
------------------<br />
TABLE<br />
SYSTEM_STATS<br />
TABLE<br />
HD_CLUSTER<br />
CLUSTER<br />
SYS_C005096<br />
INDEX<br />
MYSTATS<br />
INDEX<br />
SYS_C005376<br />
INDEX<br />
<br />
SEGMENT_NAME<br />
--------------------------------------------------------------------------------<br />
SEGMENT_TYPE<br />
------------------<br />
SYS_C005536<br />
INDEX<br />
SYS_C005404<br />
INDEX<br />
SYS_IOT_TOP_16135<br />
INDEX<br />
SYS_C006948<br />
INDEX<br />
HD_CLUSTER_INDEX<br />
INDEX<br />
SYS_C0010267<br />
INDEX<br />
SEGMENT_NAME<br />
--------------------------------------------------------------------------------<br />
SEGMENT_TYPE<br />
------------------<br />
SYS_C009993<br />
INDEX<br />
SYSTEM_STATS<br />
INDEX<br />
<br />
25 rows selected.<br />
SQL><br />
SQL> create table t ( x int primary key, y clob, z blob );<br />
Table created.<br />
SQL><br />
SQL> select segment_name, segment_type from user_segments;<br />
SEGMENT_NAME<br />
--------------------------------------------------------------------------------<br />
SEGMENT_TYPE<br />
------------------<br />
DEPT$AUDIT<br />
TABLE<br />
P1<br />
TABLE<br />
MYSTATS<br />
TABLE<br />
IT<br />
TABLE<br />
DEMO<br />
TABLE<br />
CUST_NO_KEY_TABLE<br />
TABLE<br />
SEGMENT_NAME<br />
--------------------------------------------------------------------------------<br />
SEGMENT_TYPE<br />
------------------<br />
PASSWORD_AUDIT<br />
TABLE<br />
CUSTLOG<br />
TABLE<br />
EMP_DELTAS<br />
TABLE<br />
INVENTORY_TBL<br />
TABLE<br />
P<br />
TABLE<br />
MYTABLE1<br />
SEGMENT_NAME<br />
--------------------------------------------------------------------------------<br />
SEGMENT_TYPE<br />
------------------<br />
TABLE<br />
T<br />
TABLE<br />
SYSTEM_STATS<br />
TABLE<br />
HD_CLUSTER<br />
CLUSTER<br />
SYS_C005096<br />
INDEX<br />
MYSTATS<br />
INDEX<br />
<br />
SEGMENT_NAME<br />
--------------------------------------------------------------------------------<br />
SEGMENT_TYPE<br />
------------------<br />
SYS_C005376<br />
INDEX<br />
SYS_C005536<br />
INDEX<br />
SYS_C005404<br />
INDEX<br />
SYS_IOT_TOP_16135<br />
INDEX<br />
SYS_C006948<br />
INDEX<br />
HD_CLUSTER_INDEX<br />
INDEX<br />
SEGMENT_NAME<br />
--------------------------------------------------------------------------------<br />
SEGMENT_TYPE<br />
------------------<br />
SYS_C0010267<br />
INDEX<br />
SYS_C009993<br />
INDEX<br />
SYS_IL0000021357C00002$$<br />
LOBINDEX<br />
SYS_IL0000021357C00003$$<br />
LOBINDEX<br />
SYS_C0010294<br />
INDEX<br />
SYSTEM_STATS<br />
SEGMENT_NAME<br />
--------------------------------------------------------------------------------<br />
SEGMENT_TYPE<br />
------------------<br />
INDEX<br />
SYS_LOB0000021357C00002$$<br />
LOBSEGMENT<br />
SYS_LOB0000021357C00003$$<br />
LOBSEGMENT<br />
<br />
31 rows selected.<br />
SQL><br />
SQL> drop table t;<br />
Table dropped.<br />
SQL><br />
<br />
<br />
<br />
</source></div>
Admin