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 person found this helpful
(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> ...
I had a chat with DBA, he said we have 500GB temp space available
Folks can you please suggest me
Edited by: newbie on Apr 3, 2013 2:21 PM
You have four cross joins (also called cartesian products)1 person found this helpful
in your statement. They and they alone are the cause of your TEMP space being used up.
Rewrite the statement.
Senior Oracle DBA
Are you using paralle for any particular reason? or one of the tables has degree in it definition?1 person found this helpful
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)