You Asked
1)
select s.segment_name,sum(s.blocks),sum(s.BYTES),sum(s.extents) from user_segments s where s.segment_name='TBL_1'
group by s.segment_name;
SEGMENT_NAME SUM(S.BLOCKS) SUM(S.BYTES) SUM(S.EXTENTS)
TBL_1 5504 45088768 8
2)
select e.segment_name,sum(e.bytes),sum(e.BLOCKS),count(1) count_extents from user_extents e where e.segment_name='TBL_1'
group by segment_name;
SEGMENT_NAME SUM(E.BYTES) SUM(E.BLOCKS) COUNT_EXTENTS
TBL_1 45088768 5504 8
3)
select t.TABLE_NAME ,sum(t.BLOCKS),sum(t.EMPTY_BLOCKS) from user_tables t where t.TABLE_NAME='TBL_1'
group by table_name;
TABLE_NAME SUM(T.BLOCKS) SUM(T.EMPTY_BLOCKS)
TBL_1 5352 0
Now Blocks of the segments is larger than blocks of the tables
so difference is 152.
where is remaining blocks( means 152 blocks where occupied by oracle)?
and Connor said...
The blocks in an extent is "different" to the blocks used in a table.
Think of it this way (this is a *conceptual* model - in reality, a few things are different for efficiency reasons, but we'll skip that for ease of explanation). And we'll assume defaults for blocksize etc.
- I create a table, it needs no space
- I insert my first row, so I need a block
- We currently have no space, so we create an extent for you.
- That extent is 8 blocks (64KB)
- So now I have 8 blocks *allocated*, ie, they are *mine*, no-one else can have them
- But I've only *used* 1 block for that first row
- As I keep adding rows, eventually I will use up all of the 8 blocks currently allocated to me. At *that* point, everything would appear in alignment, ie, 8 blocks allocated, and 8 blocks used.
- But when I add another row...well, I need more space...so we'll allocate a *new* extent, thus giving you another 8 blocks (16 total allocated, and 9 in use)
Once again - this is *conceptual* - the numbers here are not *exactly* what goes on
Hope this helps.
Rating
(6 ratings)
Is this answer out of date? If it is, please let us know via a Comment