http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_73a.htm#2061433

Victor,

before you get going too far with lots of temp tables in Oracle, please read this article
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1164655862293

this is one big difference between oracle & sql server.

HTH - Kevin

PD: tu ingles está perfecto

Hi,

The typical use of temp table in oracle is "temp tables are used to store intermidiatery results and the data in the temp table is eighter a trancation specifiec of session specific.

thanks
Alok.

Do we need to create temp tables in 9i or 10g....

we created the temporary tablespaces...but we did not create any temp tables...is this needed?

I know that, temp tables are used for transaction specifi or session specific.

Alok,

I think we no need to create temporary tables, Oracle will create temporary tables in the temp tablespaces when ever it is required during transactions...

am i right....

please correct me if i am wrong

Deepa,

That is a different thing. A Hash Join or a Sort or a Merge Sort will need
"temp" space but that is allocated within a universal Temporary Segment
by Oracle.

Oracle uses the nomenclature for "Global Temporary Tables" for structures
that you want to create as if they are permanent tables but to retain only
transient data (within a commit or session).

Hi,

You mean to say, when ever any new instance is created, we need to create temporary tables???

Please clarify me...

No, the Temporary Segment is automatically managed by Oracle.
That is the one used for Sorts, Hash etc

"Global Temporary Tables" are manually created only if your design needs them.

Ok Thanks,

Our Requirement means, Why can not that requiremnt use Temporary Tables??

As per the ask tom, in oracle there is no need of creating temporary tables, Oracle will automatically takes care of them.

Actually Oracle won't create explicit temporary tables automatically
Oracle SQL is so powerful you don't need them or Oracle creates and drops them automagically. Refer to inline views, refer the to the WITH statement in 9i and higher.

--
Sybrand Bakker
Senior Oracle DBA

Thanks sybrand,

If Oracle SQL is powerful, why temporary tablespaces are introuduced and why global temporary tables are introduced

There are two completely different things being discussed here temporary segments used by Oracle's internal processes and Oracle global temporary tables.
Or did I misunderstand?
We have used Oracle's global temporary tables as quick staging array while data is being integrated into finance apps. Testing showed the process performed faster with this design.
Both temporary segments and Oracle's global temporary tables are stored in the TEMP tablespace.