|
POST
|
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
12-11-2022
08:00 PM
|
0
|
1
|
2285
|
|
POST
|
please help me to understand. What are the differences between the indexes Axxx_IX1 and Sxxx_idx$ ? The Axxx_IX1 can be seen using this query : SELECT * FROM dba_indexes WHERE table_name = 'your_feature_class'; Here it says that Axxx_IX1 has Index_Type of "domain". The Sxxx_IDX$ can be found using : SELECT * FROM sde.st_geometry_columns WHERE table_name = 'your_feature_class' --where xxx is the GEOM_ID colomn. To see both of them : SELECT table_name,index_id,srid,grid,status,index_name FROM sde.st_geometry_index WHERE table_name = 'your_feature_class' . From this article it says INDEX_NAME is Name of the st_spatial_index (domain index). So I think they are different. But I do not understand the differences and when the A and S indexes are used. Help please.
... View more
12-08-2022
11:55 PM
|
0
|
5
|
2335
|
|
POST
|
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
12-08-2022
08:19 PM
|
0
|
1
|
2222
|
|
POST
|
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
11-29-2022
12:32 AM
|
0
|
1
|
2238
|
|
POST
|
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
11-28-2022
10:11 PM
|
0
|
2
|
2239
|
|
POST
|
Hi, I need to rebuild my spatial indexes using query. This is the query that I am expecting to run : CREATE INDEX SDE.A280_IX1 ON BANGKAIKAPAL_PT_1K(SHAPE) INDEXTYPE IS sde.st_spatial_index PARAMETERS('st_grids=1,3,0 st_srid=32748 tablespace NEW_TBS'); I need to find st_grids value using query. How can I do that ? I have been searching the web and can not find anything. What I want is something similar in finding SRID : "SELECT sde.st_srid (SHAPE) sridcol FROM SDE.STREET;" Thankyou
... View more
11-28-2022
08:50 PM
|
0
|
2
|
1354
|
|
POST
|
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
10-31-2022
12:39 AM
|
0
|
4
|
10817
|
|
POST
|
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
10-19-2022
11:52 PM
|
0
|
1
|
10853
|
|
POST
|
@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
10-03-2022
12:20 AM
|
0
|
3
|
10902
|
|
POST
|
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
09-26-2022
07:38 PM
|
0
|
1
|
10929
|
|
POST
|
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
... View more
09-26-2022
02:15 AM
|
0
|
16
|
13198
|
|
POST
|
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
09-25-2022
08:01 PM
|
0
|
1
|
2067
|
|
POST
|
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
08-29-2022
06:15 AM
|
0
|
1
|
1781
|
| Title | Kudos | Posted |
|---|---|---|
| 1 | 11-25-2024 12:33 AM | |
| 1 | 11-21-2025 12:57 AM | |
| 1 | 11-12-2024 08:22 PM | |
| 1 | 10-13-2024 11:55 PM | |
| 1 | 02-25-2022 05:49 AM |
| Online Status |
Offline
|
| Date Last Visited |
2 weeks ago
|