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 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: why these tables can not be seen from user_segments?
Title: RE: why these tables can not be seen from user_segments? Hi, I found this out the hard way to: dba_tables, all_tables, and user_tables also include views. Of course, views don't have segments. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: gmei [SMTP:[EMAIL PROTECTED]] 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 stuff cut
RE: why these tables can not be seen from user_segments?
They don't seem to be views, they are tables SQL select view_name from user_views where view_name like 'DR$%'; no rows selected SQL -Original Message- Sent: Tuesday, February 04, 2003 11:53 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Hi, I found this out the hard way to: dba_tables, all_tables, and user_tables also include views. Of course, views don't have segments. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- 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 stuff cut winmail.dat
Re: why these tables can not be seen from user_segments?
I think you will find that these tables are index-organized-tables. The table definition exists, but in the absence of an overflow, there is only an index segment. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 04 February 2003 16:17 Hi: Oracle 8173 on Sun Box. 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? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: why these tables can not be seen from user_segments?
Title: RE: why these tables can not be seen from user_segments? Wrong, dba_tables, all_tables, and user_tables do not include views. Views listed in dba_views, ... ex.: SQLWKS select table_name from dba_tables where table_name like 'PRCPV%'; TABLE_NAME -- 0 rows selected. SQLWKS select view_name from dba_views where view_name like 'PRCPV%'; VIEW_NAME -- PRCPV_CHART_MENU_INFO PRCPV_LANGUAGE PRCPV_MENU_INFO PRCPV_MESSAGE_TEXT PRCPV_RELATIONSHIP_LAYOUT PRCPV_REPORT_INFO PRCPV_REPORT_MENU_INFO 7 rows selected. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Whittle Jerome Contr NCI To: Multiple recipients of list ORACLE-L Sent: Tuesday, February 04, 2003 11:54 AM Subject: RE: why these tables can not be seen from user_segments? Hi, I found this out the hard way to: dba_tables, all_tables, and user_tables also include views. Of course, views don't have segments. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: gmei [SMTP:[EMAIL PROTECTED]] 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 stuff cut
Re: why these tables can not be seen from user_segments?
Guang Mei, All the DR$ objects are interMedia Index segments - some are tables, some are indexes, some are IOTs. Check out OTN and/or MetaLink for docs on how each segment type contributes to the interMedia indexing process. It's quite interesting. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] gmei [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L Sent by: [EMAIL PROTECTED] [EMAIL PROTECTED] cc: Subject: why these tables can not be seen from user_segments? 02/04/2003 09:24 AM Please respond to ORACLE-L 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 -- Author: gmei INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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 SQL select * from dba_segments where segment_name ='DR$ABSTRACT_TEXT$K'; no rows selected SQL -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jonathan Lewis Sent: Tuesday, February 04, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Subject: Re: why these tables can not be seen from user_segments? I think you will find that these tables are index-organized-tables. The table definition exists, but in the absence of an overflow, there is only an index segment. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 04 February 2003 16:17 Hi: Oracle 8173 on Sun Box. 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? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: gmei INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: why these tables can not be seen from user_segments?
I don't know what naming convention Oracle
used for the intermedia IOTs - possibly they
left them to default.
Start with:
select table_name, index_name
from user_indexes
where table_name = '{one of the missing tables}'
;
You may find that there is a primary key index
with a name like 'SYS_IOT_TOP_' - this
will be the data segment for the table. But in
any case, the primary key index for the table
will be the segment holding the data.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
UK___March 19th
USA_(FL)_May 2nd
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 04 February 2003 19:06
No matter where I look, there is no segment correspnding to it.
Anyway, here
is the user_table info (not sure if it helps).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
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 [EMAIL PROTECTED] - Original Message - To: 'Igor Neyman' [EMAIL PROTECTED] Sent: Tuesday, February 04, 2003 1:58 PM 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 -- -- PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME -- -- -- BYTES BLOCKSEXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS -- -- -- -- --- --- MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS BUFFER_ --- -- --- --- SYS_IOT_TOP_19082 INDEX RESINDEX 1048576000 128000500 1048576000 2097152 1 21474836450 1 1 DEFAULT -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED]] 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 [EMAIL PROTECTED] - Original Message - From: gmei [EMAIL PROTECTED] To: 'Igor Neyman' [EMAIL PROTECTED] 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:[EMAIL PROTECTED]] 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 [EMAIL PROTECTED] - Original Message - From: gmei [EMAIL PROTECTED] To: 'Igor Neyman' [EMAIL PROTECTED] 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 2where segment_name like 'DR$%' 3order 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:[EMAIL PROTECTED]] 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 [EMAIL PROTECTED] - Original Message
