Rebuild Indexes after moving tablespace

[yockee]

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

[yockee]

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.

[yockee]

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;

[MarceloMarques]

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. 

[yockee]

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.

[MarceloMarques · Accepted solution]

@yockee 

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.

[yockee]

thankyou so much. that clarifies it.. 🙂

[MarceloMarques]

You are welcome!