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.


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.


1. Jens - December 21, 2020

Hi Richard,

Same problem here…
I´m running an ATP DB in the Frankfurt Datacluster.

I tested this example:

I can´t run this:
exec dbms_auto_index_internal.task_proc(true);

If I execute the query several times in the example and wait several 20 Mins the report still shows this:

Executions completed : 0
Executions interrupted : 0
Executions with fatal error : 0

Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: