dbawiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
[[http://www.tutorialspoint.com/plsql/plsql_tutorial.pdf|PL/SQL tutorial from tutorialspoint.com]]
==== Write directly to the alert log ====
<code>
procedure write_alert(p_text in varchar2) is
begin
    sys.write_log(2,p_text);
end write_alert;
</code>

==== Get default host name and PL/SQL program unit ====
<code>
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
                   );
</code>
==== Rebuild unusable indexes ====
<code>
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;
/
</code>