How to find DB links connecting to a shema?

sweetritz

Hi DBAs,

I have changed the password of a schema VENUS in production database (Oracle 11.2.0.4).

As the password was stored in documents so referring that I have changes it with same password as old as it was going to expire.

instead of changing like this alter user VENUS identified by values 'HJ245345G1B41341';

I changed like this alter user VENUS identified by 'venusc0c0';

Now the issue is there are various other schemas in many remote DBs which are connecting to this schema using DB links. and in DB links they are connecting to this schema using its values

like connec to schema using values 'HJ245345G1B41341'.

Now I guess the only way to sort it out is to change all the DB links with new password values.

I need help in finding out all the schemas which are using DB links to connect to the schema VENUS?

Unknown

AUDIT unsuccessful logins

Unknown

AUDIT unsuccessful logins

sweetritz

sol.beach

Thsnks for reply.

But I'm a newbie DBA and lts still learning phase for me.

Is the below command correct? or do i need to modify? I want to audit for last 1 week

select * from dba_audit_trail where username='VENUS' and returncode in (1017) order by timestamp desc;

JimmyOTNC

select *

from dba_db_links

to find the dbLinks using the user.

Unknown

user2300390 wrote:

select *
from dba_db_links
to find the dbLinks using the user.

but how do you find the database to run the SQL against?

Mark D Powell

Sweetritz, why was a username used in database links set to expire to begin with?  You are on 11.2.  If the username had been created long ago there is a good chance the username password was still case insensitive.  When you altered it you may have made it case sensitive.  This is something you may want to check on.  If any of the remote connections are from pre-11g instances you may need to use an uppercase password.

- -

I think SOL has provided the best suggestion.  Audit the user for failed logons.

- -

If you know and have access to the remote databases you can potentially update the database links yourself.  If you do not have the necessary privileges you can contact the remote DBA and provide them the correct password.

- -

The following may also be of interest

How do I find distributed queries / transactions (either issued from or connecting to this instance)?

   http://www.jlcomp.demon.co.uk/faq/find_dist.html

- -

HTH -- Mark D Powell --

BPeaslandDBA

I always document databases that are connected to other databases. With this documentation, it is easy to see which systems would be connecting via a db link. From there, you should be able to query dba_db_links to determine which links are connecting to this database as this user.

Cheers,
Brian

Mark D Powell

Brian has raised a good point.  Creating and maintaining some simple documentation can be a wonderful thing especially down the road when you no longer remember what you did or in this case, discover.

- -

IMHO -- Mark D Powell --