2

I'm using SQL Developer and am working with Global Temporary Tables. The problem is that the temporary tables are not fully dropping when I end a session. Upon opening a new session all the "temporary" tables are listed under my schema (they don't contain data, just show what the columns were, and they have a gray circle with an x).

I'm having to manually drop each table, which is really time-consuming. Is there a setting in SQL developer or a quick code to permanently delete the temporary tables after each session?

Or better yet, a way to drop the tables during my session? (If I try this I get the error "An attempt was made to create, alter or drop an index on temporary table which is already in use.")

THANK YOU!

jo112358
  • 55
  • 1
  • 5
  • I found the answer... First you must TRUNCATE TABLE TABLENAME; then you can DROP TABLE TABLENAME;. – jo112358 Nov 18 '14 at 22:13
  • If your design requires dropping Global Temporary Tables, your design is wrong. If your design requires truncating them, your design is *probably* wrong. – Jeffrey Kemp Nov 19 '14 at 03:25

2 Answers2

1

Well this is how global temporary tables work in Oracle.

They are always present (e.g. in the system catalogs) and you only need to create them once (not every time you use them).

I'm having to manually drop each table, which is really time-consuming.

You don't have to drop them - that's the whole point of a global temporary table.

Each session has its own copy of the data in that table. The data is "removed" either when you commit or disconnect (depending on how you created the GTT).

Or better yet, a way to drop the tables during my session?

You don't need that. Just keep them around and use them.

  • I do need to be able to delete them... I write probably 25 a day playing around with code and need them to disappear forever. It gets old having to make new names for each table. I'd like to be able to do generic "TBL1" and then delete it, and later use "TBL1" for something else. I'm not talking about polished up, have all the data just how I want it tables, I'm talking about tables I write as rough-drafts, trying to get the data just how I want it. Once a rough draft table is useless to me, how do I get it to go away without having to close my session and then manually drop table? – jo112358 Nov 18 '14 at 15:58
  • 1
    This all sounds as if your approach to designing tables and using temporary tables is wrong (or at least not how GTTs are supposed to be used in Oracle). Why do you need them in the first place? Using temporary tables is _very_ uncommon in Oracle. Most of the time simply using a derived table/sub-select/common table expression is just as fine. Oracle is not SQL Server. –  Nov 18 '14 at 16:00
  • You might be right, there may be a better way than GTTs of what I'm trying to accomplish, I just don't know of it... I'm needing a temporary table to hold tons of information/calculations that I need later on. For instance, right now I have 7 temporary tables with variations of data and my final select statement pulls counts and calculations from each of those 7 tables. Tomorrow, I might want different data in those 7 tables and the same calculations at the end, and I'm trying to find a way to drop the tables that I'm using today. – jo112358 Nov 18 '14 at 16:09
1

Putting aside question about dropping and recreating temporary tables, here is a script to drop all temporary tables in your schema:

begin
for x in (select 'drop table ' || table_name || '' s from user_tables
      where temporary = 'Y') 
loop
execute immediate x.s;
end loop;
end;
/

If you use some prefix, you may add it to avoid dropping something useful:

begin
for x in (select 'drop table ' || table_name || '' s from user_tables
      where temporary = 'Y'
      and table_name like 'dropme%') 
loop
    execute immediate x.s;
end loop;
end;
/

Just remember, that creating/dropping temporary tables is DDL operation, so whatever was happening before this operation, will be committed.

vav
  • 4,584
  • 2
  • 19
  • 39