jump to navigation

Oracle 19c Automatic Indexing: Currently Broken In Autonomous ATP Databases? (What In The World) December 17, 2020

Posted by Richard Foote in 19c, Automatic Indexing, Autonomous Transaction Processing, Oracle Indexes.
1 comment so far


I’ve been playing with the free tier Oracle Autonomous Databases for quite some time, but unfortunately in recent times, I’ve hit a bit of a snag. The Automatic Index capability appears to be currently broken…

The Automatic Indexing task appears to have stopped running, yes even with the AUTO_INDEX_MODE set to “IMPLEMENT” and with a range of SQL workloads in badly need of new indexes.

I even terminated and created a new Autonomous ATP environment and once all set for Automatic Indexing (by setting AUTO_INDEX_MODE to ‘IMPLEMENT’, AUTO_INDEX_SCHEMA to a number of schemas and running index deficient SQL workloads), still no go, with the Automatic Indexing tasks no longer running.

My environments are specifically the always free Autonomous ATP environments running 19c from the Sydney data centre. 21c is not yet available in Sydney, but 19c is meant to be the stable recommended environment, so…

Note: As it’s the Autonomous Database environments, the ADMIN power user only has restricted privileges and can’t manually run procedures such as dbms_auto_index_internal.task_proc().

I’ve had a look through MOS and was unable to find anything of use.

I’ve communicated with a couple of folks and they also seem to be having similar issues.

If anyone else is having problems running Automatic Indexing in the Autonomous Database environments or have suggestions on how to address these issues, would love to hear from you.


UPDATE: This issue has now been fixed, see Part II for the details.