I am confused about the script referenced in the below documentation on removing orphaned keyset tables. How does the logic in the script differentiate a valid keyset table that is associated with a connected session from an “orphaned” keyset table?
Assuming its running as a privileged account, it appears to delete process info if it exists without checking if there is an associated oracle session. Is the assumption that we should disconnect all connections? I don’t see that statement in the instructions. Does the lock status somehow indicate something about the session being orphaned? Not sure how it could.
What happens if you delete a keyset table that is currently in use?
https://support.esri.com/en-us/knowledge-base/how-to-remove-orphaned-keyset-tables-in-oracle-0000098...
What happens if you delete a keyset table that is currently in use?
From the ESRI documentation verbatim:
Summary
ArcGIS keyset tables are used by the geodatabase for quickly traversing origin and destination relationship classes, when the input set exceeds 100 objects.
The keyset table is uniquely identified by the name KEYSET_<n>, where <n> is the ArcSDE session identifier (the session identifier is persisted in the SDE metadata table process_information).
A new keyset table is created for each session when the session either starts editing a workspace (to ensure the table exists prior to any database transactions starting), or when attempting to traverse between an origin or destination class for 100 or more objects.
The keyset table has an attribute for each supported geodatabase type and an additional attribute to identify each keyset collection. The keyset table is used in the SQL statement as a join table between the origin and destination classes.
In DB2, Informix, and SQL Server databases, the keyset table is created as a temporary table. In Oracle databases, when a user who has CREATE TABLE privileges causes a keyset table to be created, the keyset table is stored in the user's schema. If a user does not have CREATE TABLE privileges, a procedure is invoked to create the keyset table in the ArcSDE administrator's schema.
When the session terminates, the keyset table is dropped. If the session terminates abnormally and is not able to drop the keyset table, then the table is orphaned and can be manually dropped using SQL.
Procedure
Instructions provided describe how to removed orphaned keyset tables in Oracle. The following Oracle SQL procedure detects all keyset tables a session has select privileges on from the all_tables view. The procedure loops through each table and verifies if the session that owns the keyset table is valid or not by checking the sde.process_information table. If the session is not valid, the keyset table is dropped. If the session is not valid and entries exist in the sde.process_information table, those entries are deleted.
When the procedure completes, it reports the total number of keyset tables dropped.
The procedure should be executed by a session that has the privilege DROP ANY TABLE or by the SYS or SYSTEM user.
set SERVEROUTPUT ON
DECLARE
CURSOR all_keysets IS
SELECT owner, table_name
FROM all_tables
WHERE table_name LIKE 'KEYSET_%';
sess_id INTEGER;
valid_sess INTEGER;
lock_name VARCHAR2(30);
lock_handle VARCHAR2(128);
lock_status INTEGER;
cnt INTEGER DEFAULT 0;
BEGIN
FOR drop_keysets IN all_keysets LOOP
sess_id := TO_NUMBER(SUBSTR(drop_keysets.table_name, 8));
SELECT COUNT(*) INTO valid_sess FROM sde.process_information WHERE owner = drop_keysets.owner AND sde_id = sess_id;
IF valid_sess = 1 THEN
lock_name := 'SDE_Connection_ID#' || TO_CHAR (sess_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 = sess_id;
DELETE FROM sde.state_locks WHERE sde_id = sess_id;
DELETE FROM sde.table_locks WHERE sde_id = sess_id;
DELETE FROM sde.object_locks WHERE sde_id = sess_id;
DELETE FROM sde.layer_locks WHERE sde_id = sess_id;
dbms_output.put_line('Removed orphaned process_information entry ('||sess_id||')');
EXECUTE IMMEDIATE 'DROP TABLE '||drop_keysets.owner||'.'||drop_keysets.table_name;
cnt := cnt + 1;
END IF;
ELSE
EXECUTE IMMEDIATE 'DROP TABLE '||drop_keysets.owner||'.'||drop_keysets.table_name;
cnt := cnt + 1;
END IF;
END LOOP;
dbms_output.put_line('Dropped '||cnt||' keyset tables.');
END;
/
