Difference in number of blocks
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Difference in number of blocks

  1. #1
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    Difference in number of blocks

    What am I missing here? Why the difference in the number of blocks?

    Thanks

    Steve

    SQL> select blocks from dba_segments where segment_name='PS_JOB' and segment_type='TABLE' and owner = 'SYSADM';

    BLOCKS
    ----------
    155776

    SQL> select blocks from user_tables where table_name = 'PS_JOB';

    BLOCKS
    ----------
    147683

    SQL>
    I'm stmontgo and I approve of this message

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999

    Re: Difference in number of blocks

    Originally posted by stmontgo
    What am I missing here? Why the difference in the number of blocks?

    Thanks

    Steve

    SQL> select blocks from dba_segments where segment_name='PS_JOB' and segment_type='TABLE' and owner = 'SYSADM';

    BLOCKS
    ----------
    155776

    SQL> select blocks from user_tables where table_name = 'PS_JOB';

    BLOCKS
    ----------
    147683

    SQL>
    When is the last time that you gathered stats? I think that dba_segments is current while the blocks in user_table change when stats are gathered, but I could be wrong.
    this space intentionally left blank

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    Re: Re: Difference in number of blocks

    Originally posted by gandolf989
    When is the last time that you gathered stats? I think that dba_segments is current while the blocks in user_table change when stats are gathered, but I could be wrong.
    Thanks for the suggestion but no joy

    SQL> analyze table ps_job compute statistics;

    Table analyzed.

    SQL> select blocks from user_tables where table_name = 'PS_JOB';

    BLOCKS
    ----------
    147683

    SQL> select blocks from dba_segments where segment_name='PS_JOB' and segment_type='TABLE' and owner = 'SYSADM';

    BLOCKS
    ----------
    155776

    SQL>
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    BLOCKS in USER_TABLES is the number of used data blocks in the table. BLOCKS in USER_SEGMENTS is the size of the segment, in blocks.
    PHP Code:
    SQLcreate table tab
      2  
    (c char(1000));

    Table created.

    SQLbegin
      2  dbms_stats
    .gather_table_stats(ownname=>'SCOTT',tabname=>'TAB');
      
    3  end;
      
    4  /

    PL/SQL procedure successfully completed.

    SQLselect table_nameblocks from user_tables where table_name 'TAB';

    TABLE_NAME                         BLOCKS
    ------------------------------ ----------
    TAB                                     0

    SQL
    select segment_nameblocks from user_segments where segment_name 'TAB';

    SEGMENT_NAME
    --------------------------------------------------------------------------------
        
    BLOCKS
    ----------
    TAB
             8

    SQL
    insert into tab values ('1');

    1 row created.

    SQL> /

    1 row created.

    SQL> /

    1 row created.

    SQL> /

    1 row created.

    SQL> /

    1 row created.

    SQLbegin
      2  dbms_stats
    .gather_table_stats(ownname=>'SCOTT',tabname=>'TAB');
      
    3  end;
      
    4  /

    PL/SQL procedure successfully completed.

    SQLselect table_nameblocks from user_tables where table_name 'TAB';

    TABLE_NAME                         BLOCKS
    ------------------------------ ----------
    TAB                                     5

    SQL
    select segment_nameblocks from user_segments where segment_name 'TAB';

    SEGMENT_NAME
    --------------------------------------------------------------------------------
        
    BLOCKS
    ----------
    TAB
             8 

  5. #5
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by akkerend
    BLOCKS in USER_TABLES is the number of used data blocks in the table. BLOCKS in USER_SEGMENTS is the size of the segment, in blocks.
    PHP Code:
    SQLcreate table tab
      2  
    (c char(1000));

    Table created.

    SQLbegin
      2  dbms_stats
    .gather_table_stats(ownname=>'SCOTT',tabname=>'TAB');
      
    3  end;
      
    4  /

    PL/SQL procedure successfully completed.

    SQLselect table_nameblocks from user_tables where table_name 'TAB';

    TABLE_NAME                         BLOCKS
    ------------------------------ ----------
    TAB                                     0

    SQL
    select segment_nameblocks from user_segments where segment_name 'TAB';

    SEGMENT_NAME
    --------------------------------------------------------------------------------
        
    BLOCKS
    ----------
    TAB
             8

    SQL
    insert into tab values ('1');

    1 row created.

    SQL> /

    1 row created.

    SQL> /

    1 row created.

    SQL> /

    1 row created.

    SQL> /

    1 row created.

    SQLbegin
      2  dbms_stats
    .gather_table_stats(ownname=>'SCOTT',tabname=>'TAB');
      
    3  end;
      
    4  /

    PL/SQL procedure successfully completed.

    SQLselect table_nameblocks from user_tables where table_name 'TAB';

    TABLE_NAME                         BLOCKS
    ------------------------------ ----------
    TAB                                     5

    SQL
    select segment_nameblocks from user_segments where segment_name 'TAB';

    SEGMENT_NAME
    --------------------------------------------------------------------------------
        
    BLOCKS
    ----------
    TAB
             8 

    outstanding, tks
    I'm stmontgo and I approve of this message

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width