Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2005
    Location
    India
    Posts
    217

    Unanswered: Global temporary tables...

    I need the following information

    1. I found that the temp table definition stays in the memory till the DB session ends, what will happen in case of production servers where DB restart is not frequent?

    2. How to delete a temp table?Is it a good option to use temp tables?

    3. In Oracle temp table is session specific..What is a session in Oracle?
    Is it application session? or db session? or connection?

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I need the following information

    1. I found that the temp table definition stays in the memory till the DB session ends, what will happen in case of production servers where DB restart is not frequent? Don't worry about it, it doesn't take any space

    2. How to delete a temp table?Is it a good option to use temp tables? Don't drop a GTT, it only uses space during a session and it is designed to be a perm object

    3. In Oracle temp table is session specific..What is a session in Oracle?
    Is it application session? or db session? or connection? A session is a single connection to the database. If you login you have a session. When you logout your session (and any GTT tables are flushed.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    Thank you...Thats what i was looking for...

  4. #4
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    How to delete a global temp table?
    I beleive it gets deleted only when the DB is restarted? Can we delete it explicitly....

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You could use
    DELETE FROM global_temp_table_name;
    or
    TRUNCATE TABLE global_temp_table_name;
    And, check this excerpt:
    1. For a session-specific temporary table, a session gets bound to the temporary table with the first insert in the table in the session. This binding goes away at the end of the session or by issuing a TRUNCATE of the table in the session.

    2. DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table. Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed. This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, the table appears to be empty.

    3. Temporary segments are de-allocated at the end of the transaction for transaction-specific temporary tables and at the end of the session for session-specific temporary tables. If you rollback a transaction, the data you entered is lost, although the table definition persists.

    4. The data in a temporary table is, by definition, temporary, so backup and recovery of a temporary table's data is not available in the event of a system failure. To prepare for such a failure, you should develop alternative methods for preserving temporary table data.

  6. #6
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    Sorry i had put the question in a wrong..wht a meant was....

    How to DROP a global temp table?

  7. #7
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    I beleive that the data in a temp table in Oracle persists for a session...what happens in the case of connection pooling?

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Drop it as any other table ... "DROP TABLE global_temp_table_name;"

  9. #9
    Join Date
    Jun 2010
    Posts
    3

    Need help on Global Temporary Table

    Hi All,

    I need help on GTT. I need to create a table and hold few values and pass across different procedures for each user login separately.

    I am creating GTT in one procedure and using the same in another procedure and inside a trigger. When i am trying to get the values from trigger it says no records to find. Is session specific to application or database. how it is working. Please help me.

    Thanks
    Sri

Posting Permissions

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