January 2012

First day of the month (or year)

A little known but much easier way to get the first day of the month: SQL> select TRUNC(SYSDATE,’MONTH’) FROM DUAL; TRUNC(SYS ——— 01-JAN-12 Much easier than the more common TO_DATE(’01-‘||TO_CHAR(v_date,’MON-YYYY’)). It works for Year too: SQL> select TRUNC(TO_DATE(’15-AUG-2012′),’YEAR’) FROM DUAL; TRUNC(TO_ ——— 01-JAN-12

Read more →

“New” function for viewing execution plans – dbms_xplan.display_cursor

Having worked with Oracle for many (twenty!) years, I sometimes find new features released several years ago that I didn’t catch at the time. Most recently, it would be that 10g added the display_cursor function to dbms_xplan. Most usefully, you can pass in the Sql-Id (from v$sql) and get the formatted execution plan for a […]

Read more →

Eliminating RAC cluster waits for high activity databases

If you’re seeing high cluster waits because you have multiple processes all inserting into the same set of tables, the cluster waits are resolved by pointing to a single node on the cluster. But just pointing to a single node defeats the rollover benefits of running on RAC. To that end, Oracle gives us Services […]

Read more →

Unwrapping PL/SQL code

For years the Oracle line was that a software vendor could safely send out its PL/SQL code if it was wrapped. I’m sure I was not alone in thinking that we could hide sensitive code like the application’s licensing enforcement inside Oracle’s wrapper. Turns out this isn’t the case. It has been unwrapped a number […]

Read more →

KMC Data

Granville Simpson Bonyata was the Director of Software Development at Design Data Systems prior to starting KMC Data in March 2000. Prior to working for Design Data Systems, Granville was an Information Technology Manager, implementing Human Resource and Benefits applications for several Fortune50 clients. Granville, a Florida native, has a Bachelor of Science in Computer […]

Read more →

all_arguments

all_arguments contains the parameters for all procedures and functions (including within packages). An example is finding all Oracle built-in functions (that are part of the STANDARD package): select distinct object_name from all_arguments where package_name = ‘STANDARD’ order by 1;

Read more →