Hi,
I am using Oracle 19c and ArcGIS Enterprise 10.9
Recenty I move some Feature Class into another tablespace. I observe the indexes using query :
SELECT table_name, index_name, index_type, status, NUM_ROWS
FROM DBA_INDEXES WHERE STATUS='UNUSABLE' AND owner = 'SDE'
The Normal and Domain indexes become "unusable". I know I can rebuild Indexes using ArcGIS Pro but I think its to tiresome becasue I have 1000 feature classes. I prefer using phyton script or query / plsql. Is there anyone here can help or at least point me to a direction?
Moreover, I tried to delete the attribute Index. But the button is greyed out. Why ? I can rebuild the spatial index just fine. See the picture below:
How can I delete and then rebuild the Attribute Index again with the same name (or with deifferent name, its fine) ?
Thanks
Solved! Go to Solution.
I still can not move my spatial index storage to a new place.
Here is what I do :
- Drop the index: DROP INDEX SDE.A280_IX1 FORCE;
- Run this query : "CREATE INDEX SDE.A280_IX1 REBUILD PARAMETERS('st_grids=1,3,0 st_srid=32748 tablespace=NEWTBS');"
- Run : ALTER INDEX A280_IX1 rebuild Success. But this does not changed the tablespace location to a new location
- Run : ALTER INDEX A280_IX1 rebuild tablespace NEWTBS online; -- Error : ORA-29871: invalid alter option for a domain index
- Run : ALTER INDEX A280_IX1 tablespace NEWTBS; --Error: ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
- Run : ALTER INDEX A276_IX1 REBUILD MOVE TABLESPACE NEWTBS; --ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
When I query "SELECT * FROM dba_indexes WHERE rownum < 5 AND table_name = 'STREET'", the tablespace = NULL and Status = Valid
What am I missing? Are there any legitimate ways of doing it correctly?
Thanks.
UPDATE: It turns out that the default tablespace name is always NULL. Interesting !
Here is what I do :
- Observe the original tablespace : SELECT * FROM user_indexes where index_name = 'A346_IX1' --the tablespacename = NULL
- Drop it : DROP INDEX A346_IX1 FORCE; --success
- Recreate it : CREATE INDEX SDE.A280_IX1
ON STREET(SHAPE)
INDEXTYPE IS sde.st_spatial_index
PARAMETERS('st_grids=1,3,0 st_srid=32748 tablespace NEWTBS'); --success
- Observer again once more : SELECT * FROM user_indexes where index_name = 'A346_IX1' ----the tablespacename = NULL too
The result of the 2 observations are EXACTLY THE SAME.
Question 1 :
But why the default tablesapce is always NULL? Where are all the spatial indexes are stored then?
Moreover, I read article here : https://support.esri.com/en/technical-article/000009505 . It says that the spatial index's name follows this pattern : S<geom_id>_IDX$. If I follow this, then my feature class has spatial index name : S1244_idx$.
So, now I end up with 2 spatial indexes : A346_IX1 vs S1244_idx$.
Question 2 : Which one is the correct spatial index ?
Then I change the tablespace location using : ALTER TABLE "S1244_IDX$" MOVE TABLESPACE NEWTBS; ---success
Check if it works : SELECT tablespace_name
FROM user_indexes
WHERE index_name = 'S1244_IDX$'; -- tablespace is NULL.
Note : I find A346_IX1 with query :
SELECT table_name, index_name, column_name
FROM dba_ind_columns
WHERE table_owner='SDE'
AND table_name in
('STREET')
order BY table_name, column_position;
if you have a featureclass registered as traditional versioning then your featureclasses has the Base Table, Adds Table and Deletes Table, and a Spatial Index will be added to the A table and to the B Table, hence you have 2 Spatial Indexes. Oracle displays the tablespace name as null because the index_type is a domain index, but if you check the tablespace size where the indexes are located you will notice the indexes are in the correct tablespace. I hope this clarifies.
I read here. It says : "A feature class created using ST_Geometry storage 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."
Based on that article, so I am thinking that those 2 indexes are actually the same. Am I wrong?
But I think , the A<n>_IX and S<n>$_IX are 2 different entities.
Index "A<n>_IX" can be found on "dba_ind_columns" whereas the "S<n>$_IX" can be found on "user_indexes"
The A<n>_IX can not be moved straight away. It has to be dropped and then recreate. But still the tablespace_name shows NULL.
Whereas the S<n>$_IX can be moved straight away using ALTER TABLE "S<id>_IDX$" MOVE TABLESPACE NEWTBS1;
Should I move both the A<n>_IX and S<n>$_IX ?
And moreover, the Feature Class is not registered as versioned.
I am just confused and still learning to understand it fully. Please pardon me.
Question: Based on that article, so I am thinking that those 2 indexes are actually the same. Am I wrong? But I think , the A<n>_IX and S<n>$_IX are 2 different entities.
Answer: they are 2 different indexes.
Question: Should I move both the A<n>_IX and S<n>$_IX ?
Answer: yes
Example:
--------------------------------------------------------------------------------------------------------
--REBUILD INDEXES
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
--TABLE STREETS--
--------------------------------------------------------------------------------------------------------
ALTER INDEX A241_IX1 REBUILD TABLESPACE GIS_BINDEX ONLINE PARALLEL 4; --index_type=DOMAIN
/*
Error report -
ORA-29871: invalid alter option for a domain index
29871. 00000 - "invalid alter option for a domain index"
*Cause: The user specified an invalid option for altering a domain index.
*Action: Reissue the alter statement with a valid option.
*/
--GENERATE THE INDEX SQL DDL THEN DROP THE INDEX AND TRY TO RECREATE ON ANOTHER TABLESPACE!!!
DROP INDEX "GIS"."A241_IX1";
--Index "GIS"."A241_IX1" dropped.
CREATE INDEX "GIS"."A241_IX1" ON "GIS"."STREETS" ("SHAPE")
INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" PARAMETERS ('ST_GRIDS=6000:0:0 ST_SRID=300003')
TABLESPACE "GIS_BINDEX";
/*
Error report -
ORA-29850: invalid option for creation of domain indexes
29850. 00000 - "invalid option for creation of domain indexes"
*Cause: The user specified an invalid option like ASC, DESC, SORT or a
parallel clause, partitioning clause or physical attributes clause.
*Action: Choose one of the valid clauses for creation of domain indexes.
*/
DROP INDEX "GIS"."A241_IX1";
--add the tablespace as parameter
CREATE INDEX "GIS"."A241_IX1" ON "GIS"."STREETS" ("SHAPE")
INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" PARAMETERS ('ST_GRIDS=6000:0:0 ST_SRID=300003 TABLESPACE=GIS_BINDEX');
/*
Error report -
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-02216: tablespace name expected
ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 1425
29855. 00000 - "error occurred in the execution of ODCIINDEXCREATE routine"
*Cause: Failed to successfully execute the ODCIIndexCreate routine.
*Action: Check to see if the routine has been coded correctly.
*/
DROP INDEX "GIS"."A241_IX1";
--add the tablespace as parameter
CREATE INDEX "GIS"."A241_IX1" ON "GIS"."STREETS" ("SHAPE")
INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" PARAMETERS ('ST_GRIDS=6000:0:0 ST_SRID=300003 TABLESPACE GIS_BINDEX');
--Index "GIS"."A241_IX1" created. --Works!!! but do not think it uses the tablespace parameter !!!
DROP INDEX "GIS"."A241_IX1";
CREATE INDEX "GIS"."A241_IX1" ON "GIS"."STREETS" ("SHAPE")
INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" PARAMETERS ('ST_GRIDS=6000:0:0 ST_SRID=300003');
--Index "GIS"."A241_IX1" created.
--CONCLUSION: CANNOT MOVE A DOMAIN INDEX !!!
--Building Domain Indexes
https://docs.oracle.com/en/database/oracle/oracle-database/19/addci/building-domain-indexes.html#GUI...
--------------------------------------------------------------------------------------------------------
ALTER INDEX R290_SDE_ROWID_UK REBUILD TABLESPACE GIS_BINDEX ONLINE PARALLEL 4; --index_type=normal
--Index R290_SDE_ROWID_UK altered.
--------------------------------------------------------------------------------------------------------
--TABLE A290--
--------------------------------------------------------------------------------------------------------
ALTER INDEX A290_PK REBUILD TABLESPACE GIS_AINDEX ONLINE PARALLEL 4; --index_type=normal
--Index A290_PK altered.
ALTER INDEX A241_IX1_A REBUILD TABLESPACE GIS_AINDEX ONLINE PARALLEL 4; --index_type=DOMAIN
--CONCLUSION: CANNOT MOVE A DOMAIN INDEX !!! SEE EXAMPLE ABOVE WITH DETAILS !!!
ALTER INDEX A290_STATEID_IX1 REBUILD TABLESPACE GIS_AINDEX ONLINE PARALLEL 4; --index_type=normal
--Index A290_STATEID_IX1 altered.
--TABLE D290--
ALTER INDEX D290_PK REBUILD TABLESPACE GIS_DINDEX ONLINE PARALLEL 4; --index_type=normal
--Index D290_PK altered.
ALTER INDEX D290_IDX1 REBUILD TABLESPACE GIS_DINDEX ONLINE PARALLEL 4; --index_type=normal
--Index D290_IDX1 altered.
--TABLE S349_IDX$--
ALTER INDEX S349$_IX1 REBUILD TABLESPACE GIS_SINDEX ONLINE PARALLEL 4; --index_type=IOT
/*
Error report -
ORA-28650: Primary index on an IOT cannot be rebuilt
28650. 00000 - "Primary index on an IOT cannot be rebuilt"
*Cause: An attempt is made to issue alter index rebuild on IOT-TOP
*Action: Use Alter table MOVE to reorganize the table(IOT)
*/
ALTER TABLE S349_IDX$ MOVE TABLESPACE GIS_SINDEX PARALLEL 4;
--Table S349_IDX$ altered.
ALTER INDEX S349$_IX2 REBUILD TABLESPACE GIS_SINDEX ONLINE PARALLEL 4; --index_type=normal
--Index S349$_IX2 altered.
I hope this clarifies.
thankyou so much. that clarifies it.. 🙂
You are welcome!