| Diff between blocks in DBA_TABLES/DBA_SEGMENTS [message #59560] |
Wed, 03 December 2003 21:01  |
ora
Messages: 47
Registered: June 2002
|
Member |
|
|
Hi All,
I want to know abt the difference between "blocks" column in dba_tables and dba_segments views.
I know that blocks in dba_tables are blocks below HWM and empty_blocks are abover HWM and 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 can anybody tell me the diff between them.
Waiting for ur replies.
Thanx in advance.
|
|
|
|
| Re: Diff between blocks in DBA_TABLES/DBA_SEGMENTS [message #59579 is a reply to message #59560] |
Thu, 04 December 2003 18:25  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
DBA_TABLES.BLOCKS= Number of used(ever(actually until its HWM is reset)) data blocks in the table , populated after updating statistics
DBA_SEGMENTS.BLOCKS = Number of allocated data blocks for the segment.
Instead of dba_segments and sum , just use blocks and user_segments,user_tables after analyzing and see if there's any difference .
thiru@9.2.0:SQL>drop table t2;
Table dropped.
thiru@9.2.0:SQL>create table t2 as select * from dba_objects;
Table created.
thiru@9.2.0:SQL>analyze table t2 compute statistics;
Table analyzed.
thiru@9.2.0:SQL>select blocks,empty_blocks,blocks+empty_blocks from user_tables where table_name='T2';
BLOCKS EMPTY_BLOCKS BLOCKS+EMPTY_BLOCKS
---------- ------------ -------------------
874 150 1024
thiru@9.2.0:SQL>select blocks from user_Segments where segment_name='T2';
BLOCKS
----------
1024
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
