When you connect to an enterprise geodatabase from an ArcGIS client or through an ArcGIS for 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
Core geodatabase system tables enforce geodatabase behavior, store information about the geodatabase, and keep track of the user data stored in the geodatabase.
When you query an IBM Informix database that contains an enterprise geodatabase, you'll see the following core system tables, owned by the sde user:
- column_registry
- compress_log—Created the first time you compress the geodatabase.
- dbtune
- gdb_itemrelationships
- gdb_itemrelationshiptypes
- gdb_items
- gdb_itemtypes
- gdb_replicalog
- gdb_tables_last_modified
- GEOMETRY_COLUMNS
- layer_locks
- layers
- lineages_modified
- mvtables_modified
- object_locks
- process_information
- raster_columns
- sde_archives
- sde_sde_logfile_pool
- sde_xml_columns
- sde_xml_index_tags
- sde_xml_index
- server_config
- spatial_references
- state_lineages
- state_locks
- states
- table_locks
- table_registry
- tables_modified
- version
- versions
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
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:
- Domains—Stored in the gdb_items system table. A field in the gdb_itemtypes system table identifies the object as a domain.
- Geodatabase replicas—Tracked in the database in the gdb_items, gdb_itemrelationships, gdb_itemtypes, and gdb_replicalog system tables.
- 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, 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 owned by the same user as the business table.
Versions
When you register a feature class or table to participate in versioning, 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 owned by the same user who owns 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 ArcMap, and the feature class is involved in a relationship class.
- In ArcMap, open the attribute table of a feature class that is involved in a relationship class and retrieve the related table.
- Begin an edit session in ArcMap.
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:
<owner>.keyset_<process_id>, where <owner> is the name of the user who caused the creation of the keyset table, and <process_id> is the process identification number of the session that caused the creation of the keyset table.
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 Informix use shared log file tables by default. This type of log file configuration creates two tables—sde_sde_logfiles and <user_name>_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.
The following list indicates the log file tables you'd see in the database if you alter the type of log file tables used by the geodatabase:
- Session-based log file tables—sde_sde_logfiles, <user_name>_sde_logfile_data, and <user_name>_sde_session<sde_id>. These tables are created in the schema of the user whose session caused the tables to be created. Once created, sde_sde_logfiles and <user_name>_sde_logfile_data remain in the database. <user_name>_sde_logfile_data is never populated when you use session-based log file tables. The sde_sde_logfiles table is truncated when the user's session ends. The <user_name>_sde_session<sde_id> table is truncated when the connecting application no longer needs the log file records, and the table is dropped when the session disconnects.
- Pools of log file tables—This log file implementation uses the core system sde_sde_logfile_pool table and creates sde_sde_logpool_<table_Id> tables to store selections based on Object IDs. The geodatabase administrator defines the number of sde_sde_logpool_<table_Id> tables to create when configuring the geodatabase to use this type of log file table implementation. All these tables are owned by the sde user.