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?
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;
/