How To: Create the ArcSDE log file tables as global temporary tables in Oracle
ArcSDE 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 orphaned. For example, if the client application shuts down abnormally, rows in these tables will become orphaned, requiring the database administrator (DBA) to manually remove the data.
To avoid manual clean up of these tables, the DBA or SDE 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.
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 the permanent log file tables For each DBMS schema with permanent log file tables, drop the SDE_LOGFILES and SDE_LOGFILE_DATA tables.
Code: 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.
Creating global temporary tables For each DBMS schema the SDE_LOGFILES and SDE_LOGFILE_DATA tables and the associated indexes must be created.
Code: 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;
Code: 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);
Code: 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;
Code: 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);
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 10g default value.
Only applies to new DBMS users who did not already have permanent SDE log files
Create the SDE_LOGFILE_LID_GEN sequence.
Code: 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.