Need to monitor invalid objects from different DBLinks

Question

I have to make a monitor that detects invalid objects for different DBlinks.

I've tried with a code in different files for each DBLink

-- Enable logging of results to a specific file
SPOOL C:\route

SET SERVEROUTPUT ON

DECLARE
    v_count NUMBER := 0;
    -- Define a record type to store details of invalid objects
    TYPE obj_details_type IS RECORD (
        owner         VARCHAR2(100),
        object_name   VARCHAR2(100),
        status        VARCHAR2(100),
        object_type   VARCHAR2(100)
    );
    -- Define a table type to store the details of invalid objects
    TYPE obj_details_table IS TABLE OF obj_details_type;
    -- Declare a variable to store details of invalid objects
    obj_details_list obj_details_table := obj_details_table();
BEGIN
    -- Get details of invalid objects
    FOR obj IN (SELECT owner, object_name, status, object_type
                FROM dba_objects@DBLINK
                WHERE status = 'INVALID') LOOP
        -- Increase the counter
        v_count := v_count + 1;
        -- Store details in a collection
        obj_details_list.EXTEND;
        obj_details_list(obj_details_list.LAST) := obj_details_type(obj.owner, obj.object_name, obj.status, obj.object_type);
    END LOOP;

    -- Show the number of invalid objects
    DBMS_OUTPUT.PUT_LINE('No: of invalid objects: ' || v_count);

    -- Show results as a table if there are invalid objects
    IF v_count > 0 THEN
        -- Print column headers with lines and separations
        DBMS_OUTPUT.PUT_LINE('DBLINK: - DATE, TIME: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
        DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------');
        DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------');
        DBMS_OUTPUT.PUT_LINE('|' || RPAD('OWNER', 19) || '|' || RPAD('OBJECT NAME', 30) || '|' || RPAD('STATUS', 7) || ' |' || 'OBJECT TYPE' || ' |');
        DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------');
        
        -- Show details of invalid objects stored in the collection
        FOR i IN 1..obj_details_list.COUNT LOOP
            -- Imprimir cada fila en formato tabular con líneas y separaciones
            DBMS_OUTPUT.PUT_LINE('|' || RPAD(obj_details_list(i).owner, 19) || '|' || RPAD(obj_details_list(i).object_name, 30) || ' |' || RPAD(obj_details_list(i).status, 7) || ' |' || obj_details_list(i).object_type || ' |');
            DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------');
        END LOOP;
    ELSE
        DBMS_OUTPUT.PUT_LINE('No invalid objects were found.');
    END IF;

    -- Close the database link
    EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK DBLINK';
END;
/

SET SERVEROUTPUT OFF

-- Disable results recording
SPOOL OFF

the problem is that the log shows that, the max number of open-links is 4 that's why I added the EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK DBLINK in each file but the log shows:

ERROR in line 1:
ORA-02080: database link is in use
ORA-06512: in line 49


    -- Define a record type to store details of invalid objects
                                   *
ERROR in line 3:
ORA-04052: An error occurred while querying the remote object
DBLINK 
ORA-00604: An error occurred at recursive SQL level 1 
ORA-02020: too many database links in use

I was thinking if there was an easier way to make this to work by querying multiple DBLinks (I only have permissions to connect to database, data dictionary query and CREATE DATABASE LINK) I also want to know if there's a way of making the first code work whithout errors. Any ideas?

Paul W · Accepted Answer · 2024-04-11 22:15:03Z

Oracle creates a global transaction with every dblink connection, even for selects. As long as that transaction is uncommitted it won't let you close the link. So, simply add a COMMIT before attempting to close it.

I would also suggest using dynamic SQL (e.g. with EXECUTE IMMEDIATE or OPEN FOR and a ref cursor) so you can put your dblink name as a variable and not have to replace it as a literal in multiple locations. That also sets you up to loop through a cursor of dblinks within PL/SQL itself to test all your remote databases without having to do that work in client code.

Lastly, though it may not make a difference, use the supplied PL/SQL interface (dbms_session) for closing a link rather than a DDL. E.g.:

DECLARE
  var_my_link varchar2(128) := 'DBLINK';

  TYPE obj_details_type IS RECORD (
      owner         VARCHAR2(128),
      object_name   VARCHAR2(128),
      status        VARCHAR2(30),
      object_type   VARCHAR2(64)
  );
  -- Define a table type to store the details of invalid objects
  TYPE obj_details_table IS TABLE OF obj_details_type;
  -- Declare a variable to store details of invalid objects
  obj_details_list obj_details_table := obj_details_table();

BEGIN
  EXECUTE IMMEDIATE 'SELECT owner, object_name, status, object_type
                       FROM dba_objects@'||var_my_link||'
                      WHERE status = ''INVALID'''
               BULK COLLECT INTO obj_details_list;
  
  -- do stuff
  
  COMMIT; -- this is what will allow the link to close
  dbms_session.close_database_link(var_my_link); -- close the link
END;
/

Here's a version that loops through all your links:

DECLARE
  TYPE obj_details_type IS RECORD (
      owner         VARCHAR2(128),
      object_name   VARCHAR2(128),
      status        VARCHAR2(30),
      object_type   VARCHAR2(64)
  );
  -- Define a table type to store the details of invalid objects
  TYPE obj_details_table IS TABLE OF obj_details_type;
  -- Declare a variable to store details of invalid objects
  obj_details_list obj_details_table := obj_details_table();

BEGIN
  FOR rec_link IN (SELECT db_link
                     FROM user_db_links)
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'SELECT owner, object_name, status, object_type
                           FROM dba_objects@'||rec_link.db_link||'
                          WHERE status = ''INVALID'''
                   BULK COLLECT INTO obj_details_list;
      
      -- do stuff
      
      COMMIT; -- this is what will allow the link to close
      dbms_session.close_database_link(rec_link.db_link); -- close the link
    EXCEPTION
      WHEN OTHERS THEN -- do not let an error break the loop. keep going
        dbms_output.put_line(rec_link.db_link||' errored: '||SQLERRM);
    END;
  END LOOP;
END;
/

It worked perfectly, I had to adjust some parts, but it finally worked, thanks

Collectives™ on Stack Overflow

Need to monitor invalid objects from different DBLinks

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related