ORA-1652: unable to extend temp segment by 128 in tablespace TEMP01
TEMP tablespace was getting filled when running sql statement.
i increased the temp table size from 25g, 32g, 64 gb. in all cases it throws error.
I ran sql tuning advisor on sql and it gave recommendation of profile, index and gather stats.
i created profile, index and gather stats. it did magic sql completed under 5 seconds and did not throw any error.