Restored database snapshot to perform some testing. For reasons unknown, after restoring the snapshot the SPATIAL option had to be removed/reapplied to enable Spatial features as all the Spatial schemas were missing. Any table containing a field of type SDO_GEOMETRY is invalid across all schemas, as well as a number in the MDSYS and PUBLIC. Querying a table yields an error e.g.
ORA-04063: table "EXAMPLE_SCHEMA.EXAMPLE_TABLE" has errors
04063. 00000 - "%s has errors"
*Cause: Attempt to execute a stored procedure or use a view that has
errors. For stored procedures, the problem could be syntax errors
or references to other, non-existent procedures. For views,
the problem could be a reference in the view's defining query to
a non-existent table.
Can also be a table which has references to non-existent or
inaccessible types.
*Action: Fix the errors and/or create referenced objects as necessary.
Error at Line: 1 Column: 34
MDSYS.SDO_GEOMETRY type is in an invalid state, but compiling yields this error:
Error(1,1): ORA-02303: can't drop or replace a type with type or table dependents. SQLDev advises setting "Drop Type Force" preference
I don't understand why the SPATIAL schemas aren't applied immediately upon restoration. You literally have to remove the option from the option group and re-add it to have them reinstalled. But, more importantly, why are all those objects invalid? The result is an unusable database.
This doesn't exactly inspire confidence in RDS snapshots.
AWS OFFICIALUpdated a year ago
