Category Archives: Scripts

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

Posted on 26 October 2012 by Andrew Fraser

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 … Continue reading →

Posted in Scripts | Leave a comment

Oracle regexp_replace remove non alpha ASCII

Posted on 2 October 2012 by Andrew Fraser

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 … Continue reading →

Posted in Scripts | Leave a comment

Global search and replace with sed and find

Posted on 12 July 2012 by Andrew Fraser

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’.

Posted in Linux, Scripts | 1 Comment

Database Password Changes from users web page

Posted on 5 April 2012 by Andrew Fraser

This 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 || … Continue reading →

Posted in Scripts, Security | Leave a comment

List all crons and database links with scripts

Posted on 14 February 2012 by Andrew Fraser

I’m currently listing all interfaces – database links and cron jobs – into excel, and used these two scripts to automate the process. Shell script to list all crons (note that this assumes crons in /etc/cron.d run as root): for … Continue reading →

Posted in Database links, Scripts | Leave a comment

unlock orcladmin password in shell script

Posted on 5 December 2011 by Andrew Fraser

Shell script to check if orcladmin account is locked, and unlock it if required # Check to see if orcladmin account is locked, and unlock it if it is. if [ “`ldapbind -p <myport> -D cn=orcladmin -w <myorcladminpassword>`” = “bind … Continue reading →

Posted in Oracle forms, Scripts, Security | Leave a comment

Split space delimited string with regexp SQL

Posted on 13 July 2011 by Andrew Fraser

Split up a delimited string with: select regexp_substr(‘Hello world !’ ,’[^ ]+’, 1, 1) , regexp_substr(‘Hello world !’ ,’[^ ]+’, 1, 2) , regexp_substr(‘Hello world !’ ,’[^ ]+’, 1, 3) from dual ; Output: REGEX REGEX R —– —– – … Continue reading →

Posted in Scripts | Leave a comment

sed change entire line

Posted on 8 June 2011 by Andrew Fraser

Here I use sed to replace the entire 2nd line in a lot of files with a new 2nd line. The shell script: for fle in `ls *.msg` do # copy file first so as can keep permissions and ownership … Continue reading →

Posted in Linux, Scripts | Leave a comment

Remove dba_2pc_pending records

Posted on 20 December 2010 by Andrew Fraser

Old entries in dba_2pc_pending can be removed by ‘rollback force’ or if that fails, with a purge: set pages 9999 spool go.tmp select ‘rollback force ”’||local_tran_id||”’ ;’ from dba_2pc_pending ; select ‘exec dbms_transaction.purge_lost_db_entry(”’||local_tran_id||”’ )’ , ‘commit;’ from dba_2pc_pending ; spool … Continue reading →

Posted in Scripts | Leave a comment

Find delete old files

Posted on 29 October 2010 by Andrew Fraser

# Delete files older than 15 minutes find /ORA_DISK/redoarch/ -name ‘arch_*.dbf’ -mmin +15 -delete #Delete files older than 2 days 50 8 * * * find /ORA_DISK/redoarch/ -name ‘arch_*.dbf’ -mtime +2 -delete Old versions of unix find do not have … Continue reading →

Posted in Linux, Rman, Scripts | Leave a comment