0

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?

  • 1
    drop user cascade; isn't the correct Oracle syntax: dbfiddle.uk/… – Jack Douglas Mar 27 '18 at 15:01
  • I ran drop user <username> cascade. My original comment was editied. Ex: drop user rep cascade. – user147814 Mar 27 '18 at 15:09
  • 1
    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. – McNets Mar 27 '18 at 15:18
  • 1
    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 – Jack Douglas Mar 27 '18 at 15:57
  • does the user you're logged into have the DROP USER permission? – MguerraTorres Mar 27 '18 at 16:18
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.

| improve this answer | |
  • 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. – user147814 Mar 27 '18 at 18:24
-2

Check te user exist:

select * from all_users

or

select * from dba_users;

and see if exist or no.

| improve this answer | |
  • The user exists in both the tables all_users and dba_users. – user147814 Mar 27 '18 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.