Results 1 to 6 of 6

Thread: Temporary Tables

  1. #1
    Contributing Member
    Join Date
    Jul 2006
    Answers
    56

    Temporary Tables

    Is it possible to create temporary tables in Oracle? If so how can one create the same? Can someone provide the syntax for doing this?


  2. #2
    Contributing Member
    Join Date
    May 2006
    Answers
    85

    Re: Temporary Tables

    In Oracle 8i, the CREATE GLOBAL TEMPORARY TABLE command creates a temporary table which can be transaction specific or session specific. For transaction-specific temporary tables, data exists for the duration of the transaction while for session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data. The table definition itself is not temporary.


  3. #3
    Junior Member
    Join Date
    Oct 2006
    Answers
    24

    Re: Temporary Tables

    The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The on commit delete rows clause indicates that the data should be deleted at the end of the transaction. Create global temporary table my_temp_table ( column1 number, column2 number ) on commit delete rows; in contrast, the on commit preserve rows clause indicates that rows should be preserved until the end of the session. Create global temporary table my_temp_table ( column1 number, column2 number ) on commit preserve rows;


  4. #4
    Contributing Member
    Join Date
    Jul 2006
    Answers
    56

    Re: Temporary Tables

    Both of your explanation was simply superb for me to get a detailed understanding. With this understanding I even tried creating one Temporary Table.


  5. #5
    Junior Member
    Join Date
    Jan 2007
    Answers
    8

    Re: Temporary Tables

    Hai,

    The information given about Temporary tables are very useful. I tried with Session-specific. But i dont know how to create transaction-specific Temporary tables. could anybody explain transaction-specific concept with a simple example?

    -----
    Bhuvana.


  6. #6
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    Re: Temporary Tables

    Hi Bhuvana,

    Creation of Transaction-specific Temporary tables is similar to session specific temporary tables creation but the difference lies in the "On Commit" parameter specified.
    When data is inserted into the temporary table the data persists either at the session level or transaction level based "delete rows" or "preserve rows" of "On Commit" parameter.
    Example for Transaction level Temporary table,
    CREATE GLOBAL TEMPORARY TABLE temptab
    ( column1 number, column2 varchar(5))
    on commit delete rows;

    Hope its clear.

    *** Innila ***

    Last edited by Innila; 02-20-2007 at 05:35 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact