Category Archives: Scripts

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

Move objects including lobs, xml to new tablespace with dynamic SQL

Posted on 3 May 2010 by Andrew Fraser

Here objects are moved from tablespace users to tablespace users1: set pages 9999 lines 132 spool m2.sql select ‘alter table ‘||owner||’.'||table_name||’ move lob(‘||column_name||’) store as ( tablespace users1);’ from dba_lobs where tablespace_name = ‘USERS’ order by 1 / select ‘alter … Continue reading →

Posted in Scripts, Space | Leave a comment

PL/SQL to count from all tables in a schema

Posted on 12 March 2010 by Andrew Fraser

Replace SYSTEM with the name of the schema you are interested in: set serverout on size 999999 declare cnt number ; begin for c1 in (select owner, table_name from all_tables where owner = ‘SYSTEM’) loop execute immediate ‘select count(1) from … Continue reading →

Posted in Scripts | Leave a comment