Home » SQL & PL/SQL » SQL & PL/SQL » Temp usage (Oracle9i Release 9.2.0.6.0 )
Temp usage [message #387876] Sat, 21 February 2009 04:02 Go to next message
ganeshsv
Messages: 51
Registered: January 2006
Member
HI,

When we are trying to execute the below DDL statement,it causes temp segment usgae error.

TABLE_TEST table contains 44 million records , all 10 numeric columns.

CREATE TABLE TRY1 AS
SELECT /*+ NOPARALLEL(a) FROM TABLE_TEST a
UNION ALL
SELECT /*+ NOPARALLEL(a) FROM TABLE_TEST a
UNION ALL
SELECT /*+ NOPARALLEL(a) FROM TABLE_TEST a;

This is an existing code.

We just removed the NOPARALLEL hint and this code started working fine (i.e there was no temp usage).

Can you please let know why this NOPARALLEL hint caused the temp usage?

Thanks in advance.

Regds,
GS

Re: Temp usage [message #387877 is a reply to message #387876] Sat, 21 February 2009 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You didn't follow guidelines and format your code
2/ Your code is invalid (and if you used a formatter you saw it)

Regards
Michel
Re: Temp usage [message #387878 is a reply to message #387877] Sat, 21 February 2009 04:11 Go to previous messageGo to next message
ganeshsv
Messages: 51
Registered: January 2006
Member
HI,
Apologize. Missed the code.

Here is the complete code
CREATE TABLE TRY1 AS
SELECT /*+ NOPARALLEL(a)*/ col1, col2 FROM TABLE_TEST a
UNION ALL
SELECT /*+ NOPARALLEL(a)*/ col1, col3 FROM TABLE_TEST a
UNION ALL
SELECT /*+ NOPARALLEL(a)*/ col1, col4 FROM TABLE_TEST a

Thanks in advance.

Regds,
GS
Re: Temp usage [message #387894 is a reply to message #387878] Sat, 21 February 2009 09:48 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not formatted and we have no idea of what you did to claim what you said.
Also we have no idea of your environment (table parallelism, indexes, statistics, execution plans...)

Regards
Michel

[Updated on: Sat, 21 February 2009 09:48]

Report message to a moderator

Previous Topic: Info about utils packages in oracle
Next Topic: Error access remote table using synonyms
Goto Forum:
  


Current Time: Mon Dec 05 12:52:58 CST 2016

Total time taken to generate the page: 0.13122 seconds