Re: 10g dba_segments_old?

Comparing the two defn's, it looks like one of the segment flag bits has all the answers....

select owner, segment_name, partition_name, segment_type, tablespace_name, header_file, header_block,
decode(bitand(segment_flags, 131072), 131072, blocks, (decode(bitand(segment_flags,1),1,
dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno, header_block, segment_type_id, buffer_pool_id, segment_flags, segment_objd, blocks), blocks)))*blocksize, decode(bitand(segment_flags, 131072), 131072, blocks, (decode(bitand(segment_flags,1),1,
dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno, header_block, segment_type_id, buffer_pool_id, segment_flags, segment_objd, blocks), blocks))),
decode(bitand(segment_flags, 131072), 131072, extents, (decode(bitand(segment_flags,1),1,
dbms_space_admin.segment_number_extents(tablespace_id, relative_fno, header_block, segment_type_id, buffer_pool_id, segment_flags, segment_objd, extents) , extents))),
initial_extent, next_extent, min_extents, max_extents, pct_increase, freelists, freelist_groups, relative_fno, decode(buffer_pool_id, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL) from sys_dba_segs

select owner, segment_name, partition_name, segment_type, tablespace_name, header_file, header_block,
dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno, header_block, segment_type_id, buffer_pool_id, segment_flags, segment_objd, blocks)*blocksize,
dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno, header_block, segment_type_id, buffer_pool_id, segment_flags, segment_objd, blocks),
dbms_space_admin.segment_number_extents(tablespace_id, relative_fno, header_block, segment_type_id, buffer_pool_id, segment_flags, segment_objd, extents),
initial_extent, next_extent, min_extents, max_extents, pct_increase, freelists, freelist_groups, relative_fno, decode(buffer_pool_id, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL) from sys_dba_segs

When you look at SEG$ defn, there's

spare1 number, /* Segment flags - NULL = 0x0 */

/* 0x1 - bitmapped tablespace */
/* 0x2 - undo segment */
/* 0x4 - saveundo segment */
/* 0x8 - segment marked corrupt */
/* #define KTSSEGM_FLAG_COMPRESSED 0x0800 */
/* #define KTSSEGM_FLAG_HASCPRSSED 0x1000 */
/* #define KTSSEGM_FLAG_ROWMOVEMNT 0x2000 */
/* #define KTSSEGM_FLAG_HASMOVEMNT 0x4000 */
/*0x10000 - seg flushed from cache: KTSSEGM_FLAG_RECYCLEBIN */

Looks like they aren't telling what 0x20000 means...However, catspace.sqlhas the following commentary

Rem nmukherj 11/16/03 - changed the view DBA_SEGMENTS: bug2948717

bug2948717 is about performance in dba_segments, and talks about a backport to v9. Since the altered view also appears in my 9206 database, I'm guessing that's why its been changed.

Why they've gone for a dba_segments_old is anyone's guess...

Cheers
Connor

On 10/7/05, ryan_gaffuri_at_comcast.net <ryan_gaffuri_at_comcast.net> wrote:
>
> OTN has the exact same spec as for DBA_SEGMENTS?
>
>
> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4098.htm
>
> The only different I see is that dba_Segments_old tracks the increases in
> bytes used by segments that we had before we upgraded to 10g. The regular
> DBA_SEGMENTS view has not increased the number of bytes used by those
> segments. It only increments when we create a segment(table, etc...).
>
> any idea why oracle did this?
>
>

--
Connor McDonald
===========================
email: connor_mcdonald_at_yahoo.com
web: http://www.oracledba.co.uk

"Semper in excremento, sole profundum qui variat"

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