Redo generation for Indexes on Global Temporary Tables (GTT)
(Doc ID 848852.1)
Last updated on MARCH 13, 2018
Applies to:
Oracle Database - Enterprise Edition - Version 8.1.5.0 and laterInformation in this document applies to any platform.
This problem can occur on any platform.
***Checked for relevance on 13-Sep-2016***
Symptoms
- Redo generation increase is noticed after an index is created on a global temporary table (GTT)
- Data dictionary views like DBA_INDEXES show a value YES on LOGGING column for indexes created on GTT.
SQL> create global temporary table ADU_USER_TAB_PRIV_T
2 (
3 OWNER VARCHAR2(30),
4 TABLE_NAME VARCHAR2(30),
GRANTOR VARCHAR2(30),
5 6 PRIVILEGE VARCHAR2(40),
7 GRANTABLE VARCHAR2(3),
8 HIERARCHY VARCHAR2(3),
9 GRANTEE VARCHAR2(30)
10 )
11 on commit preserve rows;
Table created.
SQL> create index ADU_ADU_UTPT_IND01 on ADU_USER_TAB_PRIV_T (GRANTEE);
Index created.
SQL> select logging from dba_indexes where index_name like 'ADU_ADU_UTPT_IND01';
LOG
---
YES <<----------------
SQL> select logging from dba_tables where table_name like 'ADU_USER_TAB_PRIV_T';
LOG
---
NO <<--------------------
2 (
3 OWNER VARCHAR2(30),
4 TABLE_NAME VARCHAR2(30),
GRANTOR VARCHAR2(30),
5 6 PRIVILEGE VARCHAR2(40),
7 GRANTABLE VARCHAR2(3),
8 HIERARCHY VARCHAR2(3),
9 GRANTEE VARCHAR2(30)
10 )
11 on commit preserve rows;
Table created.
SQL> create index ADU_ADU_UTPT_IND01 on ADU_USER_TAB_PRIV_T (GRANTEE);
Index created.
SQL> select logging from dba_indexes where index_name like 'ADU_ADU_UTPT_IND01';
LOG
---
YES <<----------------
SQL> select logging from dba_tables where table_name like 'ADU_USER_TAB_PRIV_T';
LOG
---
NO <<--------------------
- Following error is generated when NOLOGGING clause is specified for the CREATE INDEX statement on a GTT
ORA-14451: unsupported feature with temporary table
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |
| This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review. |