RE: What am I missing - table not in DBA_SEGMENTS
- From: "Kenneth Naim" <kennethnaim@xxxxxxxxx>
- To: <christopherdtaylor1994@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 13 Aug 2014 10:43:04 -0400
Extents are now allocated upon first insert, there is an init.ora parameter to
change the behavior to allocate upon create. This change helps prepackaged
application that create thousands of table but only populate a small fraction
of that. Why allocate space to a table that will never get rows.
Ken
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Chris Taylor
Sent: Wednesday, August 13, 2014 10:39 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: What am I missing - table not in DBA_SEGMENTS
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:
