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?

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

2 Answers 2

Reset to default
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
1
  • 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
    Commented Mar 27, 2018 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
1
  • The user exists in both the tables all_users and dba_users.
    – user147814
    Commented Mar 27, 2018 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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