Blog Archives

TKProf by ASH

Oracle session trace + tkprof is still the best tool for performance diagnosis. But: Need access to database server user_dump_dest directory. Has to be switched on in advance Has a performance impact, especially if tracing multiple sessions or tracing for

Posted in Performance tuning, Scripts

oracle update select merge

If you want to update one table based on the values in another table, use ‘UPDATE = (SELECT) WHERE (SELECT)’ or ‘MERGE WHERE’, as in examples (3) and (3a) below. Sample data: DROP TABLE a ; CREATE TABLE a (

Posted in 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

Posted in Scripts, Security

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

Posted in Scripts

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

Posted in Scripts

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

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

Posted in Scripts, Security

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

Posted in Database links, Scripts

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

Posted in Oracle forms, Scripts, Security

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 —– —– –

Posted in Scripts