SQL & PL/SQL

Hi,

I created one table EMP1 and inserted 320 rows

SQL> select count(*) from emp1;

COUNT(*)
----------
320

Now I calcute the TABLE SIZE with

SQL> select sum(bytes) from dba_segments
2 where segment_name = 'EMP1';

SUM(BYTES)
----------
131072

SQL> select 131072/8192 "total no. of blocks allocated" from dual;

total no. of blocks allocated
-----------------------------
16
8192 is the default bd_block_size in Oracle 11g.

I calculate the row size with:

SQL> select
2 max(vsize(id)) +
3 max(vsize(fname)) +
4 max(vsize(lname)) +
5 max(vsize(job)) +
6 max(vsize(manager)) +
7 max(vsize(hiredate)) +
8 max(vsize(salary)) +
9 max(vsize(comm)) +
10 max(vsize(address)) +
11 max(vsize(dept)) +
12 max(vsize(hobby)) +
13 max(vsize(marital_status)) +
14 max(vsize(city)) +
15 max(vsize(country)) +
16 max(vsize(continent)) BYTES
17 from emp1;

BYTES
----------
164

After inserting first row I applied above sql for calculating row size and it showed 164 bytes. After inserting 320 rows again it is showing 164 bytes.

wut I found Oracle by default allocates 8 blocks when a segment is created. After I inserted 196th row in above table Oracle has allocated another extent with more 8 blocks. After that If I calculate table size it shows 131072 bytes as above i.e. 8192*16 = 131072

My question is can i FIND the exact size of let's say after inserting 5 rows in a data block.

I want to calcute that if Oracle allocated 8 blocks initially to a table segment. How many rows are there in ONE DATA BLOCK SIZE of 8192 and when it's inserting rows to second block instead of showing bytes of 8 or 16 blocks.

I'll appreciate the helpful suggestions.

Thanks and regards,
Kam