ORA-00935

select owner,sum(bytes) sumb 
  from dba_segments 
 group by OWNER
 order by sum(bytes) desc
;

ORA-00935: group function is nested too deeply

Oracle Support Document 18529.1

Error:  ORA 935  
Text:   group function is nested too deeply 
-------------------------------------------------------------------------------
Cause:  This is an internal error message not usually issued.
Action: Contact customer support.

The fix for my query is not to use the aggregate in order by:

select owner,sum(bytes) sumb
  from dba_segments 
 group by owner
 order by sumb 
;

Over million interval partitions. --not

Here is an yet another SQL tale why you should _not_ store dates as a number.

--drop table F_INVOICE_L;

create table F_INVOICE_L(
       INVOICING_DATE_WID NUMBER (8)
      ,product_wid number(20)) 
partition by range (INVOICING_DATE_WID) 
interval (1) 
subpartition by hash (product_wid) 
subpartitions 32
(PARTITION P_MINPART VALUES LESS THAN (20100101))
;

insert into F_INVOICE_L values (20140101,1);

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

select count(*) from dba_tab_partitions where table_name = 'F_INVOICE_L';

1


Waat?

There should be room for 1048575 partitions. I have only one, the P_MINPART initial one. Well ok I have 32 subpartitions. There should be room for 32767 partitions.

select 1048575/32 + 20100101 from dual;

20132868,96875

We ran out of placeholders for numbers. If we would have used date datatype we would have some time still to go with this partitioning method.

select 1048575/32 + to_date('20100101','yyyymmdd') from dual;

2099-09-18 

Quick and dirty fix

alter table F_INVOICE_L set interval (100);

insert into F_INVOICE_L values (20140101,1);

1 row inserted.

rollback;

select high_value from user_tab_partitions where table_name = 'F_INVOICE_L';

20100101
20140201



And setup the timebomb again to be triggered at new years day 2018.

alter table F_INVOICE_L set interval (1);

select 1048575/32 + 20140201 from dual;

20172968,96875

insert into F_INVOICE_L values (20170101,1);

1 rows inserted.

insert into F_INVOICE_L values (20180101,1);

SQL Error: ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions