It’s back!

Yes indeed! Now that the dates and times are available for OpenWorld 2019, then it is naturally time for the best data searching, filtering and analysis tool on the planet to step up to the plate, enter the fray and …. hmmm… I’ve run out of metaphors πŸ™‚

But of course, if you have data to view, then it is Application Express that offers so much more flexibility than the standard session catalog.

So make your way toΒ and start planning out your OpenWorld right now!

See you in San Francisco!


HIGH_VALUE (and other LONG columns) revisited

Just a quick post to ensure people don’t get caught out by a recent “trick” I saw on an AskTOM question for extracting the HIGH_VALUE out of it’s LONG storage in the dictionary to a more appropriate datatype. A reviewer (I’m sure in good faith) posted the following mechanism to extract the HIGH_VALUE

SQL> create table t1 ( x timestamp )
  2  partition by range ( x )
  3  ( partition p1 values less than ( timestamp '2019-01-01 00:00:00' ),
  4    partition p2 values less than ( timestamp '2019-02-01 00:00:00' )
  5  );

Table created.

SQL> set serveroutput on
SQL> declare
  2    v long;
  3    d date;
  4  begin
  5    for i in ( select high_value from user_tab_partitions where table_name = 'T1' )
  6    loop
  7      execute immediate 'select cast(:hv as date) from dual' into d using  i.high_value;
  8      dbms_output.put_line(d);
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

You can see that we are casting the HIGH_VALUE as a date, and voila, out pops the values in a nice DATE datatype. That all seems well and good, but it only works for TIMESTAMP partitions and not the general case. Let’s replace our table with a DATE based partitioning scheme

SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 ( x date )
  2  partition by range ( x )
  3  ( partition p1 values less than ( date '2019-01-01' ),
  4    partition p2 values less than ( date '2019-02-01' )
  5  );

Table created.

SQL> set serveroutput on
SQL> declare
  2    d date;
  3  begin
  4    for i in ( select high_value from user_tab_partitions where table_name = 'T1' )
  5    loop
  6      execute immediate 'select cast(:hv as date) from dual' into d using i.high_value;
  7      dbms_output.put_line(d);
  8    end loop;
  9  end;
 10  /
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 6
ORA-06512: at line 6

There are plenty of methods to overcome the limitations of LONG. Here’s the one I typically use – just being a small variant on the code above

SQL> set serveroutput on
SQL> declare
  2    d date;
  3  begin
  4    for i in ( select high_value from user_tab_partitions where table_name = 'T1' )
  5    loop
  6      execute immediate 'select '||i.high_value||' from dual' into d;
  7      dbms_output.put_line(d);
  8    end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

which I’ve also covered in video form here.

But just be careful using the CAST trick. It might cause you grief depending on the partitioning key you are using.

And if you’re wondering why the image associated with this post is that of a horse. It’s the old joke: “A horse walks into a bar, and the barman says: Why the LONG face?” πŸ™‚

Same dog, different leash – functions in SQL

Let’s start with this somewhat odd looking result. I have an inline function that returns a random number between 0 and 20, and I call that for each row in ALL_OBJECTS and then I extract just those rows for which the generated random number is 10. Seems simple enough….but why do I get results for which the value of the second column is most certainly not 10?

SQL> with
  2    function
  3      getrand(pval in number) return number is
  4    begin
  5      return round(dbms_random.value(0,20));
  6    end;
  7  my_rnd as
  8    ( select object_id, getrand(object_id) getrnd from all_objects  )
  9  select *
 10  from my_rnd
 11  where getrnd = 10;
 12  /

---------- ----------
        29          1
        53         10
        42          8
        30          3
        78         16
        87         18
        89          1
       145         12
       155         13
       175         15
       183         12
       198         15

This is just a new variation of the same old theme – determinism.

There is no guarantee when and how many times the database will execute a function within a single invocation of a SQL statement, or even how many times it will be executed for a single candidate row the SQL statement is processing. The optimizer is totally entitled to shift it to anywhere in the plan. What does this mean ? It means only deterministic functions make sense in SQL. Let’s model the above with a simpler example:

SQL> create table t as select * from dba_objects where rownum <= 20;

Table created.

SQL> create or replace
  2  function xgetrand(pval in number) return number is
  3    begin
  4      return round(dbms_random.value(0,20));
  5    end;
  6  /

Function created.

SQL> select *
  2  from (
  3    select object_id, xgetrand(object_id) gg from t
  4  )
  5  where gg = 10;

 OBJECT_ID         GG
---------- ----------
        38          2
         6          8

Immediately that looks odd, because the SQL reads like “I only want GG=10” yet I got back “2” and “8”. But if we drill into the execution plan, we’ll see this:

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |      |     1 |     3 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     3 |     3   (0)| 00:00:01 |

Predicate Information (identified by operation id):

   1 - filter("XGETRAND"("OBJECT_ID")=10)

Notice the FILTER information. The query has been transformed to be a simple:

SQL> select object_id, xgetrand(object_id) gg from t
  2  where xgetrand(object_id) = 10;

If you’re doubtful of that claim, we can validate it by dumping a 10053 trace. In that you’ll find the following:

Final query after transformations:******* UNPARSED QUERY IS *******

Already you can see the scope for the function being called twice per row – once for the WHERE clause, and once for the SELECT part. In fact, for all we know it could be called three times, or four times. You (the coder) does not have control over that decision. For the sake of discussion, let’s assume it is called only twice. The first execution of the function (on line 2 above) returned 10 twice across the 20 rows (because we got 2 rows back in the result set), but then we ran the function again (on line1) as we gave back each row, hence the counter-intuitive output.

In the original example, ALL_OBJECTS is being used as a source for rows which is a complex view. The function call could be pushed anywhere deep into this view, which means it might be executed tens of thousands of times, and many of those executions might return a result of “10”.

The bottom line remains the same: non-deterministic means trouble for functions in SQL.

MERGE and ORA-8006

I’m sure there will be a slew of post-Kscope wrap up posts coming out into the blogosphere, so in lieu of that, and the the fact that I’m just stuck in an airport waiting for a flight, I’ll offer something slightly more technical. I did a post a while back about a curious error “unable to get a stable set of rows” when using MERGE. Here is another variant which can occur when you allow rows to physically move during a MERGE.

How is that possible?” I hear you ask. Easy. All we need is partitioned table with ENABLE ROW MOVEMENT.

SQL> create table t (pk number primary key, x number)
  2      partition by list (pk)
  3      (partition p1 values(1),
  4       partition p2 values(2)
  5      )
  6  enable row movement;

Table created.

SQL> insert into t values (1, 1);

1 row created.

SQL> commit;

Commit complete.

SQL> merge into t
  2  using (select 1 idx, 2 new_val from dual
  3         connect by level <= 2
  4        ) u
  5  on (t.x = u.idx)
  6  when matched then
  7    update set pk=new_val;
merge into t
ERROR at line 1:
ORA-08006: specified row no longer exists

You can see that the merge is going to first change the PK column values from 1 to 2, which will move the row from one partition to another. The second row from the source (CONNECT BY LEVEL <= 2) will go hunting for that original value of 1 in its original partition and not find it there. Hence it “no longer exists”.

This is just another example of why you might want to consider cleansing input data for sensibility before aimlessly throwing it at a MERGE command.

SMON_SCN_TIME and ORA-8161? Digging deeper

In the recent versions of the Oracle database, we’ve had the ability to convert between a System Change Number (SCN) and the approximate time to which that SCN pertains. These functions are unsurprisingly called SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN. The only potential misnomer here is that even though the functions are called “timestamp” and return a datatype of timestamp, on most platforms you are going to notice that the granularity doesn’t run down into fractions of seconds

SQL> select scn_to_timestamp(14816563713652) from dual;

08-JUN-19 AM

This all looks great until you start poking around too far into the past, and you end up in territory like this:

SQL> select scn_to_timestamp(14816563693489) from dual;
select scn_to_timestamp(14816563693489) from dual
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

If I poke around in V$LOG_HISTORY, then I can pretty easily confirm that SCN 14816563693489 was indeed a valid SCN for this database at some stage in the past, so the fact that we can encounter ORA-08181 suggests that there is a finite structure that holds the mapping between SCNs and the time at which those SCNs pertain to. And indeed there is. The table is called SYS.SMON_SCN_TIME

SQL> desc sys.smon_scn_time
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------
 THREAD                                             NUMBER
 TIME_MP                                            NUMBER
 TIME_DP                                            DATE
 SCN_WRP                                            NUMBER
 SCN_BAS                                            NUMBER
 NUM_MAPPINGS                                       NUMBER
 TIM_SCN_MAP                                        RAW(1200)
 SCN                                                NUMBER
 ORIG_THREAD                                        NUMBER

and in most instances, it will hold around a week’s worth of SCN to timestamp mapping information.

SQL> select min(time_dp), max(time_dp) from sys.smon_scn_time;

--------- ---------
07-JUN-19 13-JUN-19

SQL> select count(*) from  sys.smon_scn_time;


When SMON_SCN_TIME first came into existence, the granularity of data in this table was an entry approximately every 5 minutes. Hence you could map an SCN to, at best, a 5 minute window. In more recent releases, this has been improved to a granularity of 3 seconds. But the table does not appear to reflect that evolution if you look at some sample data.

SQL> select scn, time_dp
  2  from sys.smon_scn_time s
  3  where rownum <= 10;

               SCN TIME_DP
------------------ -------------------
    14816565366476 11/06/2019 08:25:11
    14816565366679 11/06/2019 08:30:38
    14816565366808 11/06/2019 08:35:11
    14816565367164 11/06/2019 08:40:44
    14816565367291 11/06/2019 08:45:12
    14816565367475 11/06/2019 08:50:32
    14816565029366 10/06/2019 01:28:13
    14816565029605 10/06/2019 01:33:40
    14816565032515 10/06/2019 01:38:13
    14816565032779 10/06/2019 01:43:40

It still has a row every 5 minutes to handle backward compatibility, and to drill down to the 3 second level, you are expected to use the functions SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN already mentioned.

But what if you wanted to store this SCN to timestamp mapping for longer? Activating flashback data archive is one way of achieving this, but what if you just wanted to store the mapping forever without doing this? At first, it seems to be a simple task. I can simply get the lowest SCN from SMON_SCN_TIME:

SQL> select min(scn) scn from   sys.SMON_SCN_TIME;


and then use a familiar CONNECT BY technique to roll through the SCNs from that point onward

SQL> select 14816563714099+rownum scn,  
  2         scn_to_timestamp(14816563714099+rownum) ts
  3  from ( select 1 from dual connect by level <= 50 );

               SCN TS
------------------ -----------------------------------
    14816563714100 08-JUN-19 AM
    14816563714101 08-JUN-19 AM
    14816563714102 08-JUN-19 AM
    14816563714103 08-JUN-19 AM
    14816563714104 08-JUN-19 AM
    14816563714105 08-JUN-19 AM
    14816563714106 08-JUN-19 AM
    14816563714107 08-JUN-19 AM
    14816563714108 08-JUN-19 AM
    14816563714109 08-JUN-19 AM
    14816563714110 08-JUN-19 AM
    14816563714111 08-JUN-19 AM
    14816563714112 08-JUN-19 AM
    14816563714113 08-JUN-19 AM
    14816563714114 08-JUN-19 AM
    14816563714115 08-JUN-19 AM
    14816563714116 08-JUN-19 AM
    14816563714117 08-JUN-19 AM
    14816563714118 08-JUN-19 AM
    14816563714119 08-JUN-19 AM
    14816563714120 08-JUN-19 AM
    14816563714121 08-JUN-19 AM
    14816563714122 08-JUN-19 AM
    14816563714123 08-JUN-19 AM
    14816563714124 08-JUN-19 AM
    14816563714125 08-JUN-19 AM
    14816563714126 08-JUN-19 AM
    14816563714127 08-JUN-19 AM
    14816563714128 08-JUN-19 AM
    14816563714129 08-JUN-19 AM
    14816563714130 08-JUN-19 AM
    14816563714131 08-JUN-19 AM
    14816563714132 08-JUN-19 AM
    14816563714133 08-JUN-19 AM

But if we are going to store this data, we can start to see some things that can be improved.

Firstly, a lot of rows have the same timestamp. As mentioned, there is only a granularity of 3 seconds, and we can certainly burn through a lot of SCNs in a 3 second period. I can use some simple analytics to see if a timestamp matches the trailing one and filter it out if that is the case.

SQL> with
  2  t as
  3    ( select min(scn) lo from   sys.smon_scn_time ),
  4  all_scns as
  5    ( select lo+rownum scn, scn_to_timestamp(lo+rownum) ts
  6      from  t
  7      connect by level <= 100 ),
  8  delta_scns as
  9    ( select a.*, row_number() over ( partition by ts order by scn ) as rn
 10      from all_scns a )
 11  select * from delta_scns
 12  where rn = 1;

               SCN TS                                               RN
------------------ ---------------------------------------- ----------
    14816563714100 08-JUN-19 AM                   1
    14816563714101 08-JUN-19 AM                   1
    14816563714102 08-JUN-19 AM                   1
    14816563714111 08-JUN-19 AM                   1
    14816563714112 08-JUN-19 AM                   1
    14816563714113 08-JUN-19 AM                   1
    14816563714114 08-JUN-19 AM                   1
    14816563714115 08-JUN-19 AM                   1
    14816563714116 08-JUN-19 AM                   1
    14816563714117 08-JUN-19 AM                   1
    14816563714118 08-JUN-19 AM                   1
    14816563714119 08-JUN-19 AM                   1
    14816563714120 08-JUN-19 AM                   1
    14816563714121 08-JUN-19 AM                   1
    14816563714122 08-JUN-19 AM                   1
    14816563714123 08-JUN-19 AM                   1
    14816563714124 08-JUN-19 AM                   1
    14816563714125 08-JUN-19 AM                   1
    14816563714126 08-JUN-19 AM                   1
    14816563714127 08-JUN-19 AM                   1
    14816563714128 08-JUN-19 AM                   1
    14816563714129 08-JUN-19 AM                   1

Secondly, we need to stop at a particular point. I can easily get the highest SCN from the table

SQL> select max(scn) scn from   sys.smon_scn_time;


but that is when things start to get problematic. Even for just this small set of data (just a few days), on my almost entirely idle database (its my laptop):

SQL> select max(scn) - 14816563714100 from   sys.smon_scn_time;


you can see that I’ve burned through over 2million SCNs. When I put that back into my query

SQL> with
  2  t as
  3    ( select min(scn) lo, max(scn) hi from   sys.smon_scn_time ),
  4  all_scns as
  5    ( select lo+rownum scn, scn_to_timestamp(lo+rownum) ts
  6      from  t
  7      connect by level <= hi-lo  )
  8  delta_scns as
  9    ( select a.*, row_number() over ( partition by ts order by scn ) as rn
 10      from all_scns a )
 11  select * from delta_scns
 12  where rn = 1

it took a looonnnggg time to come back. This is understandable – it is millions of calls to extract a timestamp, followed by some heavy duty analytics to remove duplicates. It has become a very expensive operation to perform.

So that got me thinking – how does the database do it? I started with – how does the database get down to 3 second granularity when there is still only a row every 5 minutes in SMON_SCN_TIME? This is where the TIM_SCN_MAP column comes into play.

SQL> select scn, num_mappings, tim_scn_map
  2  from sys.smon_scn_time
  3  where scn = 14816565797824

------------------ ------------ -----------------------------------------------------------------------------
    14816565797824          100 2F90015DC12324C0790D00003290015DC22324C0790D00003590015DC32324C0790D0000...

Searching along raw data and looking for patterns, I could see that the “D0000” was repeated every 24 characters (or 12 bytes). A simple check confirmed that this appears to equate to the NUM_MAPPINGS column, so it is reasonable to assume that the raw data is a set of time mappings. Given that the maximum value for NUM_MAPPINGS is 100, this also fits the hypothesis because 100 x 3 seconds granularity yields 5 minutes, which is how often we get a row in SMON_SCN_TIME

SQL> select scn, num_mappings, length(tim_scn_map)/24
  2  from   sys.smon_scn_time
  3  /

------------------ ------------ ----------------------
    14816565366476          100                    100
    14816565366679           83                     83
    14816565366808          100                    100
    14816565367164           82                     82
    14816565367291          100                    100
    14816565367475           86                     86
    14816565029366          100                    100

So 24 characters (12 bytes) is probably an occurrence of a SCN to timestamp mapping. The task now is dig out that information from that raw encoding. I’ll grab that first 12 bytes from the row above 2F90015DC12324C0790D0000 and see if we can align it to other values in the table. The first thing I noticed is that ‘790D0000’ rarely changes across the whole table, so I converted that to decimal to see if I could match it to anything. We have to swap the bytes first, and then I got:

SQL> select to_number('0D79','xxxx') from dual;


That matched the SCN_WRP column in SMON_SCN_TIME. Grabbing the next 4 bytes gives

SQL> select to_number('C02423C1','xxxxxxxx') from dual;


That matched the SCN_BAS column in SMON_SCN_TIME. So SCN_WRP and SCN_BAS are enough to form a complete SCN number. Which would mean that the remaining 4 bytes should be the time information.

SQL> select to_number('5D01902F','xxxxxxxx') from dual;


Obviously that does not look like a date or a time, but this is an internal format that is seen in various places around Oracle database, for example, in redo logs etc. To explain where it comes from, it is better to approach it from the other direction (starting with time).

Lets say I wanted a unique number for clock time of just hours and minutes (eg: 09:37). I could do something like:

unique val = hours * 100 + minutes

That would work because I know that minutes is capped at 59, so there is no chance of an overlap by using a multiplier of 100. So here is how that concept can be taken to its full extent to get a unique number for a full date and time. I’ll pick a date/time of 2019-6-12 23:52:15 as an example

  • Take the year, 2019.
  • We don’t need anything before Oracle was “born”, so subtract 1976 = 43
  • For the months, multiply by 13 (anything more than 12 months) = 559
  • Because we’re IT nerds, we’ll start months at zero (0=January), so we add 5 for June = 564
  • For the day in a month, multiply by 32 (anything more than 31) = 18048
  • Because we’re IT nerds, we’ll start day of month at zero (=1st), so we add 11 for the 12th = 18059
  • For the hours, multiply by 24 (because hours never exceed 23) = 433416
  • No need for nerdy stuff here, because hours are already normalised at 0-23, so we add 23 = 433439
  • For the minutes, multiply by 60 (minutes are 0 to 59) = 26006340
  • Then add the minutes = 26006392
  • Same for the seconds, multiply by 60 = 1560383520
  • and finally we add the seconds = 1560383535

And voila! You can that it matches the decimal expansion of the 4 bytes of 5D01902F above. Now that we know how the time to number conversion is done, a little PL/SQL lets the reverse be done (number to time):

SQL> declare
  2    t int := 1560383535;
  3    secs int;
  4    mins int;
  5    hrs int;
  6    dy int;
  7    mon int;
  8    yr int;
  9  begin
 10   secs := mod(t,60);
 11   t := t - secs;
 12   t := floor(t / 60);
 13   mins := mod(t,60);
 14   t := t - mins;
 15   t := floor(t / 60);
 16   hrs := mod(t,24);
 17   t := t - hrs;
 18   t := floor(t / 24);
 19   dy := mod(t,32);
 20   t := t - dy;
 21   t := floor(t / 32);
 22   dy := dy + 1;
 23   mon := mod(t,13);
 24   t := t - mon;
 25   mon := mon + 1;
 26   t := floor(t / 13);
 27   yr := t + 1976;
 28   dbms_output.put_line(yr||'-'||mon||'-'||dy||' '||hrs||':'||mins||':'||secs);
 29  end;
 30  /
2019-6-12 23:52:15

To make all of this simpler and modular, I’ll build a couple of functions to bring all the bits and pieces we’ve learnt together. First a function to take a non-byte swapped hex string to a SCN

SQL> create or replace
  2  function raw_to_scn(p_scn varchar2) return number is
  3  begin
  4    return to_number(
  5                  substr(p_scn,7,2)||
  6                  substr(p_scn,5,2)||
  7                  substr(p_scn,3,2)||
  8                  substr(p_scn,1,2),'xxxxxxxx');
  9  end;
 10  /

Function created.

and then a function that will extend the anonymous block above to take a non-byte swapped hex string and return a timestamp (well, a date in reality).

SQL> create or replace
  2  function raw_to_date(p_time varchar2) return date is
  3    t int := to_number(
  4                  substr(p_time,7,2)||
  5                  substr(p_time,5,2)||
  6                  substr(p_time,3,2)||
  7                  substr(p_time,1,2),'xxxxxxxx');
  8    secs int;
  9    mins int;
 10    hrs int;
 11    dy int;
 12    mon int;
 13    yr int;
 14  begin
 15   secs := mod(t,60);
 16   t := t - secs;
 17   t := floor(t / 60);
 18   mins := mod(t,60);
 19   t := t - mins;
 20   t := floor(t / 60);
 21   hrs := mod(t,24);
 22   t := t - hrs;
 23   t := floor(t / 24);
 24   dy := mod(t,32);
 25   t := t - dy;
 26   t := floor(t / 32);
 27   dy := dy + 1;
 28   mon := mod(t,13);
 29   t := t - mon;
 30   mon := mon + 1;
 31   t := floor(t / 13);
 32   yr := t + 1976;
 33   return to_date(yr||'-'||mon||'-'||dy||' '||hrs||':'||mins||':'||secs,'yyyy-mm-dd hh24:mi:ss');
 34  end;
 35  /

Function created.

With these in place, I can create a couple of types to serve as return datatypes for a pipelined function

SQL> create or replace
  2  type scn_dte as object
  3    ( scn int, dte date );
  4  /

Type created.

SQL> create or replace
  2  type scn_dte_nt as table of scn_dte
  3  /

Type created.

and here is a function that will take important components from SMON_SCN_TIME namely SCN_BAS, NUM_MAPPINGS, SCAN_WRAP and the raw data in TIM_SCN_MAP to spit out the 3-second interval data rather than just 1 row per 5 minutes.

SQL> create or replace
  2  function full_smon_scn_timestamp(p_start_scn int default 0) return scn_dte_nt pipelined as
  3    l_map varchar2(2400);
  4  begin
  5    for i in ( select * from sys.smon_scn_time
  6               where scn > p_start_scn
  7               order by scn
  8             )
  9    loop
 10      pipe row ( scn_dte(i.scn_wrp * 4294967296 + i.scn_bas,i.time_dp));
 11      l_map := i.tim_scn_map;
 12      for j in 1 .. i.num_mappings
 13      loop
 14         pipe row (
 15           scn_dte(
 16             i.scn_wrp * 4294967296 + raw_to_scn(substr(l_map,9,8)),
 17             raw_to_date(substr(l_map,1,8))
 18             )
 19          );
 20         l_map := substr(l_map,25);
 21      end loop;
 22    end loop;
 23  end;
 24  /

Function created.

Let’s give that function a run to see what comes out:

SQL> select * from full_smon_scn_timestamp()
  2  where rownum <= 20;

               SCN DTE
------------------ -------------------
    14816563726597 07/06/2019 21:50:43
    14816563726598 07/06/2019 21:50:46
    14816563726599 07/06/2019 21:50:49
    14816563726600 07/06/2019 21:50:52
    14816563726601 07/06/2019 21:50:55
    14816563726602 07/06/2019 21:50:58
    14816563726603 07/06/2019 21:51:01
    14816563726604 07/06/2019 21:51:04
    14816563726605 07/06/2019 21:51:07
    14816563726606 07/06/2019 21:51:10
    14816563726607 07/06/2019 21:51:13
    14816563726608 07/06/2019 21:51:16
    14816563726609 07/06/2019 21:51:19
    14816563726610 07/06/2019 21:51:22
    14816563726611 07/06/2019 21:51:25
    14816563726612 07/06/2019 21:51:28
    14816563726633 07/06/2019 21:51:34
    14816563726634 07/06/2019 21:51:37
    14816563726635 07/06/2019 21:51:40
    14816563726636 07/06/2019 21:51:43

Each row in SMON_SCN_TIME contains a starting value for the SCN plus all of the mappings, so the total number of 3-second intervals is:

SQL> select sum(num_mappings+1) from sys.smon_scn_time;


and I can compare that to the total number of rows that will be returned from my function

SQL> select count(*) from full_smon_scn_timestamp();


So there you have a way to grab all of those 3-second granularity entries in SMON_SCN_TIME without having to call SCN_TO_TIMESTAMP millions of time. You could run this function (say) daily by passing in a starting SCN value equal to the highest value you have so far retrieved to build up your own custom SMON_SCN_TIME variant that you can keep for as long as you want.

Of course, poking around in raw fields is totally unsupported, so don’t come crying to me if you decide to use this for anything more than exploration and one day it all blows up in your face πŸ™‚

Kscope Sunday will be awesome

Yeah yeah I know. What kind of dufus cut-pastes a giant image into their blog post. That would be ….. me Smile

But the reality is, there is just soooooo much going on this year on the Sunday before the “official” start of the Kscope conference, it was too hard to condense it into a few sentences. So I just dumped the image from the website, but you can read about it here.

It’s my first Kscope and it will be blast to be running a day of high quality but dirt cheap learning with Maria, Jeff, Steven and Blaine.

And we’ll even have some fun and games. How good is your knowledge of the Oracle Database and the Oracle community? Our Sunday quiz will let you shine! (Well, to be honest, we’ve designed the quiz so that anyone in the community has a chance to win, so come along to have some fun and maybe pick up some prizes as well!)

See you in Seattle!



With and without WITH_PLSQL within a WITH SQL statement

OK, let’s be honest right up front. The motivation for this post is solely to be able to roll out a tongue twisting blog post title Smile. But hopefully there’s some value as well in here for you if you’re hitting the error:

ORA-32034: unsupported use of WITH clause

First some background. A cool little enhancement to the WITH clause came along in 12c that allowed PLSQL functions to be defined within the scope of the executing SQL statement. To see the benefit of this, consider the following example that I have a personal affinity with (given my surname).

Let’s say I’ve allowed mixed-case data in a table that holds names.

SQL> select surname
  2  from   names;


There’s nothing inherently wrong here, but in terms of rendering that data in a report or on screen, it would be nice to have some consistency.

“No problem” you think, “I’ll just slap an INITCAP in there”

SQL> select initcap(surname)
  2  from   names;


That works fine of course until …. yours truly gets added into the mix Smile. After a couple of new rows are added, we can start to see the shortcomings of INITCAP.

SQL> select initcap(surname)
  2  from   names;


I’d like a capital D, and letters that follow apostrophes have some nuances that might need handling. This can be solved without too much fuss – with a little bit of PLSQL I can produce a custom version of INITCAP that will handle these exceptional cases.

SQL> create or replace
  2  function MY_INITCAP(p_string varchar2) return varchar2 is
  3    l_string varchar2(1000) := p_string;
  4  begin
  5    if regexp_like(l_string,'(Mac[A-Z]|Mc[A-Z])') then
  6        null;
  7    elsif l_string like '''%' then
  8        null;
  9    else
 10      l_string := initcap(l_string);
 11      if l_string like '_''S%' then
 12         null;
 13      else
 14         l_string := replace(l_string,'''S','''s');
 15      end if;
 16    end if;
 18    return l_string;
 19  end;
 20  /

SQL> select my_initcap(surname)
  2  from   names;


But perhaps I’d like that functionality inline with the SQL so that a future maintainer can directly see what I’ve done. Yes, I could refactor the code to be 100% SQL with no reliance on PLSQL using something like this:

SQL> select
  2    case
  3      when regexp_like(surname,'(Mac[A-Z]|Mc[A-Z])') then surname
  4      when surname like '''%' then surname
  5      when initcap(surname) like '_''S%' then surname
  6      else replace(initcap(surname),'''S','''s')
  7    end ugh
  8  from names;


But if I’m doing this to help a future maintainer….well… that convoluted CASE statement probably isn’t such a nice remnant for them Smile. So since 12c, we’ve been able to add that PLSQL code directly within the SQL statement itself.

  2    function my_initcap(p_string varchar2) return varchar2 is
  3      l_string varchar2(1000) := p_string;
  4    begin
  5      if regexp_like(l_string,'(Mac[A-Z]|Mc[A-Z])') then
  6          null;
  7      elsif l_string like '''%' then
 18      return l_string;
 19    end;
 20  select my_initcap(surname)
 21  from   names;


Hopefully you can now see the benefit of the feature. Now back to the topic at hand, the ORA-32034 error. If you attempt to use the feature within an INSERT, UPDATE or DELETE statement, you’ll get a surprise:

SQL> insert into TARGET_TABLE
  2  WITH
  3    function my_initcap(p_string varchar2) 
  4                return varchar2 is
  5      l_string varchar2(1000) := p_string;
  6    begin
 20    end;
 21  select my_initcap(surname)
 22  from   names;
 23  /

ERROR at line 2:
ORA-32034: unsupported use of WITH clause

To overcome this, you need to specify the WITH_PLSQL hint

SQL> insert /*+ WITH_PLSQL */ into TARGET_TABLE
  2  WITH
  3    function my_initcap(p_string varchar2) 
  4                return varchar2 is
  5      l_string varchar2(1000) := p_string;
  6    begin
 20    end;
 21  select my_initcap(surname)
 22  from   names;
 23  /

5 rows inserted.

I’ve never ascertained the reason precisely why the hint is needed (I’m asking around internally within the database group), but I have a hypothesis: Given that you could potentially write anything within an PLSQL function (including independent transactions), my guess is that the hint is a flag to the database to say “Have an extra careful double-check of the function code to make sure it’s not doing anything dangerous, in particular, to the table we doing the DML on”.

I might be 100% wrong here – I’ll update the post if I get more information in future.