79

How to find out the users list, which is all created in the oracle 11g database. Is there any command to find out the users list which we can execute from the Command line interface!

Arsen Khachaturyan
  • 7,904
  • 4
  • 42
  • 42
Arun Blr
  • 799
  • 1
  • 5
  • 4
  • 1
    Possible duplicate of http://stackoverflow.com/questions/4833459/oracle-sql-query-for-listing-all-schemas-in-a-db – Dba Mar 17 '14 at 10:00
  • Possible duplicate of [Oracle SQL Query for listing all Schemas in a DB](http://stackoverflow.com/questions/4833459/oracle-sql-query-for-listing-all-schemas-in-a-db) – Simon D. Jan 20 '17 at 10:42

6 Answers6

104

I am not sure what you understand by "execute from the Command line interface", but you're probably looking after the following select statement:

select * from dba_users;

or

select username from dba_users;
Community
  • 1
  • 1
René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
35
select * from all_users

This will work for sure

Natali
  • 2,934
  • 4
  • 39
  • 53
Jones
  • 361
  • 3
  • 2
17

The command select username from all_users; requires less privileges

JVMATL
  • 2,064
  • 15
  • 25
Jean-François Perrot
  • 171
  • 1
  • 2
5

You can try the following: (This may be duplicate of the answers posted but I have added description)

Display all users that can be seen by the current user:

SELECT * FROM all_users;

Display all users in the Database:

SELECT * FROM dba_users;

Display the information of the current user:

SELECT * FROM user_users;

Lastly, this will display all users that can be seen by current users based on creation date:

SELECT * FROM all_users
ORDER BY created;
Gauravsa
  • 6,330
  • 2
  • 21
  • 30
3

You can think of a mysql database as a schema/user in Oracle. If you have the privileges, you can query the DBA_USERS view to see the list of schema.

Arsman Ahmad
  • 2,000
  • 1
  • 26
  • 34
2

I tried this query and it works for me.

SELECT username FROM dba_users 
ORDER BY username;

If you want to get the list of all that users which are created by end-user, then you can try this:

SELECT username FROM dba_users where Default_TableSpace not in ('SYSAUX', 'SYSTEM', 'USERS')
ORDER BY username;
Arsman Ahmad
  • 2,000
  • 1
  • 26
  • 34