Sample Maps Load Full Dataset Error: ORA-29861 domain index is marked LOADING/FAILED/UNUSABLE

[ParfaitG]

In APEX 22.1.0, after installing the Sample Maps application from Gallery and running the US States (flat) page, I am attempting to load the full dataset, both states-full.json and airports-full.json which were last touched 2 years ago from this repo (https://github.com/oracle/apex/tree/22.1/sample-apps/sample-maps/), and encounter the following error:

ORA-39861: domain index is marked LOADING FAILED/UNUSABLE...

See fuller technical info:

Technical Info (only visible for developers)

is_internal_error: false
ora_sqlcode: -29861
ora_sqlerrm: ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 56 ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 124 ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2491 ORA-06512: at "ABBREV_BACK.EBA_SAMPLE_MAP_LOAD_DATA", line 38 ORA-06512: at line 2 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 856 ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2452 ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 96 ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 330 ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC", line 414 ORA-06512: at "APEX_220100.WWV_FLOW_EXEC_LOCAL", line 2901 ORA-06512: at "APEX_220100.WWV_FLOW_EXEC", line 2618 ORA-06512: at "APEX_220100.WWV_FLOW_EXEC", line 2654 ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 35 ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 1279 ORA-06512: at "APEX_220100.WWV_FLOW_PLUGIN", line 3093
component.type: APEX_APPLICATION_PAGE_PROCESS
component.id: 1591063652815334462
component.name: Load Full Dataset
error_backtrace:
ORA-06512: at "ABBREV_BACK.EBA_SAMPLE_MAP_LOAD_DATA", line 38
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 856
ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2452
ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 56
ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 124
ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2491
ORA-06512: at "ABBREV_BACK.EBA_SAMPLE_MAP_LOAD_DATA", line 38
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 856
ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2452
ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 96
ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 330
ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC", line 414
ORA-06512: at "APEX_220100.WWV_FLOW_EXEC_LOCAL", line 2901
ORA-06512: at "APEX_220100.WWV_FLOW_EXEC", line 2618
ORA-06512: at "APEX_220100.WWV_FLOW_EXEC", line 2654
ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 35
ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 1279
ORA-06512: at "APEX_220100.WWV_FLOW_PLUGIN", line 3093
ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS", line 170
error_statement:
begin begin
    eba_sample_map_load_data(
        p_load_from          => :P501_SOURCE,
        p_states_file_name   => :P501_STATES_FILE,
        p_airports_file_name => :P501_AIRPORTS_FILE,
        p_proxy_override     => :P501_PROXY_OVERRIDE );
end;
end;

[cczarski-de]

that looks like the Spatial Index appears to be broken on your database. From here, I cannot see the cause for this. You might try:

• navigate to Supporting Objects
• Deinstall Supporting Objects
• Install Supporting Objects again (this should recreate all the Spatial Indexes)

Verify that you get no errors during Supporting objects reinstallation. If you get errors, please share them here.

After that, try to load the "full dataset" again and let us know.

[ParfaitG]

While I could not find the option "Deinstall Supporting Objects", I selected the "Remove All Supporting Objects" on the right hand pane. Afterwards, I selected "Install Supporting Objects" also in right hand pane. I received all green confirmation of successes.

In re-trying the Load Full Dataset, I still received an error, similar to previous one.

Technical Info (only visible for developers)

is_internal_error: false
ora_sqlcode: -29861
ora_sqlerrm: ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 56 ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 124 ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2491 ORA-06512: at "ABBREV_BACK.EBA_SAMPLE_MAP_LOAD_DATA", line 38 ORA-06512: at line 2 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 856 ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2452 ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 96 ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 330 ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC", line 414 ORA-06512: at "APEX_220100.WWV_FLOW_EXEC_LOCAL", line 2901 ORA-06512: at "APEX_220100.WWV_FLOW_EXEC", line 2618 ORA-06512: at "APEX_220100.WWV_FLOW_EXEC", line 2654 ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 35 ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 1279 ORA-06512: at "APEX_220100.WWV_FLOW_PLUGIN", line 3093
component.type: APEX_APPLICATION_PAGE_PROCESS
component.id: 1591063652815334462
component.name: Load Full Dataset
error_backtrace:
ORA-06512: at "ABBREV_BACK.EBA_SAMPLE_MAP_LOAD_DATA", line 38
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 856
ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2452
ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 56
ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 124
ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2491
ORA-06512: at "ABBREV_BACK.EBA_SAMPLE_MAP_LOAD_DATA", line 38
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 856
ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2452
ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 96
ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 330
ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC", line 414
ORA-06512: at "APEX_220100.WWV_FLOW_EXEC_LOCAL", line 2901
ORA-06512: at "APEX_220100.WWV_FLOW_EXEC", line 2618
ORA-06512: at "APEX_220100.WWV_FLOW_EXEC", line 2654
ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 35
ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 1279
ORA-06512: at "APEX_220100.WWV_FLOW_PLUGIN", line 3093
ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS", line 170
error_statement:
begin begin
    eba_sample_map_load_data(
        p_load_from          => :P501_SOURCE,
        p_states_file_name   => :P501_STATES_FILE,
        p_airports_file_name => :P501_AIRPORTS_FILE,
        p_proxy_override     => :P501_PROXY_OVERRIDE );
end;
end;

[cczarski-de]

hmmm ... this is odd - did not see that ever before.

• Is Spatial (SDO_GEOMETRY type) available in your database?
• You might try the following:
  • Go to SQL Commands and drop the Spatial Indexes

drop index eba_sample_map_airports_sx;
drop index eba_sample_map_simplestates_sx;
create index eba_sample_map_states_sx;

• Then load the data
• Then recreate the Spatial Indexes in SQL Commands again

create index eba_sample_map_airports_sx on eba_sample_map_airports(geometry)
indextype is mdsys.spatial_index_v2
parameters( 'layer_gtype=POINT')
/

create index eba_sample_map_simplestates_sx on eba_sample_map_simple_states(geometry)
indextype is mdsys.spatial_index_v2
/

create index eba_sample_map_states_sx on eba_sample_map_states(geometry)
indextype is mdsys.spatial_index_v2
/

Again - so far I have no idea where this comes from. Any specifics about your database environment? Version? Spatial available or not?

regards
-Carsten

[ParfaitG]

Possibly there are limitations in the DBMS environment which is outside of my ownership or management. I can confirm that I have used SDO_GEOMETRY in this database for a fully functioning map in a different APEX application with placemarkers using Lat/Lon coordinates stored in a database table.

SELECT * FROM V$VERSION

From a Burleson post, "Detect Spatial Used and Installed":

select decode(count(*), 0, 'No', 'Yes') c1 from (select 1 from all_sdo_geom_metadata where rownum = 1);

---

Regarding your specific instructions, your last CREATE INDEX command in first code block does not work:

But after running the first two commands to drop the indexes, I get same error in loading the dataset.

Technical Info (only visible for developers)

is_internal_error: false
ora_sqlcode: -29861
ora_sqlerrm: ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 56 ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 124 ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2491 ORA-06512: at "ABBREV_BACK.EBA_SAMPLE_MAP_LOAD_DATA", line 38 ORA-06512: at line 2 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 856 ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2452 ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 96 ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 330 ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC", line 414 ORA-06512: at "APEX_220100.WWV_FLOW_EXEC_LOCAL", line 2901 ORA-06512: at "APEX_220100.WWV_FLOW_EXEC", line 2618 ORA-06512: at "APEX_220100.WWV_FLOW_EXEC", line 2654 ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 35 ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 1279 ORA-06512: at "APEX_220100.WWV_FLOW_PLUGIN", line 3093
component.type: APEX_APPLICATION_PAGE_PROCESS
component.id: 1591063652815334462
component.name: Load Full Dataset
error_backtrace:
ORA-06512: at "ABBREV_BACK.EBA_SAMPLE_MAP_LOAD_DATA", line 38
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 856
ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2452
ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 56
ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 124
ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2491
ORA-06512: at "ABBREV_BACK.EBA_SAMPLE_MAP_LOAD_DATA", line 38
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220100", line 856
ORA-06512: at "APEX_220100.WWV_FLOW_DYNAMIC_EXEC", line 2452
ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 96
ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC_PLSQL", line 330
ORA-06512: at "APEX_220100.WWV_FLOW_CODE_EXEC", line 414
ORA-06512: at "APEX_220100.WWV_FLOW_EXEC_LOCAL", line 2901
ORA-06512: at "APEX_220100.WWV_FLOW_EXEC", line 2618
ORA-06512: at "APEX_220100.WWV_FLOW_EXEC", line 2654
ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 35
ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS_NATIVE", line 1279
ORA-06512: at "APEX_220100.WWV_FLOW_PLUGIN", line 3093
ORA-06512: at "APEX_220100.WWV_FLOW_PROCESS", line 170
error_statement:
begin begin
    eba_sample_map_load_data(
        p_load_from          => :P501_SOURCE,
        p_states_file_name   => :P501_STATES_FILE,
        p_airports_file_name => :P501_AIRPORTS_FILE,
        p_proxy_override     => :P501_PROXY_OVERRIDE );
end;
end;

[cczarski-de]

ah - now I see that. In my previous comment the first three statements were intended as DROP INDEX statements. The idea is:

• drop all the spatial indexes
• then do the "full dataset loading"
• recreate all the indexes.

Thus it should be three times DROP INDEX above. Can you try that, please?

[ParfaitG]

Yes, that worked! With dropping the spatial indexes, I was able to successfully run "full dataset loading" without any error and then I was able to successfully recreate the three indexes. I can now see all state polygons in the US States (flat) page.

If I am not an unusual case, consider adding same above instructions in a README or in APEX application loading data page for future users. Thank you for your patience and help!