nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Delete KEYSET tables

yockee

Hi,

I need advice on this as well. I find that the SDE schema contains 7700+ KEYSET tables. Is this too many ? Is that safe to delete them ? How to delete them safely without having any adverse effect after deletion because they reside in the production ?

My environment : ArcSDE 10.5.0, ArcMap 10.5, Oracle 11G

Thanks

Encuentra más publicaciones etiquetadas con

Accepted answers

RiccardoKlinger

We have similar issues and will work with this template article:

How To: Remove orphaned keyset tables in Oracle (esri.com)

All comments

RiccardoKlinger

We have similar issues and will work with this template article:

How To: Remove orphaned keyset tables in Oracle (esri.com)

yockee

Hi,

The KEYSET tables grow so large, around 33000 tables (they never been deleted since the geodb was created). Some questions :

1. What are the processes that trigger it ? One thing for sure is during the join for >100 records. Does JOIN process from web apps trigger this too ?

2. Do I need to reclaim the tablespace after they have been dropped ? But the total amount of their size are 0. Here is the query I use to see their total size :

SELECT segment_name,segment_type, sum(bytes/1024/1024/1024) GB
FROM dba_segments WHERE owner = 'SDE' AND segment_name LIKE 'KEYSET_%'
group by segment_name,segment_type;

Thanks

@RiccardoKlinger

AlisonWieckowicz

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-000009802

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;

JohnAnderson1

@AlisonWieckowicz I suspect the line of the script that checks for an existing connected session is:

lock_status := DBMS_LOCK.REQUEST (lock_handle,DBMS_LOCK.X_MODE,0,TRUE);

The value 0 is returned if the DBMS_LOCK.REQUEST function can obtain a lock, but if an existing Oracle session already exists, a different value will be returned.

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.