Re: What am I missing - table not in DBA_SEGMENTS

You guys are fast.

sd1p SQL>create table test ( a number ) segment creation deferred;

Table created.

sd1p SQL>select SEGMENT_CREATED from user_tables where table_name = 'TEST';

SEG

---
NO

sd1p SQL>select segment_type from dba_segments where segment_name = 'TEST';

no rows selected


sd1p SQL>insert into test values (1);

1 row created.

sd1p SQL>commit;

Commit complete.

sd1p SQL>select segment_type from dba_segments where segment_name = 'TEST';

SEGMENT_TYPE
------------------
TABLE






On Aug 13, 2014, at 10:38 AM, Chris Taylor <christopherdtaylor1994_at_gmail.com> wrote:



> Env Oracle 11.2.0.3 

> 

> I have a table that exists in the data dictionary, and I can select from it, but it does NOT show up in DBA_SEGMENTS.

> 

> The table is _NOT_ an IOT, TEMPORARY, CLUSTERED or anything else odd.

> 

> Is it because there are NO EXTENTS assigned to the table even though the data dictionary says MINEXTENTS = 1?

> 

> Is this some new behavior in 11g or has it always been this way?  I thought a table would have at least one extent when it was created when minextents = 1?

> 

> The only OTHER thing about this table is that it has "RESULT CACHE" as part of the create table script but I can't imagine that would cause this behavior.

> 

> This paste section is in HTML format so I'm not sure it will show up correctly in your mailbox:

> 

> select owner,table_name, tablespace_name, cluster_name, iot_name, status, min_extents,logging, num_rows,empty_blocks, partitioned, iot_type, temporary, secondary,nested

> from dba_tables

> where owner = 'WCDBA'

> and table_name like 'PNV_PG_TRACK%'

> /

> 

> OWNER

> TABLE_NAME

> TABLESPACE_NAME

> CLUSTER_NAME

> IOT_NAME

> STATUS

> MIN_EXTENTS

> LOGGING

> NUM_ROWS

> EMPTY_BLOCKS

> PARTITIONED

> IOT_TYPE

> TEMPORARY

> SECONDARY

> NESTED

> WCDBA

> PNV_PG_TRACK

> SUBSET_DATA

>  

>  

> VALID

> 1

> YES

> 0

> 0

> NO

>  

> N

> N

> NO

>  

> select owner,object_name, subobject_name,object_id, data_object_id, object_type,status,temporary,generated,secondary,namespace from dba_objects

> where object_name = 'PNV_PG_TRACK'

> /

> 

> OWNER

> OBJECT_NAME

> SUBOBJECT_NAME

> OBJECT_ID

> DATA_OBJECT_ID

> OBJECT_TYPE

> STATUS

> TEMPORARY

> GENERATED

> SECONDARY

> NAMESPACE

> WCDBA

> PNV_PG_TRACK

>  

> 194812

> 194812

> TABLE

> VALID

> N

> N

> N

> 1

>  

> select owner,segment_name, bytes/1024/1024/1024

> from dba_segments

> where upper(segment_name) = 'PNV_PG_TRACK'

> /

> 

> NO ROWS RETURNED

> 

> select * from dba_extents de

> where owner = 'WCDBA'

> and DE.SEGMENT_NAME = 'PNV_PG_TRACK'

> /

>  

> NO ROWS RETURNED



--
http://www.freelists.org/webpage/oracle-l