Re: DBA_SEGMENTS invalid? or slow?

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Sat, 10 Sep 2005 01:15:33 +0800
Message-Id: <6.2.1.2.0.20050910011350.01fa99b0@pop.singnet.com.sg>

Could be Bug 4142932 (see Note 4142932.8) : DBA_SEGMENTS.EXTENTS wrong for locally managed segment after TRUNCATE operation

They have also shown a test case in the note : This problem is introduced in 9.2.0.6 by the fix for bug 3338673.

The value returned from DBA_SEGMENTS.EXTENTS can be wrong for a segment in a locally managed tablespace after a TRUNCATE operation on that segment.

eg:

    CREATE TABLE A ( A NUMBER ); /* In a LOCALLY MANAGED TABLESPACE */

    ALTER TABLE a allocate extent;
    ALTER TABLE a allocate extent;
    ALTER TABLE a allocate extent;

    TRUNCATE TABLE a;
    SELECT * FROM DBA_SEGMENTS WHERE SEGMENT_NAME='A';     ^
    Shows an EXTENTS value of 4 rather than 1 (as truncated to)

Hemant

At 11:43 AM Friday, you wrote:
>When I query dba_segments I get:
>
>owner segment_name partition_name
>segment_type tablespace_name header_file header_block bytes
>blocks extents initial_extent
>next_extent min_extents max_extents pct_increase freelists
>freelist_groups relative_fno buffer_pool
>REPADMIN T_INSTRU_LDGR T_INSTRU_LDGR_200504 TABLE
>PARTITION TS_LDGR_M2 71 1289
>2212495360 270080 211 10485760 10485760 1 2147483645 0
> 1 1 71 DEFAULT
>
>Yet, when I map the tablespace, there are no extents. We have done a
>truncate of the partition with a drop storage, and it shows storage.
>
>What's up with this?
>
>Database is 9.2.0.6
>HP-UX.
>
>
>Michael Kline
>Database Administration
>SunTrust Banks, Inc.
>Mail Code TOC-7505
>1030 Wilmer Avenue
>Richmond, Va. 23227
>Tel: 804.261.9446 Net: 643.9446
>Cell: 804.744.1545
><mailto:michael.kline_at_suntrust.com>michael.kline_at_suntrust.com
>
>Seeing beyond Money(sm)
>
>
>
>
>LEGAL DISCLAIMER
>The information transmitted is intended solely for the individual or
>entity to which it is addressed and may contain confidential and/or
>privileged material. Any review, retransmission, dissemination or other
>use of or taking action in reliance upon this information by persons or
>entities other than the intended recipient is prohibited. If you have
>received this email in error please contact the sender and delete the
>material from any computer.
>
>Seeing Beyond Money is a service mark of SunTrust Banks, Inc.
>[ST:XCL]
Hemant K Chitale
http://web.singnet.com.sg/~hkchital

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

Received on Fri Sep 09 2005 - 12:17:51 CDT