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 DeperzonalisePython 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 SQLSlow 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_extTKProf 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 ASHoracle 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 mergePL/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 accountsConvert 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)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 ASCIIGlobal search and replace with sed and find
sed -e ‘s/old1/new1/g s/old2/new2/g s/old3/new3/g’ –i $(find / -name ‘*.php’ -o -name ‘*.include’ -o -name ‘*.inc’ -o -name ‘*.class’ -o -name ‘ht.access’) ‘locate’ would be an alternative to ‘find’.
Read More Global search and replace with sed and find