Re: What am I missing - table not in DBA_SEGMENTS
- From: Kenny Payton <k3nnyp@xxxxxxxxx>
- To: christopherdtaylor1994@xxxxxxxxx
- Date: Wed, 13 Aug 2014 10:47:37 -0400
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@xxxxxxxxx>
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
Other related posts:
