Awk Fixed Length File Data Masking Deperzonalise
And a couple of extra functions if want to do in a loop for all files in a directory, or generate a hash check file:
Read More Awk Fixed Length File Data Masking DeperzonaliseLogger 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/SQLOracle 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 ProcessesPython 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 PagesAnsible 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 SQLORA-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 fixOracle 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 explainedSlow 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_extSpace 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