ORA-14452: attempt to create, alter or drop an index on temporary table already in use

If you are wondering why you can't drop a global temporary table (defined with ON COMMIT PRESERVE ROWS) that you no longer need, keep in mind that there can be other active sessions holding data in the table.

In such situations you can expect to find one or more rows in V$TEMPSEG_USAGE where column SEGTYPE contains DATA and using the other pointers you can perhaps track back to the session(s) holding these segments.

For instance, in my case i had an oracle session opened by Oracle Application Express (APEX) which was staging data in a global temporary table (a rather uncommon need i must say...) and when i attempted to drop the table i got:

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

This happens because the "standard" user has no control on opening and closing oracle sessions managed by APEX, so one may have closed the page but the oracle sessions behind it is still active and actually it could be recycled for some other web user, so it's not going to disappear any time soon on its own.

For ordinary situations, supposing you are the DBA (and you are not doing this in a production database...) you can try to kill the session in order to reclaim the temporary segments and re-execute the DROP TABLE statement.

See message translations for ORA-14452 and search additional resources.

---

ORA-14452: tentativo di creazione, modifica o cancellazione di un indice di una tabella temporanea già in uso
ORA-14452: se ha intentado crear, modificar o borrar un índice de una tabla temporal ya en uso
ORA-14452: intent de crear, alterar o eliminar un índex d'una taula temporal que ja està essent utilitzada
ORA-14452: tentative de création, modification ou suppression d'un index sur une table temporaire déjà en cours d'utilisation
ORA-14452: Versuch, einen Index auf einer schon verwendeten temporären Tabelle zu erstellen, zu ändern oder zu löschen
ORA-14452: απόπειρα δημιουργίας, μεταβολής, ή διαγραφής ενός ευρετηρίου προσωρινού πίνακα που ήδη χρησιμοποιείται
ORA-14452: forsøg på at oprette, ændre eller droppe indeks på midlertidig tabel, der allerede er i brug
ORA-14452: försök gjordes att skapa, ändra eller ta bort ett index i temporär tabell som redan används
ORA-14452: forsøk på å opprette, endre eller fjerne en indeks for midlertidig tabell som allerede er i bruk
ORA-14452: jo käytössä olevan väliaikaisen taulun indeksiä yritettiin luoda, muuttaa tai poistaa
ORA-14452: kísérlet egy már használatban lévő ideiglenes tábla indexének létrehozására, módosítására vagy eldobására
ORA-14452: încercare de creare, modificare sau eliminare a unui index, într-un tabel temporar deja în uz
ORA-14452: Poging tot aanmaken, wijzigen of verwijderen van index uit tijdelijke tabel die al in gebruik is.
ORA-14452: tentativa de criar, alterar ou eliminar um índice em uma tabela temporária que já está sendo usada
ORA-14452: tentativa de criar, alterar ou suprimir um índice numa tabela temporária já em utilização
ORA-14452: попытка создать, изменить или удалить индекс в уже используемой временной таблице
ORA-14452: pokus o vytvoření, změnu nebo zrušení indexu v již použité dočasné tabulce
ORA-14452: pokus o vytvorenie, zmenu alebo zrušenie indexu na dočasnú tabuľku, ktorá sa už používa
ORA-14452: próba utworzenia, zmiany lub usunięcia indeksu dla tymczasowej tabeli, która już jest w użyciu
ORA-14452: geçici tabloda bir dizin yaratma, değiştirme veya bırakma denemesi zaten yapılıyor
ORA-14452: attempt to create, alter or drop an index on temporary table already in use