Category Archives: Scripts

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

Posted in Scripts, Security | Leave a comment

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

Posted in Scripts | Leave a comment

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

Posted in Scripts | Leave a comment

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

Posted in Linux, Scripts | 1 Comment

Database Password Changes from users web page

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

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

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

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

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

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