Home » RDBMS Server » Server Administration » Unable to drop user (10.2.0.3 Enterprise edition)
Unable to drop user [message #582544] Fri, 19 April 2013 06:13 Go to next message
AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai
Member

Dear All,

Please note today we unable to drop user due to below error,Please advice how to drop the below user without shutdown the database.


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



Please note no session was availlable for particular user.

Regards,
Anil
Re: Unable to drop user [message #582546 is a reply to message #582544] Fri, 19 April 2013 06:23 Go to previous messageGo to next message
Littlefoot
Messages: 19618
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I can tell, someone is still holding a (global) temporary table; until it is released, you can't do much about it.

How to release it in a painless manner? Well, that's something I'd like to hear as well. I didn't have to drop a user, but sometimes needed to recreate a global temporary table and couldn't do that because some session held it, although I was quite sure that I disconnected everyone involved. Maybe Oracle held some kind of "locks" (whatever) for certain period of time - can't tell.

Unfortunately, such nuances are beyond my knowledge. Looking forward to future replies!

Re: Unable to drop user [message #582569 is a reply to message #582546] Fri, 19 April 2013 08:44 Go to previous messageGo to next message
Roachcoach
Messages: 1203
Registered: May 2010
Location: UK
Senior Member
Anything holding locks should be traceable via the lock tables/views available, no?
Re: Unable to drop user [message #582573 is a reply to message #582544] Fri, 19 April 2013 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-14452: 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.

Re: Unable to drop user [message #582579 is a reply to message #582573] Fri, 19 April 2013 09:52 Go to previous messageGo to next message
AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai
Member

Hi,

Please note we have two global temporary tables in my schema,there is no lock on global temporary table.Also we trucated global temp table but still have same error.



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



Regards,
Anil
Re: Unable to drop user [message #582580 is a reply to message #582579] Fri, 19 April 2013 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So drop the GTT before dropping the user.

Regards
Michel
Re: Unable to drop user [message #582581 is a reply to message #582580] Fri, 19 April 2013 09:59 Go to previous messageGo to next message
AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai
Member

Hi,
we unable to drop table also.


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




Regards,
Anil

Re: Unable to drop user [message #582582 is a reply to message #582581] Fri, 19 April 2013 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good, so it is a problem with drop user but drop GTT.
Is there any index on the table?
If so drop them first then retry to drop the user.

Regards
Michel
Re: Unable to drop user [message #582759 is a reply to message #582582] Mon, 22 April 2013 08:13 Go to previous messageGo to next message
AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai
Member

Hi,

Sorry for delay in my response,Please note we dont have any index for GTT.


SQL> select INDEX_NAME,TABLE_NAME from user_indexes where table_name='ID_DATA_FILTER_BAK';

no rows selected

but still unable to drop.

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

Regards,
Anil


Re: Unable to drop user [message #582761 is a reply to message #582759] Mon, 22 April 2013 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure you were with the same account in the 2 sessions?

Regards
Michel
Re: Unable to drop user [message #582762 is a reply to message #582759] Mon, 22 April 2013 08:20 Go to previous messageGo to next message
Roachcoach
Messages: 1203
Registered: May 2010
Location: UK
Senior Member
AnilKampiri wrote on Mon, 22 April 2013 14:13
Hi,

Sorry for delay in my response,Please note we dont have any index for GTT.


SQL> select INDEX_NAME,TABLE_NAME from user_indexes where table_name='ID_DATA_FILTER_BAK';

no rows selected


Run it against DBA_INDEXES.

If someone created an index on the table but put it in another schema, USER_INDEXES shows nothing.
Re: Unable to drop user [message #582763 is a reply to message #582762] Mon, 22 April 2013 08:26 Go to previous messageGo to next message
AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai
Member

hi,

Please note checked in DBA_INDEXES as well no index for that table.


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

no rows selected

SQL>

Re: Unable to drop user [message #582764 is a reply to message #582762] Mon, 22 April 2013 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also see it in ALL_INDEXES if you have not the privileges on DBA_INDEXES.

Regards
Michel
Re: Unable to drop user [message #582770 is a reply to message #582764] Mon, 22 April 2013 08:45 Go to previous messageGo to next message
Roachcoach
Messages: 1203
Registered: May 2010
Location: UK
Senior Member
What happens if you connect as the user, truncate the table, attempt the drop and then query the lock views?

I can't see any normal situation (bugs aside) where those events above would not reveal something useful. Are there any triggers involved?
Re: Unable to drop user [message #582777 is a reply to message #582764] Mon, 22 April 2013 09:13 Go to previous messageGo to next message
AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai
Member

Hi,

I checked in all_indexs as well but index does'nt exit for that table,But today we used differnt query to find locked object got from below URL

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.



Regards,
Anil



Re: Unable to drop user [message #582785 is a reply to message #582777] Mon, 22 April 2013 09:48 Go to previous messageGo to next message
Roachcoach
Messages: 1203
Registered: May 2010
Location: UK
Senior Member
The GV$ views are on/for RAC....you didnt mention RAC Wink
Re: Unable to drop user [message #582786 is a reply to message #582785] Mon, 22 April 2013 10:09 Go to previous messageGo to next message
ashwani0301
Messages: 44
Registered: March 2013
Location: Karnatka, Bangalore
Member
Hi,
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]


Do you have any DDL trigger ?.
Please check it.

[Updated on: Mon, 22 April 2013 10:11]

Report message to a moderator

Re: Unable to drop user [message #582787 is a reply to message #582786] Mon, 22 April 2013 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, this is NOT an error from a DDL trigger.

Regards
Michel
Re: Unable to drop user [message #582854 is a reply to message #582787] Tue, 23 April 2013 02:31 Go to previous messageGo to next message
nlnkapardi
Messages: 26
Registered: April 2013
Location: India
Junior Member

Hi anil !

Very Good day to you !

Please check weather the User is doing any transactions. If so please Close All the trasactions and then try dropping the user.
Otherwise, If that is a RAC database See the node Which is current in use and then see any Process are used like Sometimes that session earlier might do some huge transactions Which might be into long ops as a result this might be issue. So Solution for this is Login to OEM and please Search with the help Of SID and check what that session exactly is doing.This another way.

Please feel free to reply me.

Regards
kapardi

Re: Unable to drop user [message #582862 is a reply to message #582854] Tue, 23 April 2013 02:58 Go to previous message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Login to OEM is not the solution.
Everything to investigate has already been said.
Please feel free to read the previous posts and to not add one if you have nothing more to add.

Regards
Michel
Previous Topic: about oracle database 9i
Next Topic: pga_aggregate_target
Goto Forum:
  


Current Time: Fri Sep 19 05:41:36 CDT 2014

Total time taken to generate the page: 1.46992 seconds