Note: Starting with ArcGIS 10.7.1, geodatabases in Oracle use global temporary tables owned by the user who caused the log file table to be created. Users who do not have database permissions to create tables will use global temporary log file tables owned by the SDE user.
The following workflow is used for earlier releases of ArcGIS.
Enterprise geodatabases use log file tables to maintain lists of selected objects. In Oracle, when using the shared log file architecture, two tables are created for each DBMS schema making selections. These tables are:
The sde_row_ids of the selected set are written to these tables for later use by the application. This allows for faster analysis and processing of information.
Occasionally, rows in these tables can become detached. For example, if the client application shuts down abnormally, rows in these tables will become detached, requiring the database administrator (DBA) to manually remove the data.
To avoid manual clean up of these tables, the DBA or Enterprise Geodatabase administrator may choose to re-create these tables as global temporary tables.
Global Temporary Tables
Global temporary tables are used in Oracle to preserve data that is only available for the duration of a session or a transaction. As soon as the session or transaction completes, the rows are deleted from the temporary tables based upon the configuration of the table (to either preserve or remove rows on commit).
Since log file information is only available for the duration of a session, using global temporary tables can offer the following advantages:
Additionally, these segments and their indexes are created in the temporary tablespace. Thus not impacting the backup and recovery process.
Global temporary tables do not support or maintain Oracle table statistics, therefore, it is very important to verify Oracle's ability to perform dynamic sampling is enabled for the instance.
Instructions provided describe how to re-create log file tables as global temporary tables for each database management system (DBMS) schema who currently possesses log file tables.
Note: Steps are also provided for creating global temporary tables for new users with no pre-existing permanent SDE log file tables.
DROP TABLE sde_logfiles CASCADE CONSTRAINTS; DROP TABLE sde_logfile_data CASCADE CONSTRAINTS;
New DBMS users only
Grant create table and create sequence permission to each new DBMS user with no pre-existing permanent log file tables.
CREATE GLOBAL TEMPORARY TABLE sde_logfiles ( logfile_name VARCHAR2(256) NOT NULL ENABLE, logfile_id NUMBER(*,0) NOT NULL ENABLE, logfile_data_id NUMBER(*,0) NOT NULL ENABLE, registration_id NUMBER(*,0) NOT NULL ENABLE, flags NUMBER(*,0) NOT NULL ENABLE, session_tag NUMBER(*,0) NOT NULL ENABLE, logfile_data_db VARCHAR2(32), logfile_data_owner VARCHAR2(32), logfile_data_table VARCHAR2(98), column_name NVARCHAR2(32) ) ON COMMIT PRESERVE ROWS;
CREATE UNIQUE INDEX sde_logfiles_pk ON sde_logfiles (logfile_id); CREATE UNIQUE INDEX sde_logfils_uk ON sde_logfiles(logfile_name); CREATE UNIQUE INDEX sde_logfiles_uk2 ON sde_logfiles (logfile_data_id);
CREATE GLOBAL TEMPORARY TABLE sde_logfile_data ( logfile_data_id NUMBER(*,0) NOT NULL ENABLE, sde_row_id NUMBER(*,0) NOT NULL ENABLE ) ON COMMIT PRESERVE ROWS;
CREATE INDEX sde_logfile_data_idx1 ON sde_logfile_data (logfile_data_id, sde_row_id); CREATE INDEX sde_logfile_data_idx2 ON sde_logfile_data (sde_row_id);
CREATE SEQUENCE SDE_LOGFILE_LID_GEN INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
Revoke create sequence from the user.
Create table privileges for read-only users may also be revoked.
Get help from ArcGIS experts
Download the Esri Support App