Unable to remove a user in Oracle 11g

Question

I have a streams replication related user in Oracle 11g. I see the user in dba_users table and was anle to login using the user but when I try to drop the user i get the error.

SQL> drop user <username> cascade; 
Error at line 1: 
ORA-1403: no data found. 

Loos like data got corrupted in internal tables.

Any idea how to fix this mess and remove the user?

• Jack Douglas: drop user cascade; isn't the correct Oracle syntax: dbfiddle.uk/…
• user147814: I ran drop user <username> cascade. My original comment was editied. Ex: drop user rep cascade.
• McNets: Your original comment has been formatted, anything has changed: dba.stackexchange.com/revisions/202414/1 You can redo the review if you're not satisfied.
• Jack Douglas: my point is that if it's been edited how do we know the error isn't edited too? It's helpful to see the exact command and output that ran, or at least make redactions clear. You wouldn't normally get an ORA-1403 at a SQL*Plus prompt
• SQLDevDBA: does the user you're logged into have the DROP USER permission?

Answer 1

The ORA-01403 seems to indicate that you have a System Event Trigger for DROP USER.

You'll need to hunt down the appropriate trigger.

SELECT *
from dba_triggers
where base_object_type like 'DATABASE%'
and triggering_event like 'DROP%'
and status='ENABLED'
;

If that doesn't work, then I recommend that you open a ticket with Oracle Support.

• user147814: Can you please explain more in detail as to what to do and how to fix the error. I see a trigger for the above query in dba_ triggers table.

Answer 2

Check te user exist:

select * from all_users

or

select * from dba_users;

and see if exist or no.

• user147814: The user exists in both the tables all_users and dba_users.