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