SQL> drop user mvm_2010 cascade;
drop user mvm_2010 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use

SQL> select * from v$session where username='MVM_2010';

no rows selected


SQL> select table_name from dba_tables where owner='MVM_2010' and temporary='Y'
  2  ;

TABLE_NAME
------------------------------
ID_DATA_FILTER_BAK
ID_TEMP_PARAMETERS_GT


SQL>
SQL>
SQL> SELECT v.oracle_username
FROM v$locked_object v, all_objects a
WHERE v.object_id = a.object_id
AND a.object_name = 'ID_DATA_FILTER_BAK';  2    3    4

no rows selected

SQL> SELECT v.oracle_username
FROM v$locked_object v, all_objects a
WHERE v.object_id = a.object_id
AND a.object_name = 'ID_TEMP_PARAMETERS_GT'  2    3    4
  5  /

no rows selected



SQL> truncate table mvm_2010.ID_DATA_FILTER_BAK;

Table truncated.

SQL> truncate table mvm_2010.ID_TEMP_PARAMETERS_GT;

Table truncated.



SQL> drop user mvm_2010 cascade;
drop user mvm_2010 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

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

SQL> select owner,INDEX_NAME,TABLE_OWNER from dba_indexes where table_name='ID_DATA_FILTER_BAK';

no rows selected

SQL>

http://idba-oracle.blogspot.in/2012/09/ora-14452-attempt-to-create-alter-or.html
As per this MY GTT locked by few session we will killed the session but still session exits in v$session

SELECT 'USER: '||s.username||' SID: '||s.sid||' SERIAL #: '||S.SERIAL# "USER HOLDING LOCK", s.inst_id,s.status
FROM gv$lock l
,dba_objects o
,gv$session s
WHERE l.id1 = o.object_id
AND s.sid = l.sid
AND o.owner = 'MVM_2010'
AND o.object_name = 'ID_DATA_FILTER_BAK'

USER: MKE_2012 SID: 557 SERIAL #: 564501KILLED
USER: MKE_2012 SID: 557 SERIAL #: 564501KILLED
USER: MKE_2012 SID: 557 SERIAL #: 564501KILLED
USER: MSI_2011 SID: 564 SERIAL #: 438581KILLED
USER: MSI_2011 SID: 564 SERIAL #: 438581KILLED
USER: MSI_2011 SID: 564 SERIAL #: 438581KILLED
USER: MPA_2012 SID: 604 SERIAL #: 386651KILLED
USER: MPA_2012 SID: 604 SERIAL #: 386651KILLED
USER: MPA_2012 SID: 604 SERIAL #: 386651KILLED
USER: MPA_2012 SID: 604 SERIAL #: 386651KILLED
USER: MPA_2012 SID: 604 SERIAL #: 386651KILLED
USER: MSC_GRG SID: 615 SERIAL #: 425221KILLED
USER: MSC_GRG SID: 615 SERIAL #: 425221KILLED
USER: MSC_GRG SID: 615 SERIAL #: 425221KILLED
USER: MSC_GRG SID: 615 SERIAL #: 425221KILLED
USER: MSC_GRG SID: 615 SERIAL #: 425221KILLED
USER: MSC_GRG SID: 615 SERIAL #: 425221KILLED
USER: MSC_GRG SID: 615 SERIAL #: 425221KILLED
USER: MSC_GRG SID: 615 SERIAL #: 425221KILLED
USER: MSC_GRG SID: 615 SERIAL #: 425221KILLED
USER: MLN_2006 SID: 659 SERIAL #: 383001KILLED
USER: MLN_2006 SID: 659 SERIAL #: 383001KILLED
USER: MLN_2006 SID: 659 SERIAL #: 383001KILLED
USER: MLN_2006 SID: 659 SERIAL #: 383001KILLED
USER: MLN_2006 SID: 659 SERIAL #: 383001KILLED

This session exits in database more than a day with status killed,also there is no proces address for this session. we think this might cause problem,we unable to trace solution so we planning to shutdown database today night to clear lock,thanks for your valuable time.

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
[b]ORA-14452: attempt to create, alter or drop an index on temporary table already in use
[/b]