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:
20 gigabytes of data would be placed on one table (partitioned) approximately having 100 million records
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
I am going to place multiple indexes on this table (uniques)
I am not going to compress the table
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?