Temp Size [message #417085] |
Thu, 06 August 2009 00:24 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
maheshmhs
Messages: 93 Registered: October 2008 Location: Nepal
|
Member |
|
|
Hello All,
I have one table which is 70 GB. Now i have to insert data into a new table from this table and from 12 other tables(much smaller, around 1 gb or even less) by left join. There is no cross join.
However the temp tablespace has grown too large(316 gb). Since temp tablespace has consumed so much of space, there is actually no space left for the new table to be created. Is there any way by which i could reduce the tempspace consumption, other than ctas. CTas is going to be the last option.
Thank You :
|
|
|
|
Re: Temp Size [message #417092 is a reply to message #417089] |
Thu, 06 August 2009 00:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
maheshmhs
Messages: 93 Registered: October 2008 Location: Nepal
|
Member |
|
|
CTas is going to be the last option because on doing CTAS sometimes the data types/size get changed( esp when the column has no value populated). Now this table on performing union with other table creates problem.
Although the data type can be cast to the actual data type while performing ctas, it is going to be a tedious task for there are so many other table with hundreads of columns.
So ctas has become the last option for me.
|
|
|
|
Re: Temp Size [message #417138 is a reply to message #417097] |
Thu, 06 August 2009 04:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
maheshmhs
Messages: 93 Registered: October 2008 Location: Nepal
|
Member |
|
|
Thank You Michel.
It is pretty much sure that such a big temp size is due to 12 joins. If we replace those joins with inline views does it temp size?
Thank You
|
|
|
Re: Temp Size [message #417143 is a reply to message #417138] |
Thu, 06 August 2009 05:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Views are just the same thing as inline queries, so it does not change anything.
Regards
Michel
|
|
|