Re: Silly question about GENERATED column of DBA_INDEXES
- From: "Yong Huang" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "yong321" for DMARC)
- To: Oracle-l Digest Users <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 13 Aug 2016 14:39:21 +0000 (UTC)
I cannot get the "generated" column on user/all/dba _ indexes
to take on any value other than 'N'.
Hi Norm,
I have no problem getting a 'Y' value for "generated":
SQL> create table t (x int primary key);
Table created.
SQL> select index_name, generated from user_indexes where table_name = 'T';
INDEX_NAME G
------------------------------ -
SYS_C00283437 Y <-- Here it is!
SQL> alter table t drop constraint SYS_C00283437;
Table altered.
SQL> alter table t add constraint pk_t primary key (x);
Table altered.
SQL> select index_name, generated from user_indexes where table_name = 'T';
INDEX_NAME G
------------------------------ -
PK_T N <-- I named it "PK_T"
SQL> select * from v$version where rownum = 1;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
I wrote about the generated column of dba_objects, not dba_indexes. But the
decision on its 'Y' and 'N' values may be about the same.
The GENERATED column indicates whether the name is system generated. If both
OBJECT_NAME and SUBOBJECT_NAME have values, GENERATED is about SUBOBJECT_NAME.
However, this is not completely reliable. If part of the name is
system-generated and part of the name is specified by you, this column could be
either 'Y' or 'N'. For example, although names of the secondary indexes created
as a result of a new CTXSYS.CONTEXT text index are considered system-generated,
none of the secondary objects created as a result of a new CTXSYS.CTXCAT index
are. In fact, even a completely generated name may be marked as 'N' - the name
of a journal table used during online index rebuild is not considered
generated, although the interim IOT TOP index is. The query below will leave a
completely system-generated type named like 'SYSTP%==', and yet DBA_OBJECTS
does not consider it as generated.
...(
http://yong321.freeshell.org/computer/UncommonSenseAboutCommonObjects.html)
Yong Huang
Other related posts: