topic Re: Rebuild Indexes after moving tablespace in Geodatabase Questions

Rebuild Indexes after moving tablespace

yockee — Mon, 26 Sep 2022 12:14:44 GMT

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

Re: Rebuild Indexes after moving tablespace

MarceloMarques — Mon, 26 Sep 2022 19:04:08 GMT

You can see several examples to automate the rebuild index via Oracle SQL PLUS script in my blog posts, see links below, you can also use the Pro Geoprocessing Tool to Rebuild the Indexes, right click the GP Tool Rebuild Indexes and choose batch and drap and drop the featureclasses and tables, you can save the GP Tool Batch to execute later again, you can then further automate the execution with Model Builder or export as an arcpy python script. Rebuild Indexes (Data Management)—ArcGIS Pro | Documentation

However, I am an Oracle DBA hence I prefer Oracle SQL commands to rebuild the indexes ( attribute indexes and spatial indexes ) because I can choose advanced options to rebuild indexes like "PARALLEL" and "ONLINE".

How to move an Enterprise Geodatabase with the Oracle Data Pump Utility

Note: see "7.6.Change data owner schema storage" and "8.5.Rebuild Indexes"

Oracle eGDB SDE Repo Upgrade Using Oracle Restore Point

The option to delete the attribute index Rxxx_SDE_ROWID_UK is grayed out because it is the unique index of the objectid field.

For more database best practices visit my blog page.

Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

Mapping and Charting Enterprise Databases - Esri Community

Re: Rebuild Indexes after moving tablespace

yockee — Tue, 27 Sep 2022 02:38:43 GMT

Hi @MarceloMarques
The "Rxxxx_SDE_ROWID_UK" is greyed out and I see it's status is "UNUSABLE". If its Unusable, how can I make it usable again ? I want to delete and rebuild it in order to make it usable again..

Thanks

Re: Rebuild Indexes after moving tablespace

MarceloMarques — Tue, 27 Sep 2022 15:25:26 GMT

You will need to manually drop the index in Oracle via SQL Plus or Oracle SQL Developer then open ArcGIS Pro and rebuild the index.
Error: ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE (esri.com)

Re: Rebuild Indexes after moving tablespace

yockee — Mon, 03 Oct 2022 07:20:40 GMT

@MarceloMarques Thanks..
Any Idea how to rebuild spatial index using query?
I am able to build attribute index using query, but I just can not find any articles on building spatial index using query

Re: Rebuild Indexes after moving tablespace

MarceloMarques — Mon, 03 Oct 2022 16:11:27 GMT

You rebuild the spatial index the same way you rebuild an attribute index via SQL, there is no difference in the syntax, example: "ALTER INDEX <index_name> REBUILD ONLINE PARALLEL 8 TABLESPACE <tbs_name>;"

Re: Rebuild Indexes after moving tablespace

yockee — Thu, 20 Oct 2022 07:06:30 GMT

Additional question.
1. Actually, a Feature Class has 3 indexes associated with it : Lob (SYS_IL*), Normal (R*_SDE_ROWID) and Spatial (A*_IX1). For the Lob index, do I need to move it as well ? There is no indication that the Lob Index is unusable. How can I move the Lob Index anyway ? when I run this query "ALTER INDEX SYS_IL0000196018C00023$$ REBUILD TABLESPACE DATAUSER1" There is error : "SQL Error [2243] [42000]: ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option"

2. This command fails for moving spatial index : ALTER INDEX SDE.A280_IX1 REBUILD PARALLEL 8 NOLOGGING TABLESPACE DATAUSER1; Any other alternative command ?

Re: Rebuild Indexes after moving tablespace

MarceloMarques — Thu, 20 Oct 2022 14:19:17 GMT

Read my white paper guide_books_pro_3x - OneDrive (sharepoint.com) - 11. Verifying the tablespace storage

List large objects (LOBS) by table.

SELECT * FROM USER_LOBS WHERE SEGMENT_NAME LIKE 'SYS_LOB%' ORDER BY TABLE_NAME;

If you are moving large objects (LOB)

read Oracle Support Doc ID 130814.1 How to move LOB Data to Another Tablespace.

support.oracle.com

How To Move LOB Data To Another Tablespace (Doc ID 130814.1)

select chunk 
from dba_lobs
where table_name =upper('ContainsLOB_tab');
  2    3  
     CHUNK
----------
      8192

ALTER TABLE ContainsLOB_tab
MOVE LOB(c) STORE AS (
TABLESPACE users CHUNK 17000
);
/

SQL> select chunk 
from dba_lobs
where table_name =upper('ContainsLOB_tab');

How To Move A LOB Index To Another Tablespace ? (Doc ID 871203.1)

--  1. find out the column name
select column_name from dba_lobs where index_name='SYS_IL...$$' ;
--  2. move the lob (and the lob index)
ALTER TABLE <table name> MOVE LOB(<column name from above>)
STORE AS <new lob segment name>
(tablespace <new tablespace> storage (<storage attributes>));

SQL> select chunk 
from dba_lobs
where table_name =upper('ContainsLOB_tab');

* You need a valid Oracle Support Identifier (SI) to access the Oracle Support Articles.

Re: Rebuild Indexes after moving tablespace

yockee — Mon, 31 Oct 2022 07:39:42 GMT

Hi @MarceloMarques

I try to move the spatial index into new tablespace (the same with the feature class's tablespace). Here is what I do :

- DROP INDEX SDE.A276_IX1 FORCE; -- I have to drop it otherwise it can not be rebuild

- CREATE INDEX SDE.A276_IX1 ON FEATURECLASS_LN_1K(SHAPE) INDEXTYPE IS sde.st_spatial_index
PARAMETERS('st_grids=840 st_srid=32748 ST_COMMIT_ROWS = 10000 PCTFREE 0 INITRANS 4 TABLESPACE USERDATA1'); --recreating the index because ALTER index REBUILD does not work

But when I check using query : "SELECT * FROM dba_indexes WHERE rownum < 5 AND table_name = 'FEATURECLASS_LN_1K'", the TABLESPACE_NAME = NULL and STATUS = VALID.

How can I create spatial index via query correctly ? Why is the tablespace_name is NULL ?

Re: Rebuild Indexes after moving tablespace

MarceloMarques — Mon, 31 Oct 2022 17:00:59 GMT

--DOMAIN INDEX--

SELECT * FROM user_indexes where index_name = 'A241_IX1';
/*
INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, UNIQUENESS, COMPRESSION, PREFIX_LENGTH, TABLESPACE_NAME
A241_IX1 DOMAIN GIS STREETS TABLE NONUNIQUE DISABLED <null> <null>
*/

"ALTER INDEX <index_name> REBUILD ONLINE PARALLEL 8 TABLESPACE <tbs_name>;"

ALTER INDEX GIS.A241_IX1 REBUILD ONLINE PARALLEL 8 TABLESPACE GIS_SINDEX;

/*
Error starting at line : 7 in command -
ALTER INDEX GIS.A241_IX1 REBUILD ONLINE
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.
*/

--My Oracle Support: How To Validate/Rebuild Domain Index (ORA-29871: invalid alter option for a domain index) (Doc ID 1580967.1)
/*
Use the following syntax to rebuild the Domain Index:

alter index HR.HR_LOCATIONS_SPT rebuild;

See the explanation for the ORA-29871 error below:

The command ALTER INDEX rebuild online is not a valid command for a domain index.

The modification of indexes is subject to the following restrictions:

-If index is a domain index, then you can specify only the PARAMETERS clause, the RENAME clause, the rebuild_clause (with or without the PARAMETERS clause),
the parallel_clause, or the UNUSABLE clause. No other clauses are valid.

- You cannot alter or rename a domain index that is marked LOADING or FAILED. If an index is marked FAILED, then the only clause you can specify is REBUILD.

-- Documentation about Domain Indexes: https://docs.oracle.com/en/database/oracle/oracle-database/19/addci/building-domain-indexes.html#GUID-7E2F4550-36F8-4417-9983-B4DE8277BB89

ALTER INDEX GIS.A241_IX1 REBUILD;
/*
Index GIS.A241_IX1 altered.
*/

Re: Rebuild Indexes after moving tablespace

yockee — Tue, 29 Nov 2022 06:28:27 GMT

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.

Re: Rebuild Indexes after moving tablespace

yockee — Tue, 29 Nov 2022 08:51:20 GMT

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;

Re: Rebuild Indexes after moving tablespace

MarceloMarques — Tue, 29 Nov 2022 16:19:42 GMT

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.

Re: Rebuild Indexes after moving tablespace

yockee — Fri, 09 Dec 2022 06:25:07 GMT

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.

Re: Rebuild Indexes after moving tablespace

MarceloMarques — Fri, 09 Dec 2022 19:13:44 GMT

@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#GUID-E370B5E4-BAC0-49C6-B17D-830B3A507FB4

--------------------------------------------------------------------------------------------------------
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.

Re: Rebuild Indexes after moving tablespace

yockee — Sat, 10 Dec 2022 00:33:23 GMT

thankyou so much. that clarifies it.. 🙂

Re: Rebuild Indexes after moving tablespace

MarceloMarques — Mon, 12 Dec 2022 17:16:58 GMT

You are welcome!