We are trying to create global temp table using

    Create global Temporary table t1 ( id NUMBER, description VARCHAR2(20) ) 
ON COMMIT Delete Rows;

and trying to drop it at the end manually but the temp table created cannot be dropped by after but the session the data is truncated at the end.

  1. Not able to drop it - is this how oracle works with GTT ?
  2. What are the alternatives ??
  • How do you define the table (show the DDL)? How do you drop it? How do you determine that the table remains? – mustaccio Jul 12 '19 at 17:32
  • the table Definition would be Create global temp table t1 ( id NUMBER, description VARCHAR2(20) ) ON COMMIT DROP DEFINITION; Drop table t1 ; -- to identify the table is still there select * from all_tables where TEMPORARY = 'Y' -- Or even when i try to run the same create query it says object already in the system – Guru vyas V Jul 12 '19 at 17:42
  • Please use the edit button and add relevant information to your question, as comments are ephemeral. – mustaccio Jul 12 '19 at 17:45
  • 1
    "Not able to drop it" is not error message or an exact problem description. – Balazs Papp Jul 12 '19 at 18:02
  • 2
    You misunderstand how GTTs work. Global temporary tables are not dropped automatically. "The temporary table definition persists in the same way as the definitions of regular tables, but the table segment and any data the table contains are either session-specific or transaction-specific data", says the manual. – mustaccio Jul 12 '19 at 18:07

As the comments indicate Oracle Global temporary tables work differently than some might expect. They are intended to be permanent structures. The settings allow for data to be retained when a session is closed or deleted.

The key reason to use them is that each user can only access data for that user.

Your developers can all use the same table and insert/update/delete without interfering with other users.

There is no need to drop a GTT after using it.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.