CODEWITHSUNDEEP
oracle11g
Sid133
Sid133

Reputation: 364

Checking presence of users in a database

Am running a select query to check the availability of a user in a particular database

SELECT * FROM dba_users WHERE username='<username>'

Now the concern is that i have multiple databases to be checked where this query has to be executed.

Is there a way to modify the query in such a way that it can be executed on all the databases at the same time ie, check the user's availability in all databases by using a single query ?

Upvotes: 0

Views: 58

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

A database link might help.

Choose one of the database you'd want to run that query on, and create database links to all other databases. Then run a query on UNION of all those DBA_USERS (I used ALL_USERS as I have access to it).

For example:

SQL> connect scott/tiger@db11
Connected.
SQL> create database link dbl_db20
  2  connect to user_name_here
  3  identified by its_password_here
  4  using 'xx.yy.zz.ww:1521/db20';

Database link created.

SQL> select *
  2  from (select 'DB11' what, username from all_users@db11
  3        union all
  4        select 'DB20' what, username from all_users@dbl_db20
  5       )
  6  where username = 'SCOTT';

WHAT USERNAME
---- ------------------------------
DB11 SCOTT

SQL>

Upvotes: 1

Related Questions