Oracle DBTUNE configuration parameters

Oracle DBTUNE configuration parameters
Release 9.3

• Establish the storage characteristics of tables and indexes
• Define the storage type for spatial, raster, and attribute columns
• Define how XML documents are stored
• Specify how ArcSDE log file tables are used
• Define the environment of a configuration keyword
• Make keywords available for users
• Include comments that describe the configuration keyword

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

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

Functional descriptions of parameters

• 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 object ID column, commonly referred to as the ROWID or OBJECTID.

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.

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.
• Adds and deletes tables storage parameters
Registering a business table as versioned allows multiple users to maintain and edit an object. 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 rows are modified.
ArcSDE creates two tables—the adds table and the deletes table—for each table that is registered as versioned.
Adds table parameters
The A_STORAGE parameter maintains the storage configuration of the adds table. Four other storage parameters hold the storage configuration of the indexes 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.
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 object ID column, also referred to as the ROWID. The adds table ROWID index is named A<n>_ROWID_IX1, 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_IX2, 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. Two other storage parameters hold the storage configuration of the indexes that ArcSDE creates on 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.
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>_IDX2 index that ArcSDE creates on the deletes table's SDE_DELETED_AT column.
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 with ST_Geometry storage adds a spatial index table to the Oracle database. This spatial index table is named S<n>_IDX$, where <n> is the geometry index value. This table is an Oracle Indexed Organized Table (IOT).
If you create partitioned business tables that contain an ST_Geometry column, you will 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 instead, 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 (LONGRAW 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 table 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 was used for the feature class. The spatial index table 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 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 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 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.
For information on the structure F and S tables of binary feature classes in Oracle, see Feature classes in a geodatabase in Oracle in the "Geodatabase data storage and schema" section of the help.
For information on the available options for feature class geometry storage, see the section in this topic "Geometry storage parameter", and the topic About geometry storage types.
• 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 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 RASTER_STORAGE parameter defines what data type is used to store raster data. ArcSDE provides three 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:
• ArcSDE raster stored as a BLOB data type—This is the default raster storage method of ArcSDE for Oracle.
Set the RASTER_STORAGE parameter to BLOB if you want to store your raster data in this format. If you want to make this format the default, set the RASTER_STORAGE parameter to BLOB in the DEFAULTS configuration keyword. If the RASTER_STORAGE parameter is not set, the BLOB format is assumed.
• ArcSDE raster stored in LONGRAW data type
Set the RASTER_STORAGE parameter to LONGRAW if you want to store your raster data in this format.

NOTE: It is not recommended you use LONGRAW storage because Oracle has deprecated support for this data type beginning with release 11g.

• Oracle GeoRaster type—This object relational type extends the database model to include an SDO_GEORASTER type in the Oracle DBMS.
Set the RASTER_STORAGE parameter to SDO_GEORASTER if you want to store your spatial raster data in this format.
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.

NOTE: If you specify SDO_GEORASTER for the RASTER_STORAGE parameter, you cannot set the GEOMETRY_STORAGE to SDO_GEOMETRY or ST_GEOMETRY.

For more information on raster storage in the geodatabase, see Raster datasets and raster catalogs in a geodatabase stored in Oracle in the "Geodatabase data storage and schema" section of the help.
There is an additional type of raster table—the raster attribute table. These tables store 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 in the "Image and raster data management" section of the help. To learn how to create custom configuration keywords, see DBTUNE 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—This data type can be replicated through Oracle Advanced Replication.
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 has deprecated the LONG RAW storage type. For this reason, it is recommended you not use SDEBINARY storage for new feature classes. To migrate existing feature classes from LONG RAW to BLOB or ST_GEOMETRY, see Migrating Oracle data from one storage type to another in the "Geodatabase data storage and schema" section of the help.

• 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.

See About geometry storage types for more information on geometry storage types in Oracle.

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 don't need to configure these parameters.

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 in 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 may 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 XML columns for information on using XML columns in your geodatabase.
• Log file 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 isn't used in Oracle databases.
For more information on how log file tables are used in the geodatabase, see Log file configuration options.
• User interface parameters
User interface parameters begin with UI and indicate whether or not their associated configuration keyword will be available through the ArcGIS user interface.
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.
• 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 LONGRAW 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 need to 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 Oracle data from one storage type to another in the "Geodatabase data storage and schema" section of the help.

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 will need to create another configuration keyword users can specify when they create feature classes that will 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 creating custom keywords, see DBTUNE 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 LONGRAW and one BLOB. Tables in Oracle cannot contain more than one LONGRAW field, so when the second LONGRAW 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 in the "Defining the properties of a geodatabase" section of the help.
• Comments
You can add a COMMENT parameter in the dbtune.sde file if you like 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"



See DBTUNE configuration keywords for information on adding custom keywords.

Configuration parameters specific to geodatabases in Oracle Spatial

• Creating new business tables
ArcSDE uses the B_STORAGE, B_INDEX_ROWID, and B_INDEX_USER storage parameters to store the business table and the nonspatial indexes on the business table.
• Creating Oracle Spatial metadata for new feature classes
The database view USER_SDO_GEOM_METADATA is part of Oracle Spatial, not ArcSDE. It contains metadata about SDO_GEOMETRY columns in existing tables owned by the user. Each user has its own USER_SDO_GEOM_METADATA view. To be indexed and queried, the owner of the table must record metadata for each SDO_GEOMETRY column in USER_SDO_GEOM_METADATA. The ArcSDE clients that create a feature class will choose the metadata for the feature class. Often, these clients accept a configuration keyword corresponding to a parameter group in the DBTUNE table.
The storage parameters that control the metadata for new Oracle Spatial feature classes are the following:

SDO_DIMNAME_<n>
SDO_LB_<n>
SDO_UB_<n>
SDO_TOLERANCE_<n>	
SDO_SRID	




NOTE: If a coordinate reference system is provided during the creation of a feature class, the SDO_SRID parameter is ignored and not written to the USER_SDO_GEOM_METADATA view.

Oracle Spatial permits feature geometries of two, three, or four dimensions in the combinations x/y, x/y/z, x/y/z (measure), or x/y/z/m. Through these storage parameters, ArcSDE allows you to specify metadata for each dimension. The <n> in some parameter names should be replaced by one of the digits 1, 2, 3, or 4, corresponding to the dimension number. If you do not supply these storage parameters, the ArcSDE client application that creates the feature class will determine the name, upper and lower bound (extent), and tolerance of each dimension.
• Creating a spatial index
The DBTUNE parameter SDO_INDEX_SHAPE determines how Oracle Spatial creates the spatial index. ArcSDE appends the contents of this parameter (the configuration string) to the CREATE INDEX statement before submitting the statement to Oracle. The configuration string is inserted into the SQL statement after the PARAMETERS keyword. For example:

CREATE INDEX MY_SP_INDEX ON MY_SP_TABLE(SHAPE)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS ( <configuration string is inserted here> );



The configuration string is a quoted string containing a list of parameter = value elements. There are many parameters that you can specify in the configuration string. To understand the Oracle Spatial index parameters and how they interact, read the applicable sections of the Oracle Spatial User's Guide and Reference.
Notice the differences between the physical storage parameters in the spatial index configuration string and in a business table configuration string (as specified in B_STORAGE). One difference is due to the way Oracle expects these parameters to appear in SQL statements. The Oracle statements are formatted differently, so the configuration strings are formatted differently. Also, not every physical storage parameter used for creating tables is available for creating spatial indexes.

B_STORAGE	"TABLESPACE ORSPBIZ PCTFREE 10 INITRANS 4 STORAGE(INITIAL 512000)"

SDO_INDEX_SHAPE	"tablespace=ORSPIDX initial=512000"



• Verifying polygon rotation
Both ArcSDE and Oracle Spatial expect that exterior polygon boundaries are stored in counterclockwise rotation and that inner boundaries are stored clockwise. If the rotation polygon boundary is not stored with this rotation, the polygon will fail the ArcSDE topographic validation test and will not be sent to the ArcSDE client.
• Sample DBTUNE parameter groups for Oracle Spatial feature classes
This section presents DBTUNE parameter groups that apply to several common scenarios. These samples emphasize the storage parameters for Oracle Spatial feature classes. When designing your own parameter groups, you may want to add parameters to support other needs, such as geometric networks or log files. You could also satisfy these requirements via parameters in the DEFAULTS parameter group.
In some examples, you will see the parameter sdo_indx_dims=2, which specifies how many dimensions should be indexed with an R-tree spatial index. With Oracle9i, the default value is 2, meaning the first two dimensions, x and y. For previous versions of Oracle, the default value was the number of dimensions recorded in USER_SDO_GEOM_METADATA. There were various problems creating R-tree spatial indexes on dimensions other than x and y. For example, Oracle does not support R-tree indexes on the measure dimension. If you are creating R-tree spatial indexes and are using an Oracle 9.2 or prior release, it is recommended you always include this parameter.
If you are not using Oracle Spatial by default, you can create a simple parameter group to create Oracle Spatial feature classes with mostly default settings. The tables and indexes will be created in the user's default tablespace using default physical storage parameters, unless specified otherwise in the DEFAULTS parameter group. The spatial index will be a two dimensional R-tree.

##SDO_GEOMETRY
GEOMETRY_STORAGE		"SDO_GEOMETRY"
SDO_INDEX_SHAPE	"sdo_indx_dims=2"
UI_TEXT	"Oracle Spatial: default settings"
END



With Oracle Spatial, if data is often loaded using a specific spatial reference identifier (SRID), such as the geodetic SRID 8307 (latitude/longitude WGS 84), you can create an expanded version of the previous parameter group. You don't have to specify the upper and lower bounds and tolerance, but you can if you want all your feature classes to have the same metadata for the X and Y dimensions. This is useful if you want to use the feature classes in the same feature dataset. Also, this case specifies that any polygon boundaries with reversed rotation will be reordered before sending them to ArcSDE clients.

NOTE: For geodetic data, the extents are specified in decimal degrees and the tolerances are specified in meters.

##SDO_GEOMETRY_8307
GEOMETRY_STORAGE		"SDO_GEOMETRY"
SDO_INDEX_SHAPE	"sdo_indx_dims=2"
SDO_SRID	8307
SDO_DIMNAME_1	"Lon"
SDO_LB_1	-180.000000
SDO_UB_1	180.000000
SDO_TOLERANCE_1	0.001
SDO_DIMNAME_2	"Lat"
SDO_LB_2	-90.000000
SDO_UB_2	90.000000
SDO_TOLERANCE_2	0.001
UI_TEXT	"Oracle Spatial: WGS84"
END



The following example can be used to load a feature class with an R-tree spatial index into the tablespace ORSPBIZ. The R-tree spatial index will be created in the tablespace ORSPIDX. The ArcSDE client that is loading the data will decide the values for the metadata.

##SDO_GEOMETRY_ORSPBIZ
GEOMETRY_STORAGE		"SDO_GEOMETRY"
B_STORAGE	"TABLESPACE ORSPBIZ"
SDO_INDEX_SHAPE	"tablespace=ORSPIDX sdo_indx_dims=2"
UI_TEXT	"Tablespace ORSPBIZ / ORSPIDX"
END



The following example can be used to load a feature class with a quadtree spatial index having a fixed-size tiling level (tessellation level) of 6. The spatial index will be created in the tablespace ORSPIDX. Commit interval is important for quadtree indexes. It indicates the number of business table records that will be processed before committing the index data. Without it, all the records of the business table are processed before committing the index data. This will cause problems when indexing tables with many records.

NOTE: The parameter sdo_commit_interval is so important that ArcSDE automatically includes it in SQL indexing statements for Oracle Spatial tables even if you do not specify it as part of the SDO_INDEX_SHAPE parameter. It is set to 1,000.

##SDO_GEOMETRY_QT_6
GEOMETRY_STORAGE		"SDO_GEOMETRY"
SDO_INDEX_SHAPE	"tablespace=ORSPIDX sdo_level=6 sdo_commit_interval=1000"
END