Snippets - Revision history

Stuart: /* How to get value of ORACLE_HOME from shell environment into SQLPlus or PL/SQL /

2018-12-05T12:54:16Z

‎How to get value of ORACLE_HOME from shell environment into SQL*Plus or PL/SQL

2018-12-05T12:53:55Z

‎How to find ORACLE_HOME from pmon process for a SID if there is no entry in oratab

2018-12-04T20:08:08Z

‎Write to the alert log from PL/SQL

2018-12-04T20:07:25Z

‎Write to the alert log from PL/SQL

2018-11-21T11:13:18Z

‎Simulating errors in alert log

2018-11-21T11:12:43Z

‎Search alert log for ORA-00600 and ORA-07445 errors

2018-11-04T00:58:27Z

‎Invisible / hidden / virtual columns

2018-11-04T00:57:32Z

‎Write to a trace log from PL/SQL

2018-09-26T14:16:27Z

‎Rename a datafile if the filename contains junk / unprintable characters

2018-09-26T13:55:40Z

‎Generate creation of directory names from dba_directories

@@ Line 630: / Line 630: @@
 <pre>
 var orahome varchar2(2000);
-exec dbms_system.get_env('ORACLE_HOME', :orahome) ;
+exec dbms_system.get_env('ORACLE_HOME', :orahome);
 print orahome
 </pre>
 ===How to find ORACLE_HOME from pmon process for a SID if there is no entry in oratab===
 If database is created without dbca (datafile and controlfile copy), no entry will be automatically made in /etc/oratab.<br />

@@ Line 627: / Line 627: @@
 </pre>
 ===How to find ORACLE_HOME from pmon process for a SID if there is no entry in oratab===
 If database is created without dbca (datafile and controlfile copy), no entry will be automatically made in /etc/oratab.<br />

@@ Line 244: / Line 244: @@
 Writes to trace file
 <pre>
-dbms_system.ksdwrt(1,'message sent to the alert log')
+dbms_system.ksdwrt(1,'message sent to trace log')
 </pre>
 Writes to the alertlog
 <pre>
-dbms_system.ksdwrt(2,'message sent to the alert log')
+dbms_system.ksdwrt(2,'message sent to the alertlog')
 </pre>
 Writes to the alertlog and trace file
 <pre>
-dbms_system.ksdwrt(3,'message sent to the alert log')
+dbms_system.ksdwrt(3,'message sent to trace file and the alert log')
 </pre>
 Also available, KSDIND, KSDDDT and KSDFLS

@@ Line 241: / Line 241: @@
 ===Write to the alert log from PL/SQL===
 <pre>
 dbms_system.ksdwrt(2,'message sent to the alert log')
 </pre>
 ===Do distributed SQL transactions exist?===
 <pre>

@@ Line 1,435: / Line 1,435: @@
 </pre>
 ===Simulating errors in alert log===
-Sometimes you need to inject error messages into the alertly to test your monitoring system. This generates the entries in the alertlog and in x$dbgalertext table.
+Sometimes you need to inject error messages into the alertlog to test your monitoring system. This generates the entries in the alertlog and in x$dbgalertext table.
 <pre>
 exec sys.dbms_system.ksdwrt(2, 'ORA-07445: Testing this error code for Zenoss, do not investigate.');

@@ Line 1,433: / Line 1,433: @@
 <pre>
 select indx, message_text from x$dbgalertext where message_text like '%ORA-00600%' or message_text like '%ORA-07445%' and indx > &last_queried_indx order by indx desc;
 </pre>

@@ Line 237: / Line 237: @@
 </pre>
 ===Invisible / hidden / virtual columns===
-Article showing how to dump blocks also.
+Article showing how to dump blocks also.<br />
-[https://www.oracle.com/technetwork/articles/database/invisible-columns-odb12c-2331522.html Alex Zaballa, Oracle Ace and Daniel Da Meda (OCM)]
+[https://www.oracle.com/technetwork/articles/database/invisible-columns-odb12c-2331522.html Tips and Tricks: Invisible Columns in Oracle Database 12c by Alex Zaballa, Oracle Ace and Daniel Da Meda (OCM)]
 ===Write to the alert log from PL/SQL===

@@ Line 236: / Line 236: @@
 dbms_system.ksdwrt(1,'message sent to a trace file')
 </pre>
 ===Write to the alert log from PL/SQL===

@@ Line 69: / Line 69: @@
 ls -baltri
 total 47474984
 -rw-r-----    1 oracle   oinstall 1073750016 Sep 26 13:56 ts_thaler_data_02\1776.dbf
 -rw-r-----    1 oracle   oinstall 5242888192 Sep 26 13:56 ts_thaler_data_01.dbf
 find . -inum 12409 -exec mv {} ts_thaler_data_06.dbf \;
 </pre>
-work out what the database thinks the name is and rename it there too!
+Work out what the database thinks the name is and rename it there too! AskTom has a discussion on it [https://asktom.oracle.com/pls/apex/asktom.search?tag=rename-datafile-with-dummy-characters-after-dbf here]
 <pre>
 select substr(name,i,1),ascii(substr(name,i,1)) from ( select name from v$datafile where file# = 9), ( select rownum i from dual connect by level <= 50);
@@ Line 111: / Line 102: @@
 b                         98
 f                        102
+</pre>
-You see there's 127 just before the .dbf...
+You can see there's a 127 just before the .dbf... this crept in a the backspace key was not working correctly.
+<pre>
 set serveroutput on
 declare
@@ Line 128: / Line 119: @@
 alter tablespace ts_thaler_data_online;
+</pre>
 ===Generate creation of directory names from dba_directories===
 Before deleting or dropping directories in the database, use this script to generate the create statements