I am in the process of creating a database. The tables contains all sorts of data like number, varchar2, CLOBs, BLOBs etc. Can somebody tell me how can I estimate the size of the tables to allocate the size in the tablespace?
AVIL = Available space in block to hold rows
OBS = Oracle block size
RS = Row size
Ovhd = Fixed plus variable block overhead
TBR = Total blocks required
Expected size = (( RS * number of rows) / AVIL ) * OBS) / K or M
where K = 1024 and M = 1048576
Figure RS as
for varchar2 expected number of characters for column
for number 1 + floor(num digits / 2) + 1
for date use 7
+ 1 byte per column in row
+ 3 byte row overhead per row
Figure number of bytes for block as
pctfree = decimal value of pctfree parameter * OBS
The variable area is mostly made up of 23 bytes per initran area and 2 bytes per row for the row table entry. For 1 to 4 initrans I have calculated row overhead of 86 to 156 bytes so I just use a constant for this value. Try 113 to start.
Figure AVIL as OBS - ovhd - pctfree
Total bytes = number of expected rows * RS
TBR = Total Bytes / AVIL
Expected Size = TBR * OBS / 1024 [for K]
This is one way and it is fairly quick and works pretty well. The formula can be improved by adjusting the variable area size for the number of initrans and for the number of expected rows in the block, but using a constant works well for us.
If a table has both varchar2 and LOB, then allocate one tablespace for varchar2 and number data types and another tablespace for LOB columns.
-- Author Tamil
-- Date - Jan 20, 2001
-- Example for CREATE TABLE Statement that contains LOB col
-- CHUNK is the number of bytes to be allocated for LOB Manipulation
-- The max value is 32768 (32K) which is the largest Oracle Block Size
-- Note that CHUNK can not be changed after creating the table
CREATE TABLE TEST_LOB (ID NUMBER , TEST_CLOB CLOB)
TABLESPACE TS_DVL_USER1 STORAGE (INITIAL 256K NEXT 256K PCTINCREASE 0)
LOB(TEST_CLOB) STORE AS (TABLESPACE TS_DVL_USER2
STORAGE (INITIAL 2M NEXT 2M )
CHUNK 16K PCTVERSION 20 NOCACHE LOGGING)
Follow earlier entry in thread for table sizing, but i would prefer have the extent size constant and equal to extent size set for tablespace.Only reqt of doing of sizing of table is to decide on which tablespace to put this table on.I suppose you have different tablespaces for small tables,medium and large.
For LOBS, it deoends whether you want to store in row data or out of row that is you want to define seperate storage space for lobsegment or you want to store in the table storage area.
IF out of row (which should be the case if high update and query and size is more than 4K) follow tamils response.To add to that chunk size should be equal or multiple of oracle_block_size and depends on the type and amount of IO.
And set PCTversion also depending upon whether and how many users would simultaneously read the LOB, if high concurrency and high number of transactons , keep PCTVERSION high.
Logging , if u have a standby database or want recoverable option for LOB then use LOGGING , also depends on whether u want to use cache or not.
Cache takes lot of db_block_buffers and causes lot of misses for other objects.Use only if the transaction size and chunk size is small.
READ/Write is done in chunk size.