Category Archives: Scripts

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

HTML formatted emails with utl_smtp

Posted on 27 September 2010 by Andrew Fraser

HTML format emails allows images, fonts, colours, hyperlinks. It can be done with utl_smtp (after the jump) and with utl_mail

Posted in Email, PL/SQL, Scripts | Tagged | Leave a comment

Select out all code refererencing some tables

Posted on 26 August 2010 by Andrew Fraser

This allows you to get code listings for all code (procedures, views, materialized views, etc.) that reference particular tables: set long 200000 pages 0 verify off lines 131 feed off column txt format a121 word_wrapped column spoolfile new_value spoolfile noprint … Continue reading →

Posted in Scripts | Leave a comment

Make indexes unusable before big insert

Posted on 30 July 2010 by Andrew Fraser

Make indexes unusable before doing a big insert, then rebuild them at the end, is faster than insert with indexes in place and no risk of forgetting to recreate a dropped index: create table af ( mycol varchar2(100) ) ; … Continue reading →

Posted in Scripts | Leave a comment