RE: What am I missing - table not in DBA_SEGMENTS
Date: Wed, 13 Aug 2014 14:42:56 +0000
Message-ID: <C1FB7BA65B13C542B2CB1CE5DB8F74AF16B18657_at_NC2PWEX501.us.ad.lfg.com>
The default in 11.2 is that DEFERRED_SEGMENT_CREATION is true, meaning a segment won’t be created until you insert data into the object. You can change this in your session or at the system level.
http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams075.htm#REFRN10307
Thanks,
Scott
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chris Taylor
Sent: Wednesday, August 13, 2014 10:39 AM
To: oracle-l_at_freelists.org
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
Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged, confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or entity to
which it is addressed. If you are not the intended recipient of this E-mail, you are
hereby notified that any dissemination, distribution, copying, or action taken in
relation to the contents of and attachments to this E-mail is strictly prohibited
and may be unlawful. If you have received this E-mail in error, please notify the
sender immediately and permanently delete the original and any copy of this E-mail
and any printout. Thank You.**
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 13 2014 - 16:42:56 CEST