myorama
10/1/2014 - 1:22 PM

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

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

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;
/
select count(1)
  from dba_ind_partitions 
 where index_name = '&index_part' and status != 'USABLE';
CREATE INDEX &schema.&index_part
  ON &schema.&table_name(TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(&column),'YYYYMMDD'),'IYYYIW'))) 
  LOCAL UNUSABLE;