Why is it impossible to drop user despite it exists in dba_users view

Question

I try to drop/alter user like this

drop user AVASILIEV_AXIOM_10X

And I get the following error

SQL> drop user AVASILIEV_AXIOM_10X;
drop user AVASILIEV_AXIOM_10X
          *
ERROR at line 1:
ORA-01918: user 'AVASILIEV_AXIOM_10X' does not exist

But this user exists in the dba_users view:

SQL> select username from dba_users where username='AVASILIEV_AXIOM_10X';

USERNAME
------------------------------
AVASILIEV_AXIOM_10X

SQL>

Also, I can't create user with the same name because it already exists in the DB. What's the problem?

My Oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production "CORE 11.2.0.4.0 Production" TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production

Can you see AVASILIEV_AXIOM_10X in the ALL_USERS view? If not, then drop user will not be able to find the user for you to drop. — Shannon Severance
@ShannonSeverance Any user can see any other user in ALL_USERS, with no privileges. — Jon Heller
@JonHeller That's not how I read the docs, but don't have an Oracle instance handy to test. docs.oracle.com/cd/B19306_01/server.102/b14237/… — Shannon Severance
@ShannonSeverance I tested it on 12c and just granting create session was enough for a user to see all other users. I'm not sure when a user would not be "visible" (as the manual says) to another user. — Jon Heller
@ShannonSeverance Yes, I can see this user in ALL_USERS view. What is more I can't drop it even being as SYSTEM user. I dropped it as SYS user with SYSDBA privileges! It's really strange... — BIB

Omkar Omkar · Accepted Answer · 2017-11-13T09:12:17

This is old thread but for reference Oracle Doc ID 1297361.1 suggests as follows:

SQL> drop user ahong3;

drop user ahong3 * ERROR at line 1:

ORA-01918: user 'AHONG3' does not exist

SQL> select username from dba_users where username='ahong3';

USERNAME
------------------------------
ahong3

SQL> execute hcheck.full
HCheck Version 8i-11/1.95

Found 0 potential problems and 0 warnings

PL/SQL procedure successfully completed.

CAUSE User was created forcibly in lower case. Drop user considers the user to be in upper case, and hence can't find it.

Ex: below statement will force the creation of the user in lower case.

SQL> Create user "ahong3";

SOLUTION Force the drop user statement to drop the user created in lower case by specifying the user name in double quotes:

SQL> Drop user "ahong3";

This solution worked for me

Why is it impossible to drop user despite it exists in dba_users view

2 Answers