DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Is it possible to create local temporary table..?

  1. #1
    Join Date
    Jul 2002
    Posts
    205

    Is it possible to create local temporary table..?

    Hi ,


    We create Global Temporary table by the syntax

    Create Global Temporay table ....

    This Global Temp table, people at differnet session can work. One session can not see the data of another session.

    But I want to create the table and drop it after it is used. It has to be done inside a stored procedure.

    Is it possible to create local temporarytable...? If it is possible all the people can create the local temp table at their own seesion with same name and drop the table after it is used..

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    I don't know what you want to achieve but lets see how exactly a temp table works:

    The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table. The ON COMMIT keywords indicate if the data in the table is transaction-specific (the default) or session-specific:

    ON COMMIT DELETE ROWS specifies that the temporary table is transaction specific and Oracle truncates the table (delete all rows) after each commit.
    ON COMMIT PRESERVE ROWS specifies that the temporary table is session specific and Oracle truncates the table when you terminate the session.

    There is no local temp table as far as I know.
    Last edited by Raminder; 07-04-2003 at 12:57 PM.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: Is it possible to create local temporary table..?

    Originally posted by skdas
    But I want to create the table and drop it after it is used
    That might be how you do it in other RDBMS, but what benefit do you get from that? (It sounds to me like a case of "pissing against the wind" as the French would say.)

    If you really must and all this is happening in the same schema, try creating a table with a name derived from the Audit SID (see SESSIONID in SYS_CONTEXT) - that should be unique.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    That's a very "SQLServer approach", to workaround SQL Server's difficulty in optimizing queries with many tables.

    In Oracle you don't need to do this -- a quick conversion from the temp table methodology is to embed the temp table query inside an inline view.

    Search the forum for examples -- there was one very recently.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width