0

I have to calculate storage requirements in terms of blocks for following table:

Bill(billno number(7),billdate date,ccode varchar2(20),amount number(9,2))

The table storage attributes are :

PCTFREE=20 , INITRANS=4 , PCTUSED=60 , BLOCKSIZE=8K , NUMBER OF ROWS=100000

I searched a lot on internet, referred many books but didn't got anything.

AkaSh
  • 486
  • 4
  • 16

1 Answers1

1

First you need to figure out what is the typical value for varchar2 column. The total size will depend on that. I created 2 tables from your BILL table. BILLMAX where ccode takes always 20 Char ('12345678901234567890') and BILLMIN that has always NULL in ccode. The results are:

TABLE_NAME  NUM_ROWS    AVG_ROW_LEN BLOCKS
BILLMAX     3938        37          28
BILLMIN     3938        16          13

select table_name, num_rows, avg_row_len, blocks  from user_tables 
where table_name in ( 'BILLMIN', 'BILLMAX')

As you can see, the number of blocks depends on that. Use exec dbms_stats.GATHER_TABLE_STATS('YourSchema','BILL') to refresh values inside user_tables.

The other thing that you need to take into consideration is how big will be your extents. For example :

STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )

will generate first 16 extents with 8 blocks size. After that it will start to create extents with size of 1 MB (128 blocks).

So for BILLMAX it will generate 768 blocks and BILLMIN will take 384 blocks. As you can see the difference is quite big.

For BILLMAX : 16 * 8 + 128 * 5 = 768

For BILLMIN : 16 * 8 + 128 * 2 = 384

dcieslak
  • 2,697
  • 1
  • 12
  • 19
  • I know how to get this info using `dbms_stats.GATHER_TABLE_STATS`. I want to calculate this theoretically. – AkaSh Apr 27 '16 at 07:16
  • The second part of my answer is for that. First you need to figure out how many rows fits in 1 block and then calculate number of blocks required. Then use STORAGE class to get idea how your EXTENTS look like and you have the result. – dcieslak Apr 27 '16 at 07:23