Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Ignoring Hints

One of the small changes (and, potentially big but temporary, threats) in 18.3 is the status of the “ignore hints” parameter. It ceases to be a hidden (underscore) parameter so you can now officially set parameter optimizer_ignore_hints to true in the parameter file, or at the system level, or at the session level. The threat, of course, it that some of your code may use the hidden version of the parameter (perhaps in an SQL_Patch as an opt_param() option rather than in its hint form) which no longer works after the upgrade.

But there’s more. The parameter (whether the old hidden version or the new revealed version) doesn’t make the optimizer ignore parallel() hints. But 18.3 now has a related parameter optimizer_ignore_parallel_hints to address this limitation. Here’s a quick demo – we start by creating a table and then running a query where the full tablescan is clearly the default strategy that the optimizer would take if we didn’t hint an indexed access path:

rem
rem     Script:         ignore_parallel_hints.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2019
rem 

create table t1
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        rownum                          id,
        mod(rownum,10)                  n1,
        lpad(rownum,10,'0')             v1,
        lpad('x',100,'x')               padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e4 -- > comment to avoid WordPress format issue
;

create index t1_i1 on t1(id);

set serveroutput off

prompt  =============
prompt  Baseline test
prompt  =============

select
        /*+ index(t1) */
        n1, sum(id)
from
        t1
where
        id > 0
group by
        n1
order by
        n1
;

select * from table(dbms_xplan.display_cursor);


SQL_ID  gudnnk7j7q5bz, child number 0
-------------------------------------
select  /*+ index(t1) */  n1, sum(id) from  t1 where  id > 0 group by
n1 order by  n1

Plan hash value: 356059923

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |       |       |   198 (100)|          |
|   1 |  SORT GROUP BY                       |       |    10 |    70 |   198   (2)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    | 10000 | 70000 |   196   (1)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | T1_I1 | 10000 |       |    22   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID">0)


Now we repeat the exercise with the version-specific “alter session” command below – and you should try each option with each version of Oracle if you want to do the complete test cycle – to see that the session will ignore hints and the plan will change (side note – using the underscore version  with 18.3 doesn’t raise an error, the statement is silently ignored):


alter session set "_optimizer_ignore_hints" = true;
alter session set "optimizer_ignore_hints" = true;

SQL_ID  gudnnk7j7q5bz, child number 1
-------------------------------------
select  /*+ index(t1) */  n1, sum(id) from  t1 where  id > 0 group by
n1 order by  n1

Plan hash value: 3946799371

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    27 (100)|          |
|   1 |  SORT GROUP BY     |      |    10 |    70 |    27  (12)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   | 10000 | 70000 |    25   (4)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID">0)

Then, of course, we have to test a query with a parallel() hint – or shared() hint, which is the internal equivalent you will notice occasionally in outlines or the “remote” statement for distributed execution plans – to show that we don’t yet ignore parallel queries – the plans following the code are from 18.3:


select
        /*+ parallel(t1 3) */
        n1, sum(id)
from
        t1
where
        id > 0
group by
        n1
order by
        n1
;

select * from table(dbms_xplan.display_cursor);

alter session set "optimizer_ignore_parallel_hints" = true;

select
        /*+ parallel(t1 3) */
        n1, sum(id)
from
        t1
where
        id > 0
group by
        n1
order by
        n1
;

select * from table(dbms_xplan.display_cursor);




SQL_ID  7jynurdtc48kv, child number 0
-------------------------------------
select  /*+ parallel(t1 3) */  n1, sum(id) from  t1 where  id > 0 group
by  n1 order by  n1

Plan hash value: 2919148568

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |       |       |    10 (100)|          |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |    10 |    70 |    10  (10)| 00:00:01 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    SORT GROUP BY         |          |    10 |    70 |    10  (10)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |    10 |    70 |    10  (10)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE       | :TQ10000 |    10 |    70 |    10  (10)| 00:00:01 |  Q1,00 | P->P | RANGE      |
|   6 |       HASH GROUP BY      |          |    10 |    70 |    10  (10)| 00:00:01 |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          | 10000 | 70000 |     9   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| T1       | 10000 | 70000 |     9   (0)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   8 - access(:Z>=:Z AND :Z<=:Z) -- > comment added to avoid wordpress format issue
       filter("ID">0)

Note
-----
   - Degree of Parallelism is 3 because of table property


Session altered.


SQL_ID  7jynurdtc48kv, child number 1
-------------------------------------
select  /*+ parallel(t1 3) */  n1, sum(id) from  t1 where  id > 0 group
by  n1 order by  n1

Plan hash value: 3946799371

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    27 (100)|          |
|   1 |  SORT GROUP BY     |      |    10 |    70 |    27  (12)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   | 10000 | 70000 |    25   (4)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID">0)


tl;dr

In 18.3 Oracle exposes the parameter optimizer_ignore_hints – any code using depending on the hidden version of this parameter will no longer behave as expected. 18.3 also introduces optimiser_ignore_parallel_hints to allow you to ignore parallel hints as well.

 

When Oracle Statistic Gathering times out.

In a previous post, I explained how to see where the Auto Stats job has been running and timed out:

SYS.STATS_TARGET$

I got a case where it always timed out at the end of the standard maintenance window. One table takes many hours, longer than the largest maintenance window, it will always be killed at the end. And, because it stayed stale, and staler each day, this table was always listed first by the Auto Stat job. And many tables never got their chance to get their stats gathered for … years.

In that case, the priority is to gather statistics. That can be long. Then I run the job manually:

exec dbms_auto_task_immediate.gather_optimizer_stats;

Here, it will never time-out (and the auto job will not start at maintenance window start). This manual gathering can take many days. Of course, this gives time to think about a solution, like reading Nigel Bayliss recommendations:

How to Gather Optimizer Statistics Fast!

If I want to kill the manual job, because one table takes really too long and I decide to skip it for the moment, here is my query to find it:

select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' /* '||action||' started on '||logon_time||'*/;' "Kill me with this:" from gv$session where module='DBMS_SCHEDULER' and action like 'ORA$AT^_OS^_MANUAL^_%' escape '^';

Which gives me the kill statement, and the time when I started it:

Before killing, I’ll check the long queries from it with the goal to find a solution for it:

select executions,users_executing,round(elapsed_time/1e6/60/60,1) hours,substr(coalesce(info,sql_text),1,60) info,sql_id from gv$sql natural left outer join (select address,hash_value,sql_id,plan_hash_value,child_address,child_number,id,rtrim(operation||' '||object_owner||' '||object_name) info from gv$sql_plan where object_name is not null) where elapsed_time>1e6*10*60 and action like 'ORA$AT_OS_%' order by last_active_time,id

In this example, I can see that one table is running for 4 days:

Now I kill this statistic gathering job. What I want for the moment is to exclude this table from the automatic statistics gathering. Unfortunately, I cannot change the AUTOSTATS_TARGET at table level, then I lock the stats. And run DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS again.

This is just to quickly resolve the gap we had on many tables. The few tables locked will need further considerations. I even got a funny case where the statistics gathering was long because… statistics where stale. It was in 11g, an IOT where the CBO decided to with ‘db file sequential reads’. I deleted the statistics and the gathering used an optimized execution plan then. When you have really bad statistics, it may be better to have no statistics (and then do dynamic sampling) rather than completely stale ones.

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;

SURNAME
------------------------------
jones
brown
SMITH

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;

INITCAP(SURNAME)
------------------------------
Jones
Brown
Smith

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;

INITCAP(SURNAME)
------------------------------
Jones
Brown
Smith
Mcdonald
Johnson'S

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;
 17
 18    return l_string;
 19  end;
 20  /

SQL> select my_initcap(surname)
  2  from   names;

MY_INITCAP(SURNAME)
--------------------------
Jones
Brown
Smith
McDonald
Johnson's

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;

UGH
-------------------------------
Jones
Brown
Smith
McDonald
Johnson's

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.


SQL> WITH
  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
       ...
 17
 18      return l_string;
 19    end;
 20  select my_initcap(surname)
 21  from   names;

MY_INITCAP(SURNAME)
-----------------------------------------
Jones
Brown
Smith
McDonald

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  /

WITH
*
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.

Advice on fragmentation and shrinkage

If you have performed some sort of data cleanup or similar on a table, then the deleted space will be reused by future insertions. But if

  • that cleanup was the last task you were performing on that table, ie, you were not expecting a lot of new data to ever come in again, or
  • you are performing a lot of full scan queries on that table and you want to make sure they are as efficient as possible

then there may be benefits to performing a shrink on that table to reclaim that space. One of the cool things about the segment advisor is that it will detect if there are some benefits to be gained by shrinking a segment. Here’s an example of that. I create a large table and then delete every 2nd row.


SQL> create table scott.demo_table as
  2  select d.* from dba_objects d,
  3   ( select 1 from dual connect by level <= 100 ) ;

Table created.

SQL>
SQL> delete from scott.demo_table
  2  where mod(object_id,2) = 0;

4125300 rows deleted.

SQL> commit;

Commit complete.

Now I run the segment advisor and I get a nice report on what can be done to reclaim that space.



SQL>
SQL>
SQL> set serveroutput on size 1000000
SQL> set linesize 200
SQL> set verify off
SQL>
SQL> declare
  2    l_object_id     number;
  3    l_task_name     varchar2(50) := 'TEST_TASK';
  4    l_object_type   varchar2(50) := upper('TABLE');
  5    l_attr1         varchar2(50) := upper('SCOTT');
  6    l_attr2         varchar2(50) := upper('DEMO_TABLE');
  7  begin
  8    begin  DBMS_ADVISOR.delete_task(task_name => l_task_name); exception when others then null; end;
  9
 10    dbms_advisor.create_task (
 11      advisor_name      => 'Segment Advisor',
 12      task_name         => l_task_name);
 13
 14    dbms_advisor.create_object (
 15      task_name   => l_task_name,
 16      object_type => l_object_type,
 17      attr1       => l_attr1,
 18      attr2       => l_attr2,
 19      attr3       => NULL,
 20      attr4       => 'null',
 21      attr5       => NULL,
 22      object_id   => l_object_id);
 23
 24    dbms_advisor.set_task_parameter (
 25      task_name => l_task_name,
 26      parameter => 'RECOMMEND_ALL',
 27      value     => 'TRUE');
 28
 29    dbms_advisor.execute_task(task_name => l_task_name);
 30
 31
 32    for cur_rec in (select f.impact,
 33                           o.type,
 34                           o.attr1,
 35                           o.attr2,
 36                           f.message,
 37                           f.more_info
 38                    from   dba_advisor_findings f
 39                           join dba_advisor_objects o on f.object_id = o.object_id and f.task_name = o.task_name
 40                    where  f.task_name = l_task_name
 41                    order by f.impact desc)
 42    loop
 43      dbms_output.put_line('..');
 44      dbms_output.put_line('Type             : ' || cur_rec.type);
 45      dbms_output.put_line('Attr1            : ' || cur_rec.attr1);
 46      dbms_output.put_line('Attr2            : ' || cur_rec.attr2);
 47      dbms_output.put_line('Message          : ' || cur_rec.message);
 48      dbms_output.put_line('More info        : ' || cur_rec.more_info);
 49    end loop;
 50
 51    dbms_advisor.delete_task(task_name => l_task_name);
 52  end;
 53  /
..
Type             : TABLE
Attr1            : SCOTT
Attr2            : DEMO_TABLE
Message          : Enable row movement of the table SCOTT.DEMO_TABLE and perform shrink, estimated savings is 596868412 bytes.
More info        : Allocated Space:1342177280: Used Space:745308868: Reclaimable Space :596868412:

PL/SQL procedure successfully completed.

But what if that segment sits in a manual segment space managed tablespace? We can see from the above that a shrink-style operation will yield some benefits, but there’s a problem. You cannot perform an ALTER TABLE SHRINK command unless a segment sits in an ASSM tablespace. So is the segment advisor of any use in these cases? Let’s re-run the demo to find out


SQL> select * from dba_tablespaces where tablespace_name = 'NO_ASSM'
  2  @pr
==============================
TABLESPACE_NAME               : NO_ASSM
BLOCK_SIZE                    : 8192
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
MAX_SIZE                      : 2147483645
PCT_INCREASE                  :
MIN_EXTLEN                    : 65536
STATUS                        : ONLINE
CONTENTS                      : PERMANENT
LOGGING                       : LOGGING
FORCE_LOGGING                 : NO
EXTENT_MANAGEMENT             : LOCAL
ALLOCATION_TYPE               : SYSTEM
PLUGGED_IN                    : NO
SEGMENT_SPACE_MANAGEMENT      : MANUAL
DEF_TAB_COMPRESSION           : DISABLED
RETENTION                     : NOT APPLY
BIGFILE                       : NO
PREDICATE_EVALUATION          : HOST
ENCRYPTED                     : NO
COMPRESS_FOR                  :
DEF_INMEMORY                  : DISABLED
DEF_INMEMORY_PRIORITY         :
DEF_INMEMORY_DISTRIBUTE       :
DEF_INMEMORY_COMPRESSION      :
DEF_INMEMORY_DUPLICATE        :
SHARED                        : SHARED
DEF_INDEX_COMPRESSION         : DISABLED
INDEX_COMPRESS_FOR            :
DEF_CELLMEMORY                :
DEF_INMEMORY_SERVICE          :
DEF_INMEMORY_SERVICE_NAME     :
LOST_WRITE_PROTECT            : OFF
CHUNK_TABLESPACE              : N

PL/SQL procedure successfully completed.

SQL>
SQL> create table scott.demo_table tablespace no_assm as
  2  select d.* from dba_objects d,
  3   ( select 1 from dual connect by level <= 100 ) ;

Table created.

SQL>
SQL> delete from scott.demo_table
  2  where mod(object_id,2) = 0;

4125300 rows deleted.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> set serveroutput on size 1000000
SQL> set linesize 200
SQL> set verify off
SQL>
SQL> declare
  2    l_object_id     number;
  3    l_task_name     varchar2(50) := 'TEST_TASK';
  4    l_object_type   varchar2(50) := upper('TABLE');
  5    l_attr1         varchar2(50) := upper('SCOTT');
  6    l_attr2         varchar2(50) := upper('DEMO_TABLE');
  7  begin
  8    begin  DBMS_ADVISOR.delete_task(task_name => l_task_name); exception when others then null; end;
  9
 10    dbms_advisor.create_task (
 11      advisor_name      => 'Segment Advisor',
 12      task_name         => l_task_name);
 13
 14    dbms_advisor.create_object (
 15      task_name   => l_task_name,
 16      object_type => l_object_type,
 17      attr1       => l_attr1,
 18      attr2       => l_attr2,
 19      attr3       => NULL,
 20      attr4       => 'null',
 21      attr5       => NULL,
 22      object_id   => l_object_id);
 23
 24    dbms_advisor.set_task_parameter (
 25      task_name => l_task_name,
 26      parameter => 'RECOMMEND_ALL',
 27      value     => 'TRUE');
 28
 29    dbms_advisor.execute_task(task_name => l_task_name);
 30
 31
 32    for cur_rec in (select f.impact,
 33                           o.type,
 34                           o.attr1,
 35                           o.attr2,
 36                           f.message,
 37                           f.more_info
 38                    from   dba_advisor_findings f
 39                           join dba_advisor_objects o on f.object_id = o.object_id and f.task_name = o.task_name
 40                    where  f.task_name = l_task_name
 41                    order by f.impact desc)
 42    loop
 43      dbms_output.put_line('..');
 44      dbms_output.put_line('Type             : ' || cur_rec.type);
 45      dbms_output.put_line('Attr1            : ' || cur_rec.attr1);
 46      dbms_output.put_line('Attr2            : ' || cur_rec.attr2);
 47      dbms_output.put_line('Message          : ' || cur_rec.message);
 48      dbms_output.put_line('More info        : ' || cur_rec.more_info);
 49    end loop;
 50
 51    dbms_advisor.delete_task(task_name => l_task_name);
 52  end;
 53  /
..
Type             : TABLE
Attr1            : SCOTT
Attr2            : DEMO_TABLE
Message          : Perform re-org on the object DEMO_TABLE, estimated savings is 600175966 bytes.
More info        : Allocated Space:1342177280: Used Space:742001314: Reclaimable Space :600175966:

PL/SQL procedure successfully completed.

SQL>
SQL>

As you can see, the segment advisor will take that into account and adjust its recommendations accordingly. And one of the cool things with 12.2 and above, is that tables can be reorganised without an outage!


SQL> alter table scott.demo_table move online;

Table altered.

Nice!

Generic data models … generic applications … ugh

There’s a hesitation to publish this example, because publishing it may be interpreted as an endorsement of this approach and it certainly isn’t. Over the years there have been plenty of articles describing the long term pain that typically comes from generic data models. Here’s a few to whet your appetite.

https://rodgersnotes.wordpress.com/2010/09/21/muck-massively-unified-code-key-generic-three-table-data-model/

https://www.red-gate.com/simple-talk/opinion/opinion-pieces/bad-carma/

https://asktom.oracle.com/pls/asktom/asktom.search?tag=query-on-design

But I’m posting this example because it serves as a nice tutorial for DBMS_SQL, and also, there is perhaps the suggestion that the requestor is moving away from a generic data model to a more well structured one. We’ll go with the benefit of the doubt here Smile

The incoming data for this example was the “classic” generic data model where the table name, column name and column values were not defined in the data dictionary but as values within a table


SQL> CREATE TABLE data_table
  2    (
  3      row_seq      int,
  4      table_name   VARCHAR2(30),
  5      column_name  VARCHAR2(30),
  6      column_value VARCHAR2(30)
  7    );

Table created.

SQL>
SQL>
SQL>
SQL> insert into data_table values ( 1,'TEST_TAB','ORDER_NUMBER','ORD1001');

1 row created.

SQL> insert into data_table values ( 1,'TEST_TAB','CUST_NAME','CUST1001');

1 row created.

SQL> insert into data_table values ( 1,'TEST_TAB','INVOICE_NUMBER','INV001');

1 row created.

SQL> insert into data_table values ( 1,'TEST_TAB','ORDER_DATE','04/11/2018 15:14:00');

1 row created.

SQL> insert into data_table values ( 1,'TEST_TAB','AMOUNT','1001');

1 row created.

SQL>
SQL> insert into data_table values ( 2,'TEST_TAB','ORDER_NUMBER','ORD1002');

1 row created.

SQL> insert into data_table values ( 2,'TEST_TAB','CUST_NAME','CUST1002');

1 row created.

SQL> insert into data_table values ( 2,'TEST_TAB','INVOICE_NUMBER','INV002');

1 row created.

SQL> insert into data_table values ( 2,'TEST_TAB','ORDER_DATE','02/11/2018 15:14:00');

1 row created.

SQL> insert into data_table values ( 2,'TEST_TAB','AMOUNT','1002');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL> select * from data_table;

   ROW_SEQ TABLE_NAME                     COLUMN_NAME                    COLUMN_VALUE
---------- ------------------------------ ------------------------------ ------------------------------
         1 TEST_TAB                       ORDER_NUMBER                   ORD1001
         1 TEST_TAB                       CUST_NAME                      CUST1001
         1 TEST_TAB                       INVOICE_NUMBER                 INV001
         1 TEST_TAB                       ORDER_DATE                     04/11/2018 15:14:00
         1 TEST_TAB                       AMOUNT                         1001
         2 TEST_TAB                       ORDER_NUMBER                   ORD1002
         2 TEST_TAB                       CUST_NAME                      CUST1002
         2 TEST_TAB                       INVOICE_NUMBER                 INV002
         2 TEST_TAB                       ORDER_DATE                     02/11/2018 15:14:00
         2 TEST_TAB                       AMOUNT                         1002

10 rows selected.

The task here was to take those values and convert into INSERT statements, so with the sample data above, the aim is to insert those rows into a table called TEST_TAB. Given that the DML must generated entirely from metadata, we can use DBMS_SQL to handle it. Constructing the DML is easier than you might think due to some handy analytic SQL functions plus the ever useful LISTAGG.


SQL> select
  2    row_number() over
  3      ( partition by table_name, row_seq order by column_name ) as seq,
  4    count(*) over
  5      ( partition by table_name, row_seq ) as col_cnt,
  6    listagg(column_name,',') within group
  7      ( order by column_name ) over ( partition by table_name, row_seq ) as cols,
  8    listagg(':'||column_name,',') within group
  9      ( order by column_name ) over ( partition by table_name, row_seq ) as bindcols,
 10    column_value
 11  from data_table
 12  order by table_name, row_seq, column_name
 13  @pr
==============================
SEQ                           : 1
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : 1001
==============================
SEQ                           : 2
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : CUST1001
==============================
SEQ                           : 3
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : INV001
==============================
SEQ                           : 4
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : 04/11/2018 15:14:00
==============================
SEQ                           : 5
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : ORD1001
==============================
SEQ                           : 1
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : 1002
==============================
SEQ                           : 2
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : CUST1002
==============================
SEQ                           : 3
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : INV002
==============================
SEQ                           : 4
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : 02/11/2018 15:14:00
==============================
SEQ                           : 5
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : ORD1002

Now that we have each data value, plus all the components for an INSERT statement (including binding values not using any literals), we’re good to go:


SQL> create table test_tab (
  2    order_number varchar2(10),
  3    cust_name varchar2(10),
  4    invoice_number varchar2(10),
  5    order_date varchar2(30),
  6    amount varchar2(10)
  7  );

Table created.


SQL> declare
  2    l_sql varchar2(32000);
  3    l_cur     pls_integer := dbms_sql.open_cursor;
  4    l_execute pls_integer;
  5  begin
  6    for i in (
  7      select   table_name,
  8               column_name,
  9               row_number() over ( partition by table_name, row_seq order by column_name ) as seq,
 10               count(*) over ( partition by table_name, row_seq ) as col_cnt,
 11               listagg(column_name,',') within group ( order by column_name ) over ( partition by table_name, row_seq ) as cols,
 12               listagg(':'||column_name,',') within group ( order by column_name ) over ( partition by table_name, row_seq ) as bindcols,
 13               column_value
 14      from data_table
 15      order by table_name, row_seq, column_name
 16   ) loop
 17       if i.seq = 1 then
 18         l_sql := 'insert into '||i.table_name||'('||i.cols||') values ('||i.bindcols||')';
 19         dbms_sql.parse(l_cur,l_sql,dbms_sql.native);
 20       end if;
 21       dbms_sql.bind_variable(l_cur,i.column_name,i.column_value);
 22       if i.seq = i.col_cnt then
 23         l_execute := dbms_sql.execute(l_cur);
 24       end if;
 25   end loop;
 26   dbms_sql.close_cursor(l_cur);
 27  end;
 28  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from test_tab;

ORDER_NUMB CUST_NAME  INVOICE_NU ORDER_DATE                     AMOUNT
---------- ---------- ---------- ------------------------------ ----------
ORD1001    CUST1001   INV001     04/11/2018 15:14:00            1001
ORD1002    CUST1002   INV002     02/11/2018 15:14:00            1002

2 rows selected.

SQL>

Timestamp Oddity

[Editorial note: this is something I started writing in 2013, managed to complete in 2017, and still failed to publish. It should have been a follow-on to another posting on the oddities of timestamp manipulation.]

Just as national language support used to be, timestamps and time-related columns are still a bit of a puzzle to the Oracle world – so much so that OEM could cripple a system if it was allowed to do the check for “failed logins over the last 30 minutes”. And, just like NLS, it’s one of those things that you use so rarely that you keep forgetting what went wrong the last time you used it. Here’s one little oddity that I reminded myself about recently:

rem
rem     Script:         timestamp_anomaly.sql
rem     Author:         Jonathan Lewis
rem     Dated:          April 2013
rem
create table t1 (
        ts_tz   timestamp(9) with time zone,
        ts_ltz  timestamp(9) with local time zone
);

insert into t1 values(systimestamp, systimestamp);
commit;

alter table t1 add constraint ts_ltz_uk unique (ts_ltz);
alter table t1 add constraint ts_tz_uk  unique (ts_tz);


Nothing terribly difficult – just a table with two variants on the timestamp data type and a unique constraint on both: except for one problem. Watch what happens as I create the unique constraints:

SQL> alter table t1 add constraint ts_ltz_uk unique (ts_ltz);

Table altered.

SQL> alter table t1 add constraint ts_tz_uk  unique (ts_tz);
alter table t1 add constraint ts_tz_uk  unique (ts_tz)
                                        *
ERROR at line 1:
ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key

Slightly unexpected – unless you’ve memorized the manuals, of course, which I hadn’t. I wonder if you can create a unique index on timestamp with time zone:


SQL> create unique index ts_tz_uk on t1(ts_tz);

Index created.

You can’t have a unique constraint, but you CAN create a unique index! How curious – did that really happen ?

SQL> select index_name, column_name from user_ind_columns where table_name = 'T1';

INDEX_NAME           COLUMN_NAME
-------------------- --------------------
TS_LTZ_UK            TS_LTZ
TS_TZ_UK             SYS_NC00003$

The index is on a column called SYS_NC00003$ – which looks suspiciously like one of those “function-based-index” things:


SQL> select * from user_ind_expressions where table_name = 'T1';

INDEX_NAME           TABLE_NAME           COLUMN_EXPRESSION                        COLUMN_POSITION
-------------------- -------------------- ---------------------------------------- ---------------
TS_TZ_UK             T1                   SYS_EXTRACT_UTC("TS_TZ")                               1

Oracle has silently invoked the sys_extract_utc() function on our (free-floating) timestamp column to normalize it to UTC. This is really not very friendly but it does make sense, of course – it would be rather expensive to enforce uniqueness if there were (at least) 24 different ways of storing the same absolute value – and 24 is a conservative estimate.

 

 

PeopleSoft Adminstrator Podcast: #184 – nVision Performance

I recorded a second podcast with Dan Iverson and Kyle Benson for the PeopleSoft Administrator Podcast, this time about nVision.

(10 May 2019) #184 – nVision Performance

You can listen to the podcast on psadmin.io, or subscribe with your favourite podcast player, or in iTunes.

Linux Scripting, Part III

In the previous blog posts, we learned how to set up the first part of a standard shell script- how to interactively set variables, including how to pass them as part of the script execution. In this next step, we’ll use those to build out Azure resources. If you’re working on-premises, you can use this type of scripting with SQL Server 2019 Linux but will need to use CLI commands and SQLCMD. I will cover this in later posts, but honestly, the cloud makes deployment quicker for any business to get what they need deployed and with the amount of revenue riding on getting to market faster, this should be the first choice of any DBA with vision.

When I started at Microsoft close to a year ago, along with subsequent journey to Azure, it was challenging to locate the Azure commands for BASH, (which is still incorrectly referred to as “the CLI”). I could locate the Powershell commands immediately, but as Microsoft matures its foothold in Linux, it is realizing, just like those of us that were adept in other shell languages- there’s one shell that’s winning the war and that’s BASH. We can love the shell we started with, the one that has served us well, but BASH has been around so long and is so robust, we need to recognize that instead of re-inventing the wheel unnecessarily, we can use what is out there already. I now locate the AZ commands to be used with BASH faster than the Powershell specific commands. I am glad they will continue to support Powershell Azure commands, but highly recommend learning about the standard AZ commands, using them in Powershell and learning BASH for the future.

OK, off my soapbox and let’s get to the learning…</p />
</p></div>

    	  	<div class=

DBMS_JOB – the joy of transactions

This is a followup to yesterdays post on DBMS_JOB and is critical if you’re upgrading to 19c soon. Mike Dietrich wrote a nice piece last week on the “under the covers” migration of the now deprecated DBMS_JOB package to the new Scheduler architecture. You should check it out before reading on here.

Mike’s post concerned mainly what would happen during upgrade (spoiler: the DBMS_JOB jobs become scheduler jobs but you can maintain them using the old API without drama), but immediately Twitter was a buzz with a couple of concerns that I wanted to address:

1) What about new jobs submitted via the old API after the upgrade?

For comparison, here’s what you see currently in 18c – DBMS_JOB is quite separate from the scheduler.


SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

SQL> declare
  2    j int;
  3  begin
  4    dbms_job.submit(j,'begin dbms_session.sleep(60); end;',sysdate,'sysdate+1');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select job, what from user_jobs;

       JOB WHAT
---------- --------------------------------------------------
       181 begin dbms_session.sleep(60); end;

SQL> select job_name, job_action from user_scheduler_jobs;

no rows selected


Now here’s the same in 19c


SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> declare
  2    j int;
  3  begin
  4    dbms_job.submit(j,'begin dbms_session.sleep(60); end;',sysdate,'sysdate+1');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select job, what from user_jobs;

       JOB WHAT
---------- --------------------------------------------------
        22 begin dbms_session.sleep(60); end;

SQL> select job_name, job_action from user_scheduler_jobs;

JOB_NAME             JOB_ACTION
-------------------- ------------------------------------------------------------
DBMS_JOB$_22         begin dbms_session.sleep(60); end;

SQL> select * from user_scheduler_jobs
  2  @pr
==============================
JOB_NAME                      : DBMS_JOB$_22
JOB_SUBNAME                   :
JOB_STYLE                     : REGULAR
JOB_CREATOR                   : MCDONAC
CLIENT_ID                     :
GLOBAL_UID                    :
PROGRAM_OWNER                 :
PROGRAM_NAME                  :
JOB_TYPE                      : PLSQL_BLOCK
JOB_ACTION                    : begin dbms_session.sleep(60); end;
NUMBER_OF_ARGUMENTS           : 0
SCHEDULE_OWNER                :
SCHEDULE_NAME                 :
SCHEDULE_TYPE                 : PLSQL
START_DATE                    : 26-MAY-19 07.12.47.000000 PM +08:00
REPEAT_INTERVAL               : sysdate+1
EVENT_QUEUE_OWNER             :
EVENT_QUEUE_NAME              :
EVENT_QUEUE_AGENT             :
EVENT_CONDITION               :
EVENT_RULE                    :
FILE_WATCHER_OWNER            :
FILE_WATCHER_NAME             :
END_DATE                      :
JOB_CLASS                     : DEFAULT_JOB_CLASS
ENABLED                       : TRUE
AUTO_DROP                     : TRUE
RESTART_ON_RECOVERY           : FALSE
RESTART_ON_FAILURE            : FALSE
STATE                         : SCHEDULED
JOB_PRIORITY                  : 3
RUN_COUNT                     : 0
UPTIME_RUN_COUNT              :
MAX_RUNS                      :
FAILURE_COUNT                 : 0
UPTIME_FAILURE_COUNT          :
MAX_FAILURES                  :
RETRY_COUNT                   : 0
LAST_START_DATE               :
LAST_RUN_DURATION             :
NEXT_RUN_DATE                 : 27-MAY-19 07.12.47.000000 PM -07:00
SCHEDULE_LIMIT                :
MAX_RUN_DURATION              :
LOGGING_LEVEL                 : OFF
STORE_OUTPUT                  : TRUE
STOP_ON_WINDOW_CLOSE          : FALSE
INSTANCE_STICKINESS           : TRUE
RAISE_EVENTS                  :
SYSTEM                        : FALSE
JOB_WEIGHT                    : 1
NLS_ENV                       : NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA'...
SOURCE                        :
NUMBER_OF_DESTINATIONS        : 1
DESTINATION_OWNER             :
DESTINATION                   :
CREDENTIAL_OWNER              :
CREDENTIAL_NAME               :
INSTANCE_ID                   :
DEFERRED_DROP                 : FALSE
ALLOW_RUNS_IN_RESTRICTED_MODE : FALSE
COMMENTS                      :
FLAGS                         : 9007199254872624
RESTARTABLE                   : FALSE
HAS_CONSTRAINTS               : FALSE
CONNECT_CREDENTIAL_OWNER      :
CONNECT_CREDENTIAL_NAME       :
FAIL_ON_SCRIPT_ERROR          : FALSE

PL/SQL procedure successfully completed.

You can see that it will be enabled by default and is classed as a regular job. Even if you submit a one-off job, it will still be classed as regular not lightweight.


SQL> declare
  2    j int;
  3  begin
  4    dbms_job.submit(j,'begin dbms_session.sleep(60); end;',sysdate);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select job, what from user_jobs;

       JOB WHAT
---------- --------------------------------------------------
        25 begin dbms_session.sleep(60); end;

SQL> select * from user_scheduler_jobs
  2  @pr
==============================
JOB_NAME                      : DBMS_JOB$_25
JOB_SUBNAME                   :
JOB_STYLE                     : REGULAR
JOB_CREATOR                   : MCDONAC
CLIENT_ID                     :
GLOBAL_UID                    :
PROGRAM_OWNER                 :
PROGRAM_NAME                  :
JOB_TYPE                      : PLSQL_BLOCK
JOB_ACTION                    : begin dbms_session.sleep(60); end;
NUMBER_OF_ARGUMENTS           : 0
SCHEDULE_OWNER                :
SCHEDULE_NAME                 :
SCHEDULE_TYPE                 : ONCE
START_DATE                    : 26-MAY-19 08.37.09.000000 PM +08:00
REPEAT_INTERVAL               :
EVENT_QUEUE_OWNER             :
EVENT_QUEUE_NAME              :
EVENT_QUEUE_AGENT             :
EVENT_CONDITION               :
EVENT_RULE                    :
FILE_WATCHER_OWNER            :
FILE_WATCHER_NAME             :
END_DATE                      :
JOB_CLASS                     : DEFAULT_JOB_CLASS
ENABLED                       : TRUE
AUTO_DROP                     : TRUE
RESTART_ON_RECOVERY           : FALSE
RESTART_ON_FAILURE            : FALSE
STATE                         : SCHEDULED
JOB_PRIORITY                  : 3
RUN_COUNT                     : 0
UPTIME_RUN_COUNT              :
MAX_RUNS                      :
FAILURE_COUNT                 : 0
UPTIME_FAILURE_COUNT          :
MAX_FAILURES                  :
RETRY_COUNT                   : 0
LAST_START_DATE               :
LAST_RUN_DURATION             :
NEXT_RUN_DATE                 : 27-MAY-19 11.37.09.268652 AM +08:00
SCHEDULE_LIMIT                :
MAX_RUN_DURATION              :
LOGGING_LEVEL                 : OFF
STORE_OUTPUT                  : TRUE
STOP_ON_WINDOW_CLOSE          : FALSE
INSTANCE_STICKINESS           : TRUE
RAISE_EVENTS                  :
SYSTEM                        : FALSE
JOB_WEIGHT                    : 1
NLS_ENV                       : NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENC
NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_L
NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_T
NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINA
NLS_NCHAR_CONV_EXCP='FALSE'
SOURCE                        :
NUMBER_OF_DESTINATIONS        : 1
DESTINATION_OWNER             :
DESTINATION                   :
CREDENTIAL_OWNER              :
CREDENTIAL_NAME               :
INSTANCE_ID                   :
DEFERRED_DROP                 : FALSE
ALLOW_RUNS_IN_RESTRICTED_MODE : FALSE
COMMENTS                      :
FLAGS                         : 9007199254880304
RESTARTABLE                   : FALSE
HAS_CONSTRAINTS               : FALSE
CONNECT_CREDENTIAL_OWNER      :
CONNECT_CREDENTIAL_NAME       :
FAIL_ON_SCRIPT_ERROR          : FALSE

PL/SQL procedure successfully completed.

Important Note: There is one critical thing you need to be aware of with this change. DBMS_JOB is an “old-school” public API, hence anyone and everyone pretty much had access to it. Anyone familiar with DBMS_SCHEDULER will know that the components within it are true database objects, which can be protected with privileges. So when you upgrade to 19c, to ensure that you do not get nasty surprises, users that are using DBMS_JOB will need the CREATE JOB privilege otherwise their previous ability to submit jobs will disappear. For example:


SQL> conn scott/tiger@db192_pdb1
Connected.

SQL> declare
  2    j int;
  3  begin
  4    dbms_job.submit(j,'begin dbms_session.sleep(60); end;',sysdate);
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 9387
ORA-06512: at "SYS.DBMS_ISCHED", line 9376
ORA-06512: at "SYS.DBMS_ISCHED", line 175
ORA-06512: at "SYS.DBMS_ISCHED", line 9302
ORA-06512: at "SYS.DBMS_IJOB", line 196
ORA-06512: at "SYS.DBMS_JOB", line 168
ORA-06512: at line 4

2) What about the cherished transactional nature of DBMS_JOB?

If the old style jobs are now scheduler jobs, do we lose the transactional element of DBMS_JOB? Nope. Even though we will create a paired scheduler entry, DBMS_JOB is still transactional (which I love!).


SQL> declare
  2    j int;
  3  begin
  4    dbms_job.submit(j,'begin dbms_session.sleep(60); end;',sysdate,'sysdate+1');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select job, what from user_jobs;

       JOB WHAT
---------- --------------------------------------------------
        21 begin dbms_session.sleep(60); end;

SQL> select job_name, job_action from user_scheduler_jobs;

JOB_NAME             JOB_ACTION
-------------------- ------------------------------------------------------------
DBMS_JOB$_21         begin dbms_session.sleep(60); end;

SQL> roll;
Rollback complete.
SQL> select job, what from user_jobs;

no rows selected

SQL> select job_name, job_action from user_scheduler_jobs;

no rows selected

SQL>

If you love that transactional capability too, then make sure to visit the database ideas page to vote up this idea. I have no issue with DBMS_SCHEDULER doing commits by default, but it would be cool if (say) for lightweight jobs we had an option to choose whether we commit or not.

Re-partitioning 2

Last week I wrote a note about turning a range-partitioned table into a range/list composite partitioned table using features included in 12.2 of Oracle. But my example was really just an outline of the method and bypassed a number of the little extra problems you’re likely to see in a real-world system, so in this note I’m going to bring in an issue that you might run into – and which I’ve seen appearing a number of times: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION.

It’s often the case that a system has a partitioned table that’s been around for a long time, and over its lifetime it may have had (real or virtual) columns added, made inivisble, dropped, or mark unused. As a result you may find that the apparent definition of the table is not the same as the real definition of the table – and that’s why Oracle has given us (in 12c) the option to “create table for exchange”.

You might like to read a MoS note giving you one example of a problem with creating an exchange table prior to this new feature. ORA-14097 At Exchange Partition After Adding Column With Default Value (Doc ID 1334763.1) I’ve created a little model by cloning the code from that note.


rem
rem     Script:         pt_exchange_problem.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2019
rem

create table mtab (pcol number)
partition by list (pcol) (
        partition p1 values (1),
        partition p2 values (2)
);

alter table mtab add col2 number default 0 not null;

prompt  ========================================
prompt  Traditional creation method => ORA-14097
prompt  ========================================

create table mtab_p2 as select * from mtab where 1=0;
alter table mtab exchange partition P2 with table mtab_p2;

prompt  ===================
prompt  Create for exchange
prompt  ===================

drop table mtab_p2 purge;
create table mtab_p2 for exchange with table mtab;
alter table mtab exchange partition P2 with table mtab_p2;

[/sourcecode}


Here's the output from running this on an instance of 18.3


Table created.

Table altered.

========================================
Traditional creation method => ORA-14097
========================================

Table created.

alter table mtab exchange partition P2 with table mtab_p2
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

===================
Create for exchange
===================

Table dropped.


Table created.


Table altered.

So we don’t have to worry about problems creating an exchange table in Oracle 12c or later. But we do still have a problem if we’re trying to convert our range-partitioned table into a range/list composite partitioned table by doing using the “double-exchange” method. In my simple example I used a “create table” statement to create an empty table that we could exchange into; but without another special version of a “create table” command I won’t be able to create a composite partitioned table that is compatible with the simple table that I want to use as my intermediate table.

Here’s the solution to that problem – first in a thumbnail sketch:

  • create a table for exchange (call it table C)
  • alter table C modify to change it to a composite partitioned table with one subpartition per partition
  • create a table for exchange (call it table E)
  • Use table E to exchange partitions from the original table to the (now-partitioned) table C
  • Split each partition of table C into the specific subpartitions required

And now some code to work through the details – first the code to create and populate the partitioned table.


rem
rem     Script:         pt_comp_from_pt_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2019
rem

drop table t purge;
drop table pt_range purge;
drop table pt_range_list purge;

-- @@setup

create table pt_range (
        id              number(8,0)     not null,
        grp             varchar2(1)     not null,
        small_vc        varchar2(10),
        padding         varchar2(100)
)
partition by range(id) (
        partition p200 values less than (200),
        partition p400 values less than (400),
        partition p600 values less than (600)
)
;

insert into pt_range
select
        rownum-1,
        mod(rownum,2),
        lpad(rownum,10,'0'),
        rpad('x',100,'x')
from
        all_objects
where
	rownum <= 600 -- > comment to avoid WordPress format issue
;

commit;

Then some code to create the beginnings of the target composite partitioned table. We create a simple heap table “for exchange”, then modify it to be a composite partitioned table with a named starting partition and high_value and a template defining a single subpartition then, as a variant on the example from last week, specifying interval partitioning.


prompt	==========================================
prompt	First nice feature - "create for exchange"
prompt	==========================================

create table pt_range_list for exchange with table pt_range;

prompt	============================================
prompt	Now alter the table to composite partitioned
prompt	============================================

alter table pt_range_list modify
partition by range(id) interval (200)
subpartition by list (grp) 
subpartition template (
        subpartition p_def      values(default)
)
(
	partition p200 values less than (200)
)
;

If you want to do the conversion from range partitioning to interval partitioning you will have to check very carefully that your original table will be able to convert safely – which means you’ll need to check that the “high_value” values for the partitions are properly spaced to match the interval you’ve defined and (as a special requirement for the conversion) there are no omissions from the current list of high values. If your original table doesn’t match these requirement exactly you may end up trying to exchange data into a partition where it doesn’t belong; for example, if my original table had partitions with high value of 200, 600, 800 then there may be values in the 200-399 range currently stored in the original “600” range partition which shouldn’t go into the new “600” interval partition. You may find you have to split (and/or merge) a few partitions in your range-partitioned table before you can do the main conversion.

Now we create create the table that we’ll actually use for the exchange and go through each exchange in turn. Because I’ve got an explicitly named starting partition the first exchange takes only two steps – exchange out, exchange in. But because I’m using interval partitioning in the composite partitioned table I’m doing a “lock partition” before the second exchange on all the other partitions as this will bring the required target partition into existence. I’m also using the “[sub]partition for()” syntax to identify the pairs of [sub]partitions – this isn’t necessary for the original range-partitioned table, of course, but it’s the only way I can identify the generated subpartitions that will appear in the composite partitioned table.


create table t for exchange with table pt_range;

prompt	=======================================================================
prompt	Double exchange to move a partition to become a composite subpartition
prompt	Could drive this programatically by picking one row from each partition
prompt	=======================================================================

alter table pt_range exchange partition p200 with table t;
alter table pt_range_list exchange subpartition p200_p_def with table t;

alter table pt_range exchange partition for (399) with table t;
lock  table pt_range_list partition for (399) in exclusive mode;
alter table pt_range_list exchange subpartition for (399,'0') with table t;

alter table pt_range exchange partition for (599) with table t;
lock  table pt_range_list partition for (599) in exclusive mode;
alter table pt_range_list exchange subpartition for (599,'0') with table t;

prompt	=====================================
prompt	Show that we've got the data in place
prompt	=====================================

execute dbms_stats.gather_table_stats(user,'pt_range_list',granularity=>'ALL')

break on partition_name skip 1

select  partition_name, subpartition_name, num_rows 
from    user_tab_subpartitions 
where   table_name = 'PT_RANGE_LIST'
order by
        partition_name, subpartition_name
;

Now that the data is in the target table we can split each default subpartition into the four subpartitions that we want for each partition. To cater for the future, though, I’ve first modified the subpartition template so that each new partition will have four subpartitions (though the naming convention won’t be applied, of course, Oracle will generate system name for all new partitions and subpartitions).


prompt  ================================================
prompt  Change the subpartition template to what we want
prompt  ================================================

alter table pt_range_list
set subpartition template(
        subpartition p_0 values (0),
        subpartition p_1 values (1),
        subpartition p_2 values (2),
        subpartition p_def values (default)
)
;

prompt  ====================================================
prompt  Second nice feature - multiple splits in one command
prompt  Again, first split is fixed name.
prompt  We could do this online after allowing the users in
prompt  ====================================================

alter table pt_range_list split subpartition p200_p_def
        into (
                subpartition p200_p_0 values(0),
                subpartition p200_p_1 values(1),
                subpartition p200_p_2 values(2),
                subpartition p200_p_def
        )
;

alter table pt_range_list split subpartition for (399,'0')
        into (
                subpartition p400_p_0 values(0),
                subpartition p400_p_1 values(1),
                subpartition p400_p_2 values(2),
                subpartition p400_p_def
        )
;

alter table pt_range_list split subpartition for (599,'0')
        into (
                subpartition p600_p_0 values(0),
                subpartition p600_p_1 values(1),
                subpartition p600_p_2 values(2),
                subpartition p600_p_def
        )
;

Finally a little demonstration that we can’t add an explicitly named partition to the interval partitioned table; then we insert a row to generate the partition and show that it has 4 subpartitions.

Finishing off we rename everything (though that’s a fairly pointless exercise).


prompt  ==============================================================
prompt  Could try adding a partition to show it uses the new template
prompt  But that's not allowed for interval partitions: "ORA-14760:"
prompt  ADD PARTITION is not permitted on Interval partitioned objects
prompt  So insert a value that would go into the next (800) partition
prompt  ==============================================================

alter table pt_range_list add partition p800 values less than (800);

insert into pt_range_list (
        id, grp, small_vc, padding
)
values ( 
        799, '0', lpad(799,10,'0'), rpad('x',100,'x')
)
;

commit;

prompt  ===================================================
prompt  Template naming is not used for the subpartitions,
prompt  so we have to use the "subpartition for()" strategy 
prompt  ===================================================

alter table pt_range_list rename subpartition for (799,'0') to p800_p_0;
alter table pt_range_list rename subpartition for (799,'1') to p800_p_1;
alter table pt_range_list rename subpartition for (799,'2') to p800_p_2;
alter table pt_range_list rename subpartition for (799,'3') to p800_p_def;

prompt  ==============================================
prompt  Might as well clean up the partition names too
prompt  ==============================================

alter table pt_range_list rename partition for (399) to p400;
alter table pt_range_list rename partition for (599) to p600;
alter table pt_range_list rename partition for (799) to p800;

prompt  =======================================
prompt  Finish off by listing the subpartitions 
prompt  =======================================

execute dbms_stats.gather_table_stats(user,'pt_range_list',granularity=>'ALL')

select  partition_name, subpartition_name, num_rows 
from    user_tab_subpartitions 
where   table_name = 'PT_RANGE_LIST'
order by
        partition_name, subpartition_name
;

It’s worth pointing out that you could do the exchanges (and the splitting and renaming at the same time) through some sort of simple PL/SQL loop – looping through the named partitions in the original table and using a row from the first exchange to drive the lock and second exchange (and splitting and renaming). For exanple something like the following which doesn’t have any of the error-trapping and defensive mechanisms you’d want to use on a production system:



declare
        m_pt_val number;
begin
        for r in (select partition_name from user_tab_partitions where table_name = 'PT_RANGE' order by partition_position) 
        loop
                execute immediate
                        'alter table pt_range exchange partition ' || r.partition_name ||
                        ' with table t';
        
                select id into m_pt_val from t where rownum = 1;
        
                execute immediate 
                        'lock table pt_range_list partition for (' || m_pt_val || ') in exclusive mode';
        
                execute immediate
                        'alter table pt_range_list exchange subpartition  for (' || m_pt_val || ',0)' ||
                        ' with table t';
        
        end loop;
end;
/

If you do go for a programmed loop you have to be really careful to consider what could go wrong at each step of the loop and how your program is going to report (and possibly attempt to recover) the situation. This is definitely a case where you don’t want code with “when others then null” appearing anywhere, and don’t be tempted to include code to truncate the exchange table.