Oracle DBTUNE configuration parameters
Configuration parameters, which are stored in the parameter_name column of the DBTUNE table, identify database objects to be stored in the database. Their corresponding values, which are stored in the config_string column of DBTUNE, identify how the object will be stored in the database. The parameters and their configuration strings are grouped together in the DBTUNE table by configuration keywords. Keyword/Parameter name combinations are unique, but most parameter names are not and are reused under a number of different keywords throughout the DBTUNE table.
Valid values for the parameter_name column are fixed; you cannot invent new parameter names. Likewise, the config_string column accepts only certain numeric values or SQL strings. In most cases, these strings are appended to SQL CREATE TABLE and CREATE INDEX statements so they must match the SQL syntax necessary for the given DBMS.
In geodatabases stored in an Oracle database, parameter name–configuration string pairs are used by ArcSDE for the following purposes:
- Establish the storage characteristics of tables and indexes.
- Define the storage type for spatial, raster, and attribute columns.
- Define how XML documents are stored.
- Make keywords available for users in the ArcGIS interface.
- Provide comments that describe the configuration keyword.
Keyword/Parameter_name combinations are unique. For instance, you could not have the same parameter defined under the same keyword, as shown here:
KEYWORD PARAMETER_NAME CONFIG_STRING ------------ ----------------------- ---------------------- DEFAULTS RASTER_STORAGE BLOB DEFAULTS RASTER_STORAGE SDO_GEOMETRY
However, most parameters can be used under a number of different keywords throughout the DBTUNE table. For example, the RASTER_STORAGE parameter also appears grouped with several other keywords. In this example, you see it is included in the SDELOB keyword.
SQL> SELECT * FROM SDE.DBTUNE 2 WHERE KEYWORD = 'SDELOB'; KEYWORD PARAMETER_NAME CONFIG_STRING ------------- ----------------------- --------------------- SDELOB ATTRIBUTE_BINARY BLOB SDELOB GEOMETRY_STORAGE SDELOB SDELOB RASTER_STORAGE BLOB
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 DBTUNE 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, ArcSDE reads the config_string for RAS_STORAGE and places it in the SQL statement used to create the SDE_RAS_<raster_column_ID> table.
This DEFAULTS DBTUNE entry becomes a SQL statement similar to the code shown here after the DBTUNE entry:
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. If the raster tablespace had not been specified in the last example, the user's default tablespace would have been used.
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 (DBA), 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;
SQL> connect system/<password> SQL> SELECT default_tablespace FROM dba_users WHERE username = <'USER'>;
SQL> connect <user>/<password> SQL> SELECT * FROM user_tablespaces WHERE tablespace_name = <'TABLESPACE'>;
You can supply the appropriate tablespace names for your data by altering the dbtune.sde file or using the sdedbtune command to alter specific values in the DBTUNE table.
In the dbtune file, uncomment the appropriate TABLESPACE line or lines associated with the configuration keyword you want to use, such as DEFAULTS. Commented lines are prefaced with a single pound sign (#). Remove this pound sign and replace the <text> with the name of the correct tablespace. Then import the dbtune file into the DBTUNE table. Users can then specify that keyword (or accept the DEFAULTS) and the tables and indexes of the datasets they create will be stored in the tablespace you specified in the dbtune file. You can use the sdedbtune command to alter the value of one parameter at a time using the alter operation. See Altering the dbtune file prior to creating the geodatabase and Altering the contents of the DBTUNE table after it has been created for specifics on editing the dbtune file and table.
Valid parameter list
The following table is an alphabetic list of all the possible configuration parameters that can be used in a geodatabase in Oracle. The values in bold indicate which value is the default.
Following the table is a more in-depth explanation of the parameters grouped by their functionality.
|
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 ObjectID 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 |
|
|
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 ObjectID 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. |
|
|
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. |
Binary geometry storage only (SDEBINARY and SDELOB) |
|
F_INDEX_FID |
Feature table FID column index storage definition |
See your Oracle documentation for CREATE INDEX parameters. |
Binary geometry storage only (SDEBINARY and SDELOB) |
|
F_INDEX_LEN |
Feature table length column index storage definition |
See your Oracle documentation for CREATE INDEX parameters. |
Binary geometry storage only (SDEBINARY and SDELOB) |
|
F_STORAGE |
Feature table storage definition |
See your Oracle documentation for CREATE TABLE parameters. |
Binary geometry storage only (SDEBINARY and SDELOB) |
|
GEOMETRY_STORAGE |
Indicates storage data type for spatial column |
ST_GEOMETRY,SDEBINARY, SDELOB, OGCWKB, 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. |
|
|
LD_INDEX_ROWID |
SDE_LOGFILE_DATA and SDE_LOGPOOL tables' SDE_ROWID column index storage definition |
See your Oracle documentation for CREATE INDEX parameters. |
|
|
LD_STORAGE |
SDE_LOGFILE_DATA and SDE_LOGPOOL_<SDE_ID> tables' storage definition |
See your Oracle documentation for CREATE TABLE parameters. |
|
|
LF_INDEXES |
SDE_LOGFILES table column indexes storage definition |
See your Oracle documentation for CREATE INDEX parameters. |
|
|
LF_STORAGE |
SDE_LOGFILES table storage definition |
See your Oracle documentation for CREATE TABLE parameters. |
|
|
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 |
BLOB, LONGRAW, SDO_GEORASTER, or ST_RASTER |
|
|
RDT_INDEX_COMPOSITE |
Contains the storage information for the composite index that is created on the SDO_GEORASTER blocks table (The index is named SDE_RDT_<N>_PK, where N is the rastercolumn_id value of the raster column.) |
See your Oracle documentation for CREATE INDEX parameters. |
Oracle Spatial only |
|
RDT_STORAGE |
Contains the storage information for the SDO_GEORASTER blocks table (The blocks table is named sde_rdt_<N>, where N is the rastercolumn_id value for the raster column.) |
See your Oracle documentation for CREATE TABLE parameters. |
Oracle Spatial only |
|
S_INDEX_ALL |
Spatial index table first index storage definition when using binary geometry storage |
See your Oracle documentation for CREATE INDEX parameters. |
Binary geometry storage only (SDEBINARY and SDELOB) |
|
S_INDEX_SP_FID |
Spatial index table second index storage definition |
See your Oracle documentation for CREATE INDEX parameters. |
Binary geometry storage only (SDEBINARY and SDELOB) |
|
S_STORAGE |
Represents the spatial index storage definition |
See your Oracle documentation for CREATE TABLE parameters. |
|
|
SDO_COMMIT_INTERVAL |
Number of rows inserted into the spatial index table between each database COMMIT(This becomes a parameter in the CREATE INDEX statement.) |
1000 (Refer to your Oracle Spatial Users Guide for information about all these values.) |
Oracle Spatial only |
|
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 (Refer to your Oracle Spatial Users Guide for information about all these values.) |
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 (Refer to your Oracle Spatial Users Guide for information about all these values.) |
Oracle Spatial only |
|
SDO_LB_1 SDO_LB_2 SDO_LB_3 SDO_LB_4 |
Lower dimension boundary for Oracle Spatial geometry type; units specified in coordinate system of the data default values based on 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 (Refer to your Oracle Spatial Users Guide for information about all these values.) |
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 client (such as ArcCatalog or the sdelayer command). |
Oracle Spatial only |
|
SDO_TOLERANCE_1 SDO_TOLERANCE_2 SDO_TOLERANCE_3 SDO_TOLERANCE_4 |
|
A value greater than 0 (Refer to your Oracle Spatial Users Guide for information about all these values.) |
Oracle Spatial only |
|
SDO_UB_1 SDO_UB_2 SDO_UB_3 SDO_UB_4 |
|
A value greater than the corresponding SDO_LB values (Refer to your Oracle Spatial Users Guide for information about all these values.) |
Oracle Spatial only |
|
SESSION_INDEX |
ArcSDE session-based and stand-alone log file indexes storage definition |
See your Oracle documentation for CREATE INDEX parameters. |
|
|
SESSION_STORAGE |
ArcSDE session-based and stand-alone log file tables storage definition |
See your Oracle documentation for CREATE TABLE parameters. |
|
|
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. |
For more information on storing LOBs, see BLOB data storage in geodatabases in Oracle. |
|
ST_INDEX_PARTITION_LOCAL |
Specifies whether a partitioned table's sde.st_spatial_index is created as a global or local index (FALSE indicates the st_spatial_index will be created as a global index; TRUE specifies the spatial index will be created as a local index.) |
TRUE or FALSE |
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, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of network configuration |
Description up to 8,000 characters |
|
|
UI_TERRAIN_TEXT |
User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of terrain configuration |
Description up to 8,000 characters |
|
|
UI_TEXT |
User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of associated noncomposite configuration keyword |
Description up to 8,000 characters |
|
|
UI_TOPOLOGY_TEXT |
User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of topology configuration |
Description up to 8,000 characters |
|
|
UNICODE_STRING |
Determines whether Unicode text types will be used or not(If set to TRUE, character fields will be stored in UNICODE compliant data types. For example, if the UNICODE_STRING parameter is set to FALSE, a string data type would be VARCHAR2. If UNICODE_STRING is set to TRUE, the data type of the field would be NVARCHAR2.) |
TRUE or FALSE |
|
|
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 |
|
|
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. |
|
|
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. |
For more information on storing LOBs, see BLOB data storage in geodatabases in Oracle. |
|
XML_DOC_MODE |
Storage type for XML documents |
COMPRESSED or UNCOMPRESSED |
|
|
XML_DOC_STORAGE |
Storage clause for sde_xml_doc<n> table |
See your Oracle documentation for CREATE TABLE parameters. |
|
|
XML_DOC_TEXT_TYPE |
Data type for document text column |
BLOB or LONGRAW |
Obsolete after ArcSDE release 9 |
|
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 |
Since XML_DOC_MODE is set to COMPRESSED by default, the XML_DOC_UNCOMPRESSED_TYPE parameter not present by default; Possible values: CLOB or NCLOB |
|
|
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. |
For more information on storing LOBs, see BLOB data storage in geodatabases in Oracle. |
|
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. |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
XML_IDX_INDEX_TEXT |
XML index creation parameters (See Oracle Text Reference.) |
See your Oracle documentation for CREATE INDEX parameters. |
|
|
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. |
|
|
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 |
|
|
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)(Consult your Oracle documentation for recommended settings.) |
|
|
XML_IDX_TEXT_UPDATE_METHOD |
Oracle Text index change tracking method:
|
NONE, BUFFERED, or IMMEDIATE |
|
|
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. |
|
|
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. |
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
A business table is any Oracle table created by an ArcSDE client, the sdetable administration command, or the ArcSDE C application programming interface (API) SE_table_create function. Use the DBTUNE table's 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 created with the C API function SE_table_create_index and the create_index operation of the sdetable command.
- The B_INDEX_ROWID parameter holds the storage configuration of the index that ArcSDE creates on a register table's ObjectID column, commonly referred to as the ROWID or OBJECTID.
- The B_INDEX_SHAPE parameter holds the storage configuration of the spatial column index that ArcSDE creates when a spatial column is added to a business table. This index is created by the ArcSDE C API function SE_layer_create. This function is called by ArcGIS when it creates a feature class and by the add operation of the sdelayer command.
- The B_INDEX_RASTER parameter holds the storage configuration of the raster column index that ArcSDE creates when a raster column is added to a business table. This index is created by the ArcSDE C API function SE_rastercolumn_create. This function is called by ArcGIS when it creates a feature class and by the add, copy, and import operations of the sderaster command.
- 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.
Note:ArcSDE registers all tables that it creates. Tables not created by ArcSDE can also be registered with the sdetable or sdelayer commands. The TABLE_REGISTRY system table maintains a list of the currently registered tables.
Adds and deletes tables storage parameters
Registering a business table or feature class as versioned allows multiple users to maintain and edit an object. ArcSDE creates two tables—the adds table and the deletes table—for each table that is registered as versioned.
At appropriate intervals, the 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 system table. For instance, if the business table ROADS is listed with a registration ID of 10, ArcSDE 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 configurationof 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 ArcSDE creates on the multiversioned state ID and ObjectID (also referred to as the ROWID) 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 ArcSDE 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 ArcSDE 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 table.
- The A_INDEX_USER parameter holds the storage configuration of user-defined indexes that ArcSDE 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 system table. For instance, if the business table ROADS is listed with a registration ID of 10, ArcSDE creates the deletes table as D10.
Two other storage parameters hold the storage configuration of the indexes that ArcSDE creates on the deletes table. The D_INDEX_STATE_ROWID parameter holds the storage configuration of the D<n>_IDX1 index that ArcSDE 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 ArcSDE creates on the deletes table's DELETED_AT, SDE_DELETES_ROW_ID, and SDE_STATE_ID columns.
For more information on the structure of adds and deletes tables and how they are used, see Versioned tables in a geodatabase in Oracle.
Spatial index and feature tables parameters
A feature class created using ST_Geometry stroage with a spatial index creates an additional table within the Oracle database. The spatial index table is named S<n>_IDX$, where <n> is the geometry index value for the table. The value can be obtained by querying the SDE.ST_GEOMETRY_COLUMNS 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 Enterprise Manager. The value of <n> represents the LAYER_ID value stored in the LAYERS 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
A feature class created with an ArcSDE compressed binary storage (LONG RAW or BLOB data type) format adds two tables to the Oracle database—the feature table and the spatial index table. The spatial index table is created as S<n>, where <n> is the layer ID of the spatial index table's feature class as found in the LAYERS table. Three indexes are created on the feature table, and two indexes are created on the spatial index table. Configuration parameters that apply to spatial indexes usually begin with S_.
The storage parameters for these tables and indexes follow the same pattern as the B_STORAGE and B_INDEX_* storage parameters of the business table. The S_STORAGE parameter holds the Oracle CREATE TABLE storage configuration of the spatial index table and its indexes for both ST_Geometry and binary storage. The S_INDEX_ALL parameter only applies to binary storage and holds the Oracle CREATE INDEX storage configuration of the spatial table's first index. The spatial index is created as S<n>_IX1, where <n> is the layer ID of the index's feature class found in the LAYERS table.
The S_INDEX_SP_FID parameter holds the Oracle CREATE INDEX storage configuration of the spatial table's second index if binary storage is used for the feature class. The spatial index is created as S<n>_IX2, where <n> is the layer ID of the index's feature class found in the LAYERS table.
Feature class parameters only apply when using binary storage. These parameters begin with F_.
- The F_STORAGE parameter holds the Oracle CREATE TABLE storage configuration string of the feature table. The feature table is created as F_<n>, where <n> is the layer ID of the table's feature class as found in the LAYERS table.
- The F_INDEX_FID parameter holds the Oracle CREATE INDEX storage configuration string of the feature table's spatial column index. The spatial column index is created as F<n>_UK1, where <n> is the layer ID of the index's feature class as found in the LAYERS table.
- The F_INDEX_AREA parameter holds the Oracle CREATE INDEX storage configuration of the feature table's area column index. The spatial column area index is created as F<n>_AREA_IX2, where <n> is the layer ID of the index's feature class as found in the LAYERS table.
- The F_INDEX_LEN parameter holds the Oracle CREATE INDEX storage configuration of the feature table's length column index. The spatial column length index is created as F<n>_LEN_IX3, where <n> is the layer ID of the index's feature class as found in the LAYERS table.
Raster table and index storage parameters
A raster column added to a business table is actually a foreign key reference to raster data stored in a schema consisting of four tables and five supporting indexes. The raster table parameters define configuration for the raster tables and indexes.
The RASTER_STORAGE parameter defines what data type is used to store raster data. Options are BLOB, LONG RAW, SDO_GEORASTER, or ST_Raster. The ST_Raster and SDO_GEORASTER types can be accessed via SQL.
Caution:Do not use LONG RAW storage for new data, because Oracle may deprecate support for this data type in a future release. Although LONG RAW still works, it is best to not use it; you will have to migrate it eventually to a different storage type if you do use LONG RAW.
If you want most of the raster columns in your database to use the same raster storage format, set the RASTER_STORAGE parameter once in the DEFAULTS configuration keyword. For example, to change the default RASTER_STORAGE from BLOB to SDO_GEORASTER, the following change is made:
## DEFAULTS RASTER_STORAGE "SDO_GEORASTER" <other parameters> END
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.
ArcSDE provides four raster storage formats for Oracle. The RASTER_STORAGE parameter indicates which geometry storage method is to be used. The RASTER_STORAGE parameter has the following values:
- BLOB
- ST_RASTER
- SDO_GEORASTER
- LONGRAW
Note:The LONG RAW data type may not be supported in a future release of Oracle, therefore, do not use it for new raster data.
There is an additional type of raster table—the raster attribute table. This table (or tables; there can be multiple such tables) stores attribute values based on cell values in the raster. The B_STORAGE parameter defines the storage of these tables. If you need to define a different storage location for these tables than you do for other feature class business tables, be sure to create a raster keyword you can use when creating raster datasets and raster catalogs that specifies different storage information for the raster attribute tables.
To learn more about raster attribute tables, see Raster dataset attribute tables. To learn about custom configuration keywords, see Custom configuration keywords.
Geometry storage parameters
ArcSDE for Oracle provides five spatial data storage formats. The GEOMETRY_STORAGE parameter indicates which geometry storage method is to be used. You should set the GEOMETRY_STORAGE parameter in the DEFAULTS configuration keyword to reflect the geometry storage type with which most of your feature classes will be created. 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 you want to store your spatial data in this format. (Beginning with ArcSDE 9.3, if the GEOMETRY_STORAGE parameter is not set, ST_GEOMETRY format is assumed.)
- ArcSDE compressed binary stored as a BLOB data type
Set the GEOMETRY_STORAGE parameter to SDELOB if you want to store your spatial data in this format. If you want to make this format the default, set the GEOMETRY_STORAGE parameter to SDELOB in the DEFAULTS configuration keyword.
- ArcSDE compressed binary—Set the GEOMETRY_STORAGE parameter to SDEBINARY if you want to store your spatial data in compressed binary format stored as a LONG RAW.Note:
Oracle may deprecate the LONG RAW storage type in a future release. For this reason, it is recommended that you not use SDEBINARY storage for new feature classes. To migrate existing feature classes from LONG RAW to BLOB or ST_GEOMETRY, see Migrating raster columns to the ST_Raster type using the Migrate Storage geoprocessing tool.
- Oracle Spatial geometry type—This object relational type extends the database model to include an SDO_GEOMETRY type in the Oracle DBMS.
Set the GEOMETRY_STORAGE parameter to SDO_GEOMETRY if you want to store your spatial data in this format. If you want to make this format the default, set the GEOMETRY_STORAGE parameter to SDO_GEOMETRY in the DEFAULTS configuration keyword.
- OGC well-known binary (WKB) geometry type—This type provides a portable representation of a geometry as a contiguous stream of bytes. The OGCWKB representation supports only simple 2D geometries.
Set the GEOMETRY_STORAGE parameter to OGCWKB if you want to store your spatial data in this format. If you want to make this format the default, set the GEOMETRY_STORAGE parameter to OGCWKB in the DEFAULTS configuration keyword.
Note:The ArcSDE for Oracle Windows installation includes several versions of the dbtune file; each specifies a different geometry storage in the DEFAULTS keyword. If you are performing a new installation of ArcSDE for Oracle (not upgrading the database), you can use one of the alternate versions of the file to populate your DBTUNE table during the postinstallation setup if you want your default geometry storage to be a type other than ST_GEOMETRY.
XML parameters
Note:If you do not use XML columns and XML documents in your 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 successfully 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 in size. 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 ArcSDE 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, ArcSDE 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.
A typical XML document that contains metadata describing a GIS resource will be greater than 4 KB in size. Tests show XML columns associated with ArcIMS Metadata Services perform best when the LOB data is stored out of line in a separate tablespace from the XML document table. However, a metadata service may contain gazetteer data instead of typical metadata XML documents. Gazetteer data is very small, typically less than 100 bytes in size. Metadata Services containing gazetteer data will perform best when the LOB data is stored in line. See Configuring an Oracle database to support ArcSDE XML columns for information on configuring ArcSDE XML columns to be used with Metadata Services in your geodatabase.
Log file table parameters
Log file tables are used by ArcSDE to maintain sets of selected records.
Log file parameters affect log file data tables and indexes. They begin with the letter L or SESSION. The parameters are as follows:
- LF_STORAGE defines the configuration for the LOGFILES table.
- LF_INDEXES configures creation of indexes logfiles_pk and logfiles_uk on the LOGFILES table.
- LD_STORAGE defines configuration for the LOGFILE_DATA and LOGPOOL_<SDE_ID> tables.
- LD_INDEX_ROWID configures creation of the index LOGFILE_DATA_idx1 on the LOGFILE_DATA table and the index LOGPOOL_<SDE_ID>_idx1 on the LOGPOOL_<SDE_ID> pools table.
- LD_INDEX_DATA_ID configures the creation of the LOGFILE_DATA_idx2 index on the LOGFILE_DATA table and of the LOGPOOL_<SDE_ID>_idx1 index on the LOGPOOL_<SDE_ID>.
- SESSION_STORAGE defines configuration for the LOGDATA_<SDE_ID>_<Current_standalone_id> stand-alone log table and SESSION_<sde_id> session table.
- SESSION_INDEX configures the creation of the LOGDATA_<SDE_ID>_<sde_id>_<Current_standalone_id>_idx1 index for the stand-alone log table and the LOGSESSION_<SDE_ID>_idx1 index on the session table.
- SESSION_TEMP_TABLE is not used in Oracle databases.
For more information on how log file tables are used in the geodatabase, see ArcSDE log file table configuration options for Oracle.
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_TERRAIN_TEXT is used for terrain keywords. UI_NETWORK_TEXT is used for network keywords. See Making configuration keywords available in ArcGIS for more information on how to use UI parameters.
Additional parameters
Some individual parameters that can also be set in the DBTUNE 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.
Beginning with Oracle 10g, Oracle does not recommend the use of the manual undo method. See the documentation provided with your Oracle 10g installation for details.
ATTRIBUTE_BINARY parameter
ArcSDE defines attribute columns used to store binary data as LONG RAW or as BLOB. The default and recommended setting is BLOB.
If the storage parameter is not set in the DEFAULTS configuration keyword when a dbtune file is imported by the sdedbtune administration tool, ArcSDE inserts the ATTRIBUTE_BINARY storage parameter under the DEFAULTS configuration keyword with a configuration string set to BLOB.
Note:Prior to ArcSDE 9.2, LONGRAW was the default value for the ATTRIBUTE_BINARY parameter. When you upgrade an existing ArcSDE geodatabase to a 9.2 or later release, this value is not changed in the DBTUNE table. To make BLOB the default data type for binary attribute columns, you must manually alter the DEFAULTS ATTRIBUTE_BINARY parameter to BLOB. After you make this change, new feature classes created with the DEFAULTS keyword will use BLOB for binary columns. To migrate the attribute columns in existing data from LONG RAW to BLOB, see Migrating LONG RAW attribute columns in Oracle using ArcSDE commands or Migrate Storage.
If you are using feature class representations, you must create the feature class with a configuration keyword that has the ATTRIBUTE_BINARY parameter set to BLOB. If you have your DEFAULTS ATTRIBUTE_BINARY value set to LONGRAW, you must create another configuration keyword users can specify when they create feature classes that contain representation classes.
For example, you could add the following configuration keyword REPRESENTATIONS as follows:
##REPRESENTATIONS ATTRIBUTE_BINARY BLOB UI_TEXT "Configuration keyword used to create feature classes containing representation classes" END
For more information on custom keywords, see Custom configuration keywords. If a feature class is created with a configuration keyword that contains an ATTRIBUTE_BINARY parameter set to LONGRAW and multiple representations are created, an error message will be returned:
Unable to create the representation. Underlying DBMS error.
This happens because each time a new representation class is added, two new fields are added to the business table of the feature class—one LONG RAW and one BLOB. Tables in Oracle cannot contain more than one LONG RAW field, so when the second LONG RAW field is added, it fails.
UNICODE_STRING parameter
The UNICODE_STRING parameter specifies whether or not text columns will be stored as VARCHAR2 (nonUnicode) or NVARCHAR2 (Unicode) data types.
For a discussion of Unicode data, see An overview of Unicode.
Comments
You can add a COMMENT parameter in the dbtune.sde file if you want by adding a line beginning with a single pound sign (#). You might do this if you create your own custom keywords and want to add comments on how or when the keyword should be used. For example, you could add a comment to a user's log file keyword:
#COMMENT "This keyword is used by ArcSDE to create log file tables for all users logged in as editor"