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

[MarceloMarques]

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

[yockee]

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

[MarceloMarques]

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)

[yockee]

@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

[MarceloMarques]

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>;"

[yockee]

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 ?

[MarceloMarques]

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.

[yockee]

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 ? 

[MarceloMarques]

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

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