| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Fw: RE: why these tables can not be seen from user_segments?
Guang solved his problem, just didn't report to the list :)
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
> You are correct. Thank you.
>
> Guang
>
> SQL> SELECT index_name from user_indexes where table_name =
> 'DR$ABSTRACT_TEXT$K'
> 2 ;
>
> INDEX_NAME
> ------------------------------
> SYS_IOT_TOP_19082
>
> SQL> select * from user_segments where segment_name='SYS_IOT_TOP_19082';
>
> SEGMENT_NAME
> --------------------------------------------------------------------------
--Received on Tue Feb 04 2003 - 14:16:41 CST
> ----
> PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
> ------------------------------ ------------------ ------------------------
--
> ----
> BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
> ---------- ---------- ---------- -------------- ----------- -----------
> MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS BUFFER_
> ----------- ------------ ---------- --------------- -------
> SYS_IOT_TOP_19082
> INDEX RESINDEX
> 1048576000 128000 500 1048576000 2097152 1
> 2147483645 0 1 1 DEFAULT
>
>
> > -----Original Message-----
> > From: Igor Neyman [mailto:ineyman_at_perceptron.com]
> > Sent: Tuesday, February 04, 2003 1:53 PM
> > To: gmei
> > Subject: Re: why these tables can not be seen from user_segments?
> >
> >
> > That's exactly, what I was talking about:
> >
> > IOT_TYPE is IOT, which means this table is index-organized,
> > and if it does
> > not have overflow, then you will see only index segment, no
> > table segments
> > created.
> >
> > Now do:
> >
> > SELECT index_name from user_indexes where table_name =
> > 'DR$ABSTRACT_TEXT$K'
> >
> > Then, use index name as a segment_name, when querying user_segments.
> >
> > Igor Neyman, OCP DBA
> > ineyman_at_perceptron.com
> >
> >
> >
> > ----- Original Message -----
> > From: "gmei" <gmei_at_incyte.com>
> > To: "'Igor Neyman'" <ineyman_at_perceptron.com>
> > Sent: Tuesday, February 04, 2003 1:47 PM
> > Subject: RE: why these tables can not be seen from user_segments?
> >
> >
> > > No matter where I look, there is no segment correspnding to
> > it. Anyway,
> > here
> > > is the user_table info (not sure if it helps).
> > >
> > > SQL> select TABLE_NAME,IOT_TYPE
> > > 2 from user_tables where table_name='DR$ABSTRACT_TEXT$K';
> > >
> > > TABLE_NAME IOT_TYPE
> > > ------------------------------ ------------
> > > DR$ABSTRACT_TEXT$K IOT
> > >
> > > > -----Original Message-----
> > > > From: Igor Neyman [mailto:ineyman_at_perceptron.com]
> > > > Sent: Tuesday, February 04, 2003 1:38 PM
> > > > To: gmei
> > > > Subject: Re: why these tables can not be seen from user_segments?
> > > >
> > > >
> > > > Obviously, you are missing tables with "$K" and "$N" suffixes.
> > > > Look into user/dba_tables, is there anything special about
> > > > those tables.
> > > >
> > > > Igor Neyman, OCP DBA
> > > > ineyman_at_perceptron.com
> > > >
> > > >
> > > >
> > > > ----- Original Message -----
> > > > From: "gmei" <gmei_at_incyte.com>
> > > > To: "'Igor Neyman'" <ineyman_at_perceptron.com>
> > > > Sent: Tuesday, February 04, 2003 1:29 PM
> > > > Subject: RE: why these tables can not be seen from user_segments?
> > > >
> > > >
> > > > >
> > > > > SQL> select segment_name, segment_type from user_segments
> > > > > 2 where segment_name like 'DR$%'
> > > > > 3 order by segment_name;
> > > > >
> > > > > SEGMENT_NAME
> > > > >
> > > > --------------------------------------------------------------
> > > > ------------
> > > > --
> > > > > ----
> > > > > SEGMENT_TYPE
> > > > > ------------------
> > > > > DR$ABSTRACT_TEXT$I
> > > > > TABLE
> > > > >
> > > > > DR$ABSTRACT_TEXT$R
> > > > > TABLE
> > > > >
> > > > > DR$ABSTRACT_TEXT$X
> > > > > INDEX
> > > > >
> > > > >
> > > > > SEGMENT_NAME
> > > > >
> > > > --------------------------------------------------------------
> > > > ------------
> > > > --
> > > > > ----
> > > > > SEGMENT_TYPE
> > > > > ------------------
> > > > > DR$COREREF_ABSTRACT$I
> > > > > TABLE
> > > > >
> > > > > DR$COREREF_ABSTRACT$R
> > > > > TABLE
> > > > >
> > > > > DR$COREREF_ABSTRACT$X
> > > > > INDEX
> > > > >
> > > > >
> > > > > SEGMENT_NAME
> > > > >
> > > > --------------------------------------------------------------
> > > > ------------
> > > > --
> > > > > ----
> > > > > SEGMENT_TYPE
> > > > > ------------------
> > > > > DR$COREREF_TITLE$I
> > > > > TABLE
> > > > >
> > > > > DR$COREREF_TITLE$R
> > > > > TABLE
> > > > >
> > > > > DR$COREREF_TITLE$X
> > > > > INDEX
> > > > >
> > > > >
> > > > > SEGMENT_NAME
> > > > >
> > > > --------------------------------------------------------------
> > > > ------------
> > > > --
> > > > > ----
> > > > > SEGMENT_TYPE
> > > > > ------------------
> > > > > DR$TEST_CONTEXT$I
> > > > > TABLE
> > > > >
> > > > > DR$TEST_CONTEXT$R
> > > > > TABLE
> > > > >
> > > > > DR$TEST_CONTEXT$X
> > > > > INDEX
> > > > >
> > > > >
> > > > > 12 rows selected.
> > > > >
> > > > > SQL>
> > > > >
> > > > > > -----Original Message-----
> > > > > > From: Igor Neyman [mailto:ineyman_at_perceptron.com]
> > > > > > Sent: Tuesday, February 04, 2003 1:24 PM
> > > > > > To: gmei
> > > > > > Subject: Re: why these tables can not be seen from
> > user_segments?
> > > > > >
> > > > > >
> > > > > > Guang,
> > > > > >
> > > > > > Try without specifying "segment_type":
> > > > > >
> > > > > > select segment_name, segment_type from user_segments
> > > > > > where segment_name like 'DR$%'
> > > > > > order by segment_name;
> > > > > >
> > > > > >
> > > > > > Igor Neyman, OCP DBA
> > > > > > ineyman_at_perceptron.com
> > > > > >
> > > > > >
> > > > > >
> > > > > > ----- Original Message -----
> > > > > > From: "gmei" <gmei_at_incyte.com>
> > > > > > To: <ineyman_at_perceptron.com>
> > > > > > Sent: Tuesday, February 04, 2003 1:12 PM
> > > > > > Subject: RE: why these tables can not be seen from
> > user_segments?
> > > > > >
> > > > > >
> > > > > > > No, they are not indexes. Indexes end with "$X".
> > > > > > >
> > > > > > > Guang
> > > > > > >
> > > > > > > ----------
> > > > > > > SQL> select index_name from user_indexes
> > > > > > > 2 where index_name like 'DR$%'
> > > > > > > 3 order by index_name;
> > > > > > >
> > > > > > > INDEX_NAME
> > > > > > > ------------------------------
> > > > > > > DR$ABSTRACT_TEXT$X
> > > > > > > DR$COREREF_ABSTRACT$X
> > > > > > > DR$COREREF_TITLE$X
> > > > > > > DR$TEST_CONTEXT$X
> > > > > > >
> > > > > > > SQL> select segment_name from user_segments
> > > > > > > 2 where segment_type='INDEX'
> > > > > > > 3 and segment_name like 'DR$%'
> > > > > > > 4 order by segment_name;
> > > > > > >
> > > > > > > SEGMENT_NAME
> > > > > > >
> > > > > > --------------------------------------------------------------
> > > > > > ------------
> > > > > > --
> > > > > > > ----
> > > > > > > DR$ABSTRACT_TEXT$X
> > > > > > > DR$COREREF_ABSTRACT$X
> > > > > > > DR$COREREF_TITLE$X
> > > > > > > DR$TEST_CONTEXT$X
> > > > > > >
> > > > > > >
> > > > > > > > -----Original Message-----
> > > > > > > > From: root_at_fatcity.com
> > [mailto:root_at_fatcity.com]On Behalf Of
> > > > > > > > Igor Neyman
> > > > > > > > Sent: Tuesday, February 04, 2003 11:40 AM
> > > > > > > > To: Multiple recipients of list ORACLE-L
> > > > > > > > Subject: Re: why these tables can not be seen from
> > > > user_segments?
> > > > > > > >
> > > > > > > >
> > > > > > > > Are those missing tables - IOTs?
> > > > > > > > If so, you will not see them as table segments, they will
> > > > > > > > show up as index
> > > > > > > > segments.
> > > > > > > >
> > > > > > > > Igor Neyman, OCP DBA
> > > > > > > > ineyman_at_perceptron.com
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > ----- Original Message -----
> > > > > > > > To: "Multiple recipients of list ORACLE-L"
> > > > <ORACLE-L_at_fatcity.com>
> > > > > > > > Sent: Tuesday, February 04, 2003 10:24 AM
> > > > > > > >
> > > > > > > >
> > > > > > > > > Hi:
> > > > > > > > >
> > > > > > > > > Oracle 8173 on Sun Box.
> > > > > > > > >
> > > > > > > > > SQL> select count(*) from user_tables;
> > > > > > > > >
> > > > > > > > > COUNT(*)
> > > > > > > > > ----------
> > > > > > > > > 326
> > > > > > > > >
> > > > > > > > > SQL> select count(*) from user_segments where
> > > > > > segment_type='TABLE';
> > > > > > > > >
> > > > > > > > > COUNT(*)
> > > > > > > > > ----------
> > > > > > > > > 318
> > > > > > > > >
> > > > > > > > > SQL> select segment_name from user_segments
> > > > > > > > > 2 where segment_type='TABLE'
> > > > > > > > > 3 and segment_name like 'DR$%'
> > > > > > > > > 4 order by segment_name;
> > > > > > > > >
> > > > > > > > > SEGMENT_NAME
> > > > > > > > >
> > > > > > > >
> > --------------------------------------------------------------
> > > > > > > > ------------
> > > > > > > > --
> > > > > > > > > ----
> > > > > > > > > DR$ABSTRACT_TEXT$I
> > > > > > > > > DR$ABSTRACT_TEXT$R
> > > > > > > > > DR$COREREF_ABSTRACT$I
> > > > > > > > > DR$COREREF_ABSTRACT$R
> > > > > > > > > DR$COREREF_TITLE$I
> > > > > > > > > DR$COREREF_TITLE$R
> > > > > > > > > DR$TEST_CONTEXT$I
> > > > > > > > > DR$TEST_CONTEXT$R
> > > > > > > > >
> > > > > > > > > 8 rows selected.
> > > > > > > > >
> > > > > > > > > SQL> select table_name from user_tables
> > > > > > > > > 2 where table_name like 'DR$%'
> > > > > > > > > 3 order by table_name;
> > > > > > > > >
> > > > > > > > > TABLE_NAME
> > > > > > > > > ------------------------------
> > > > > > > > > DR$ABSTRACT_TEXT$I
> > > > > > > > > DR$ABSTRACT_TEXT$K
> > > > > > > > > DR$ABSTRACT_TEXT$N
> > > > > > > > > DR$ABSTRACT_TEXT$R
> > > > > > > > > DR$COREREF_ABSTRACT$I
> > > > > > > > > DR$COREREF_ABSTRACT$K
> > > > > > > > > DR$COREREF_ABSTRACT$N
> > > > > > > > > DR$COREREF_ABSTRACT$R
> > > > > > > > > DR$COREREF_TITLE$I
> > > > > > > > > DR$COREREF_TITLE$K
> > > > > > > > > DR$COREREF_TITLE$N
> > > > > > > > >
> > > > > > > > > TABLE_NAME
> > > > > > > > > ------------------------------
> > > > > > > > > DR$COREREF_TITLE$R
> > > > > > > > > DR$TEST_CONTEXT$I
> > > > > > > > > DR$TEST_CONTEXT$K
> > > > > > > > > DR$TEST_CONTEXT$N
> > > > > > > > > DR$TEST_CONTEXT$R
> > > > > > > > >
> > > > > > > > > 16 rows selected.
> > > > > > > > >
> > > > > > > > > Why tables like DR$ABSTRACT_TEXT$K,
> > DR$ABSTRACT_TEXT$N do
> > > > > > > > not have their
> > > > > > > > > corresponding segments? What are they and their
> > functions?
> > > > > > > > >
> > > > > > > > > TIA.
> > > > > > > > >
> > > > > > > > > Guang Mei
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > > > > > > > > --
> > > > > > > > > Author: gmei
> > > > > > > > > INET: gmei_at_incyte.com
> > > > > > > > >
> > > > > > > > > Fat City Network Services -- 858-538-5051
> > > > > http://www.fatcity.com
> > > > > > > > San Diego, California -- Mailing list and web
> > > > > > > hosting services
> > > > > > > >
> > > > > > >
> > > >
> > ---------------------------------------------------------------------
> > > > > > > > To REMOVE yourself from this mailing list, send an
> > > > E-Mail message
> > > > > > > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > > > 'ListGuru') and in
> > > > > > > > the message BODY, include a line containing:
> > UNSUB ORACLE-L
> > > > > > > > (or the name of mailing list you want to be removed
> > > > from). You may
> > > > > > > > also send the HELP command for other information
> > > > (like subscribing).
> > > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > > > > > --
> > > > > > > Author: Igor Neyman
> > > > > > > INET: ineyman_at_perceptron.com
> > > > > > >
> > > > > > > Fat City Network Services -- 858-538-5051
> > > http://www.fatcity.com
> > > > > > San Diego, California -- Mailing list and web hosting
> > services
> > > > >
> > >
> > ---------------------------------------------------------------------
> > > > > > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > > > > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > > > > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > > > > (or the name of mailing list you want to be removed
> > from). You may
> > > > > > also send the HELP command for other information
> > (like subscribing).
> > > > > >
> > > > >
> > > >
> > >
> >
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: ineyman_at_perceptron.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
 |
 |