This discussion is archived
6 Replies Latest reply: Aug 19, 2013 3:04 PM by Entrep.Karthick RSS

Problem dropping extended statistics

wadams Newbie
Currently Being Moderated
I created some extended statistics on a table, but now I can't delete them. I want to delete the statistics/virtual column represented by SYS_STUDDJ802H#C$E$W27NW$#B7DO.
SQL> SELECT *
      2 FROM   dba_stat_extensions 
      3 WHERE  table_name = 'T_E2_TRANS_INFO'

OWNER      TABLE_NAME           EXTENSION_NAME                 EXTENSION                                     CREATO DRO
---------- -------------------- ------------------------------ --------------------------------------------- ------ ---
INTERFACE  T_E2_TRANS_INFO      SYS_NC00063$                   (TRUNC("TRANSACTION_DATE"))                   SYSTEM NO
INTERFACE  T_E2_TRANS_INFO      SYS_STUDDJ802H#C$E$W27NW$#B7DO ("PARTITION_ID",TRUNC("TRANSACTION_DATE"))    USER   YES

SQL> exec dbms_stats.drop_extended_stats('INTERFACE','T_E2_TRANS_INFO','(partition_id, trunc(transaction_date))');
BEGIN dbms_stats.drop_extended_stats('INTERFACE','T_E2_TRANS_INFO','(partition_id, trunc(transaction_date))'); END;

*
ERROR at line 1:
ORA-20000: extension "(partition_id, trunc(transaction_date))" does not exist
ORA-06512: at "SYS.DBMS_STATS", line 8478
ORA-06512: at "SYS.DBMS_STATS", line 31747
ORA-06512: at line 1


SQL> exec dbms_stats.drop_extended_stats('INTERFACE','T_E2_TRANS_INFO','(PARTITION_ID, trunc(TRANSACTION_DATE))');
BEGIN dbms_stats.drop_extended_stats('INTERFACE','T_E2_TRANS_INFO','(PARTITION_ID, trunc(TRANSACTION_DATE))'); END;

*
ERROR at line 1:
ORA-20000: extension "(PARTITION_ID, trunc(TRANSACTION_DATE))" does not exist
ORA-06512: at "SYS.DBMS_STATS", line 8478
ORA-06512: at "SYS.DBMS_STATS", line 31747
ORA-06512: at line 1


SQL> exec dbms_stats.drop_extended_stats('INTERFACE','T_E2_TRANS_INFO','(PARTITION_ID, TRUNC(TRANSACTION_DATE))');
BEGIN dbms_stats.drop_extended_stats('INTERFACE','T_E2_TRANS_INFO','(PARTITION_ID, TRUNC(TRANSACTION_DATE))'); END;

*
ERROR at line 1:
ORA-20000: extension "(PARTITION_ID, TRUNC(TRANSACTION_DATE))" does not exist
ORA-06512: at "SYS.DBMS_STATS", line 8478
ORA-06512: at "SYS.DBMS_STATS", line 31747
ORA-06512: at line 1

SQL> exec dbms_stats.drop_extended_stats('INTERFACE','T_E2_TRANS_INFO','("PARTITION_ID", TRUNC("TRANSACTION_DATE"))');
BEGIN dbms_stats.drop_extended_stats('INTERFACE','T_E2_TRANS_INFO','("PARTITION_ID", TRUNC("TRANSACTION_DATE"))'); END;

*
ERROR at line 1:
ORA-20000: extension "("PARTITION_ID", TRUNC("TRANSACTION_DATE"))" does not exist
ORA-06512: at "SYS.DBMS_STATS", line 8478
ORA-06512: at "SYS.DBMS_STATS", line 31747
ORA-06512: at line 1
Anybody run into this?

Thanks!
  • 1. Re: Problem dropping extended statistics
    andyschwarz Journeyer
    Currently Being Moderated
    I tried to get such a situation with no luck. How did you get the ("PARTITION_ID",TRUNC("TRANSACTION_DATE"))?
    I tried on a partition table but I never get a "PARTITION_ID" into the function.
    drop table ex_stats_test purge;
    create table ex_stats_test
    (mnr number,
     mvol number)
    PARTITION BY range (mnr)
     (PARTITION PARTITION_000000  VALUES LESS THAN (MAXVALUE)); 
    
    insert into EX_STATS_TEST values (1,2);
    commit;
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
    SELECT DBMS_STATS.CREATE_EXTENDED_STATS('ANDY','EX_STATS_TEST','(TRUNC("MVOL"))') FROM DUAL;
    
    
    select * from  dba_stat_extensions where owner='ANDY';
    
    exec DBMS_STATS.DROP_EXTENDED_STATS ('ANDY','EX_STATS_TEST','(TRUNC("MVOL"))');
  • 2. Re: Problem dropping extended statistics
    user392774 Newbie
    Currently Being Moderated
    Hi,

    I have also exaclty the same problem, cannot drop extended statistics even it if show that is user, can drop. I tried in various ways upper, lower but all fails with ORA-20000, that extenstion does not exists.

    I think it is something like bug.

    Thank you
  • 3. Re: Problem dropping extended statistics
    Entrep.Karthick Newbie
    Currently Being Moderated

    We are using 11.02.03 and we also got the similar error. I can able to drop around 6 extension on the same table, but one extension alone didn't get dropped. Can anyone, please let know if you have any solution to overcome.

  • 4. Re: Problem dropping extended statistics
    Ishan Journeyer
    Currently Being Moderated

    I am on 11.2.0.3

     

    I ran a few test cases and here are the results.

     

    SQL> drop table test;

     

     

    Table dropped.

     

     

     

     

    SQL> create table test (col1 number, col2 date);

     

     

    Table created.

     

     

     

     

    SQL> select DBMS_STATS.CREATE_EXTENDED_STATS ( 'SCOTT',  'TEST', '(col1, col2)') from dual;

     

     

    DBMS_STATS.CREATE_EXTENDED_STATS('SCOTT','TEST','(COL1,COL2)')                 

    --------------------------------------------------------------------------------

    SYS_STUFLHATC5RBD6JHJZWT$X2AAH                                                 

     

     

    SQL> select DBMS_STATS.CREATE_EXTENDED_STATS ( 'SCOTT',  'TEST', '(col1 + col2)') from dual;

     

     

    DBMS_STATS.CREATE_EXTENDED_STATS('SCOTT','TEST','(COL1+COL2)')                 

    --------------------------------------------------------------------------------

    SYS_STUYYVMEJB1W1YYGKWV2_S234C                                                 

     

     


     

    SQL> begin

      2  DBMS_STATS.DROP_EXTENDED_STATS ('SCOTT',  'TEST', '(col1 + col2)');

      3  end;

      4  /

     

     

    PL/SQL procedure successfully completed.

     

     

    SQL>

    SQL> begin

      2  DBMS_STATS.DROP_EXTENDED_STATS ('SCOTT',  'TEST', '(col1, col2)');

      3  end;

      4  /

     

     

    PL/SQL procedure successfully completed.

     

     

     

     

    SQL> select DBMS_STATS.CREATE_EXTENDED_STATS ( 'SCOTT',  'TEST', '(col1, trunc(col2))') from dual;

    select DBMS_STATS.CREATE_EXTENDED_STATS ( 'SCOTT',  'TEST', '(col1, trunc(col2))') from dual

           *

    ERROR at line 1:

    ORA-20001: Invalid Extension: Column group can contain only columns seperated

    by comma

     

    I was able to drop the extensions. However, when I tried to create a similar extension as yours, I got the error.

     

    Can you post your CREATE_TABLE and EXTENSION CREATION code. I'll try to replicate it in mine and see if it's related to version or something else. Also, please do post your ORACLE version as well.

     

    PS: Sometimes we create objects with double quotes(eg. "partition_id" etc.) and many a times this too results in unexpected errors(which I feel could be the reason in your case).

     

    Ishan

  • 5. Re: Problem dropping extended statistics
    Entrep.Karthick Newbie
    Currently Being Moderated

    CREATE TABLE REPORT

    (

      CLEARANCE                NUMBER(10)           NOT NULL,

      REPORT                      NUMBER(19)           NOT NULL,

      CONTENT_EXPIRATION_DATE  TIMESTAMP(6),

      CREATION_DATE            DATE                 NOT NULL,

      CREATION_DATETIME        TIMESTAMP(6)         NOT NULL,

      EXPIRATION_DATE          TIMESTAMP(6),

      FILLER_ORDER_NUMBER      VARCHAR2(30 BYTE),

      FORWARDED                CHAR(1 BYTE)         NOT NULL,

      IS_ABNORMAL              CHAR(1 BYTE)         NOT NULL,

      IS_ANNOTATED             CHAR(1 BYTE),

      IS_DOWNLOADED            CHAR(1 BYTE),

      IS_FORWARDED             CHAR(1 BYTE)         NOT NULL,

      IS_LATEST                CHAR(1 BYTE)         NOT NULL,

      IS_PRINTED               CHAR(1 BYTE)         NOT NULL,

      IS_VIEWED                CHAR(1 BYTE)         NOT NULL,

      ORDER_RESULT             NUMBER(19),

      ORDERING_CG_FNAME        VARCHAR2(30 BYTE),

      ORDERING_CG_LNAME        VARCHAR2(30 BYTE),

      ORDERING_CG_MNAME        VARCHAR2(30 BYTE),

      ORDERING_CG_SUFFIX       VARCHAR2(5 BYTE),

      ORDERING_CLIENT_ID       VARCHAR2(80 BYTE),

      ORIGINAL_REPORT          NUMBER(19),

      OWNERID                  NUMBER(19)           NOT NULL,

      PERSON                   NUMBER(19),

      PERSON_ACCOUNT_NUMBER    VARCHAR2(80 BYTE),

      PERSON_FIRST_NAME        VARCHAR2(30 BYTE),

      PERSON_LAST_NAME         VARCHAR2(30 BYTE),

      PERSON_MIDDLE_NAME       VARCHAR2(30 BYTE),

      PERSON_SUFFIX            VARCHAR2(5 BYTE),

      PLACER_ORDER_NUMBER      VARCHAR2(30 BYTE),

      PREVIOUS_REPORT          NUMBER(19),

      RECEIVING_CG_FNAME       VARCHAR2(30 BYTE),

      RECEIVING_CG_LNAME       VARCHAR2(30 BYTE),

      RECEIVING_CG_MNAME       VARCHAR2(30 BYTE),

      RECEIVING_CG_SUFFIX      VARCHAR2(5 BYTE),

      RECEIVING_CLIENT_ID      VARCHAR2(80 BYTE),

      RECEIVING_ORGANIZATION   NUMBER(19)           NOT NULL,

      REPORT_PRIORITY          VARCHAR2(40 BYTE),

      REPORT_REQUEST_DATE      TIMESTAMP(6),

      REPORT_SERVICE_DATE      TIMESTAMP(6),

      REPORT_STATUS            VARCHAR2(40 BYTE)    NOT NULL,

      REPORT_SUBJECT           VARCHAR2(100 BYTE),

      REPORT_TYPE              VARCHAR2(40 BYTE),

      SPONSOR                  NUMBER(19)           NOT NULL

    )

    PARTITION BY RANGE (CREATION_DATETIME)

    /

     

    --I use the below way to create ext stats:

     

    SELECT DBMS_STATS.create_extended_stats( USER, 'REPORT','("RECEIVING_ORGANIZATION","IS_LATEST","SPONSOR")' ) FROM DUAL;

     

    --List of ext stats:

     

    "RECEIVING_ORGANIZATION","IS_LATEST","IS_VIEWED","IS_PRINTED"

     

    "PERSON","RECEIVING_ORGANIZATION","IS_LATEST"

     

    "RECEIVING_ORGANIZATION","IS_LATEST",UPPER("PERSON_LAST_NAME"),UPPER("PERSON_FIRST_NAME")

     

    "RECEIVING_ORGANIZATION","IS_LATEST","REPORT_TYPE"

     

    "RECEIVING_ORGANIZATION","IS_LATEST","SPONSOR","REPORT_TYPE"

     

    "RECEIVING_ORGANIZATION","IS_LATEST","PLACER_ORDER_NUMBER"

     

    -- to drop ext stats: (since its test system, yesterday I could able to creatre and drop them, when I try today, all the above ext dropped except 3rd one in the above list)

     

    BEGIN

        FOR rec IN ( SELECT table_name, DBMS_LOB.substr(extension, 3000)  extension

                            FROM user_stat_extensions

                          WHERE table_name = 'REPORT'

                             AND creator = 'USER' ) LOOP

            DBMS_STATS.drop_extended_stats( USER, rec.table_name, rec.extension );

        END LOOP;

    END;

     

    I searched metalinik: Bug 12739959 : ORA-2000 WITH DBMS_STATS.DROP_EXTENDED_STATS WHEN CURSOR_SHARING=FORCE

     

    But it seems misleading and they say about similar error but while using CURSOR_SHARING=FORCE, I think we are not in this.

     

    Please let me know if you find any. My guess is something with UPPER (internal functions) in extended stats leads to bug or something.

  • 6. Re: Problem dropping extended statistics
    Entrep.Karthick Newbie
    Currently Being Moderated

    Eureka!!!!

     

    I found the reason for this issue.

     

    When we create extended stats which involves expressions like UPPER, TRUNC (any internal functions) and if you have created any IDX similar to the column grouping or columns (i.e similar expressions, internal functions) involved in extended stats, it wont allow you to drop that particular extended stats.

     

    In my case -

     

    I have an IDX :

     

    CREATE INDEX IDX_REPORT_3 ON REPORT
    (RECEIVING_ORGANIZATION, IS_LATEST, UPPER("PERSON_LAST_NAME"), UPPER("PERSON_FIRST_NAME"));
    

     

    I created extended stats: "RECEIVING_ORGANIZATION","IS_LATEST",UPPER("PERSON_LAST_NAME"),UPPER("PERSON_FIRST_NAME")

     

    I gather stats on table, so it will create column stats for this column expression UPPER("PERSON_LAST_NAME") and UPPER("PERSON_FIRST_NAME") (system generated name SYS_NC$0000)

     

    SELECT *
      FROM user_tab_col_statistics
    WHERE table_name = 'REPORT';
    

     

    When these column stats exists, you can't drop the extended stats with similar column expressions. So drop this column expression stats using:

     

    BEGIN
            DBMS_STATS.delete_column_stats( ownname => USER, tabname => 'REPORT', colname => 'SYS_NC$0045'); -- replace your value accordingly
    END;
    

     

    Once done, then you can drop the extended stats:

     

    BEGIN
        FOR rec IN ( SELECT table_name, DBMS_LOB.substr(extension, 3000)  extension
                            FROM user_stat_extensions
                          WHERE table_name = 'REPORT'
                             AND creator = 'USER' ) LOOP
            DBMS_STATS.drop_extended_stats( USER, rec.table_name, rec.extension );
        END LOOP;
    END;
    

     

     

    So in short:

     

    - drop the column expression stats which is similar to extended stats column expression grouping

    - then drop extended stats

     

    Enjoy!!!!

     

    One update to this: I thought I can drop the column expression stats and then drop extended stats. But that's not the case. Actually we need to drop the IDX associated with that column expression and then we need to drop extended stats and recreate the IDX. So be careful - if you plan to drop any extended stats which involves column expression which is similar to any IDX column groups/expressions.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points