DB Link "Used By" Needs Additional Logic for Accuracy

For readability in code, we have started creating DB Links that use the following naming standard:
"{Account Name or Abbrev used by the link}@{Instance Connecting to}"
so we create a DB Link name like "MLA_NET@UKXXX069" and Oracle interestingly injects ".WORLD" just before the "@" in the name so we get "MLA_NET.WORLD@UKXXX069" stored in Oracle. As I am sure you know the ".WORLD" is not needed when using a DB Link, so our code does not include it. I was just looking to find our dependencies on a specific link and discovered the Used By tab for DB Links seems to assume the ".WORLD" is always at the end of the link.

As you can see here, I have 7 different DB Links that use the MLA_NET account, but connect to different Instances:
image

I found the Used By returned the same result set for all 7 DB Links, so I turned on Spool SQL to see how Toad populates Used By and attached is the Spool file.

Debug.SQL (2.9 KB)

When looking for Views or Materialized Views Toad was scanning for '@MLA_NET' and should be looking for '@MLA_NET.WORLD@UKXXX069' if someone included the '.WORLD' or '@MLA_NET@UKXXX069' if they excluded the '.WORLD' as both are valid in Oracle.

I assume very few people use a similar naming strategy for DB Links so this is probably impacting a very small number of users, it should probably be given a low priority.

If you have any questions. please let me know...

@JohnDorlon ,

A clean fix for this issue would be to pass the full DB Link name (including the .WORLD ) into the View and Materialized View search code (like you do for the sys.DBA_DEPENDENCIES and sys.DBA_SYNONYMS searches), then update the IF statement at the beginning of the loops from:

  for cs_var in cs loop
    if instr(upper(no_comments(cs_var.text)), :link) > 1 then ...

to (View Search code)

   FOR cs_var IN cs LOOP
      IF (INSTR( UPPER( no_comments( cs_var.text ) ), :link ) > 1
       OR INSTR( UPPER( no_comments( cs_var.text ) )
               , REPLACE( :link, '.WORLD', '') ) > 1)
      THEN ...

and (Materialized View Search code)

   FOR cs_var IN cs LOOP
      IF (INSTR( UPPER( no_comments( cs_var.query) ), :link ) > 1
       OR INSTR( UPPER( no_comments( cs_var.query) )
               , REPLACE( :link, '.WORLD', '') ) > 1)
      THEN ...

This code will scan the text for either the full DB Link name or the name without the '.WORLD' text.

Hi Michael,

Thanks. I saw this yesterday, just haven't had a chance to really look yet. I will soon.

-John

I agree that the change will work in your situation. However, I can create both .WORLD and a non-.WORLD link. I don't know why anyone would do that, but I need to make sure that I don't return the wrong result for that.

Do you have WORLD or .WORLD specified as db_domain?

SELECT value
from v$parameter
where name = 'db_domain'

I was surprised to see that query returns lowercase 'world', without the dot .
image

I'm willing to make the change when WORLD is there (upper or lower) but not when it is blank.

My value for DB_DOMAIN is blank. You may be able to change that and then re-create your db links. Might want to do that in test first though. I am not 100% sure of how else that may affect your database. I think WORLD was the default for older DB versions and blank is the default for newer. Maybe your DB has been upgraded from older to newer versions. Just a guess.

Edit: actually I am still trying to be able to reproduce Oracle's adding the .WORLD to the link. Still not able to after changing db_domain and restarting. hmmm

I checked a Schema created as Oracle 12.1.0.2 and one created as Oracle 19. Both had "world" as the db_domain. It appears to be an Oracle default GSK just kept and is not something I can modify with my accounts.

I can't find a setting that would force insertion of '.'{upper(db_domain)} in DB Links. Many posts point to global_name forcing it, but in my env that is set to false. This is much more complex than I initially thought. I created some standalone code that allows me to get DB_Link Utilization in the our Oracle environment. I think additional conditional logic is probably needed based on db_domain being populated. In pseudo code it would look like this:

    IF (INSTR( UPPER( no_comments( cs_var.query) ), :link ) > 1
       OR( db_domain is not NULL 
           AND INSTR( UPPER( no_comments( cs_var.query) )
                 , REPLACE( :link,('.'|| UPPER(db_domain)), '') ) > 1)
      THEN ...

Sorry, when I initially looked at this I really did think it was going to be simple... Feel free to back burner it or ignore it as I have a workaround coded.

Update:
db_domain is not actually what drives the value appended per this Blog Post

Looks like it is the suffix of GLOBAL_NAME which is really an entry in sys.props$ .

I think I have it.
In the declaration create a var to hold the domain

  v_domain    GLOBAL_NAME.GLOBAL_NAME%TYPE;

Start the code block with this:

BEGIN
   rslt_size := 0;

   --Save full domain including leading . or NULL if no domain present
   SELECT REGEXP_REPLACE( UPPER( GLOBAL_NAME )
                        , '([^\.]{0,})(.*)'
                        , '\2'
                         )
     INTO v_domain
     FROM GLOBAL_NAME;

Then use this logic in the loop:

   LOOP
      IF (INSTR( UPPER( no_comments( cs_var.text ) ), :link ) > 1
       OR (v_domain IS NOT NULL
       AND INSTR( UPPER( no_comments( cs_var.text ) )
                , REPLACE( :link
                         , v_domain
                         , ''
                          )
                 ) > 1))
      THEN...

In the MV search you would replace cs_var.text with cs_var.query .

That was a major pain to find a solution that will correctly work if the DB was created with or without a domain assigned. I think it is now time for a scotch! I could not full test the code for a DB without a domain, but did verify this worked as hoped:

SELECT REGEXP_REPLACE( 'MY.WORLD'
                     , '([^\.]{0,})(.*)'
                     , '\2'
                      )   TEST1                 --String with single-part domain
     , REGEXP_REPLACE( 'MY.WORLD.COM'
                     , '([^\.]{0,})(.*)'
                     , '\2'
                      )   TEST2                  --String with multi-part domain
     , REGEXP_REPLACE( 'NO_DOMAIN'
                     , '([^\.]{0,})(.*)'
                     , '\2'
                      )   TEST3                          --String with no domain
     , CASE
          WHEN REGEXP_REPLACE( 'NO_DOMAIN'
                             , '([^\.]{0,})(.*)'
                             , '\2'
                              )
                  IS NULL
          THEN
             '1'
          ELSE
             '0'
       END                AS TEST3_IS_NULL
  FROM DUAL;

which produces:
image

Thanks for the legwork. I'll take a look at this soon. I logged it so I don't forget.

I am looking at this today.

So, I assume that the global_name in your environment has .WORLD, is that right?

John,

Yes the Global Name on my Instances end with '.WORLD'

1 Like

I'll have something in there next beta. Let me know how it works for you.

1 Like

This seems to work perfectly for our environment now. Great work!!!!

1 Like