Create log file tables in an enterprise geodatabase as global temporary tables in Oracle

Summary

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:

• SDE_LOGFILES
• SDE_LOGFILE_DATA

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:

• Immediate removal of the session's rows when the session terminates
• Less redo log information being generated

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.

Procedure

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.

1. Drop the permanent log file tables
   For each DBMS schema with permanent log file tables, drop the SDE_LOGFILES and SDE_LOGFILE_DATA 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.

2. Creating global temporary tables
   For each DBMS schema, the SDE_LOGFILES and SDE_LOGFILE_DATA tables and the associated indexes must be created.

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);

3. Enable dynamic sampling
   Global temporary tables do not have statistics computed. Oracle's Cost Based Optimizer (CBO) uses statistics to determine the most efficient access path. Enable Oracle's ability to dynamically sample the statistics to ensure the CBO is choosing the best execution path. The minimum recommended value is 2, Oracle's 11g default value.
4. Only applies to new DBMS users who did not already have permanent SDE log files
   Create the SDE_LOGFILE_LID_GEN sequence.

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.