How To: Set Storage Parameters Creating an Oracle Table with ST

Summary

When creating a table with an ST_Geometry attribute using Structured Query Language (SQL), one can specify the storage parameters for the binary ST_Geometery points' attribute.

Instructions provided describe how Oracle's CREATE TABLE command provides the ability to set a LOB segment's name and specific properties for the LOB and storage parameters.

Procedure

The following example demonstrates creating a table with the ST_Geometry attribute, named shape, and the ability to define the LOB segment name and the storage clause.

Code:
SQL> CREATE TABLE streets 
  2    (objectid    NUMBER(38) NOT NULL,
  3     name        VARCHAR2(64) NOT NULL,
  4     laddr       NUMBER(9),
  5     raddr       NUMBER(9),
  6     shape       st_geometry)
  7    LOB (shape.points) 
  8    STORE AS streets_shape_seg
  9     (TABLESPACE user_data ENABLE STORAGE IN ROW CHUNK 8K RETENTION CACHE
 10    STORAGE (INITIAL 500M PCTINCREASE 10)
 11    INDEX
 12     (TABLESPACE lob_idx STORAGE (INITIAL 50M PCTINCREASE 10)))
 13    PCTFREE 10 INITRANS 4
 14    TABLESPACE user_data STORAGE (INITIAL 1M PCTINCREASE 0);

Table created.

Lines 1-6: CREATE TABLE statement defining attributes.

Line 7: LOB (shape.points) represents the LOB item shape.points (the attribute named shape which is the ST_Geometry type).

Line 8: STORE AS streets_shape_seg represents the name of the LOB segment created for storing out-of-line LOB attributes.

Line 9: Represents the tablespace where the LOB segment is created. ENABLE STORAGE IN ROW indicates that the binary object should be stored in row when the number of bytes is < 4000. CHUNK 8K represents the size for LOB manipulation: the default size is the segment's block size. RETENTION represents the amount of time updated LOBs are maintained in automatic undo. The length of time is based on the init.ora parameter UNDO_RETENTION. CACHE represents whether the segment block should be in the SGA cache or not.

Line 10: Indicates the LOB segment's storage clause, the initial extent is 500M and each additional extent increases by 10%.

Line 11-12: INDEX represents the tablespace where the LOB segment's index is created. The STORAGE parameter sets the storage clause, the initial extent is 50M and each additional extent increases by 10%.

Line 13: Indicates the street's physical attributes, pctfree 10 defining 10% of the block is free for future inserts and initrans defining the number of initial transaction slots for concurrent transactions accessing the block.

Warning:
The LOB segment name (Line 8) must be unique in the user's schema. No two segments, such as tables or indexes, may share the same name.

Note:
See Oracle's documentation for additional information.

Article ID:000009509

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Related Information

Oracle documentation

Set storage parameters creating an Oracle table with ST_Geometry

Summary

Procedure

Related Information

Discover more on this topic