dbms_output and the scheduler

One of the nifty things in 12c is the ability to pick up DBMS_OUTPUT output from your scheduler jobs. So if you haven’t built an extensive instrumentation or logging facility, you’ll still have some details you can pick up from the scheduler dictionary views. Let’s look at an example

SQL> create or replace
  2  procedure do_stuff is
  3  begin
  4    dbms_output.put_line('Commenced');
  5    dbms_lock.sleep(30);
  6    dbms_output.put_line('Working');
  7    dbms_lock.sleep(30);
  8    dbms_output.put_line('Done');
  9   (more...)

Granular detail from a summary

We had an interesting question on AskTom a few days ago.  Given a set of 12 values (forecasts in this case), one for each month of the year , can we manufacture a set of weekly forecasts for the same period.  Now it is perhaps a little dubious to “invent” detailed data out of summarised data, but we can come up with a reasonable algorithm for doing so.

We could simply divide each (more...)

LOGGING and temporary space

We had an interesting question on AskTom this week.  The poster had been told by their DBA that the reason their large INSERT-AS_SELECT statement was consuming lots of temporary segment space, was because the database had been recently altered to enable FORCE LOGGING, presumably to ensure easier consistency in a physical standby node.

So … here’s a simple test case to demonstrate that this assertion is wrong.

First we build up table, and then (more...)

Parsing … no big deal eh ?

Most of us have probably seen the standard demo when it comes to emphasizing the need for sharable SQL, aka, using bind variables where appropriate.  The demo traditionally compares two similar scripts, where one of them generates a lot of SQL statements with literals, and the other recasts the same script with bind variables for dramatic improvement.

Here’s a simple version I’ve whipped up:

SQL> create table t ( x int primary key) organization  (more...)


A nice little feature in 12c is the FETCH FIRST n ROWS syntax, which is a simple shorthand to avoid using inline views and the like to get a subset of the rows from what would normally be a larger resultset.

Here’s a simple example showing the syntax

SQL> select *
  2  from t
  3  order by 1
  4  fetch first 8 rows only;


8  (more...)

SQL statements using literals

16 years ago, someone “Ask-ed Tom” how to find those SQL statements that were not using bind variables.   You can see the question here (because we don’t delete stuff ever Smile) but I’ll paraphrase the answer below:

Tom took the following approach

  • take a copy of SQL statements in the library cache
  • create a routine that would hunt for constants in the SQL text (that is, numbers and anything within quotes) and replace them (more...)


I wrote a few years back that for single row operations, MERGE might in fact have a large overhead than the do-it-yourself approach (ie, attempt an update, if it fails, then do an insert).

Just to show that it’s always good to revisit things as versions change, here’s the same demo (scaled up now because my laptop is faster Smile)

As you can see, there is still a little difference between between the two operations. (more...)

Datatypes for DATES

Richard Foote has written a post about not using the DATE datatype for storing dates.

So I’ve come up with a revolutionary system to store dates as well…using the very efficient RAW datatype.

Here’s a demo

SQL> create table t ( x raw(7) );

Table created.

SQL> create or replace
  2  procedure store_date(p_yyyymmddhh24miss varchar2) is
  3  begin
  4    insert into t
  5    values
  6      (
  7        hextoraw(
  8         to_char(to_number(substr(p_yyyymmddhh24miss,1,2))+100,'FM0X')||
  9         to_char(to_number(substr(p_yyyymmddhh24miss,3,2))+100,'FM0X')||
 10         to_char(to_number(substr(p_yyyymmddhh24miss,5,2)),'FM0X')||
 11          (more...)

Let me START WITH sequences

It’s always cool that you can learn stuff every single day, even on the most simple of topics.  This one came from an AskTom question.  We define a sequence to start with 100, and then alter it to modify the INCREMENT BY.

SQL> create sequence test_seq INCREMENT BY 25 START WITH 100 MAXVALUE 1000 NOCACHE NOCYCLE;

Sequence created.

SQL> @pt "select * From user_sequences where sequence_name = 'TEST_SEQ'"
SEQUENCE_NAME                 : TEST_SEQ
MIN_VALUE                     :  (more...)

A cool thing with EXCHANGE PARTITION (part 1)

“Common sense” would tell us that if I am running a query against a table, and then all of a sudden I rip out a giant chunk of that table with a DDL command, then either

  • the query should crash, or
  • the DDL command should not be permitted to run.

But in fact, with Oracle, we can even go one better in many situations.  We can allow the DDL and still have the query (more...)