Thanks for sharing note, Sourav.
However, interestingly note mentions somethings odd, though.
"The parameter is valid only for integrated extract(IE) and it do not have any
effect for Classic Extract because **CE can't mine hidden columns.**"
Thanks,
Yogi | Technical Consultant - Databases | Fidelity International
5th Floor, Building 9, Candor Tech Space,
Sector 48, Gurgaon 122 002, Haryana, India.
T: +91 124 615 3656 | I: 8 779 3656 | E:
yogesh.tiwari@xxxxxxx<mailto:yogesh.tiwari@xxxxxxx>
Disclaimer: The information transmitted is intended for the person or entity to
which it is addressed and may contain confidential, privileged or copyrighted
material or attorney work product. If you receive this in error, please contact
the sender and delete the material from any system. Any unauthorised copying,
disclosure or distribution of the material in this e-mail is strictly
forbidden. Any comments or statements made are not necessarily those of
Fidelity. All e-mails may be monitored or recorded.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Sourav Biswas
Sent: 17 January 2019 21:47
To: Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>; Stefan Knecht
<knecht.stefan@xxxxxxxxx>
Subject: Re: How to disable Extended Statistics in Oracle 12c
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<mailto:knecht.stefan@xxxxxxxxx>>
Sent: Thursday, January 17, 2019 5:15:47 PM
To: biswas.sourav@xxxxxxxxxxx<mailto: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<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.freelists.org_webpage_oracle-2Dl&d=DwMFAg&c=SsZxQMfaWJ1sSVfloc5FVGba8BA_qR4Jzdt8ol2oSPA&r=z73EKtGMkOyHMZwSjVYW896tQVgTZQlAucPsWFx6Th0&m=4GJkWbcUMGxyP6PhIVRGGShK3I_JWNrxKiSMj5DrjNg&s=SqAZzjeRR3ko20LK622klfpfzL_NWEWd6jpepWI7tuc&e=>
--
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at
zztat.net<https://urldefense.proofpoint.com/v2/url?u=http-3A__zztat.net_&d=DwMFAg&c=SsZxQMfaWJ1sSVfloc5FVGba8BA_qR4Jzdt8ol2oSPA&r=z73EKtGMkOyHMZwSjVYW896tQVgTZQlAucPsWFx6Th0&m=4GJkWbcUMGxyP6PhIVRGGShK3I_JWNrxKiSMj5DrjNg&s=tDZH5Rc5eX3f_Z_VdT_cUxul88jx3yth1PCMLGitqf8&e=>
| @zztat_oracle |
fb.me/zztat<https://urldefense.proofpoint.com/v2/url?u=http-3A__fb.me_zztat&d=DwMFAg&c=SsZxQMfaWJ1sSVfloc5FVGba8BA_qR4Jzdt8ol2oSPA&r=z73EKtGMkOyHMZwSjVYW896tQVgTZQlAucPsWFx6Th0&m=4GJkWbcUMGxyP6PhIVRGGShK3I_JWNrxKiSMj5DrjNg&s=3CbwmR3aosG-N6jaLRJuR78on0mlOTQoSFI164FWI4Y&e=>
|
zztat.net/blog/<https://urldefense.proofpoint.com/v2/url?u=http-3A__zztat.net_blog_&d=DwMFAg&c=SsZxQMfaWJ1sSVfloc5FVGba8BA_qR4Jzdt8ol2oSPA&r=z73EKtGMkOyHMZwSjVYW896tQVgTZQlAucPsWFx6Th0&m=4GJkWbcUMGxyP6PhIVRGGShK3I_JWNrxKiSMj5DrjNg&s=nHtu-sOD7SbY3H8wH43GLQJV2G_xkPEwTcVNczs9e7c&e=>
