Re: 10g dba_segments_old?
- From: Connor McDonald <mcdonald.connor@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 7 Oct 2005 12:10:47 +0800
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@xxxxxxxxxxx <ryan_gaffuri@xxxxxxxxxxx> 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@xxxxxxxxx
web: http://www.oracledba.co.uk
"Semper in excremento, sole profundum qui variat"
Other related posts:
