How to drop a global temporary table?
Hello!I have a problem with droping a global temporary table from a Java-application.
For performance I temporary store a result from a complex view into a temporary table.
The table is created like:
CREATE GLOBAL TEMPORARY TABLE tbl_names(ids NUMBER(19)) ON COMMIT PRESERVE ROWS
When everything is done I would like to clean up the database, in other words I would like to drop (completely nuke) this table.
First I just tried to drop the table afterwards in the same transaction like:
DROP TABLE tbl_names
But that gave me a ORA-14452: attempt to create, alter or drop an index on a temporary table already in use
I found out that perhaps I should truncate it before.
So I put the code (from Java) like (following illustrates logic):
connection = DriverManager.getConnection(args[1], args[2], args[3]);
fillTempTable(connection);
connection.commit();
connection = DriverManager.getConnection(args[1], args[2], args[3]);
truncateTempTable(connection);
connection.commit();
connection = DriverManager.getConnection(args[1], args[2], args[3]);
dropTempTable(connection);<<<<ERROR
connection.commit();
connection = DriverManager.getConnection(args[1], args[2], args[3]);
dropTempView(connection);
connection.commit();
But I still get the same error message.
ORA-14452: attempt to create, alter or drop an index on a temporary table already in use
After each commit I think there should be nothing thats can be said as "already using the table".
So if any one got any input please let me know
Best regards
Fredrik