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?
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 ?
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
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.