How to know the names of other users in oracle10g?

Question

Hi guys

I want to know how can we know the other users of Oracle 10g on the same system? Is there any query for that?

Thank you!!!

Answer 1

select username
from all_users
/

This view doesn't have much information. If you need more you will need privileges on its DBA_USERS equivalent. As its name suggests, that view requires a DBA level of access.

Answer 2

Along with the all_users table provided by APC, you can also query dba_users which contains more data.

SQL> select username, account_status, default_tablespace from dba_users;

SQL> desc dba_users;
     Name                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     USERNAME                                  NOT NULL VARCHAR2(30)
     USER_ID                                   NOT NULL NUMBER
     PASSWORD                                           VARCHAR2(30)
     ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
     LOCK_DATE                                          DATE
     EXPIRY_DATE                                        DATE
     DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
     TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
     CREATED                                   NOT NULL DATE
     PROFILE                                   NOT NULL VARCHAR2(30)
     INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
     EXTERNAL_NAME                                      VARCHAR2(4000)
     PASSWORD_VERSIONS                                  VARCHAR2(8)
     EDITIONS_ENABLED                                   VARCHAR2(1)

For finding information on only the currently connected users you can use a join of dba_tables and v$session.

SQL> SELECT a.username, s.sid, s.program 
     FROM dba_users a JOIN v$session s ON (a.username = s.username) 
     ORDER BY a.username;

Answer 3

APC's answer is right. Should you need other users related needs in Oracle:

Oracle User Schema Owner