Dropping Temporary Tables in SQL Developer

Question

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!

Answer 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.

Answer 2

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.