Online Partition Move fails with ORA-00932 – 12.1.0.2

Oracle 12c introduced feature to move table partitions and sub-partitions as online operations.So, this blog is related to this feature and the issue I faced.

The database was upgraded from 11.2.0.4 to 12.1.0.2 and this issue is seen in upgraded databases only.

Lets start!!!

SQL> create table sales_part
    (product char(25),channel_id number,cust_id number,
    amount_sold number, time_id date)
    partition by range (time_id)
    (
    partition sale_jan values less than (to_date('01-02-2015','dd-mm-yyyy')),
    partition sale_feb values less than (to_date('01-03-2015','dd-mm-yyyy')),
    partition sale_mar values less than (to_date('01-04-2015','dd-mm-yyyy')),
    partition sale_apr values less than (to_date('01-05-2015','dd-mm-yyyy')),
    partition sale_may values less than (to_date('01-06-2015','dd-mm-yyyy')),
    partition sale_jun values less than (to_date('01-07-2015','dd-mm-yyyy')),
    partition sale_jul values less than (to_date('01-08-2015','dd-mm-yyyy')),
    partition sale_aug values less than (to_date('01-09-2015','dd-mm-yyyy')),
    partition sale_sep values less than (to_date('01-10-2015','dd-mm-yyyy')),
    partition sale_oct values less than (to_date('01-11-2015','dd-mm-yyyy')),
    partition sale_nov values less than (to_date('01-12-2015','dd-mm-yyyy')),
    partition sale_dec values less than (to_date('01-01-2016','dd-mm-yyyy'))
   ) 
   TABLESPACE USERS;

SQL> insert into sales_part
    select
    'Oracle Enterprise Edition' as product,
     mod(rownum,5) as channel_id,
     mod(rownum,1000) as cust_id ,
     5000 as amount_sold,
     to_date
     ('01.' || lpad(to_char(mod(rownum,6)+4),2,'0') || '.2015' ,'dd.mm.yyyy')
     as time_id
     from dual connect by level commit;

Commit complete.

SQL> create index idx_sale_local on sales_part (cust_id,time_id)local tablespace users;

Index created.

SQL> create index idx_sale_global on sales_part (channel_id)  tablespace users;

Index created.

SQL>                        
SQL> exec dbms_stats.gather_table_stats('ANAND','SALES_PART', cascade => true);

PL/SQL procedure successfully completed.

SQL>

Table and Index details

SQL>@partition_table 

TABLE_NAME      PARTITION_NAME            HIGH_VALUE                                                                            LAST_ANALYZED      TABLESPACE_NAM   NUM_ROWS
--------------- ------------------------- ------------------------------------------------------------------------------------- ------------------ -------------- ----------
SALES_PART      SALE_JAN                  TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0
SALES_PART      SALE_FEB                  TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0
SALES_PART      SALE_MAR                  TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0
SALES_PART      SALE_APR                  TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33333
SALES_PART      SALE_MAY                  TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33334
SALES_PART      SALE_JUN                  TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33334
SALES_PART      SALE_JUL                  TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33333
SALES_PART      SALE_AUG                  TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33333
SALES_PART      SALE_SEP                  TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33333
SALES_PART      SALE_OCT                  TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0
SALES_PART      SALE_NOV                  TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0
SALES_PART      SALE_DEC                  TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0

12 rows selected.

SQL> @index
Enter value for owner: ANAND
Enter value for table_name: SALES_PART

TABLE_OWNER     TABLE_NAME            INDEX_OWNER     INDEX_NAME            TABLESPACE_NAME   NUM_ROWS      CLUST STATUS                   INDEX_TYPE
--------------- --------------------- --------------- --------------------- --------------- ---------- ---------- ------------------------ ------------
ANAND           SALES_PART            ANAND           IDX_SALE_GLOBAL       USERS               200000       6084 VALID                    NORMAL
ANAND           SALES_PART            ANAND           IDX_SALE_LOCAL                            200000     199784 N/A                      NORMAL

SQL> @partition_index
Enter value for index_name: IDX_SALE_LOCAL
Enter value for owner: ANAND

Index                                                                                                                   Number
Name                  PARTITION_NA STATUS                   TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE    of Rows
--------------------- ------------ ------------------------ --------------- -------------- ----------- ------------ ----------
IDX_SALE_LOCAL        SALE_JAN     USABLE                   USERS                                                            0
                      SALE_FEB     USABLE                   USERS                                                            0
                      SALE_MAR     USABLE                   USERS                                                            0
                      SALE_APR     USABLE                   USERS                    65536     1048576                   33333
                      SALE_MAY     USABLE                   USERS                    65536     1048576                   33334
                      SALE_JUN     USABLE                   USERS                    65536     1048576                   33334
                      SALE_JUL     USABLE                   USERS                    65536     1048576                   33333
                      SALE_AUG     USABLE                   USERS                    65536     1048576                   33333
                      SALE_SEP     USABLE                   USERS                    65536     1048576                   33333
                      SALE_OCT     USABLE                   USERS                                                            0
                      SALE_NOV     USABLE                   USERS                                                            0
                      SALE_DEC     USABLE                   USERS                                                            0

Now I plan to move Partition SALE_JUN to tablespace DEMO.

SQL> alter table anand.sales_part move partition sale_jun online tablespace demo
  2  update indexes (
  3  anand.idx_sale_local (partition sale_jun tablespace demo)
  4  );
alter table anand.sales_part move partition sale_jun online tablespace demo
                  *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

After searching for the issue in MOS and reviewing DOC Id 2028583.1, I applied patch 15894842 and 20123899

Oracle Database 12c                                                  12.1.0.2.0
There are 1 products installed in this Oracle Home.

List of Bugs fixed by Installed Patches:

Bug        Fixed by  Installed at                   Description
            Patch
---        --------  ------------                   -----------
20123899   20123899  Fri Oct 02 10:32:54 UTC 2015   INSERT IN TO STAT TABLE FAILS WITH ORA-932
                                                    INCONSISTENT DATATYPES
15894842   15894842  Fri Oct 02 10:31:52 UTC 2015   INSERT IN TO STAT TABLE FAILS WITH ORA-00932
                                                    INCONSISTENT DATATYPES

Even after applying the above patches, the online partition move failed with the same error. This time I traced the session –

SQL> alter session set events '932 trace name errorstack level 3';

Session altered.

SQL>alter table anand.sales_part move partition sale_jun online tablespace demo
    update indexes (
     anand.idx_sale_local (partition sale_jun tablespace demo)
     );
 alter table anand.sales_part move partition sale_jun online tablespace demo
                   *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

The trace file showed

*** 2015-10-02 10:56:40.306
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00932: inconsistent datatypes: expected NUMBER got BINARY
----- Current SQL Statement for this session (sql_id=8qwf072kqbt3g) -----
insert into histgrm$ select :1, col#, row#, bucket, endpoint, intcol#, epvalue, ep_repeat_count, epvalue_raw, spare1, spare2 from histgrm$ where obj# = :2

----- Call Stack Trace -----

So the issue is related to HISTGRM$ table. After spending few minutes searching for issue in MOS, I found the below useful documents:-

Bug 20703000 : INSERT INTO STAT TABLE HISTGRM$ FAILS WITH ORA-00932: INCONSISTENT DATATYPES
Moving Table Partition With CLOB Creates ORA-604 , ORA-932 (Doc ID 2040742.1)

After applying the patch 20703000, online partition move worked fine and below is the changed sql on histgrm$

PARSING IN CURSOR #140348537646088 len=257 dep=1 uid=0 oct=2 lid=0 tim=41782225111 hv=216722248 ad='8635af20' sqlid='66x6dxn6fpuu8'
insert into histgrm$ (obj#, col#, row#, bucket, endpoint, intcol#, epvalue,  ep_repeat_count, epvalue_raw, spare1, spare2 ) 
select :1, col#, row#, bucket, endpoint, intcol#, epvalue, ep_repeat_count, epvalue_raw, spare1, spare2 from histgrm$ where obj# = :2
END OF STMT

Hope this helps!!

Advertisements

2 thoughts on “Online Partition Move fails with ORA-00932 – 12.1.0.2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s