English

FAQ: Is there an example of dbtune settings using Oracle Spatial?

Question

Is there an example of dbtune settings using Oracle Spatial?

Answer

Here is an example of DBTUNE settings that could be used when loading data by way of ArcSDE into Oracle Spatial format.

Note the following:

--Use of Locally Managed uniform extents.
The data are being loaded into tablespaces that were created using Locally Managed Uniform extents; therefore, all settings in relation to table extents were deliberately omitted from the dbtune string. For more details on this, refer to Oracle's White Paper "How to Stop Defragmenting and Start Living", in Related Information, below.

--Indexes and data are separated into different tablespaces, as per the Oracle Open Flexible Architecture (OFA) recommendations and the White Paper mentioned above.

--Verify the setting: SDO_INDEX_SHAPE "tablespace=SDE_SP_IDX".
This ensures the RTREE index is created in the intended tablespace. The syntax is different, using '='. The string in quotes is passed to Oracle, and defined by Oracle Spatial.

--Use of RTREE index.
In the majority of cases, Oracle would recommend the use of an RTREE index.

--Set the upper and lower bounds, and the dimension names to ensure the meta is written to the XXX_SDO_GEOM_METADATA tables.

--Make sure the appropriate SDO_SRID value is set. This is obtained from the MDSYS.CS_SRS table.

##SDO_TAS
ATTRIBUTE_BINARY "LONGRAW"
GEOMETRY_STORAGE "SDO_GEOMETRY"
A_INDEX_ROWID "TABLESPACE SDE_IDX NOLOGGING PCTFREE 10 INITRANS 4"
A_INDEX_SHAPE "TABLESPACE SDE_IDX NOLOGGING PCTFREE 10 INITRANS 4"
A_INDEX_STATEID "TABLESPACE SDE_IDX NOLOGGING PCTFREE 10 INITRANS 4"
A_INDEX_USER "TABLESPACE SDE_IDX NOLOGGING PCTFREE 10 INITRANS 4"
A_STORAGE "TABLESPACE SDE_DATA PCTFREE 10 PCTUSED 90 INITRANS 4"
B_INDEX_ROWID "TABLESPACE SDE_IDX NOLOGGING PCTFREE 10 INITRANS 4"
B_INDEX_SHAPE "TABLESPACE SDE_IDX NOLOGGING PCTFREE 10 INITRANS 4"
B_INDEX_USER "TABLESPACE SDE_IDX NOLOGGING PCTFREE 10 INITRANS 4"
B_STORAGE "TABLESPACE SDE_DATA PCTFREE 10 PCTUSED 90 INITRANS 4"
D_INDEX_DELETED_AT "TABLESPACE SDE_IDX NOLOGGING PCTFREE 10 INITRANS 4"
D_INDEX_STATE_ROWID "TABLESPACE SDE_IDX NOLOGGING PCTFREE 10 INITRANS 4"
D_STORAGE "TABLESPACE SDE_DATA PCTFREE 10 PCTUSED 90 INITRANS 4"
SDO_COMMIT_INTERVAL 500
SDO_DIMNAME_1 "X"
SDO_DIMNAME_2 "Y"
SDO_INDEX "RTREE"
SDO_INDEX_SHAPE "tablespace=SDE_SP_IDX"
SDO_LB_1 0.000000
SDO_LB_2 5000000.000000
SDO_SRID 81936
SDO_TOLERANCE_1 0.000500000000
SDO_TOLERANCE_2 0.000500000000
SDO_UB_1 1000000.000000
SDO_UB_2 6000000.000000
SDO_VERIFY "TRUE"
END

Note:
As of the ArcSDE 9.x releases, the SDO_VERIFY parameter in any dbtune keyword is now obsolete and cannot be used.

Related Information