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_at_hotmail.com> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
> *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_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Sourav Biswas <biswas.sourav_at_hotmail.com>
> 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/

--
http://www.freelists.org/webpage/oracle-l