Re: Where is a temp tables DDL stored?

"g3000" <carlton_gregory_at_yahoo.com> wrote in message news:1126705798.012355.18740_at_g49g2000cwa.googlegroups.com...
> Im on Win2k3 server Standard Ed.
> Oracle9i ( 9.2.0.6 )
>
> Creating several temp tables that are transaction specific.
>
> Im noticing that after executing the ddl for these tables and viewing
> them via Enterprise Manager the tablespace is listed as CWMLITE and not
> the default tablespace of the user who was creating it.
>
> Can I define a tablespace for a temp table as I do for a regular table?
> The 9i docs dont give an example that way.
>
> I have the TEMP tablespace defined as the users temporary tablespace.
>

If you are using proper global temporary tables, then you shouldn't see a tablespace at all.

The definition of the table structure is in the schema of the table creator.

But when a user uses the table, their local copy goes into whichever tablespace is their temporary tablespace (or into the temporary tablespace of the owner of the procedure if they are running transactions through a standard procedure).

In fact, if you try to specify a tablespace for a global temporary table, you should be getting an error message:

    SQL> create global temporary table gtt2(n1 number) tablespace users_assm;

    create global temporary table gtt2(n1 number) tablespace users_assm     *
    ERROR at line 1:
    ORA-14451: unsupported feature with temporary table

When you query the data dictionary views, you should get a null for the tablespace of a global temporary table.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005