CODEWITHSUNDEEP
sqldatabaseoracle10g
Jacob
Jacob

Reputation: 14731

Get schema(user) name from database

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

Upvotes: 1

Views: 6584

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 220762

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);

Upvotes: 2

Vincenzo Maggio
Vincenzo Maggio

Reputation: 3869

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!

Upvotes: 0

Related Questions