Temp Tablespace space needed to create an index of 52GB - preventing 01652
Hi,
I've seen quiet a few threads on the subject, but could not find a clear (to me) answer on the subject.
We have a huge table made by 30/40 partitions of roughly 20M records each (each record being about 1k of size), overall 800GB.
The index we already have shows:
1* select sum(bytes/1024/1024) from dba_segments where segment_name like 'CALL_LOG_I0'
SQL> /
SUM(BYTES/1024/1024)
--------------------
52088
so that I assume it's 52GB in size. Since we have to create another very similar one I would assume the size would be similar.
Command would be:
create index call_log_i0_new on call_log (msisdn, generation_timestamp, cdr_reference_number, Multiple_AMA_Sequence_Number) NOLOGGING online;
Tried the NOSORT option but it's not compatible with the online option.
After a while the TEMP tablespace got filled up (we have about 20GB space in there).
My question is if I need to have a temp tablespace at least as big as the index size (52GB) to let the index be built online.
Thanks in advance !
Cheers,
Mike