Keyset Tables - Oracle SDE

Question

Hello,

Looking at a current setup of our Oracle 11g SDE I've found that Keyset tables are unable to be generated under the users' schema as they do not have  Create Table  privileges.  I will need to now check for tables which may be generated under the SDE schema after reading the below.

"""

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.

"""

Should users be granted create table permission to generate these Keyset tables, and are they worth it? Also noting that Oracle retains the tables, which I would guess needs to be cleared-out regularly.

If anyone has experience of this and particularly Oracle SDE condifuration it would be enormously helpful.

yockee · Answer

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

WilliamCraft · Answer

What software version is your enterprise geodatabase and client application?  Keyset tables are supposed to get cleared out automatically by ArcGIS automatically but this does not always happen.  Purging them regularly has been a required practice in older versions of the software, although leaving those tables in the geodatabase isn't necessarily hazardous.  My methodology has actually been to script the cleanup of those tables, no matter which schema they reside in, on a nightly basis.  The script to validate that a particular keyset table doesn't belong to an active session, however.  As far as whether to grant users CREATE TABLE privileges so the keyset tables can be created in their own schema, that's entirely up to you.  I think there are valid pros and cons for doing this.  If your security requirements are very strict and users should not be able to create objects, then allow those tables to continue to be created in the SDE schema.  Otherwise, from an organization standpoint, it might be 'tidier' to keep objects in the proper user schema based on who creates or attempts to create them.