Hi Stefan,
Using Golgengate 12.2.0.2, it replicates all the hidden columns, including
System generated. Even "defgen" utility is able to get details of all such
hidden columns.
However, I came across Doc ID 2292517.1, which helped me fix my issue. To
implement the fix, I'd to upgrade the Extract process from Classic to
Integrated.
And rest now the trail files no longer contain hidden columns. Although, defgen
still reflects them, but I manually removed those selected columns and
everything is in good shape.
Thanks and regards,
Sourav Biswas
________________________________
From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
Sent: Thursday, January 17, 2019 5:15:47 PM
To: biswas.sourav@xxxxxxxxxxx; Oracle Mailinglist
Subject: Re: How to disable Extended Statistics in Oracle 12c
This sounds like a Goldengate bug to me. These columns can't and shouldn't be
dealt with during replication, at least not that I could see it make any sense.
They're internal and maintained by Oracle.
Have you tried filing an SR against GoldenGate ?
Stefan
On Thu, Jan 17, 2019 at 6:01 PM Sourav Biswas
<biswas.sourav@xxxxxxxxxxx<mailto:biswas.sourav@xxxxxxxxxxx>> wrote:
Hi Jonathan,
Thanks for your prompt reply.
We have a Goldengate(12.2.0.2) setup, that replicates these tables to
Abinitio(Using Goldengate BigDataAdapter), which further is consumed by Hadoop.
Now, when we have Hidden Columns on Goldengate trailfiles, there respective
processes fail as they are not able to match data. In case we have to stick
with DB hidden columns, then the application team need to re-write a lot of
codes to map all cols with correct data type. In particular these are SIEBEL
tables, which have huge number columns in the tables.
Best Regards,
Sourav Biswas
+91-9650017306
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>>
Sent: Thursday, January 17, 2019 4:08 PM
To: Oracle Mailinglist
Subject: Re: How to disable Extended Statistics in Oracle 12c
When you have a descending column in an index Oracle will ALWAYS create a
column that is hidden, virtual, and not user_generated. It takes no space in
the table segment, only a little space in the data dictionary so that Oracle
can hold its definition and gather stats on it.
I am curious to know why you think you need to get rid of it.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Sourav Biswas <biswas.sourav@xxxxxxxxxxx<mailto:biswas.sourav@xxxxxxxxxxx>>
Sent: 17 January 2019 10:28
To: Oracle Mailinglist
Subject: How to disable Extended Statistics in Oracle 12c
Hi All,
I am using 12.1.0.2.0 database. Whenever, I create a Function based index, it
creates a hidden column for extended statistics.
Ex:
SQL> CREATE UNIQUE INDEX "SIEBEL"."TEMP_CTLG_CAT_U1" ON
"SIEBEL"."TEMP_CTLG_CAT" ("NAME", "CTLG_ID", "EFF_END_DT" DESC, "CONFLICT_ID")
TABLESPACE "SBLINDEX";
Index created.
SQL> select * from dba_stat_extensions where table_name in ('TEMP_CTLG_CAT');
OWNER TABLE_NAME EXTENSION_NAME EXTENSION
CREATO DROPPABLE
---------- -------------------- ------------------------------
------------------------------------------------------------------ ------
----------
SIEBEL TEMP_CTLG_CAT SYS_NC00053$ ("EFF_END_DT")
SYSTEM NO
And when I drop this function based index, the extention name also drops:-
Ex:
SQL> drop index SIEBEL.TEMP_CTLG_CAT_U1;
Index dropped.
SQL> select * from dba_stat_extensions where table_name in ('TEMP_CTLG_CAT');
no rows selected
I tried using "_optimizer_enable_extended_stats" to FALSE, but the behaivior
didn't change:-
SQL> alter system set "_optimizer_enable_extended_stats"=FALSE scope=both
sid='*';
Please suggest whether there is a way in oracle 12c, to create function based
indexes and avoid these extended statistics and automatic creation of hidden
columns.
Best Regards,
Sourav Biswas
+91-9650017306
--
//www.freelists.org/webpage/oracle-l
--
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net<http://zztat.net/> | @zztat_oracle |
fb.me/zztat<http://fb.me/zztat> | zztat.net/blog/<http://zztat.net/blog/>