| unable to drop portal user [message #311236] |
Thu, 03 April 2008 16:37  |
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 #311246 is a reply to message #311236] |
Thu, 03 April 2008 17:20  |
 |
BlackSwan
Messages: 26766
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 |
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 #311545 is a reply to message #311236] |
Fri, 04 April 2008 13:36 |
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 |
|
BlackSwan
Messages: 26766
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
