Tablespace Space allocation

[shrik_m]

How to give initial extent,next extent, pct free and pct used for a tablespace. Is there any calculation for the same...?

I know the calculation for allocating space for a single table.

This is quite urgent.

Thanks in anticipation..

shrik_m

[sreddy]

there are couple of tech. papers in response to Mary posting 'temporary tablespaces' which is couple of days old posting.

search for it, you have great tech papers on space management of oracle database which gives fair idea.

[rcaballe]

But DEFAULT STORAGE in tablespace creation only means the STORAGE parameter of a new table if you don't put it.
So, you know for tables, the only question you should ask yourself is:
What parameter value for STORAGE I want for new tables if I forgot define it in table creation?

[pwoneill]

I read a Metalink paper on sizing to avoid fragmentation once, I found it interesting but wonder what experienced DBA's feel about it.

It's a PDF and you need access to Metalink, the address is [url]http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049[/url]

[sreddy]

We(our dba group) feel, authors of tech white paper trying to say:

Do manually what oracle 'locally managed tablspace auto allocate option' does for you (Leaving the concept of bit map indexing taking care of extent management). In otherwords try creating initial _extent and next_extent same size 128k/160k with %INCREASE set to 0, max_extents 4096 and adding the 2 datablocks over head. Of course this limits 2Gb datafile. If you want to use >2GB, you have to modify these sizes either 5120k/5120M but, still you will be facing problems if you move accross different servers unless all of your servers support > 2GB file size.

Disk is cheap now a days, but not dba resouces and time.

By randomly (or per requirement exactly ) allocating extent sizes, though you have lots of freespace you do get extent allocation error when it can't find the extent size/chunk its looking for, means you are wasting space over there and fragmenting tablespace.

By giving proper extent size(in multiple of db_block and db_file_blockmultiblock_read_count) all you are giving is more than what its needed and you are avoiding fragmentation by doing it, not wasting it as such, cuz, when the table grows if at all it uses it, in the other case it can't use it though free space is available.

Here is example with one of our adhocly designed databases, which I endup redesigning it.

I have 1.1GB freespace on tablespace which has 112 free extents, of which the largest was 71 Meg. When we were loading the data, it asked to extend the size by 120Meg which is less than the max free chunk 71 meg. Load failed. so 1.1GB is waste(at least that context)... when it can't find free contigspace of extent its needed.

So, If you follow SAFE you are giving lttle extra space for couple of objects whether those objects needs or not. Say importing/having 32k table to a tablespace whose extent size is 128K/160K. Thats what locally managed tablespaces AUTOALLOCATE option does for you. no matter what table in need of/requests it just creates extents of 64k/1m/8m/64m which is always multiple of youe db_block and db_file_multiblock_read_count. But, you never endup the situation I explained above.

We experimented with SAFE and we 100 % utilization of space giving 2 blocks overhead per datafile.

I appreciate the rest of DBA`s comments.

[Edited by sreddy on 01-11-2001 at 01:35 PM]

[shrik_m]

I think the proper answer is still not available...! Let me tell u the scenario. I have 10 tables. In that 3 tables each has 10 columns. The size of each table is around 100 bytes while creating the table. Each table receives around 1000 records per day.

Now tell me what size can be allotted to the tablespace where I need not touch the sizing for another 1 year in terms of initial extent, next,pct etc.,

shrik_m

[sreddy]

I don't see the problem here Or I am not clear with what you are saying.

I meant by avoiding REORG, avoiding FRGMENTATION, utilising space in efficient way, doesn't mean by not touching (monitoring) it at all. Thats what I do, I estimated future growth and give enought space and monitor it and when my tablespace is say 90% full, I add one more datafile of 2GB.
I will make sure that segment level storage clause will be taken off.

Monitoring space requirements is part of DBA life no matter you use locally managed/dictionary managed cuz, at somepoint of time disk might ran out, thought you use locally managed tablespaces. so, you have to anyway.