This content has been marked as final. Show 4 replies
Databases are often optimized to handle only a few joins at a time. Depending on how much data you have per table, this will certainly generate a lot of garbled data.
(1) Talk to your DBA. I'm sure he/she is aware by now (temp tablespaces don't shrink themselves). Maybe your DBA can help you come up with a better solution.
(2) Try progressively building your table using intermediate results; e.g., CREATE TABLE t1 AS SELECT * FROM s1,s2, then CTAS 't1 X s3' and so on. Because you have a space issue, drop the intermediate tables as you move along. Also, identify a target tablespace for the intermediate and final tables, e.g., CREATE TABLE t1 TABLESPACE <your TBS> ...
Are you using paralle for any particular reason? or one of the tables has degree in it definition?
May be you can use the hint no_parallel to avoid the parallel query and reduce the use of temp.
The query might look similar to this:
insert into target t
select /*+ NO_PARALLEL(s1) NO_PARALLEL(s2) NO_PARALLEL(s3) NO_PARALLEL(s4)*/ * from source s1,s2,s3,s4 (4 tables)