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 || Date : 05-Apr-2012 || Purpose : Allow users to reset their database passwords from a […]
Read More Database Password Changes from users web pageI’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 fle in `ls /var/spool/cron/* | grep -v tmp` do echo $fle =============== grep -v ‘^#’ […]
Read More List all crons and database links with scriptsShell 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 successful” ] then echo orcladmin account is ok, is not locked. else echo unlocking orcladmin […]
Read More unlock orcladmin password in shell scriptSplit 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 […]
Read More Split space delimited string with regexp SQLHere 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 identical cp -p $fle test_$fle # replace entire 2nd line with a new 2nd line […]
Read More sed change entire lineOld 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 off
Read More Remove dba_2pc_pending records# 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 the -delete option, so instead: # Delete files older than 15 minutes find /ORA_DISK/redoarch/ -name […]
Read More Find delete old filesHTML format emails allows images, fonts, colours, hyperlinks. It can be done with utl_smtp (after the jump) and with utl_mail
Read More HTML formatted emails with utl_smtpThis 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 spool go.tmp select ‘@2 ‘|| decode(type , ‘MATERIALIZED VIEW’ , ‘MATERIALIZED_VIEW’ , type) ||’ ‘||name||’ […]
Read More Select out all code refererencing some tablesMake 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) ) ; create index af1 on af ( mycol ) ; alter index af1 unusable ; insert […]
Read More Make indexes unusable before big insert