| blocks in dba_tables/dba_segments [message #59512] |
Fri, 28 November 2003 02:51  |
ora
Messages: 47
Registered: June 2002
|
Member |
|
|
Hi,
Can anyone explain my wht's the difference between BLOCKS columns in DBA_TABLES & DBA_SEGEMENTS ?
dba_tables view show me total 80 blocks in one of my table when i queried
select sum(blocks) + sum(empty_blocks) from dba_tables where table_name='TPOL';
and in dba_segments the following query showing me 25760 blocks in table, the query is :
select sum(blocks) from dba_segments where segment_name='TPOL';
Plz help me in this .
|
|
|
|
| Re: blocks in dba_tables/dba_segments [message #59515 is a reply to message #59512] |
Fri, 28 November 2003 05:18  |
Frank Naude
Messages: 4587
Registered: April 1998
|
Senior Member |
|
|
Hi,
Your table's statistics are out of date. Try to analyze the table in question and check again.
select blocks+empty_blocks from dba_tables where table_name='TPOL';
analyze table TPOL compute statistics;
select blocks+empty_blocks from dba_tables where table_name='TPOL';
Best regards.
Frank
|
|
|
|
| Re: blocks in dba_tables/dba_segments [message #59516 is a reply to message #59512] |
Fri, 28 November 2003 05:25 |
ora
Messages: 47
Registered: June 2002
|
Member |
|
|
Hi Frank,
First of all i want to tell u that i m great fan of ur knowledge. Ok, thanx for ur reply but i have analyzed the table but the output is still same.
Well i think dba_segments contains info abt the all the extents allocated to the segment but dba_tables contain info abt the current extent in which processing is going on.
Block means total no of block below HWM in that extent and empty_blocks means total no of blocks above HWM in that extent. That is my thinking if i m wrong plz put me on the right track.
Thanx once again and plz do reply me.
|
|
|
|
| Re: blocks in dba_tables/dba_segments [message #59519 is a reply to message #59516] |
Fri, 28 November 2003 06:35 |
Frank Naude
Messages: 4587
Registered: April 1998
|
Senior Member |
|
|
Hi,
The BLOCKS and EMPTY_BLOCKS values in DBA_TABLES are only updated when you analyze your table. BLOCKS indicate the number of used data blocks in the table (below the HWM), while EMPTY_BLOCKS shows the number of empty (never used) data blocks in the table (above the HWM).
If you total BLOCKS + EMPTY_BLOCKS + [[header blocks]], you should get about the same values as is listed in DBA_SEGMENTS.
If you get different results, please post your output so we can investigate this matter further.
Best regards.
Frank
|
|
|
|
| Re: blocks in dba_tables/dba_segments [message #59536 is a reply to message #59512] |
Tue, 02 December 2003 02:52 |
ora
Messages: 47
Registered: June 2002
|
Member |
|
|
Hi Frank,
I m sorry i couldn't sent u the output due to some reason but here it is what u want.
Well as per or discussion i want to know abt the difference between "blocks" column in dba_tables and dba_segments views.
According to me sum of blocks + sum of empty_blocks in dba_tables should be equal to sum of blocks in dba_segments but it's not u can see here :
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Dec 3 02:16:36 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
repadm@STAGING> set lines 120
repadm@STAGING> analyze table tpol compute statistics
2 /
Table analyzed.
repadm@STAGING> select sum(blocks) + sum(empty_blocks) from dba_tables where table_name='TPOL';
SUM(BLOCKS)+SUM(EMPTY_BLOCKS)
-----------------------------
96
repadm@STAGING> select sum(blocks) from dba_segments where segment_name='TPOL';
SUM(BLOCKS)
-----------
25768
repadm@STAGING>
Now plz tell me the diff between them.
Waiting for ur replies.
Thanx in advance.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
