Global temporary tables (GTT) and indexes

Global temporary tables (GTT) and indexes

In the category frequently asked and answered. The question: Can we create indexes on Global Temporary tables (GTT) on the standby Database?

The answer is yes, but as always: it depends.

I will just answer this with an example. Everything is done on the Read-only Active Data Guard standby database.

So, when you create a temporary table

That works as expected, but when you try to add a user index:

That obviously fails with ORA-16000: database or pluggable database open for read-only access.
Even with the new feature DML Redirection it is not possible. So when you would need this kind of custom indexes on the Global temporary table, you will have to add them in the primary.

However! You CAN have primary key indexes created on the standby on these GTTs.

When you create the Global temporary table like this:

then you can see it has added the primary key index as well

As always, questions, remarks? 
Find me on twitter @vanpupi

Leave a Reply

Your email address will not be published. Required fields are marked *

20 + twenty =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: