Re: cant drop any user

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 21 Nov 2007 06:22:05 -0800
Message-ID: <1195654918.861568@bubbleator.drizzle.com>

spin wrote:

> On Nov 20, 2:56 pm, ngarima..._at_gmail.com wrote:

>> whem i try to drop any user or tbspaces i have the next error
>>
>> drop user $USER CASCADE;
>> *
>> ERROR at line 1:
>> ORA-00604: error occurred at recursive SQL level 2
>> ORA-01422: exact fetch returns more than requested number of rows
>>
>> i check the sys.dual and it have 1 row
>>
>> select count(*) from dual;
>>
>> COUNT(*)
>> ----------
>> 1
>>
>> if i create a new user i can drop it, but cant drop the current users
>> or tbspaces.
>>
>> anyone can help me with this problem plz

> 
> try select count(1) from dual;
>  may be you have a null value

There is no difference between COUNT(*) and COUNT(1) or COUNT('I like chocolate'). And none of this has anything to do with dropping a user in a database with a corrupt system table.

The query that might answer some of the problem is:

SELECT name, COUNT(*)
FROM user$
GROUP BY name
HAVING COUNT(*) > 1;

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Nov 21 2007 - 08:22:05 CST