Workaround ORA-01652 when creating function-based index on partitioned table

01 create_unusable_index.sql

CREATE INDEX &schema.&index_part
  ON &schema.&table_name(TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(&column),'YYYYMMDD'),'IYYYIW'))) 
  LOCAL UNUSABLE;

02 count_unusable_partitions.sql

select count(1)
  from dba_ind_partitions 
 where index_name = '&index_part' and status != 'USABLE';

03 create_index_loop.sql

DECLARE
  sql_query VARCHAR2(500);
BEGIN
  FOR r IN (
select rownum, 'alter index '||index_owner||'.'||index_name||' rebuild partition ' || partition_name || ' parallel(degree 4)' query
  from dba_ind_partitions where index_name = '&index_part' and status != 'USABLE'
  )
  LOOP
  execute immediate r.query;
  --dbms_output.put_line(r.rownum || ': ' || r.query);

END LOOP;
END;
/