Oracle Database Discussions

Hi: I am on 10.2.0.3.

I have an interesting problem with global temporary tables.

I created a few and was using them for awhile.

Now I am trying to drop one (it has a couple indexes as well) and here is what I am getting:

SQL> drop table sysadm.PS_BPA_RATES_TA011;
drop table sysadm.PS_BPA_RATES_TA011
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

Needless to say I checked the active sessions (using v$session) but there is none which locks this table.

I truncated it (although it had 0 rows but I was trying to free up space), also tried to flush shared_pool & buffer_cache (it's a test DB) - which were successful but did not help - I am getting the same error.

Someone suggested checking V$TEMPSEG_USAGE where segtype='DATA'. This view shows 3 rows with session# & sql_id but both does not show any session:

SQL> select * from V$TEMPSEG_USAGE where segtype='DATA';

USERNAME USER SESSION_ADDR SESSION_NUM SQLADDR SQLHASH SQL_ID
------------------------------ ------------------------------ ---------------- ----------- ---------------- ---------- -------------
TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
------------------------------- --------- --------- ---------- ---------- ---------- ---------- ----------
SYSADM SYSADM C00000021C1D2318 123 C0000002DB467ED0 146923226 0ahnqxh4c3rqu
TEMP TEMPORARY DATA 204 465289 1 128 4

SYSADM SYSADM C00000021C1CE508 32644 C00000021B6D3F58 2784120300 0c5411akz4mgc
TEMP TEMPORARY DATA 204 506761 14 1792 4

SYSADM SYSADM C00000021C1CE508 32644 C00000021B6D3F58 2784120300 0c5411akz4mgc
TEMP TEMPORARY DATA 204 505993 1 128 4

SYSADM SYSADM C00000021C1CE508 32644 C00000021B6D3F58 2784120300 0c5411akz4mgc
TEMP TEMPORARY DATA 204 505865 1 128 4


SQL> select session_num,sql_id from V$TEMPSEG_USAGE where segtype='DATA';

SESSION_NUM SQL_ID
----------- -------------
123 0ahnqxh4c3rqu
32644 0c5411akz4mgc
32644 0c5411akz4mgc
32644 0c5411akz4mgc


SQL> select sid,serial#,process from v$session where sql_id='0ahnqxh4c3rqu';

no rows selected

SQL> select sid,serial#,process from v$session where sid=32644;

no rows selected

I know that recycling the database should help but I can not do it right now - and I am sure there should be a solution.

Any help appreciated. TIA,
GregX