Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Question Unanswered: Use of Temporary Tables

    I am attempting to use a GLOBAL TEMPORARY TABLE which is Inserted into and Selected From for the prupose of loading a set of reporting tables. All of the Insert and Select statements are contained within a stored procedure. I understand from other posts that I have read that creating the temp table within the procedure is a bad idea.

    What I need to understand is when a session-table should be created and how if the only time that it is needed is for this 1 stored procedure that runs once every night.

    Also, does a GLOBAL TEMPORARY TABLE become available to each new session after it is created or does the CREATE have to be done for each session?

    None of this information was clear in my manuals or in the Google results.

    Thanks,

    David

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    go to http://asktom.oracle.com
    and do a key word search on global temporary table
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    A global temporary table is created exactly like any other table: create once, in one place, and grant usage of it to as many roles/users as you need.

    The difference is that the contents of the table are session-specific and temporary. Your session can't see rows my session inserts, and mine can't see yours. Also, the data is automatically deleted either when you commit, or when you disconnect (depending on the ON xxx DELETE ROWS option specified when the table was created).

    So:
    - no create/drop statements in your programs
    - no "session_id" columns required to distinguish your data from mine
    - no need to "tidy up" by deleting temp. data at the end of the program.

Posting Permissions

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