Get schema(user) name from database

Question

How to find the schema(user) name from database using a select statement or are there any built in functions available?

I am using the following package for compiling all invalid objects in my schema, so instead of hard coding schema name, I would like to use a select statement or function which returns schema name.

DBMS_UTILITY.compile_schema('SCOTT');

Thanks

Answer 1

I guess, the USER system variable would correspond to what you need, if "my schema" corresponds to "my user", what it usually does:

declare
  u varchar2(100);
begin
  select user into u from dual;
  DBMS_UTILITY.compile_schema(u);
end;

Or without a PL/SQL block

DBMS_UTILITY.compile_schema(user);

Answer 2

Schemas in Oracle DB are users, so if you want to take all schemas you need to use the dba_users table:

SELECT USERNAME
FROM DBA_USERS;

Remember that this is a system table so appropriate user granting is necessary.

If you need the current user, you can user the USER_USERS view. See here for further information.

Hope this helps!