Logger Oracle PL/SQL

A logger.write procedure as an extension to dbms_output.put_line. It does a dbms_output.put_line, but also writes same message into a log table. Unlike dbms_output, the writes to the log table are visible while the program is running, via an asynchronous autonomous transaction. Output (both types) includes the line number and the name of calling package/procedure, for […]

Read More Logger Oracle PL/SQL
October 20, 2022

Oracle Orphaned Processes

Error reported to application attempting to connect to database: ORA-12516, TNS:listener could not find available handler with matching protocol stack Underlying cause (this reported in database alert log rather than at application side) was: ORA-00020: maximum number of processes (nnn) exceeded Fix was to kill a large number of orphaned processes, with no associated session, […]

Read More Oracle Orphaned Processes
October 20, 2022

Python create Confluence Pages

Python script to create confluence page (+ child pages) from confluence markup .txt files. Handles page name collisions. Can be called from Jenkins “windows batch command” with: python awrConfluence.py “%p_test_description%” H/t J. Antunes and Somaiah Kumbera at https://stackoverflow.com/questions/33168060/how-can-i-create-a-new-page-to-confluence-with-python

Read More Python create Confluence Pages
October 20, 2022

Ansible Oracle SQL

Ansible being used to run a sqlplus script, handling pluggables and dataguard. 1) Initial setup, on central ansible control host The use of a non-standard / non-default filename for the key is purely optional. If ssh-copy-id is not an option, then can instead logon to each target dbserver as oracle and manually append the line […]

Read More Ansible Oracle SQL
October 8, 2022

ORA-00600 17287 fix

There are some MoS docs for ORA-00600 [17287] suggesting to rerun various dictionary install scripts. Sometimes that might be needed – but in my case would have been overkill, because just recompiling the invalid objects to get them to status invalid was all that was needed – with a few extra grants needed applied back […]

Read More ORA-00600 17287 fix
September 10, 2020

2 Comments

Oracle hints profiles baselines explained

Ideally fix performance issues globally: that is, with database parameter changes and/or dbms_stats.set_prefs. But if you need to fix locally one specific sql, Oracle comes with 6 (count them, 6!) methods for doing that. In order of preference: 1) Optimizer Hint Written into sql code. Keeps the fix in place even through future application sql […]

Read More Oracle hints profiles baselines explained
January 23, 2019

Slow performance fix oracle alert log v$diag_alert_ext x$diag_alert_ext

v$diag_alert_ext aka x$diag_alert_ext should be useful for querying the oracle alert log. Unfortunately with comon conditions in the where clause, queries against it run unusably slow. This is covered in Mos doc 1684140.1 “Selects from v$diag_alert_ext run slowly with large alert logs“, but that ends with the unhelpful “Solution: There is no solution.“. Bug 18643828 […]

Read More Slow performance fix oracle alert log v$diag_alert_ext x$diag_alert_ext
August 23, 2018

2 Comments

Space usage lob sub partitions

Querying partition space usage is a little awkward with lobs and sub partitions, but can be done with sql like this: WITH base AS ( SELECT lp.table_owner , lp.table_name , lp.partition_name , SUM ( s.bytes ) / 1024 / 1024 AS lob_mb , COUNT(*) AS num_subpartitions FROM dba_lob_partitions lp JOIN dba_lob_subpartitions lsp ON lsp.table_owner = […]

Read More Space usage lob sub partitions
August 14, 2018