Oracle configuration parameters

Configuration parameters identify objects (such as tables, indexes, and columns) to be stored in the database. Their corresponding values identify how the object will be stored in the database. The parameters and their configuration strings are grouped by configuration keywords.

Keyword and parameter name combinations must be unique; you cannot have two different settings for the same parameter under the same keyword.

However, most parameters can be used under different configuration keywords. For example, you could set the GEOMETRY_STORAGE parameter to one value under DEFAULTS, and create a custom keyword that includes the GEOMETRY_STORAGE parameter set to a different value.

Legacy:

Configuration keywords and parameters are largely legacy functionality. They were implemented when databases required far more intervention and tinkering on the part of the database administrator to get them to perform well. For the most part, you don't need to alter the configuration parameters in the geodatabase.

When you create an enterprise geodatabase, it is populated with default configuration keywords and parameters. In the majority of cases, the default parameter values are sufficient. Possible exceptions include the following:

• You may need to change the spatial type used for features. To create the majority of feature classes using a spatial type different than the default value, change the GEOMETRY_STORAGE parameter for the DEFAULTS keyword. To allow users to create only a few feature classes that use a different spatial type, create a custom keyword and include the GEOMETRY_STORAGE parameter set to the spatial type required, and include the UI_TEXT parameter to make the custom keyword available to users.
• In most cases, you can set the UNICODE_STRING parameter for the DEFAULTS keyword to FALSE. New text fields will use the database character set (VARCHAR2) instead of the national character set (NVARCHAR2).

Additional parameters exist to control the following, but you are less likely to use these parameters. They remain for backward compatibility and, therefore, remain in this page to help you understand their intended use when they were added:

• Define the data type for raster and attribute columns.
• Establish the storage characteristics of tables and indexes. You need to understand how Oracle creates and stores tables and indexes to properly alter these settings. Therefore, consult the Oracle documentation for the version of Oracle you are using before you alter these settings.
• Provide comments that describe the configuration keyword.
• Define how XML documents are stored. Because ArcGIS does not use XML documents directly, it is unlikely you'll need to set these parameters.

An example of how parameters are used

In the following example, a SQL statement returns the values for all the parameters that begin with RAS and are grouped under the DEFAULTS keyword in the geodatabase DBTUNE system table:

SQL> SELECT * FROM SDE.DBTUNE
  2  WHERE KEYWORD = 'DEFAULTS' AND PARAMETER_NAME LIKE 'RAS%';

KEYWORD   PARAMETER_NAME   CONFIG_STRING
------------  ----------------------   -------------------------
DEFAULTS   RASTER_STORAGE   BLOB

DEFAULTS   RAS_INDEX_ID        PCTFREE 0 INITRANS 8 TABLESPACE IDX1 NOLOGGING

DEFAULTS   RAS_STORAGE        PCTFREE 0 INITRANS 8 TABLESPACE RASTER

The config_string for the RAS_STORAGE parameter includes Oracle SQL syntax for a CREATE TABLE statement. The RAS_STORAGE parameter is used to control the storage of SDE_RAS_<raster_column_ID> tables. Therefore, if you specify the DEFAULTS keyword when creating a raster dataset in the geodatabase, ArcGIS reads the config_string for RAS_STORAGE and places it in the SQL statement used to create the SDE_RAS_<raster_column_ID> table.

Configuration keywords and parameter values are used for the SQL statements that ArcGIS issues to create the table. The following DEFAULTS keyword/parameter value translates to the SQL statement that follows:

DEFAULTS  RAS_STORAGE  PCTFREE 0 INITRANS 8 TABLESPACE RASTER

CREATE TABLE myuser.sde_ras_6
(raster_id number(38),
 raster_flags number(38),
 description varchar2(65))
PCTFREE			0
INITRANS       8
TABLESPACE raster

If a tablespace is not specified, Oracle stores tables and indexes in the user's default tablespace using the tablespace's default storage parameters. In the previous example, if TABLESPACE raster was not included, rasters would be stored in the user's default tablespace.

You can determine a user's default tablespace by querying the DEFAULT_TABLESPACE field of the USER_USERS Oracle system table when connected as that user. As the Oracle database administrator, query the DEFAULT_TABLESPACE field of the DBA_USERS table using a WHERE clause to specify the user.

SQL> connect <user>/<password>
SQL> SELECT default_tablespace 
 FROM user_users;

or

SQL> connect system/<password>
SQL> SELECT default_tablespace 
 FROM dba_users 
 WHERE username = <'USER'>;

Obtain a list of default storage parameters for a tablespace by querying USER_TABLESPACES:

SQL> connect <user>/<password>
SQL> SELECT * FROM user_tablespaces 
WHERE tablespace_name = <'TABLESPACE'>;

You can supply the appropriate tablespace names for the data by altering configuration parameter settings. You can export current configuration parameter settings using the Export Geodatabase Configuration Keyword tool, alter the values, then import changes using the Import Geodatabase Configuration Keyword tool.

Valid parameter list

The following table is an alphabetic list of all the possible configuration parameters you can use in a geodatabase in Oracle. Where applicable, the first value listed is the default.

Following the table is a more in-depth explanation of the parameters grouped by their functionality.

Configuration parameter summary

Parameter name	Description	Values	Notes
A_INDEX_RASTER	Storage definition for the Adds table raster column index	See your Oracle documentation for CREATE INDEX parameters.
A_INDEX_ROWID	Storage definition for the Adds table Object ID column index	See your Oracle documentation for CREATE INDEX parameters.
A_INDEX_SHAPE	Storage definition for the Adds table spatial column index	See your Oracle documentation for CREATE INDEX parameters.
A_INDEX_STATEID	Storage definition for the Adds table sde_state_id column index	See your Oracle documentation for CREATE INDEX parameters.
A_INDEX_USER	Defines storage for the Adds table index	See your Oracle documentation for CREATE INDEX parameters.
A_INDEX_XML	Storage definition for the Adds table XML column index	See your Oracle documentation for CREATE INDEX parameters.
A_STORAGE	Defines the storage of the Adds table	See your Oracle documentation for CREATE TABLE parameters.
ATTRIBUTE_BINARY	Indicates storage type for binary attribute (nonspatial) fields	BLOB or LONGRAW	Legacy: Oracle deprecated LONG RAW. Use the default value (BLOB) for this parameter.
AUX_INDEX_COMPOSITE	Raster AUX table composite column index storage definition	See your Oracle documentation for CREATE INDEX parameters.
AUX_STORAGE	Raster AUX table storage definition	See your Oracle documentation for CREATE TABLE parameters.
B_INDEX_RASTER	Business table raster column index storage definition	See your Oracle documentation for CREATE INDEX parameters.
B_INDEX_ROWID	Business table object ID column index and raster rowid index R<N>_SDE_ROWID_UK storage definition	See your Oracle documentation for CREATE INDEX parameters.
B_INDEX_SHAPE	Business table spatial column index storage definition	See your Oracle documentation for CREATE INDEX parameters.	Legacy: Binary geometry storage only; no longer used.
B_INDEX_TO_DATE	Storage parameter info for creating the index R<registration_id>_sde_todate, which is used when updating the history table during an archive operation	See your Oracle documentation for CREATE INDEX parameters.
B_INDEX_USER	Business table user index storage definition	See your Oracle documentation for CREATE INDEX parameters.
B_INDEX_XML	Business table XML column index table storage definition	See your Oracle documentation for CREATE INDEX parameters.
B_STORAGE	Business table and raster attribute table storage definition	See your Oracle documentation for CREATE TABLE parameters.
BLK_INDEX_COMPOSITE	Raster BLK table composite column index storage definition	See your Oracle documentation for CREATE INDEX parameters.
BLK_STORAGE	Raster BLK table storage definition	See your Oracle documentation for CREATE TABLE parameters.
BND_INDEX_COMPOSITE	Raster BND table composite column index storage definition	See your Oracle documentation for CREATE INDEX parameters.
BND_INDEX_ID	Raster BND table RID column index storage definition	See your Oracle documentation for CREATE INDEX parameters.
BND_STORAGE	Raster BND table storage definition	See your Oracle documentation for CREATE TABLE parameters.
COMMENT	Line used for comments	Can place any comment up to 8,000 characters
COMPRESS_ROLLBACK_SEGMENT	Version compression rollback segment (only applies to databases that are using manual undo space management)	Name of a rollback segment
D_INDEX_DELETED_AT	Deletes table sde_deleted_at column index storage definition	See your Oracle documentation for CREATE INDEX parameters.
D_INDEX_ STATE_ROWID	Deletes table sde_states_id and sde_deletes_row_id column index storage definition	See your Oracle documentation for CREATE INDEX parameters.
D_STORAGE	Deletes table storage definition	See your Oracle documentation for CREATE TABLE parameters.
F_INDEX_AREA	Feature table area column index storage definition	See your Oracle documentation for CREATE INDEX parameters.	Legacy: Binary geometry storage only; no longer used.
F_INDEX_FID	Feature table FID column index storage definition	See your Oracle documentation for CREATE INDEX parameters.	Legacy: Binary geometry storage only; no longer used.
F_INDEX_LEN	Feature table length column index storage definition	See your Oracle documentation for CREATE INDEX parameters.	Legacy: Binary geometry storage only; no longer used.
F_STORAGE	Feature table storage definition	See your Oracle documentation for CREATE TABLE parameters.	Legacy: Binary geometry storage only; no longer used.
GEOMETRY_STORAGE	Indicates storage data type for spatial column	ST_GEOMETRY or SDO_GEOMETRY
LD_INDEX_DATA_ID	SDE_LOGFILE_DATA and SDE_LOGPOOL tables' index storage definition	See your Oracle documentation for CREATE INDEX parameters.	Legacy: Not used starting with 10.7.1.
LD_INDEX_ROWID	SDE_LOGFILE_DATA and SDE_LOGPOOL tables' SDE_ROWID column index storage definition	See your Oracle documentation for CREATE INDEX parameters.	Legacy: Not used starting with 10.7.1.
LD_STORAGE	SDE_LOGFILE_DATA and SDE_LOGPOOL_<SDE_ID> tables' storage definition	See your Oracle documentation for CREATE TABLE parameters.	Legacy: Not used starting with 10.7.1.
LF_INDEXES	SDE_LOGFILES table column indexes storage definition	See your Oracle documentation for CREATE INDEX parameters.	Legacy: Not used starting with 10.7.1.
LF_STORAGE	SDE_LOGFILES table storage definition	See your Oracle documentation for CREATE TABLE parameters.	Legacy: Not used starting with 10.7.1.
MD_URI_LOB_STORAGE	Defines the storage of the URI column of a mosaic dataset	See your Oracle documentation for LOB clauses in the CREATE TABLE statement.
MVTABLES_MODIFIED_INDEX	MVTABLES_MODIFIED index storage definition	See your Oracle documentation for CREATE INDEX parameters.
MVTABLES_MODIFIED_TABLE	MVTABLES_MODIFIED table storage definition	See your Oracle documentation for CREATE TABLE parameters.
RAS_INDEX_ID	Raster RAS table RID index storage definition	See your Oracle documentation for CREATE INDEX parameters.
RAS_STORAGE	Raster RAS table storage definition	See your Oracle documentation for CREATE TABLE parameters.
RASTER_STORAGE	Indicates the storage type used for raster data	RASTERBLOB or BLOB	ArcGIS 10.4.1 and earlier clients and ArcGIS AllSource 1.3 and earlier clients cannot create or access raster datasets or mosaic datasets that use RASTERBLOB storage.
S_INDEX_ALL	Spatial index table first index storage definition when using binary geometry storage	See your Oracle documentation for CREATE INDEX parameters.	Legacy: Binary geometry storage only; no longer used.
S_INDEX_SP_FID	Spatial index table second index storage definition	See your Oracle documentation for CREATE INDEX parameters.	Legacy: Binary geometry storage only; no longer used.
S_STORAGE	Represents the spatial index storage definition	See your Oracle documentation for CREATE TABLE parameters.	Esri ST_Geometry storage only
SDO_COMMIT_INTERVAL	Specifies the number of rows inserted into the index table between each database COMMIT operation. (This becomes a parameter in the CREATE INDEX statement, but only if a quad-tree index is used.)	1000	Oracle Spatial only Refer to the Oracle Spatial Users Guide for information about all these values.
SDO_DIMNAME_1 SDO_DIMNAME_2 SDO_DIMNAME_3 SDO_DIMNAME_4	The name of each dimension for Oracle Spatial geometry types; corresponding values are: 1 = X 2 = Y 3 = Z 4 = M	The dimension name	Oracle Spatial only Refer to the Oracle Spatial Users Guide for information about all these values.
SDO_ELEM_INFO_VARRAY_STORAGE	Defines storage for the SDO_ELEM_INFO_ARRAY portion of an SDO_Geometry object	See the Oracle documentation for information on the CREATE TABLE varray_storage_clause.	Oracle Spatial only
SDO_INDEX_SHAPE	The Oracle Spatial geometry types spatial index storage parameters	Various spatial index storage parameters, including <tablespace_name> and sdo_indx_dims=# (default is 2), which specifies how many dimensions should be indexed with an R-tree spatial index	Oracle Spatial only Refer to the Oracle Spatial Users Guide for information about all these values.
SDO_LB_1, SDO_LB_2, SDO_LB_3, SDO_LB_4	Lower dimension boundary for Oracle Spatial geometry type; units specified in the coordinate system of the data default values based on the extent of data to be loaded; for data with geodetic SAID, SDO_LB_1 must be 180, and SDO_LB_2 must be 90	A value greater than the corresponding SDO_UB values	Oracle Spatial only Refer to the Oracle Spatial Users Guide for information about all these values.
SDO_ORDINATES_VARRAY_STORAGE	Defines storage for the SDO_ORDINATES_ARRAY portion of an SDO_Geometry object	See the Oracle documentation for information on the CREATE TABLE varray_storage_clause.	Oracle Spatial only
SDO_SRID	Oracle Spatial coordinate reference identifier assigned to the SDO_Geometry column	If the configuration keyword you specify when creating a feature class contains the SDO_SRID parameter set to a valid coordinate reference system, that value is used for the feature class and is written to the Oracle USER_SDO_GEOM_METADATA view. The value specified by the SDO_SRID parameter overrides any coordinate reference system specified by the ArcGIS client.	Oracle Spatial only
SDO_TOLERANCE_1 SDO_TOLERANCE_2 SDO_TOLERANCE_3 SDO_TOLERANCE_4	The distance two ordinates can be apart in the given dimension and still be considered the same. Used by Oracle Spatial functions. Must be greater than zero. For geodetic data, units are meters; otherwise, units are specified in the coordinate system of the data.	A value greater than 0	Oracle Spatial only Refer to the Oracle Spatial Users Guide for information about all these values.
SDO_UB_1, SDO_UB_2, SDO_UB_3, SDO_UB_4	Upper dimension boundary for Oracle Spatial geometry type. Used by Oracle Spatial functions. The value must be greater than zero. For geodetic data, SDO_UB_1 should be set to 180 and SDO_UB_2 set to 90. Tolerances are specified in meters with a minimum value of 0.05. The default value is based on the extent of data to be loaded.	A value greater than the corresponding SDO_LB values	Oracle Spatial only Refer to the Oracle Spatial Users Guide for information about all these values.
SE_ANNOCAD_LOB_STORAGE	Defines the storage of the SE_ANNO_CAD_DATA column, which is appended to any table that uses SDO_GEOMETRY storage and stores either CAD or annotation data.	See the Oracle documentation for LOB clauses in the CREATE TABLE statement.	Oracle Spatial only
SESSION_INDEX	Storage definition for the session-based log file table index	See your Oracle documentation for CREATE INDEX parameters.	Legacy: Not used starting with 10.7.1.
SESSION_STORAGE	Session-based log file table storage definition	See your Oracle documentation for CREATE TABLE parameters.	Legacy: Not used starting with 10.7.1.
ST_GEOM_LOB_STORAGE	Controls the storage of the SHAPE.POINTS column for an ST_Geometry object	See your Oracle documentation for CREATE TABLE parameters.
ST_INDEX_PARTITION_LOCAL	Specifies whether a partitioned table's sde.st_spatial_index is created as a global (FALSE) or local (TRUE) index	FALSE or TRUE	Only applies to partitioned business tables containing ST_Geometry columns
STATES_INDEX	STATES table storage definition	See your Oracle documentation for CREATE INDEX parameters.
STATES_LINEAGES_TABLE	STATE_LINEAGES table storage definition	See your Oracle documentation for CREATE TABLE parameters.
STATES_TABLE	STATES table storage definition	See your Oracle documentation for CREATE TABLE parameters.
UI_NETWORK_TEXT	User interface parameter for network configuration that indicates the associated configuration keyword will appear in the ArcGIS user interface	Description up to 8,000 characters	Legacy: No longer used.
UI_TERRAIN_TEXT	User interface parameter for terrains that indicates the associated configuration keyword will appear in the ArcGIS user interface	Description up to 8,000 characters
UI_TEXT	User interface parameter that indicates the associated noncomposite configuration keyword will appear in the ArcGIS user interface	Description up to 8,000 characters
UI_TOPOLOGY_TEXT	User interface parameter for topologies that indicates the associated configuration keyword will appear in the ArcGIS user interface	Description up to 8,000 characters
UNICODE_STRING	Determines whether text columns will be encoded using the database character set (FALSE) or the national character set (TRUE).	TRUE or FALSE	If set to FALSE, ArcGIS uses VARCHAR2 for string data types. If set to TRUE, ArcGIS uses NVARCHAR2 for string data types.
VERSIONS_INDEX	VERSIONS table index storage definition	See your Oracle documentation for CREATE INDEX parameters.
VERSIONS_TABLE	VERSIONS table storage definition	See your Oracle documentation for CREATE TABLE parameters.
XML_COLUMN_STORAGE	Specifies the type of XML columns to create: either ArcSDE XML or native DBMS XML	SDE_XML or DB_XML	Legacy: No longer used.
XML_DOC_INDEX	Storage clause for xmldoc<n>_pk and xml_doc<n>_ix indexes on the sde_xml_doc<n> table	See your Oracle documentation for CREATE INDEX parameters.	Legacy: No longer used.
XML_DOC_LOB_STORAGE	Storage and access information for XML documents in the xml_doc column of the sde_xml_doc<n> table	See your Oracle documentation for LOB storage parameters.	Legacy: No longer used.
XML_DOC_MODE	Storage type for XML documents	COMPRESSED or UNCOMPRESSED	Legacy: No longer used.
XML_DOC_STORAGE	Storage clause for sde_xml_doc<n> table	See your Oracle documentation for CREATE TABLE parameters.	Legacy: No longer used.
XML_DOC_UNCOMPRESSED_TYPE	When the XML_DOC_MODE parameter is set to UNCOMPRESSED, the XML_DOC_UNCOMPRESSED_TYPE parameter determines the storage format for XML documents.	Because XML_DOC_MODE is set to COMPRESSED by default, the XML_DOC_UNCOMPRESSED_TYPE parameter is not present by default. Possible values: CLOB or NCLOB	Legacy: No longer used.
XML_DOC_VAL_LOB_STORAGE	Storage and access information for the XML document content in the xml_doc_val column of the sde_xml_doc<n> table	See your Oracle documentation for LOB storage parameters.	Legacy: No longer used.
XML_IDX_INDEX_DOUBLE	Storage clause for the xmlix<n>_db index on the double_tag column of the sde_xml_idx<n> table	See your Oracle documentation for CREATE INDEX parameters.	Legacy: No longer used.
XML_IDX_INDEX_ID	Storage clause for the xmlix<n>_id index on the ID column of the xml_idx<n> table	See your Oracle documentation for CREATE INDEX parameters.	Legacy: No longer used.
XML_IDX_INDEX_PK	Storage clause for xmlix<n>_pk index on the xml_key_column identity column of the sde_xml_idx<n> table	See your Oracle documentation for CREATE INDEX parameters.	Legacy: No longer used.
XML_IDX_INDEX_STRING	Storage clause for xmlix<n>_st index on the string_tag column of the sde_xml_idx<n> table	See your Oracle documentation for CREATE INDEX parameters.	Legacy: No longer used.
XML_IDX_INDEX_TAG	Storage clause for the xmlix<n>_tg index on the tag_id column of the sde_xml_idx<n> table	See your Oracle documentation for CREATE INDEX parameters.	Legacy: No longer used.
XML_IDX_INDEX_TEXT	XML index creation parameters	See your Oracle documentation for CREATE INDEX parameters.	Legacy: No longer used.
XML_IDX_STORAGE	Storage clause for sde_xml_idx<n> table (the index table of an XML column)	See your Oracle documentation for CREATE TABLE parameters.	Legacy: No longer used.
XML_IDX_TEXT_TAG_STORAGE	Storage and access information for the contents of the text_tag column in the sde_xml_idx<n> table (the index table of an XML column)(If no value is specified [default] or if DISABLE STORAGE IN ROW is not specified, this LOB data is stored in line.)	<no value>, ENABLE STORAGE IN ROW,or DISABLE STORAGE IN ROW	Legacy: No longer used.
XML_IDX_TEXT_UPDATE_MEMORY	The amount of memory to use when building and updating the text index, such as 2M to allocate 2 MB	An integer, greater than 0 but less than the amount of available RAM given in MB (indicated with M)	Legacy: No longer used.
XML_IDX_TEXT_UPDATE_METHOD	Oracle Text index change tracking method: NONE—Manual update by running Oracle Text package (default) BUFFERED—ArcGIS updates when stream is closed IMMEDIATE—ArcGIS updates on row insert or update	NONE, BUFFERED, or IMMEDIATE	Legacy: No longer used.
XML_INDEX_TAGS_INDEX	Storage clause for xml_indextags_pk index of the sde_xml_indexes table	See your Oracle documentation for CREATE INDEX parameters.	Legacy: No longer used.
XML_INDEX_TAGS_TABLE	Storage clause for sde_xml_index_tags table and the xml_indextags_ix1 and xml_indextags_ix2 indexes on the tag_name and tag_alias columns, respectively	See your Oracle documentation for CREATE TABLE parameters.	Legacy: No longer used.

Note:

For the XML parameters, <n> refers to the xml_column_id associated with a specific XML column.

Functional descriptions of parameters

The following sections provide a more in-depth explanation of the parameters listed in the previous table.

Business table and index storage parameters

The business table is the attribute table of a feature class or nonspatial table. Use the B_STORAGE parameter to define the storage configuration of a business table.

Five index storage parameters exist to support the creation of business table indexes:

• The B_INDEX_USER parameter holds the storage configuration for user-defined indexes.
• The B_INDEX_ROWID parameter holds the storage configuration of the index that ArcGIS creates on a register table's ObjectID column, commonly referred to as the ROWID or OBJECTID.
• The B_INDEX_RASTER parameter holds the storage configuration of the raster column index that ArcGIS creates when a raster column is added to a business table. ArcGIS creates this index when it creates a feature class containing a raster.
• The B_INDEX_TO_DATE parameter specifies the storage for the index R<registration_id>_sde_todate. This index is created when archiving is enabled on a business table and is used when updating the history table during an archive operation.

Adds and deletes tables storage parameters

Registering a business table or feature class as versioned allows multiple users to maintain and edit an object. ArcGIS creates two tables—the adds table and the deletes table—for each table that is registered as versioned.

At appropriate intervals, users merge the changes they have made with the changes made by other users and reconcile any conflicts that arise when the same features are modified.

Adds table parameters

The A_STORAGE parameter maintains the storage configuration of the adds table. The adds table is named A<n>, where <n> is the registration ID listed in the TABLE_REGISTRY geodatabase system table. For instance, if the business table ROADS is listed with a registration ID of 10, ArcGIS creates the adds table as A10.

Five other storage parameters hold the storage configuration of the indexes of the adds table:

• The A_INDEX_RASTER parameter specifies the storage configuration of the index that is created on a raster column in the adds table. The index is named SDE_RIX_<N>_A. <N> is the raster column ID.
• The A_INDEX_ROWID parameter holds the storage configuration of the index that ArcGIS creates on the versioned state ID and ObjectID columns. The adds table ROWID index is named A<n>_PK, where <n> is the business table's registration ID with which the adds table is associated.
• The A_INDEX_STATEID parameter holds the storage configuration of the index that ArcGIS creates on the adds table's SDE_STATE_ID column. The SDE_STATE_ID column index is called A<n>_STATE_ID_IX1, where <n> is the business table's registration ID with which the adds table is associated.
• The A_INDEX_SHAPE parameter holds the storage configuration of the index that ArcGIS creates on the adds table's spatial column. If the business table contains a spatial column, the column and the index on it are duplicated in the adds table. The adds table's spatial column index is called A<n>_IX1_A, where <n> is the layer ID of the feature class as it is listed in the LAYERS geodatabase system table.
• The A_INDEX_USER parameter holds the storage configuration of user-defined indexes that ArcGIS creates on the adds table. The user-defined indexes on the business tables are duplicated on the adds table.

Deletes table parameters

The D_STORAGE parameter holds the storage configuration of the deletes table. The deletes table is named D<n>, where <n> is the registration ID listed in the TABLE_REGISTRY geodatabase system table. For instance, if the business table ROADS is listed with a registration ID of 10, ArcGIS creates the deletes table as D10.

Two other storage parameters hold the storage configuration of the indexes that ArcGIS creates on the deletes table. The D_INDEX_STATE_ROWID parameter holds the storage configuration of the D<n>_IDX1 index that ArcGIS creates on the deletes table's SDE_STATE_ID and SDE_DELETES_ROW_ID columns. The D_INDEX_DELETED_AT parameter holds the storage configuration of the D<n>_PK index that ArcGIS creates on the deletes table's DELETED_AT, SDE_DELETES_ROW_ID, and SDE_STATE_ID columns.

Spatial index parameters

Feature classes that use ST_Geometry storage use a separate table to store information for the spatial index. The spatial index table is named S<n>_IDX$, where <n> is the geometry index value for the table. The geometry index value is stored in the SDE.ST_GEOMETRY_COLUMNS geodatabase system table. The spatial index table is created as an Oracle Indexed Organized Table (IOT). The spatial index on the ST_Geometry attribute appears as A<n>_IX1 when viewed through Oracle Enterprise Manager. The value of <n> represents the LAYER_ID value stored in the LAYERS geodatabase system table.

If you create partitioned business tables that contain an ST_Geometry column, you may also want the spatial index to be partitioned. There are two types of partitioning methods: global and local. By default, global partitioned indexes are created on partitioned business tables. To create a local partitioned index, you must add the keyword LOCAL to the end of the CREATE INDEX statement. To enable ArcGIS to add LOCAL to the end of the CREATE INDEX statement for the spatial index, set the parameter ST_INDEX_PARTITION_LOCAL to TRUE under the DEFAULTS keyword.

If the business table with the ST_Geometry column is not partitioned, however, and you set ST_INDEX_PARTITION_LOCAL to TRUE, you will get the following error message:

ORA-14016: underlying table of a LOCAL partitioned index must be partitioned

Raster table and index storage parameters

When you specify a keyword with RASTER_STORAGE set to BLOB, the raster column added to a business table is a foreign key reference to raster data stored in a schema consisting of supporting tables and indexes. When RASTER_STORAGE is set to RASTERBLOB (the default), a BLOB column is created on the business table. and supporting raster information is stored in additional tables.

The following raster table parameters define configuration for the supporting raster tables and indexes:

The RASTER_STORAGE parameter supersedes the RASTER_BINARY_TYPE, which continues to work but is no longer supported.

The RAS_STORAGE parameter holds the Oracle CREATE TABLE storage configuration of the RAS table.

The RAS_INDEX_ID parameter holds the Oracle CREATE INDEX storage configuration of the RAS table index.

The BND_STORAGE parameter holds the Oracle CREATE TABLE storage configuration of the BND table.

The BND_INDEX_COMPOSITE parameter holds the Oracle CREATE INDEX storage configuration of the BND table's composite column index.

The BND_INDEX_ID storage holds the Oracle CREATE INDEX storage configuration of the BND table's row ID (RID) column index.

The AUX_STORAGE parameter holds the Oracle CREATE TABLE storage configuration of the AUX table.

The AUX_INDEX_COMPOSITE parameter holds the Oracle CREATE INDEX storage configuration of the AUX table's index.

The BLK_STORAGE parameter holds the Oracle CREATE TABLE storage configuration of the BLK table.

The BLK_INDEX_COMPOSITE parameter holds the Oracle CREATE TABLE storage configuration of the BLK table's index.

The MD_URI_LOB_STORAGE parameter defines how LOBs are stored in the URI column of a mosaic dataset.

The B_STORAGE parameter defines the storage location for raster attribute tables. This table (or tables; there can be multiple such tables) stores attribute values based on cell values in the raster. If you want to store these tables in a different location than feature class business tables, create a raster keyword that specifies different storage information for the raster attribute tables. Instruct data creators to use this keyword when they create raster datasets.

Geometry storage parameters

The GEOMETRY_STORAGE parameter indicates which geometry storage method to use for new feature classes in a geodatabase. Set the GEOMETRY_STORAGE parameter in the DEFAULTS configuration keyword to reflect the geometry storage type to use for most feature classes in the geodatabase. The GEOMETRY_STORAGE parameter has the following possible values:

• ST_Geometry for Oracle—This type extends the database to include an ST_GEOMETRY data type. Set the GEOMETRY_STORAGE parameter to ST_GEOMETRY if to store spatial data in this format. If the GEOMETRY_STORAGE parameter is not set, ST_GEOMETRY format is assumed.
• Oracle Spatial geometry type—This object relational type extends the database model to include an SDO_GEOMETRY type.

  Set the GEOMETRY_STORAGE parameter to SDO_GEOMETRY to store spatial data in this format. To make this format the default spatial type, set the GEOMETRY_STORAGE parameter to SDO_GEOMETRY in the DEFAULTS configuration keyword.

Log file table parameters

Log file table parameters may be present in the DBTUNE table, but they are no longer used.

User interface parameters

User interface parameters begin with UI and indicate whether their associated configuration keyword will be available through the ArcGIS user interface and ArcObjects. UI_TEXT is used for noncomposite configuration keywords. UI_TOPOLOGY_TEXT is used for topology keywords. UI_NETWORK_TEXT is used for network keywords. The default configuration keywords that need UI parameters already have them. You would only add one of these parameters if you created your own custom keywords.

BLOB storage parameters

BLOBs can be used for RASTER_STORAGE and ATTRIBUTE_BINARY parameters. Use of BLOB storage for geometry storage is deprecated.

The RASTER_STORAGE parameter controls how raster data is stored in a raster dataset, raster catalog, or raster attribute. The ATTRIBUTE_BINARY parameter controls the storage of binary data that is not vector or raster.

To create BLOB columns, the parameters must be set as follows within a given configuration keyword:

RASTER_STORAGE BLOB
ATTRIBUTE_BINARY BLOB

Esri recommends the following LOB storage parameters:

• Always enable in-row storage because most GIS data fits within the 3,964-byte, in-row threshold. Performance is best when data is stored in row.
• Enable the cache because geodatabase data is frequently read.
• Because ArcGIS does not perform updates on BLOB data but instead performs only inserts and deletes, set the PCT_VERSION to 0, as there is no need to maintain older versions of the data within the LOB segment.
• Do not use a chunk size less than 8K. Chunk sizes of 2K and 4K increase the amount of I/O because the Oracle server process must fetch more chunks. You will probably find that an 8K chunk size wastes less space than 16K. If you use a chunk size of 2K or 4K, you will find that it wastes less space, but tests have found that the display time for most raster and vector data increases dramatically over storing in an 8K chunk size. Since the chunk size must always be a multiple of the data block size, the best data block size to use for storing GIS data in BLOBs is 8K.

The following example shows how the raster DBTUNE storage parameters have been modified to accommodate a raster blocks table stored as a BLOB data type:

RASTER_STORAGE "BLOB"
BLK_STORAGE "PCTFREE 0 INITRANS 4 TABLESPACE RASTER 
             LOB (BLOCK_DATA) STORE AS 
             (TABLESPACE RASTER_LOB_SEGMENT 
              CACHE PCTVERSION 0)" 

AUX_STORAGE "PCTFREE 0 INITRANS 4 TABLESPACE RASTER 
             LOB (OBJECT) STORE AS 
             (TABLESPACE RASTER 
              CACHE PCTVERSION 0)"

If the raster block pixel data is less than 3,965 bytes, it is stored within the BLOCK_DATA column in the RASTER tablespace. However, if it exceeds this threshold, it is stored in the LOB segment in the RASTER_LOB_SEGMENT tablespace. The LOB index is only used if the number of chunks exceeds 12. This is unlikely to happen for geodatabase data. Consider a LOB segment with a chunk size of 8K.

In the following example, if the business table's binary data is less than 3,965 bytes, it is stored within the business table's BLOB column in the BIZZTABS tablespace. However, if it exceeds this threshold, it is stored in the LOB segment in the BIZZ_LOB_SEGMENT tablespace. The BLOB column in this example is DOCUMENT.

ATTRIBUTE_BINARY "BLOB"

B_STORAGE "PCTFREE 0 INITRANS 4 TABLESPACE BIZZTABS 
             LOB (DOCUMENT) STORE AS 
             (TABLESPACE BIZZ_LOB_SEGMENT 
              CACHE PCTVERSION 0)"

A_STORAGE "PCTFREE 0 INITRANS 4 TABLESPACE BIZZTABS 
             LOB (DOCUMENT) STORE AS 
             (TABLESPACE BIZZ_LOB_SEGMENT 
              CACHE PCTVERSION 0)"

If the above B_STORAGE DBTUNE parameter is used to create a table that does not have a DOCUMENT column, the following error is returned by Oracle:

ORA-00904: "DOCUMENT": invalid identifier

Do not add B_STORAGE or A_STORAGE parameters referencing a specific BLOB column to the DEFAULTS keyword, as the business table must contain these columns. Instead, create separate configuration keywords and add these storage parameters to the keywords. The keyword that contains the storage parameter is referenced during the creation of the table. It should also be noted that storage parameters of the DEFAULTS keyword are used if they are not included with a specific keyword. Due to this fact, it is not necessary to add a particular storage parameter within a keyword if its configuration string is identical to the storage parameter under the DEFAULTS keyword. For instance, if all the storage parameters except B_STORAGE and A_STORAGE of a new keyword, ROADS, have the same configuration string as those of the DEFAULTS keyword, you only need to create the B_STORAGE and A_STORAGE parameters under the ROADS keyword. All other storage parameters are read from the DEFAULTS keyword since they are not found in the ROADS keyword.

XML parameters

Note:

ArcGIS clients do not work directly with XML columns. If you do not use XML columns and XML documents in the geodatabase, you do not need to configure these parameters.

The XML_COLUMN_STORAGE parameter determines whether XML columns are created as ArcSDE XML or native Oracle XML. The default setting is to use ArcSDE XML (SDE_XML).

An XML column may have two text indexes associated with it: one for the XML document table and one for the XML index table. To create an XML column, the XML_IDX_INDEX_TEXT parameter must have an appropriate value. This value is used in the PARAMETERS clause when creating the XML column's context text indexes. An appropriate value for the XML_IDX_INDEX_TEXT parameter is not the same as the values that are used for other DBTUNE parameters used to create other types of indexes. The value in the PARAMETERS clause controls the storage parameters for the text indexes, the language of linguistic analysis for indexing and searching text in the XML documents, the schedule with which the text indexes are updated, and other settings that are specific to text indexes.

XML documents are stored as large objects (LOBs) in the XML document table in the XML_DOC and XML_DOC_VAL columns and in the XML index table in the TEXT_TAG column. It is important to configure these columns accurately to achieve the best possible search performance. LOBs are stored in line if the LOB data is stored in the same block as the rest of the data in the row. However, in-line storage is only possible if the LOB data is less than 4 KB. With out-of-line storage, the data is stored in the LOB segment, and only the LOB locator is stored with the rest of the data in the row.

You can specify whether LOB data associated with an XML column is stored in line or out of line using the DBTUNE parameters XML_DOC_LOB_STORAGE and XML_DOC_VAL_LOB_STORAGE and XML_IDX_TEXT_TAG_STORAGE. Append the value "DISABLE STORAGE IN ROW" to store the data out of line, or "ENABLE STORAGE IN ROW" to store the data in line.

When LOB data is stored out of line for an XML column, by default, ArcGIS places that data in the same tablespace as the XML document table. The LOB data can be moved to a different tablespace than the one containing the XML document table.

Additional parameters

Some individual parameters that can also be set in the DBTUNE geodatabase system table include the following:

COMPRESS_ROLLBACK_SEGMENT parameter

Periodically compressing the versioned database’s state tree is a required maintenance procedure.

The transactions of the compress operation tend to be large; if you are using the Oracle manual undo method, Esri recommends that you create a separate, large rollback segment to contain the changes. The COMPRESS_ROLLBACK_SEGMENT storage parameter stores the name of a rollback segment that you have created for this purpose. Add the COMPRESS_ROLLBACK_SEGMENT storage parameter to the DEFAULTS configuration keyword.

Oracle does not recommend the use of the manual undo method. See the documentation provided with the Oracle installation for details.

UNICODE_STRING parameter

The UNICODE_STRING parameter specifies whether or not text columns will be stored using the database character set (VARCHAR2) or national character set (NVARCHAR2). Consult the Oracle documentation to decide whether to set this parameter to FALSE (which will use VARCHAR2) or TRUE (NVARCHAR2).