I found I had to disable the virbr0 network interface to get Oracle RAC 11gR2 11.2.0.2 to install correctly (on RHEL 5.8 64 bit 2.6.18-308.el5 with ocfs2). Problem was that root.sh failed on the second node. The error recorded in ocssd.log, “has a disk HB, but no network HB”, appeared to point to the multicast […]
Read More RAC 11.2.0.2 Grid install fails on second node until disable virbr0 virtual networkProblem today where one hourly cron MV refresh hung (waiting on “SQL*Net message from dblink”), causing the other refreshes to queue up waiting on locks, eventually using up all the session so users got “ORA-00018: maximum number of sessions exceeded” To prevent this happening in the future, it is possible to timeout the MV refresh […]
Read More Timeout SQL materialized view refreshJava 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)http://createqrcode.appspot.com/ is good for creating QR Codes. Don’t use www.qrtag.net, because they redirect the URL to point at their own website.
Read More QR Code Tag createList out users by last activity in Atira’s Pure Research Information System with: SELECT a.username , u.name_first_name forename , u.name_last_name surname , TRUNC ( MAX ( a.entry_date ) ) last_audit_entry FROM audit_entry a , users u WHERE a.username = u.username(+) GROUP BY a.username , u.name_first_name , u.name_last_name ORDER BY 4 , 3 , 2 , […]
Read More Atira Pure Research – List Users by last activityReplace 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 ASCIIsed -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 findThis SQL works for all years from AD 9 onwards: SELECT ‘2011’ , ‘2011’ || ‘/’ || SUBSTR ( 1 + TO_NUMBER ( ‘2011’ ) , -2 ) formatted FROM DUAL ; Results: ‘201 Formatted —- ——— 2011 2011/12
Read More Display four digit year as tax financial academic yearMy notes from the 2012 Oracle User Group Scotland Conference:
Read More Oracle User Group Scotland Conference 2012This is a Pl/sql wrapper around “alter user” to allow front end interfaces (Servicedesk, Javascript or PHP web pages, etc.) to safely change passwords. CREATE OR REPLACE PROCEDURE sys.php_reset_passwords /*************************** || Name : sys.php_reset_passwords || Author : Andrew Fraser || Date : 05-Apr-2012 || Purpose : Allow users to reset their database passwords from a […]
Read More Database Password Changes from users web page