6 Replies Latest reply: Aug 19, 2013 5:04 PM by Entrep.Karthick RSS

    Problem dropping extended statistics

    wadams
      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
          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
            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

              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

                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

                  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

                    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.