dbawiki

procedure write_alert(p_text in varchar2) is
begin
    sys.write_log(2,p_text);
end write_alert;

procedure log_text ( p_text              in all_log_messages.text%type
                   , p_server            in all_log_messages.server%type      default utl_inaddr.get_host_name
                   , p_database          in all_log_messages.database%type    default sys.database_name
                   , p_last_check        in all_log_messages.last_check%type  default sysdate
                   , p_severity          in all_log_messages.severity%type    default 'INFO'
                   , p_script_name       in all_log_messages.script_name%type default $$PLSQL_UNIT
                   );

set serveroutput on size unlimited
begin
    for indexes in
    (
        select 'alter index '||owner||'.'||index_name||' rebuild online parallel' cmd
        from    dba_indexes
        where   status = 'UNUSABLE'
        union all
        select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' online parallel'
        from    dba_ind_partitions
        where   status = 'UNUSABLE'
        union all
        select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||subpartition_name||' online parallel'
        from    dba_ind_subpartitions
        where   status = 'UNUSABLE'
    )
    loop
        dbms_output.put_line(indexes.cmd);
        execute immediate indexes.cmd;
    end loop;
end;
/