Problem 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 ASCII