0

In Unix, connecting to oracle server, I create a temp table on commit preserve rows. I then first truncate the table then I go to drop the table. Trying to drop the table I receive the following error:

ORA-14452: attempt to create, alter or drop an index on temporary table already in use (DBD ERROR: error possibly near <> indicator at char 11 in 'drop table <>temp01')

I cannot end session using Kill through commands because I do not have permission.

Filburt
  • 17,626
  • 12
  • 64
  • 115
joepitz1
  • 194
  • 2
  • 12
  • 4
    Why are you trying to drop the table? In Oracle, a global temp table is global-- its definition is visible to all sessions. Global temp tables are created once, at the same time that permanent tables are created, and are not dropped (outside of builds where you are making schema changes). This is different from other databases where temporary tables are local and are therefore commonly created and dropped within the session. – Justin Cave Jan 26 '15 at 19:58
  • Thanks, I am assuming Oracle does not have a feature for local temp tables where they can be dropped? I need to create temp tables throughout the day and dont want to many tables building up. – joepitz1 Jan 26 '15 at 20:06
  • 1
    Oracle does not have local temporary tables, no. Depending on the problem you're trying to solve, potentially you want to use a collection. If you want to use temporary tables, though, why would the "build up"? Why wouldn't you create one temporary table and then use it multiple times throughout the day. – Justin Cave Jan 26 '15 at 20:30
  • I guess I could. So even if all sessions ended the global temp table would remain forever? – joepitz1 Jan 26 '15 at 20:57
  • 1
    Yes. Just like a permanent table, the global temporary table exists forever. It's just the data that is temporary. – Justin Cave Jan 26 '15 at 21:14
  • Thank You, you have help make sense of this unknown area for me, Thanks Again – joepitz1 Jan 26 '15 at 21:36

2 Answers2

2

Seems to me, the error is pretty clear:

$ oerr ora 14452
14452, 00000, "attempt to create, alter or drop an index on temporary table already in use"
// *Cause:  An attempt was made to create, alter or drop an index on temporary
//          table which is already in use.
// *Action: All the sessions using the session-specific temporary table have
//          to truncate table and all the transactions using transaction 
//          specific temporary table have to end their transactions.

So, make sure that all sessions are not using the table. If even one other session is using the table, You will get this error, and won't be able to drop it.

halfer
  • 19,824
  • 17
  • 99
  • 186
Mark J. Bobak
  • 13,720
  • 6
  • 39
  • 67
0

Before dropping the table, it needs to be truncated it in all sessions. This works for me.

TRUNCATE TABLE temp_table;
DROP TABLE temp_table;

This was the part of the error message that addresses the need to truncate first.

*Action: All the sessions using the session-specific temporary table have to truncate table and all the transactions using transaction specific temporary table have to end their transactions.

Tarzan
  • 4,270
  • 8
  • 50
  • 70