Let's say I have an input of 20 gigabytes of raw data (text file) which in turn I would into an external table and would then swap partition in order to place them on my database.

The question now is given 20 gigabytes, what should I expect the size of my TEMP/UNDO and USERS (default) tablespace to be when this happens given the following assumptions:

  1. 20 gigabytes of data would be placed on one table (partitioned) approximately having 100 million records

  2. I am going to bulk process this data using CTAS (then drop the orignal and rename the new table) as well as do some mass/bulk updates and selects

  3. I am going to place multiple indexes on this table (uniques)

  4. I am not going to compress the table

  5. I am using a 32kb block size for my USERS(or default) tablespace

Given these considerations, I have come up with this sizing estimate and as to why I chose these: (Please correct me if I'm wrong since I am not an experienced DBA)

USERS tablespace (40 gigabyte) ~ I might use a bigfile tablespace data file instead of a regular one. I am assuming that the size of the input text file would be of the same size when I transfer it to a table. The reason why I doubled it is because I am going to use CTAS which from what I understand would be similar to copying the table itself.

TEMP/UNDO tablespaces (20 gigabyte for each) ~ From what I understand, temp is used for sorting data and UNDO would be used for rollback and updates (please correct me if I am wrong about this), and given that most of my queries and update statements would load most if not all of the 100 million records on my table, I am thinking that the exact size for sorting and undo should be the same as the data file size itself. (Again this might be a less educated guess and please correct me if I am mistaken)

Summing that up I would have around 80 gigabytes of tablespaces on my database machine, exclusing the redo logs and the indexes which I think I'll put on another tablespace sizing around 10-20 gigabytes giving me a total of 100 gigabytes

So right now I am looking at 100 gigabytes table space needed for an input of 20 gigabytes.

Is this okay or am I doing something wrong here and blowing the tablespace sizing out of proportion?

  • I think this site has a higher population compared to dbastackexchange which would yield me a faster response/answer, but if the mods would want to move my question there for the sake of segregation then I have nothing against that – Avias Nov 28 '13 at 8:53
  • shameless bump on this – Avias Nov 28 '13 at 10:10
  • Another shameless bump on this – Avias Nov 28 '13 at 14:28
  • Reckon it would be faster just to import it and try. Read up on SQLLoader. Which would be more beneficial. Just create the tablespaces with initial size xGB and autoextend on. This will make sure that your tablespaces won't fill up. – Munchi Nov 28 '13 at 14:53
  • Temp is used when the table won't fit the buffer cache. And UNDO size depends on your strategy, if you do single or bulk insert/update commit. – Munchi Nov 28 '13 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.