set SERVEROUTPUT ON exec dbms_output.enable(100000); DECLARE CURSOR process_list IS SELECT sde_id, owner, nodename FROM sde.process_information; lock_name VARCHAR2(30); lock_handle VARCHAR2(128); lock_status INTEGER; cnt INTEGER DEFAULT 0; BEGIN FOR check_locks IN process_list LOOP lock_name := 'SDE_Connection_ID#' || TO_CHAR (check_locks.sde_id); DBMS_LOCK.ALLOCATE_UNIQUE (lock_name,lock_handle); lock_status := DBMS_LOCK.REQUEST (lock_handle,DBMS_LOCK.X_MODE,0,TRUE); IF lock_status = 0 THEN DELETE FROM sde.process_information WHERE sde_id = check_locks.sde_id; DELETE FROM sde.state_locks WHERE sde_id = check_locks.sde_id; DELETE FROM sde.table_locks WHERE sde_id = check_locks.sde_id; DELETE FROM sde.object_locks WHERE sde_id = check_locks.sde_id; DELETE FROM sde.layer_locks WHERE sde_id = check_locks.sde_id; cnt := cnt + 1; dbms_output.put_line('Removed entry ('||check_locks.sde_id||'): '||check_locks.owner||'/'||check_locks.nodename||''); END IF; END LOOP; /* Remove any orphaned lock entries... */ DELETE FROM sde.state_locks WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information); DELETE FROM sde.table_locks WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information); DELETE FROM sde.object_locks WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information); DELETE FROM sde.layer_locks WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information); COMMIT; dbms_output.put_line('Removed '||cnt||' entries.'); END; /
It should be limited to the memory of the machine
The technical article link is broken for me but I know what you're referring to. However, does the memory issue only apply to 3 tier connections only or does it include direct connects?