How to get user of a schema with sql/plsql

Question

I am login with a user abc in a database xyz with Oracle SQL Developer. How can I get the usernames of the schema through which I am logged in?

There is no such thing as "the usernames of a schema" in Oracle. A schema and a username are essentially the same thing. — , Oct 22 '15 at 08:30

score 0 · Accepted Answer · edited May 23 '17 at 12:23

I believe by usernames you mean operating system usernames. Username in Oracle database (and SQL Developer), is a synonym to the schema name.

So in your case, your schema called abc. Now assume your operating system username is 'John', and you want to know other users who are connected to the schema 'abc', then you can run the query:

SELECT osuser 
FROM   v$session 
WHERE  schemaname = 'abc';

Refer to this post for more details

score 0 · Answer 2 · answered Oct 21 '15 at 07:19

0

If you just want to know the list of users in the schema you are in, try this:

 SELECT * FROM all_users

answered Oct 21 '15 at 07:19

brenners1302

1,440
10
18

This query will return the schema names/usernames of the entire database – Hawk Oct 21 '15 at 07:34

Grzegorz Kazior · Answer 3 · 2015-10-22T08:42:10.390

If you do not change current schema the following code will be fine:

-- In PLSQL
DECLARE
  vv_SchemaName VARCHAR2(100);
BEGIN
  vv_SchemaName := Sys_Context('USERENV','CURRENT_SCHEMA');
  dbms_output.put_line(vv_SchemaName);
END;  

-- IN SQL
SELECT Sys_Context('USERENV','CURRENT_SCHEMA') FROM DUAL

Current_schema is a bit different from LOGGED USER see the example:

-- The output will be: 
-- Current schema:LOGGED_SCHEMA session user:LOGGED_SCHEMA
-- Current schema:CHANGED_SCHEMA session user:LOGGED_SCHEMA
-- When you are connected to LOGGED_SCHEMA and have CHANGED_SCHEMA.
DECLARE
  vv_SchemaName  VARCHAR2(100);
  vv_SessionUser VARCHAR2(100);
BEGIN
  vv_SchemaName  := Sys_Context('USERENV','CURRENT_SCHEMA');
  vv_SessionUser := Sys_Context('USERENV','SESSION_USER'  ); 
  dbms_output.put_line('Current schema:' || vv_SchemaName || ' session user:' || vv_SessionUser);
  EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=CHANGED_SCHEMA';
  vv_SchemaName  := Sys_Context('USERENV','CURRENT_SCHEMA');
  vv_SessionUser := Sys_Context('USERENV','SESSION_USER'  ); 
  dbms_output.put_line('Current schema:' || vv_SchemaName || ' session user:' || vv_SessionUser);  
END;

So If you plan to connect to one user and work on another one than depending on your needs using Sys_Context('USERENV','SESSION_USER' ) may be a better option.

How to get user of a schema with sql/plsql

3 Answers3