Tablespace Size Calculation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Tablespace Size Calculation

  1. #1
    Join Date
    Jan 2002
    Posts
    65
    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.
    Sabitabrata

  2. #2
    Join Date
    Feb 2000
    Posts
    175
    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.

  3. #3
    Join Date
    Jan 2002
    Posts
    65
    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.
    Sabitabrata

  4. #4
    Join Date
    Oct 2000
    Posts
    467
    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 ?
    Vinit

  5. #5
    Join Date
    Jan 2002
    Posts
    65
    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.



    Sabitabrata

  6. #6
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    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.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  7. #7
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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

  8. #8
    Join Date
    Jan 2002
    Posts
    65
    Thanks all of u
    Sabitabrata

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width