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

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

TKProf by ASH

Oracle session trace + tkprof is still the best tool for performance diagnosis. But: Need access to database server user_dump_dest directory. Has to be switched on in advance Has a performance impact, especially if tracing multiple sessions or tracing for a long time. And ASH is a good enough alternative much of the time. The […]

Read More TKProf by ASH
October 6, 2016

oracle update select merge

If you want to update one table based on the values in another table, use ‘UPDATE = (SELECT) WHERE (SELECT)’ or ‘MERGE WHERE’, as in examples (3) and (3a) below. Sample data: DROP TABLE a ; CREATE TABLE a ( id VARCHAR2(10) , text1 VARCHAR2(10) , text2 VARCHAR2(10) ) ; INSERT INTO a ( id […]

Read More oracle update select merge
November 25, 2014

One Comment

PL/SQL to find and lock or drop user accounts

Script to hunt and lock/drop user accounts, can call from a central script connecting to multiple databases: BEGIN FOR d1 IN ( SELECT username FROM dba_users WHERE username LIKE ‘AGXDL%’ AND account_status != ‘LOCKED’ ORDER BY 1 ) LOOP dbms_output.put_line ( ‘alter user ‘ || d1.username || ‘ account lock ‘ ) ; execute immediate […]

Read More PL/SQL to find and lock or drop user accounts
October 10, 2013

Convert java dates to oracle sql dates (esp. odm_publicobject createdate)

Java dates are recorded in milliseconds after 01-Jan-1970 00:00:00 GMT. To convert these to oracle dates, use sql like: SELECT TO_DATE(’01-JAN-1970 00:00:00′,’DD-MON-YYYY HH24:MI:SS’) + createdate/(1000*60*60*24) createdate , creator FROM odm_publicobject WHERE TO_DATE(’01-JAN-1970 00:00:00′,’DD-MON-YYYY HH24:MI:SS’) + createdate/(1000*60*60*24) > sysdate – 30 ORDER BY 1 , 2 ; Note that the above does not correct for any […]

Read More Convert java dates to oracle sql dates (esp. odm_publicobject createdate)
October 26, 2012

Oracle regexp_replace remove non alpha ASCII

Replace everything that isn’t a standard ASCII alpha character with a space: SELECT REGEXP_REPLACE ( ‘xxXX € Ááé 123 %; test text æ¸¬è© ¦ “xmxmx” number²’ , ‘[^a-zA-Z]’ , ‘ ‘ ) FROM DUAL h/t Kok Yan Lo and jadarnel27 Update : an alternative method from Paul Bradley: select ‘ÄÊÍÕØÓÑ’ str, substr(upper(utl_raw.cast_to_varchar2((nlssort(‘ÄÊÍÕØÓÑ’, ‘nls_sort=binary_ai’)))),1,10) str2 from […]

Read More Oracle regexp_replace remove non alpha ASCII
October 2, 2012