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 a long time. And ASH is a good enough alternative much of the time. The […]

Read More TKProf by ASH
October 6, 2016

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 ( id VARCHAR2(10) , text1 VARCHAR2(10) , text2 VARCHAR2(10) ) ; INSERT INTO a ( id […]

Read More oracle update select merge
November 25, 2014

One Comment

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 ( ‘alter user ‘ || d1.username || ‘ account lock ‘ ) ; execute immediate […]

Read More PL/SQL to find and lock or drop user accounts
October 10, 2013

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 ORDER BY 1 , 2 ; Note that the above does not correct for any […]

Read More Convert java dates to oracle sql dates (esp. odm_publicobject createdate)
October 26, 2012

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 Update : an alternative method from Paul Bradley: select ‘ÄÊÍÕØÓÑ’ str, substr(upper(utl_raw.cast_to_varchar2((nlssort(‘ÄÊÍÕØÓÑ’, ‘nls_sort=binary_ai’)))),1,10) str2 from […]

Read More Oracle regexp_replace remove non alpha ASCII
October 2, 2012

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 || Date : 05-Apr-2012 || Purpose : Allow users to reset their database passwords from a […]

Read More Database Password Changes from users web page
April 5, 2012

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 fle in `ls /var/spool/cron/* | grep -v tmp` do echo $fle =============== grep -v ‘^#’ […]

Read More List all crons and database links with scripts
February 14, 2012

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 successful” ] then echo orcladmin account is ok, is not locked. else echo unlocking orcladmin […]

Read More unlock orcladmin password in shell script
December 5, 2011

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 […]

Read More Split space delimited string with regexp SQL
July 13, 2011

One Comment