KEYSET tables are one of the most “hidden” tables, that ArcSDE Geodatabase Administrator will not notice on frequent basis. The reason behind that, is that these tables can only be viewed within the database management system (not from ArcCatalog or ArcGIS platform).

Question: How does KEYSET tables being created in the geodatabase ( in Oracle RDBMS) ?

KEYSET tables are being created in Oracle database whenever a selection is being made by the ArcGIS Desktop. OR, by starting an editing session.

*.* If the schema user who is editing Lacks “create table” privilege, these KEYSET tables will be created under the ‘SDE’ schema. Otherwise, the KEYSET tables will be created under the schema owner editing session.

This is the KEYSET table definition in Oracle:

<user_schema>.keyset_<process_id>

where process_id is the associated with current “live” ArcSDE process in the “process_information” table under “SDE” schema.

 

Question: I see orphan KEYSET tables in my database, how should i deal with it ?

First, use the following SQL queries to gather information about the (number of KEYSET tables), and the owner of the KEYSET tables.

select count(*) from all_tables where table_name like ‘KEYSET_%’;

select distinct(owner) from all_tables where table_name like ‘KEYSET_%’;

These orphan tables in your Oracle Geodatabase is because of the abnormal termination of the editor’s session.

To clean it up, use the following PL/SQL Code:

//=========================================================================//

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

//=========================================================================//

save the PL/SQL script for example as “keyset_cleanup.sql” and then run it using sqlplus

SQL> @keyset_cleanup.sql

*.* the script will check the process_id information in the table name, if it has an assoicate “active” ArcSDE connection or not. if it does not have active connection, it will drop it.

Cleaning Up Geodatabase KEYSET tables could improve your ArcSDE Geodatabase performance, and these junk tables would be removed.

Remark: this script worked fine with ArcGIS 10.1 Geodatabase version.

References:

http://webhelp.esri.com/arcgisserver/9.3/dotnet/index.htm#geodatabases/keyset_t235544603.htm

http://www.sspinnovations.com/blog/2013/11/09/cleaning-gunk-out-your-oracle-gdb

http://support.esri.com/cn/knowledgebase/techarticles/detail/34258