-
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
-
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.
-
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
-
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:
SQL> create table tab
2 (c char(1000));
Table created.
SQL> begin
2 dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TAB');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select table_name, blocks from user_tables where table_name = 'TAB';
TABLE_NAME BLOCKS
------------------------------ ----------
TAB 0
SQL> select segment_name, blocks 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.
SQL> begin
2 dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TAB');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select table_name, blocks from user_tables where table_name = 'TAB';
TABLE_NAME BLOCKS
------------------------------ ----------
TAB 5
SQL> select segment_name, blocks from user_segments where segment_name = 'TAB';
SEGMENT_NAME
--------------------------------------------------------------------------------
BLOCKS
----------
TAB
8
-
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:
SQL> create table tab
2 (c char(1000));
Table created.
SQL> begin
2 dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TAB');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select table_name, blocks from user_tables where table_name = 'TAB';
TABLE_NAME BLOCKS
------------------------------ ----------
TAB 0
SQL> select segment_name, blocks 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.
SQL> begin
2 dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TAB');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select table_name, blocks from user_tables where table_name = 'TAB';
TABLE_NAME BLOCKS
------------------------------ ----------
TAB 5
SQL> select segment_name, blocks 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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|