When you connect to an enterprise geodatabase from an ArcGIS client or through an ArcGIS Server web service, you interact with the datasets that you or other databases users have added to the geodatabase. To track that data and to implement geodatabase behavior, enterprise geodatabases use system tables.
The system tables and their contents should not be altered using anything other than ArcGIS software or SDK. However, you can view the contents of the system tables using SQL.
Core system tables
The system tables and their contents should not be altered using anything other than ArcGIS software or SDK. However, you can view the contents of the system tables using SQL.
When you query an Oracle database that contains an enterprise geodatabase, you'll see the following core system tables in schema of the sde user:
- BRANCH_TABLES_MODIFIED
- BRANCHES
- COLUMN_REGISTRY
- COMPRESS_LOG—Created the first time you compress the geodatabase.
- DBTUNE
- GDB_CONFLICTS
- GDB_ITEMRELATIONSHIPS
- GDB_ITEMRELATIONSHIPTYPES
- GDB_ITEMS
- GDB_ITEMTYPES
- GDB_LOCKS
- GDB_REPLICALOG
- GDB_TABLES_LAST_MODIFIED
- GEOMETRY_COLUMNS
- INSTANCES
- LAYER_LOCKS
- LAYERS
- LINEAGES_MODIFIED
- MULTIBRANCH_TABLES
- MVTABLES_MODIFIED
- OBJECT_LOCKS
- PROCESS_INFORMATION
- RASTER_COLUMNS
- SDE_ARCHIVES
- SDE_LOGFILE_POOL
- SDE_TABLES_MODIFIED
- SDE_XML_COLUMNS
- SDE_XML_INDEX_TAGS
- SERVER_CONFIG
- SPATIAL_REFERENCES
- STATE_LINEAGES
- STATE_LOCKS
- STATES
- ST_COORDINATE_SYSTEMS
- ST_GEOMETRY_COLUMNS
- ST_GEOMETRY_INDEX
- ST_SPATIAL_REFERENCES
- TABLE_LOCKS
- TABLE_REGISTRY
- TABLES_LAST_EDIT_TIME
- VERSION
- VERSIONS
*These branch versioning tables are not present in geodatabases stored in Oracle 11.x or 12.1.0.1 databases because branch versioning requires, at minimum, Oracle 12.1.0.2.
The following tables are present in the geodatabase but are no longer used. They may be removed in a future release.
- LOCATORS
- METADATA
- SDE_LAYER_STATS
- SDE_XML_INDEXES
Tables that implement enterprise geodatabase functionality
Information for some geodatabase functionality is stored in core system tables only. For example, information for the following functionality is stored in core system tables, and no additional tables are created in the database when you define or enable this functionality on user data:
- Attribute rules—Stored in the GDB_ITEMS system table.
- Branch versions—Six fields are added to the table or feature class business table when it is registered to participate in branch versioning to track edits.
- Domains—Stored in the GDB_ITEMS system table. A field in the GDB_ITEMTYPES system table identifies the object as a domain.
- Relationship classes—Stored in the GDB_ITEMS and GDB_ITEMRELATIONSHIPS system tables.
The geodatabase functionality described in the following sections, however, creates additional internal tables when you enable or make use of the functionality.
Geodatabase archives
You can track transaction time history for your data using geodatabase archiving. Transaction time represents the moment in time when the feature was added to, deleted from, or updated in the database.
When you enable geodatabase archiving on a table or feature class, an archive class is created. An archive class is a copy of the business table and contains all the same fields plus three new fields: GDB_FROM_DATE, GDB_TO_DATE, and GDB_ARCHIVE_OID. When you enable archiving on a table or feature class that participates in a traditional version, a record is also added to the SDE_ARCHIVES system table. This record stores the registration IDs of the table that was enabled for archiving and its associated archive class table.
The name of the archive class table is the same as the original business table name with an underscore and H appended to it. For example, if a feature class named buildings is enabled for archiving, an archive class, buildings_H, is created. This archive class table is stored in the same schema as the business table.
When you trim unneeded archive records from archive classes that are not registered as versioned, that transaction is recorded in the METADATA system table.
Traditional versions
When you register a feature class or table to participate in traditional versions, two tables are created to track edits to the data: the adds table and the deletes table. Collectively, these are referred to as delta tables.
The adds table (A_<registration_id>) maintains information about each inserted or updated record (feature) in a versioned business table and is queried to identify which records have been added or modified at a particular geodatabase state.
The deletes table (D_<registration_id>) maintains information about the rows that were deleted or updated in a versioned table and is queried to identify which rows have been deleted or modified at a particular state. When a row is deleted, the record is not physically removed; it's flagged as deleted and never returned in subsequent database queries.
The registration_id in the adds table and deletes table names is the value for the versioned table in the TABLE_REGISTRY system table.
These tables are created in the same user schema as the table or feature class that is registered as versioned.
In addition to the delta tables, the core system tables that track versioned tables and edits are the STATES, STATE_LINEAGES, MVTABLES_MODIFIED, and VERSIONS tables.
Keyset tables
Keyset tables are used by ArcGIS clients to improve query performance. Keyset tables store a list of selected rows when an ArcGIS client executes a geodatabase relationship query that joins tables using attributes that are type integer, number, date, or string. They accommodate joins using attributes other than the Object ID field.
No keyset tables are present in the geodatabase until you perform one of the following operations:
- Select more than 99 records from a feature class in a map in ArcGIS Pro or ArcMap, and the feature class is involved in a relationship class.
- In ArcGIS Pro or ArcMap, open the attribute table of a feature class that is involved in a relationship class and retrieve the related table.
One keyset table is created as a global temporary table per connection per session. Because it is a temporary table, the keyset table is deleted when the user disconnects from the geodatabase.
Keyset table names are formatted as follows:
<user_schema>.keyset_<process_id>, where <schema_name> is the schema of the user who caused the table to be created and <process_id> is the process identification number of the user's session that caused the table's creation.
Log file tables
Log file tables are used by ArcGIS clients to improve query performance by storing lists of selected rows. Log file tables use joins based on Object ID attributes.
Geodatabases in Oracle use global temporary tables in the user's schema. This type of log file configuration creates two tables—SDE_LOGFILES and SDE_LOGFILE_DATA—in the schema of the user who performs an action that requires log file tables. Once created, these tables remain in the geodatabase; however, all log file entries are deleted when the connecting application deletes all of its log files.