Remove orphaned keyset tables in Oracle

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