Home » RDBMS Server » Server Administration » unable to drop portal user (Oracle 10.2.0.3)
unable to drop portal user [message #311236] Thu, 03 April 2008 16:37 Go to next message
shail_rh
Messages: 10
Registered: November 2007
Location: USA
Junior Member
Hi All,

I was trying to copy portal application's data from production to test environment. The import of the related schemas were giving lot of problem cuz I missed one step. Now when I am trying to drop portal schema , its not getting dropped. Even some of the tables and packages of portal schema is not dropping. Does anybody have any idea of this problem? When I am trying to drop it using

drop user portal cascade;

its just hanging there..doing nothing.

Thanks in Advance

Shail
Re: unable to drop portal user [message #311239 is a reply to message #311236] Thu, 03 April 2008 16:49 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>ts just hanging there..doing nothing.
You may be correct or you may be mistaken.

Are the REDO logfiles switching?

Do repeated queries like
select * from v$sess_io where SID = <SessionID_doing_drop>;
show increasing values for the session doing the drop user?
Re: unable to drop portal user [message #311243 is a reply to message #311236] Thu, 03 April 2008 17:01 Go to previous messageGo to next message
shail_rh
Messages: 10
Registered: November 2007
Location: USA
Junior Member
Thanks for replying. Redo logs are not switching at all, neither that query provided by you is giving incremental results.

shail
Re: unable to drop portal user [message #311246 is a reply to message #311236] Thu, 03 April 2008 17:20 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
I would not be surprised if you have tied yourself in knots from multiple sessions.

Run the following query:
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, 
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
/

If any rows are returned, then you have a bottleneck.
Re: unable to drop portal user [message #311454 is a reply to message #311236] Fri, 04 April 2008 09:33 Go to previous messageGo to next message
shail_rh
Messages: 10
Registered: November 2007
Location: USA
Junior Member
I ran this query and it gives no rows. Anyways this schema contains some Queue tables too. but as far as i know when we try to drop queye tables it throws an error not just hangs ...
Please let me know if you have any more suggestion.


Thanks
Shailendra
Re: unable to drop portal user [message #311529 is a reply to message #311454] Fri, 04 April 2008 12:40 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Possible but not likely, maybe your archive destination is full.
Re: unable to drop portal user [message #311541 is a reply to message #311236] Fri, 04 April 2008 13:01 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
> i know when we try to drop queye tables it throws an error
but you decided that we do not need to know EXACTLY which error occurs.

You're On Your Own (YOYO)!
Re: unable to drop portal user [message #311545 is a reply to message #311236] Fri, 04 April 2008 13:36 Go to previous messageGo to next message
shail_rh
Messages: 10
Registered: November 2007
Location: USA
Junior Member
I have dropped the queue tables using DBMS_AQADM.DROP_QUEUE but there are some tables which just doesnt give any response. Even though this is the only user associated with a tablespace called PORTAL , when I tried to drop that it was also not getting dropped. Even though its not responding but i am seeing following in alert log...

ORA-604 signalled during: DROP TABLESPACE PORTAL INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS ...


Thanks
Shailendra
Re: unable to drop portal user [message #311573 is a reply to message #311236] Fri, 04 April 2008 16:59 Go to previous message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
It is possible you are hitting some sort of Oracle bug.
One possible work around would be to do something like the following from SQL*Plus



set term off echo off feedback off pages 0
spool cleanup.sql
select 'drop '||object_type||' '||object_name||';'
from user_objects;
spool off
@cleanup.sql

-- this may need to be run multiple times before all objects get eliminated
Previous Topic: Suddenly SQL server cannot connect to Oracle Server :(
Next Topic: How to mount this database?
Goto Forum:
  


Current Time: Mon Dec 05 11:05:35 CST 2016

Total time taken to generate the page: 0.09483 seconds