Latest Contributions by yockee

Many thanks for the explanation. Best regard ... View more

Thanks. Nice insight. But, how come I find the Axx table even though it's not registered as versioned ? Maybe it **was** registered as Versioned before and now it's not. Is it possible? ... View more

thankyou so much. that clarifies it.. 🙂 ... View more

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

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; ... View more

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

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 ?  ... View more

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 ? ... View more

@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 ... View more

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

Hi, I want to move ALL user data into different tablespace, separating them from SDE System files. I just want to move the base table, without moving the indexes etc.  is doing it via ArcSDE Configuration keyword the same as issuing this particular command : ALTER TABLE SDE.ROAD_DATA MOVE TABLESPACE  DATAUSER1; ?  I have done a little research, by issuing that ALTER TABLESPACE command, all the functionality are still normal, such as : editing from arcgis pro, updating attribute via feature service and editing the shape via feature service, also all the viewing (pan, zoom), select from Arcgis pro are still working normally. Are these indicators sufficient to qualify what I am doing is correct ? many thanks @mody_buchbinder @George_Thompson @KimberlyGarbade    Thanks   ... View more

So, if the reason of separating tablespace is based on avoiding the system table to fill up, then it is not exactly correct because the tablespace, where system table resides, can be set to automatically increment its size. Correct ? ... View more