Forum Stats

  • 3,780,538 Users
  • 2,254,408 Discussions
  • 7,879,374 Comments

Discussions

Categories

How to find DB links connecting to a shema?

sweetritz
sweetritz Member Posts: 183
edited Nov 25, 2014 1:53PM in General Database Discussions

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?

Tagged:
· Share on TwitterShare on Facebook

Best Answer

Answers

  • Unknown
    Unknown
    Accepted Answer

    AUDIT unsuccessful logins

    · Share on TwitterShare on Facebook
  • sweetritz
    sweetritz Member Posts: 183
    edited Nov 25, 2014 12:18PM

    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;

    · Share on TwitterShare on Facebook
  • JimmyOTNC
    JimmyOTNC Member Posts: 162 Bronze Badge

    select *

    from dba_db_links

    to find the dbLinks using the user.

    · Share on TwitterShare on Facebook
  • Unknown
    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?

    · Share on TwitterShare on Facebook
  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond

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

    · Share on TwitterShare on Facebook
  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    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

    · Share on TwitterShare on Facebook
  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond

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

    · Share on TwitterShare on Facebook
This discussion has been closed.