Oracle SQL Tuning: Quick Test Template

Sometime using explain plan doesn't clearly give a answer to which way to write a query. This template allows a query to be run several times to give a idea of which way is faster.

BEGIN FOR a IN 1..1000 LOOP FOR b IN ( ... paste query here ... ) LOOP   NULL; END LOOP; END LOOP; END; /

Done.

Remove Oracle Job Process

Trying to kill a database job can be frustrating sometimes and they just won't die if you don;t do it right, especially if the job is owned by a different user.

So first identify the job you are looking for using:

SELECT * FROM dba_jobs ;

Then use DBMS_IJOB to remove the job. Use the value from the JOB column above as the parameter value.

EXEC SYS.DBMS_IJOB.REMOVE(?);

I'm using Oracle 11 but I believe that, although this is undocumented, this has been around for several years now.

Removed.

Oracle REGEXP to Strip Numbers From a String

This example uses the Oracle REGEXP functions to check if a string contains numbers and also strip away unwanted characters. Unful to prevent errors when converting strings to numbers with TO_NUMBER.

WITH testData AS ( SELECT '12ab34' theString FROM DUAL UNION ALL SELECT 'a12b34c' FROM DUAL UNION ALL SELECT '%1$AB2.34' FROM DUAL UNION ALL SELECT 'abcd' FROM DUAL UNION ALL SELECT '12.34' FROM DUAL UNION ALL SELECT '1234' FROM DUAL ) SELECT theString, REGEXP_REPLACE(theString,'[^0-9]') justNumbers, REGEXP_REPLACE(theString,'[^0-9\.]') numbersAndDecimal, (CASE REGEXP_INSTR(theString,'[0-9]') WHEN 0 THEN 0 ELSE 1 END) existsCheck, (CASE REGEXP_INSTR(theString,'\D') WHEN 0 THEN 1 ELSE 0 END) onlyNumbersCheck FROM testData ;

Gives the output:

THESTRING JUSTNUMBE NUMBERSAN EXISTSCHECK ONLYNUMBERSCHECK --------- --------- --------- ----------- ---------------- 12ab34 1234 1234 1 0 a12b34c 1234 1234 1 0 %1$AB2.34 1234 12.34 1 0 abcd 0 0 12.34 1234 12.34 1 0 1234 1234 1234 1 1 6 rows selected.

Oracle Queries to See Executing SQL

A couple of queries to see what is executing on the database.

The first uses v$sqltext to query executing SQL:

SELECT vs.username, vs.sid, vs.serial#, vs.machine, vst.sql_text, LTRIM(TO_CHAR(FLOOR(vs.last_call_et/3600),'00'))||':' ||LTRIM(TO_CHAR(FLOOR(MOD(vs.last_call_et,3600)/60),'00'))||':' ||LTRIM(TO_CHAR(MOD(vs.last_call_et,60),'00')) runtime FROM v$session vs, v$sqltext vst WHERE vs.status = 'ACTIVE' AND vs.username IS NOT NULL AND vs.sql_address = vst.address AND vs.sql_hash_value = vst.hash_value AND vs.audsid != USERENV('SESSIONID') ORDER BY vs.last_call_et DESC, vs.username, vst.piece ;

This one uses v$sqlarea and will get PL/SQL and SQL:

SELECT vs.username, vs.sid, vs.serial#, vs.machine, vsa.optimizer_mode, vsa.sql_text, LTRIM(TO_CHAR(FLOOR(vs.last_call_et/3600),'00'))||':' ||LTRIM(TO_CHAR(FLOOR(MOD(vs.last_call_et,3600)/60),'00'))||':' ||LTRIM(TO_CHAR(MOD(vs.last_call_et,60),'00')) last_call FROM v$session vs, v$sqlarea vsa WHERE vs.sql_hash_value = vsa.hash_value AND vs.sql_address = vsa.address AND vs.audsid != USERENV('SESSIONID') ORDER BY vs.last_call_et DESC, vs.username ;

Done.

Oracle OWA_UTL Error

Using Oracle 11, I'm just trying to debug some code that uses the owa package and I'm getting the following error because I'm not testing it in a web environment.

ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYS.OWA_UTIL", line 328 ORA-06512: at "SYS.OWA_UTIL", line 2172 ORA-06512: at "XXXXXXXXXXXXXXXXXXX", line 383

To solve this we need a wrapper around our code to get the owa package to work.

DECLARE l_vcarr OWA.VC_ARR; BEGIN l_vcarr(1) := 1; OWA.INIT_CGI_ENV(l_vcarr); -- Call my code here END; /

anonymous block completed.

Linux: Convert text files from Windows to Linux

The age old question, why are line endings different between Windows and Linux files? Anyway not got time to ponder why, I just want to convert Windows files to Linux so I can get on.

tr -d "\r" < source.txt > target.txt

This will remove the carriage return (CR) from the source.txt file and just leave the line feed (LF) in the target.txt. Obviously if you use source.txt instead of target.txt you don't get a second file.

Converted.

Another Oracle Lock Query

The lock queries already posted show the oldest lock and who is waiting on a lock. However often you just want a plain who has locks on a table...so.

SELECT do.object_name, do.object_type, vs.username, vs.osuser, vs.sid||','||vs.serial# sid_serial, ( CASE glo.locked_mode WHEN 0 THEN 'None' WHEN 1 THEN 'Null' WHEN 2 THEN 'Row-S (SS)' WHEN 3 THEN 'Row-X (SX)' WHEN 4 THEN 'Share' WHEN 5 THEN 'S/Row-X (SSX)' WHEN 6 THEN 'Exclusive' ELSE TO_CHAR(glo.locked_mode) END ) lock_mode, do.status object_status FROM dba_objects do, gv$locked_object glo, v$session vs WHERE do.object_id = glo.object_id AND glo.session_id = vs.sid ORDER BY 1,3

Done.

Linux: Find the Top 40 Largest Files in Directory

This command finds the largest 40 files in and under the current directory.

find . -type f -print0 | xargs -0 du -h | sort -hr | head -40

Found.