Log file tables configuration for the BIS workspace in Oracle (Bathymetry Solution)

In ArcGIS, by default, log file tables are used if the selection set contains 100 or more records. ArcMap, or any application that runs on top of ArcMap, such as ArcGIS for Maritime:Bathymetry, which performs selection sets, uses these log file tables. ArcSDE keeps track of large selections using the log file tables instead of the records being held in memory. The same applies when sorting, selecting, or updating records in the BIS tables.

When working with results in the Bathymetry Information System (BIS) workspace, it is best to understand the log file table configuration options so that you can choose the right option for your database needs. There are four different ArcSDE log file options: shared, session-based, stand-alone, and pools of session-based or stand-alone log files.

The default ArcSDE log file table configuration for Oracle is shared ArcSDE log files.

在大多数情况下，数据库管理系统 (DBMS) 的默认 ArcSDE 日志文件表配置应足以满足需要，并且使用推荐的设置。

It is recommended that you store the log file tables in a separate tablespace. This can be achieved by setting specific parameters in the SERVER_CONFIG and DBTUNE tables.

Creating log file tablespaces

Use the following statement to create log file tablespaces in Oracle SQL Plus or Oracle SQL Developer.

CREATE SMALLFILE TABLESPACE sdelogfile
DATAFILE 'D:\oracle\ORADATA\BISDB\SDE\sdelogfile01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 100M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K 
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';

CREATE SMALLFILE TABLESPACE sdelogfileidx
DATAFILE 'D:\oracle\ORADATA\BISDB\SDE\sdelogfileidx01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 100M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K 
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';

Changing DBTUNE log file parameters

After creating the data files, modify the DBTUNE table to include a new configuration keyword. Based on this new keyword, the data and database objects will be stored in the tablespaces location that was previously defined. The DBTUNE table is stored under the sde user in Oracle. To modify the DBTUNE table, it's necessary to export the DBTUNE table into a text file and make modifications in the text file. After the updates to the text file are made, import the new DBTUNE table from the text file.

If you only use direct connections at your site, you may not have installed the ArcSDE server. You need to install at least the ArcSDE command line tools, if you haven't already, to get the sdedbtune and sdeconfig tools.

步骤:

1. Export the DBTUNE table through a command line.

   sdedbtune -o export -f dbtune_logfile.sde -u sde -p sde -i sde:oracle11g:bisdb
   

2. Modify the dbtune_logfile.sde ##LOGFILE_DEFAULTS configuration keyword in a text editor.

   ##LOGFILE_DEFAULTS
   LD_INDEX_DATA_ID	"PCTFREE 0 INITRANS 4 TABLESPACE SDELOGFILEIDX NOLOGGING "
   LF_INDEXES	"PCTFREE 0 INITRANS 4 TABLESPACE SDELOGFILEIDX NOLOGGING "
   LF_STORAGE	"PCTFREE 0 INITRANS 4 TABLESPACE SDELOGFILE"
   SESSION_INDEX	"PCTFREE 0 INITRANS 4 TABLESPACE SDELOGFILEIDX NOLOGGING "
   SESSION_TEMP_TABLE	0
   SESSION_STORAGE	"PCTFREE 0 INITRANS 4 TABLESPACE SDELOGFILE"
   LD_STORAGE	"PCTFREE 0 INITRANS 4 TABLESPACE SDELOGFILE "
   LD_INDEX_ROWID	"PCTFREE 0 INITRANS 4 TABLE PACE SDELOGFILEIDX NOLOGGING "
   END
   

3. Import the modified dbtune_logfile.sde file through a command line.

   sdedbtune -o import -f dbtune_logfile.sde -u sde -p sde -i sde:oracle11g:bisdb
   

Creating log file tables

The sde log file tables can be created in ArcMap by performing a large selection.

步骤:

1. Grant QUOTA on SDELOGFILE and SDELOGFILEIDX permissions in OEM to the ArcSDE editor/viewer user.

   ALTER USER <user_name> QUOTA UNLIMITED ON "SDELOGFILE";
   ALTER USER <user_name> QUOTA UNLIMITED ON "SDELOGFILEIDX";
   

2. Grant CREATE TABLE permission for the ArcSDE editor/viewer user.
3. Start ArcMap.
4. Select more than 100 features.

   This automatically creates the log file tables.

5. Right-click the feature class in the Table Of Contents window, and click Remove to remove the feature class from the map.
6. Remove CREATE TABLE permissions as appropriate.

Your BIS workspace is now ready to store data and products and be accessed by other users.