Frequently asked question
Should the Oracle LOB segment's index name be specified when creating an ST_Geometry attribute?
Answer
Oracle is deprecating the lob_index_clause name clause used when creating an Oracle LOB attribute.Oracle's MetaLink Note: 159995.1 contains the following announcement:
"lob_index_clause
This clause is deprecated as of Oracle8i. Oracle generates an index for each LOB column. Oracle names and manages the LOB indexes internally. Although it is still possible for you to specify this clause, Oracle Corporation strongly recommends that you no longer do so. In any event, do not put the LOB index in a different tablespace from the LOB data."
The example below creates a streets table with a ST_Geometry attribute and defines the storage clause option for the LOB segment and specifies the LOB segment's index 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 lob_seg ENABLE STORAGE IN ROW CHUNK 8K RETENTION CACHE
10 STORAGE (INITIAL 500M PCTINCREASE 10)
11 INDEX
12 (TABLESPACE lob_seg STORAGE (INITIAL 50M PCTINCREASE 10)))
13 PCTFREE 10 INITRANS 4
14 TABLESPACE user_data STORAGE (INITIAL 1M PCTINCREASE 0);
Table created.
Because Oracle no longer recommends setting the index clause, the above CREATE TABLE statement should be executed without the index 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 lob_seg ENABLE STORAGE IN ROW CHUNK 8K RETENTION CACHE
10 STORAGE (INITIAL 500M PCTINCREASE 10))
11 PCTFREE 10 INITRANS 4
12 TABLESPACE user_data STORAGE (INITIAL 1M PCTINCREASE 0);
Table created.
Note:
See Oracle's documentation in the Related Information below for additional information.
Article ID:000009517
Software:
- Legacy Products
Receive notifications and find solutions for new or common issues
Get summarized answers and video solutions from our new AI chatbot.
Related Information
Discover more on this topic
Search for related information
Find training related to this topic
Explore ideas and give feedback
Get help from ArcGIS experts
Download the Esri Support App