Tablespace Size Calculation

[sabita_ban]

Hi,

Can some one tell me as to how can I calculate tablespace size in which some temporary tables are to be created?

An example or a document link will be of great help.

[moff]

Try the following....

To display the total size (in Mb) of each tablespace try :-

select tablespace_name, sum(bytes/(1024*1024)) "Total Mb"
from dba_data_files
group by tablespace_name;

To display the free space within a tablespace try :-

select tablespace_name,sum(bytes/(1024*1024)) "Free Mb"
from dba_free_space
group by tablespace_name

Tod display the biggest free extent in each tablespace try :-

select tablespace_name,max(bytes/(1024*1024)) "Biggest Free extent Mb"
from dba_free_space
group by tablespace_name


Cheers
Moff.

[sabita_ban]

moff,

May be u have mis understood my requirement.

What I want to know is
suppose I have a process which creates temporary tables in the database.
Now in the create table statement if I don't specify the Tablespace, then the table will be created in the system table space right.
So I need to have a seperate tablespace. Depending upon the maximum data that can be available in the table I need to calculate this tablespace size.
This can be done obviously by creating a datafile.
But the problem is how can I determine the optimum datafile size for the tablespace?

I hope I am clear now.

[vinit]

The object will be created in the user's default tablespace and not system. Secondly why give quotas on system tablespace to other users ??
So now is your user tablespace (datafile) big enough to hold the temporary table created ?

[sabita_ban]

Vinit,

By system table space I actually tried to mean default tablespace only as in my case my default tablespace is system.

It is always better to have a seperate tablespace for creating temporary tables which are going to get frequently created and dropped. This operation will cause database fragmentation.To confine this database fragmentation we would like to go for a seperate tablespace for these temporary tables.

Now I would like to ask the same question with a bit more elaboration.

Suppose I know that the temporary tables that are going to get created can have at max 50 columns ,min 3 cols, max 100000 rows min 0 rows.
Now for such a table we are opting for a seperate tablespace altogether.
Now to create the tablespace I would issue something like the following query

create tablespace XXX datafile
'YYY' size 850M reuse
default storage (
initial 1M
next 1M
MINEXTENTS 1 MAXEXTENTS UNLIMITED
pctincrease 0);

Now what I need to know is how can I calculate the size (like 850M in the above query) of the datafile to be created for the Tablespace.

[Raminder]

This is purely anticipation and forecasting.
You have to forecast the size of the temporary tables which are likely to be created at one point of time.
That is, first calculate the size (in bytes) of each row, multiply by the max number of rows likely to be created and you have the size of one table. Add up for all the temp tables and you can arrive at an approximate figure.
Do not forget to add bytes taken by row header and column header.

[SANJAY_G]

Search this forum for "table size estimation" there have been many discussions on the topic..
Based on your MAX tablesize and MAX no. of tables at any given time, you can estimate your datafile size.

There is no fixed farmula for that...

Sanjay

[sabita_ban]

Thanks all of u