Differences

This shows you the differences between two versions of the page.

--- pl_sql [2018/12/08 12:49] – created 0.0.0.0
+++ pl_sql [2020/04/28 15:20] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== PL/SQL ======
 [[http://www.tutorialspoint.com/plsql/plsql_tutorial.pdf|PL/SQL tutorial from tutorialspoint.com]]
-=====Write directly to the alert log=====
+==== Write directly to the alert log ====
-<code>0@@</code>
+<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>
-=====Get default host name and PL/SQL program unit=====
-<code>1@@</code>