Exemple d'utilisation du segment advisor

Cet exemple va permettre de récupérer de l'espace disque : 

set serveroutput on size unlimited
DECLARE
  CURSOR curs_list_index
  IS
    SELECT regexp_replace(regexp_replace(c1, 'alter index ', ''), ' shrink space', '') index_name
    FROM TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS)
    WHERE TASK_ID IN
      (SELECT task_id
      FROM dba_advisor_log
      WHERE task_name LIKE 'SYS_AUTO_SPCADV%'
      AND EXECUTION_START > (sysdate - 7)
      )
  AND C1 LIKE 'alter index%'
  ORDER BY task_id;
 
  cmd varchar2(160);
  ecode number;
  emesg varchar2(200);
BEGIN
   for rec in curs_list_index
   loop
      if substr(rec.index_name, 2, 3) = 'xxx' then
         begin
            cmd := 'alter index ' || rec.index_name || ' rebuild online parallel 16 nologging';
            dbms_output.put_line(cmd);
            execute IMMEDIATE cmd;
            cmd := 'alter index ' || rec.index_name || ' noparallel';
            execute IMMEDIATE cmd;
         exception
            -- ORA-00054: resource busy and acquire with NOWAIT specified
            when others then
               ecode := SQLCODE;
               emesg := SQLERRM;
               dbms_output.put_line(TO_CHAR(ecode) || '-' || emesg);      
         end;
      end if;
   end loop;
END;

Vous pouvez aussi l'étendre aux segments de type table. Vous serez surpris de l'espace gagné !