Avatar billede fredand Forsker
12. februar 2008 - 17:47 Der er 4 kommentarer og
1 løsning

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
Avatar billede arne_v Ekspert
13. februar 2008 - 05:12 #1
Class.forName("oracle.jdbc.OracleDriver");
        Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:arnepc3", "xxxx", "xxxx");
        Statement stmt = con.createStatement();
        stmt.executeUpdate("CREATE GLOBAL TEMPORARY TABLE tbl_names(ids NUMBER(19)) ON COMMIT PRESERVE ROWS");
        stmt.executeUpdate("INSERT INTO tbl_names VALUES(123)");
        stmt.executeUpdate("TRUNCATE TABLE tbl_names");
        stmt.executeUpdate("DROP TABLE tbl_names");
        con.close();

works for me on 9i.
Avatar billede arne_v Ekspert
13. februar 2008 - 05:14 #2
I see two possible explanations of the difference:
1) you use a different version
2) you apparently use different connections

You could start by trying with same connection - at least for TRUNCATE og DROP.
Avatar billede arne_v Ekspert
13. februar 2008 - 05:36 #3
Or maybe as the very first check whether you close each connection as you should.
Avatar billede fredand Forsker
14. februar 2008 - 21:18 #4
Hello Arne!

You solved it!!

The error was that I forgot to close the connection.
Once I did as you said it worked like a dream!

Please give a svar so I can reward you!!

Thanks alot!!
/Fredrik
Avatar billede arne_v Ekspert
14. februar 2008 - 21:29 #5
here it comes
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester