max_enabled_roles – nice touch

Some people get very excited with roles, and quickly the number of roles proliferates to huge numbers…Until this happens

ORA-28031: maximum of 148 enabled roles exceeded

But in helping someone out on AskTom, I just found a nice touch in 11.2.0.4.  I had granted my account 200 roles (since the max_enabled_roles parameter is capped at 148), fully expecting to get the standard error when attempting to connect.  But I didn’t – all was fine.  You can see what is going on via the following demo.


SQL> begin
  2  for i in 1 .. 200 loop
  3    execute immediate 'create role rr'||i;
  4    execute immediate 'grant rr'||i||' to scott';
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> conn scott/tiger
Connected.

SQL> select count(*) from session_roles;

  COUNT(*)
----------
       148

So even though I granted 200 roles, when I queried SESSION_ROLES, I did not see all 200 , I only saw 148.

My alert.log shows this

Wed Nov 30 10:32:35 2016
Maximum of 148 enabled roles exceeded for user SCOTT. Not loading all the roles.

Some people might prefer an explicit error, but hopefully any good alert log monitoring would quickly pick this up.  And since if you exceed 148 there is no workaround (except to reduce the number of roles granted), I think avoiding the error is a better strategy.

Simple demo of message propagation

If you’re using AQ, then it’s simple to setup simple enqueue and dequeue facilities on your local database to provide all sorts of asynchronous style processing in your applications.  As long as you’re applications are designed and built to handle it, the “fire and forget” model to keep user applications responsive, and all of the “heavy lifting” done in the background is a very attractive one.

You can also use AQ to achieve the same concept across multiple databases, and the database will take care of propagating the messages from one database to the other.  Here’s a simple demo of that.

Database 1




SQL> connect / as sysdba
Connected.

--
-- A user to hold all of our AQ stuff
--
SQL> create user aqtest identified by aqtest;

User created.

SQL> grant connect, resource, aq_administrator_role to aqtest;

Grant succeeded.

SQL> grant execute on dbms_aq to aqtest;

Grant succeeded.

SQL> grant execute on dbms_aqadm to aqtest;

Grant succeeded.

SQL> alter user aqtest quota unlimited on users;

User altered.

SQL> grant create database link to aqtest;

Grant succeeded.

SQL> begin
  2    dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','AQTEST',FALSE);
  3    dbms_aqadm.grant_system_privilege('DEQUEUE_ANY','AQTEST',FALSE);
  4  end;
  5  /

PL/SQL procedure successfully completed.

--
-- Now we connect as AQTEST and construct our message payload types and our local queues
--

SQL> connect aqtest/aqtest
Connected.

SQL> create type aqtest.message_typ as object(subject varchar2(30), text varchar2(80));
  2  /

Type created.

SQL> begin
  2    dbms_aqadm.create_queue_table(queue_table => 'aqtest.messages_qtab',
  3                                  queue_payload_type =>  'aqtest.Message_typ',
  4                                  multiple_consumers => TRUE);
  5    dbms_aqadm.create_queue(queue_name => 'MSG_QUEUE',
  6                            queue_table => 'aqtest.messages_qtab');
  7    dbms_aqadm.start_queue(queue_name => 'MSG_QUEUE');
  8  end;
  9  /

PL/SQL procedure successfully completed.

--
-- And here is a basic enqueue routine.  If a remote address is specify, then we will propagate
-- the message to that address.  Otherwise the message will stay in the local queue.
--
SQL> create or replace procedure enqueue_msg(p_msg in varchar2,
  2                                          p_remote_address in varchar2 default null)
  3  as
  4    l_enqueue_options    dbms_aq.enqueue_options_t;
  5    l_message_properties dbms_aq.message_properties_t;
  6    l_message_handle     raw(16);
  7    l_message            aqtest.message_typ;
  8    l_recipients         dbms_aq.aq$_recipient_list_t;
  9  BEGIN
 10    l_recipients(1) := SYS.aq$_agent('RECIPIENT', p_remote_address, null);
 11    l_message_properties.recipient_list := l_recipients;
 12
 13    l_message := message_typ('NORMAL MESSAGE',  p_msg );
 14    dbms_aq.enqueue(queue_name => 'msg_queue',
 15                    enqueue_options => l_enqueue_options,
 16                    message_properties => l_message_properties,
 17                    payload => l_message,
 18                    msgid => l_message_handle);
 19  end;
 20  /

Procedure created.

Database 2, we do the exact same setup



SQL> connect / as sysdba
Connected.

--
-- A user to hold all of our AQ stuff
--
SQL> create user aqtest identified by aqtest;

User created.

SQL> grant connect, resource, aq_administrator_role to aqtest;

Grant succeeded.

SQL> grant execute on dbms_aq to aqtest;

Grant succeeded.

SQL> grant execute on dbms_aqadm to aqtest;

Grant succeeded.

SQL> alter user aqtest quota unlimited on users;

User altered.

SQL> grant create database link to aqtest;

Grant succeeded.

SQL> begin
  2    dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','AQTEST',FALSE);
  3    dbms_aqadm.grant_system_privilege('DEQUEUE_ANY','AQTEST',FALSE);
  4  end;
  5  /

PL/SQL procedure successfully completed.

--
-- Now we connect as AQTEST and construct our message payload types and our local queues
--

SQL> connect aqtest/aqtest
Connected.

SQL> create type aqtest.message_typ as object(subject varchar2(30), text varchar2(80));
  2  /

Type created.

SQL> begin
  2    dbms_aqadm.create_queue_table(queue_table => 'aqtest.messages_qtab',
  3                                  queue_payload_type =>  'aqtest.Message_typ',
  4                                  multiple_consumers => TRUE);
  5    dbms_aqadm.create_queue(queue_name => 'MSG_QUEUE',
  6                            queue_table => 'aqtest.messages_qtab');
  7    dbms_aqadm.start_queue(queue_name => 'MSG_QUEUE');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Back to Database 1


--
-- We need a database link to the remote database, plus a quick query to test that its working
--

SQL> create database link remote_db connect to aqtest identified by aqtest using 'db11';

Database link created.

SQL> select * from tab@remote_db;

no rows selected


--
-- We get our propagation schedule running, and we're ready to go.   
--

SQL> begin
  2    dbms_aqadm.schedule_propagation(queue_name  => 'MSG_QUEUE',
  3                                        destination => 'remote_db',
  4                                        start_time  => sysdate,
  5                                        latency     => 0);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select * from user_queue_schedules
  2  @pr
==============================
QNAME                         : MSG_QUEUE
DESTINATION                   : REMOTE_DB
START_DATE                    :
START_TIME                    : 14:16:01
PROPAGATION_WINDOW            :
NEXT_TIME                     :
LATENCY                       : 0
SCHEDULE_DISABLED             : N
PROCESS_NAME                  : J000
SESSION_ID                    : 400, 38936
INSTANCE                      : 1
LAST_RUN_DATE                 : 28-NOV-16 02.16.01.283000 PM +08:00
LAST_RUN_TIME                 : 14:16:01
CURRENT_START_DATE            : 28-NOV-16 02.16.01.283000 PM +08:00
CURRENT_START_TIME            : 14:16:01
NEXT_RUN_DATE                 : 28-NOV-16 02.16.01.280000 PM +08:00
NEXT_RUN_TIME                 : 14:16:01
TOTAL_TIME                    : 0
TOTAL_NUMBER                  : 0
TOTAL_BYTES                   : 0
MAX_NUMBER                    : 0
MAX_BYTES                     : 0
AVG_NUMBER                    : 0
AVG_SIZE                      : 0
AVG_TIME                      : 0
FAILURES                      : 0
LAST_ERROR_DATE               :
LAST_ERROR_TIME               :
LAST_ERROR_MSG                :
MESSAGE_DELIVERY_MODE         : PERSISTENT
ELAPSED_DEQUEUE_TIME          :
ELAPSED_PICKLE_TIME           :
JOB_NAME                      : AQ_JOB$_6438

PL/SQL procedure successfully completed.

--
-- a message that will not be propagaged, because remote recipient is not specified
--
SQL> begin
  2    enqueue_msg('This message will stay local');
  3    commit;
  4  end;
  5  /

PL/SQL procedure successfully completed.

--
-- a message that WILL be propagated, because remote recipient is specified
--
SQL> begin
  2    enqueue_msg('This message will be propagated.',
  3                 'aqtest.msg_queue_other@remote_db');
  4    commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

--
-- So if everything is working correctly, we have 2 messages on the local queue, and 1 message on the remote queue
--

SQL>
SQL> select t1.cnt,
  2         t2.cnt
  3  from (select count(*) cnt from messages_qtab) t1,
  4       (select count(*) cnt from messages_qtab_other@remote_db) t2
  5  /

       CNT        CNT
---------- ----------
         2          1

1 row selected.

SQL>
SQL>

And there you go.  Messages between databases using the in-built propagation mechanisms.

Just one more week

image

I’m off to UKOUG again this year.  It’s an awesome conference, and I’ll be doing three talks there.

On Monday at 3:30 it’s my first keynote talk Smile  “12 Things You’ll Love About the Oracle Database 12.2”, followed up at 6pm by “AskTom – One Year On”.

On Tuesday, at 3:30 I’ll be doing a talk for anyone want to come up to speed on all of the partitioning features with “Partitioning 101”.

A couple of the talks are in Hall 1, which is always nice for the attendees, but as a speaker, you can hardly see the audience since they are shrouded in darkness Smile

image

Head on over to http://tech16.ukoug.org/ for all of the details.  If you’re coming the conference, feel free to pop over and say “Hi”.

Attribute clustering (part 3)

So in part1 and part2, we looked at creating tables with clustered data.  If you’re ready to climb aboard the attribute clustering heading toward Fastville Smile you might want to take an existing table and cluster it.  In part 2 we saw how we had to be extra careful with syntax.  The same rule applies with altering a table to cluster it.  Lets start with our SOURCE_DATA table which was not clustered.



SQL> create table source_data  as
  2      select d.*
  3      from dba_objects d
  4      where object_id is not null
  5      order by dbms_random.random;

Table created.

SQL>
SQL> select object_id, owner from source_data where rownum < 50;

 OBJECT_ID OWNER
---------- ------------------------------
     37627 PUBLIC
     79081 ORDSYS
     26953 SYS
     74744 PUBLIC
     47975 PUBLIC
     94003 APEX_050000
     59014 SYS
     66409 PUBLIC
     36977 PUBLIC
     74886 PUBLIC
       565 SYS
       ...
       ...

As you see, the data has no particular ordering.  Let’s now run an ALTER command to cluster this table



SQL> alter table source_data clustering by linear order(object_id);

Table altered.

SQL> select object_id, owner from source_data where rownum < 50;

 OBJECT_ID OWNER
---------- ------------------------------
     37627 PUBLIC
     79081 ORDSYS
     26953 SYS
     74744 PUBLIC
     47975 PUBLIC
     94003 APEX_050000
     59014 SYS
     66409 PUBLIC
     36977 PUBLIC
     74886 PUBLIC
       565 SYS
     35456 SYS
     74656 SYS
     ...
     ...

As you can see, nothing seems to have happened. Now that is to be expected, because we have only set an attribute on the table, similar to (say) setting the compression attribute.

Let us now MOVE the data to “reload” the table data.



SQL> alter table source_data move;

Table altered.

SQL>
SQL> select object_id, owner from source_data where rownum < 50;

 OBJECT_ID OWNER
---------- ------------------------------
     37627 PUBLIC
     79081 ORDSYS
     26953 SYS
     74744 PUBLIC
     47975 PUBLIC
     94003 APEX_050000
     59014 SYS
     66409 PUBLIC
     36977 PUBLIC
     74886 PUBLIC
       565 SYS
     35456 SYS
     74656 SYS
     ...
     ...

And …. Bzzzzt! Still nothing has happened. The issue is here is not the MOVE command. We made a tiny error in our initial ALTER statement. Let’s try it again



SQL> alter table source_data ADD clustering by linear order(object_id);

Table altered.

SQL>
SQL> alter table source_data move;

Table altered.

SQL>
SQL> select object_id, owner from source_data where rownum < 50;

 OBJECT_ID OWNER
---------- ------------------------------
         2 SYS
         3 SYS
         4 SYS
         5 SYS
         6 SYS
         7 SYS
         8 SYS
         9 SYS
        10 SYS
        11 SYS
        12 SYS
        13 SYS
        14 SYS
        15 SYS
        16 SYS
     ...
     ...

As you can see, the keyword ADD makes all the difference.  As I said in the previous posts, always validate that your clustering is happening as you expect.

Attribute clustering (part 2)

In the previous post, I demonstrated attribute clustering by

  • creating a table of source data which contained data in randomised order, via

SQL> create table source_data  as
  2      select d.*
  3      from dba_objects d
  4      where object_id is not null
  5      order by dbms_random.random;



  • and then it loading into a table with attribute clustering defined to cluster the data into the desired order.

The obvious question is then “Why not just cluster the data by combining the two operations ?”, namely


SQL> create table t1
  2  clustering by linear order(object_id)
  3  as select d.* from dba_objects d
  4  order by dbms_random.random;

There was a reason I did my first demo in the way I did.  Let’s look at the execution plan for that CREATE statement.  Now, bear with me, for simplicity sake, rather than show the execution plan for DBA_OBJECTS access (which (which is a huge complex view), I’ll use SOURCE_DATA both with and without ORDER BY clauses as a representative.


SQL> create table t1
  2  clustering by linear order(object_id)
  3  as select * from source_data;

------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes |
------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT           |             | 99386 |    10M|
|   1 |  LOAD AS SELECT                  | T1          |       |       |
|   2 |   OPTIMIZER STATISTICS GATHERING |             | 99386 |    10M|
|   3 |    SORT ORDER BY                 |             | 99386 |    10M|
|   4 |     TABLE ACCESS FULL            | SOURCE_DATA | 99386 |    10M|
------------------------------------------------------------------------

SQL> create table t1
  2  clustering by linear order(object_id)
  3  as select * from dba_objects
  4  order by dbms_random.random;
  
------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes |
------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT           |             | 99386 |    10M|
|   1 |  LOAD AS SELECT                  | T1          |       |       |
|   2 |   OPTIMIZER STATISTICS GATHERING |             | 99386 |    10M|
|   3 |    SORT ORDER BY                 |             | 99386 |    10M|
|   4 |     TABLE ACCESS FULL            | SOURCE_DATA | 99386 |    10M|
------------------------------------------------------------------------

Notice that the execution plans appear the same.  We can see a SORT ORDER BY in the first CTAS, even though there is no ORDER BY in the SELECT part.  We can hence reasonably assume this is to achieve the desired clustering.  But the second CTAS raises an interesting question.  If I have to sort the data for the clustering and  there was an ORDER BY clause in the query, which one of those, if any, does the SORT ORDER BY in the execution relate to ?  Well, lets run our CTAS command and find out.


SQL> create table t1
  2  clustering by linear order(object_id)
  3  as select * from source_data
  4  order by dbms_random.random;

Table created.

SQL>
SQL> select object_id, owner from t1 where rownum < 50;

 OBJECT_ID OWNER
---------- ------------------------------
     72256 SYS
     29101 PUBLIC
     26037 SYS
     47229 PUBLIC
     54002 SYS
     65203 PUBLIC
     49264 SYS
     71998 SYS
     91256 APEX_040200
     85311 PUBLIC
     72948 SYS
     68626 SYS
       337 SYS
     54564 SYS
     11256 SYS
     40694 SYS
     44193 PUBLIC
     31856 SYS
     26153 SYS
     25604 PUBLIC
  ...
  ...

Notice that the data is not clustered as per our expectation.  It looks like the final ORDER BY took precedence over our attempt to cluster the data.

So you need to be careful here when using attribute clustering. The database engine is quite particular on the syntax and ordering operations.

In the example above, simply in-lining the order by statement resolves the issue


SQL> create table t1
  2  clustering by linear order(object_id)
  3  as
  4  select *
  5  from
  6  ( select * from source_data order by dbms_random.random );

Table created.

SQL>
SQL> select object_id, owner from t1 where rownum < 50;

 OBJECT_ID OWNER
---------- ------------------------------
         2 SYS
         3 SYS
         4 SYS
         5 SYS
         6 SYS
         7 SYS
         8 SYS
         9 SYS
        10 SYS
        11 SYS
        12 SYS
        13 SYS
        14 SYS
        15 SYS
        16 SYS
        17 SYS
        ...
        ...

And just in case you are wondering, there was no change the execution plan for the above statement. That is, you don’t see any view resolution and multiple SORT ORDER BY lines. It looks as before


------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes |
------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT           |             | 99386 |    10M|
|   1 |  LOAD AS SELECT                  | T1          |       |       |
|   2 |   OPTIMIZER STATISTICS GATHERING |             | 99386 |    10M|
|   3 |    SORT ORDER BY                 |             | 99386 |    10M|
|   4 |     TABLE ACCESS FULL            | SOURCE_DATA | 99386 |    10M|
------------------------------------------------------------------------

But the 1 level of in-lining was enough to fix the issue.   So always validate that you are getting the clustering you are expecting.

Attribute clustering (part 1)

One of the nice facilities in 12c is the ability to instruct the database to co-locate data during loading in order to facilitate reduced resource consumption for subsequent queries.

Here’s an example of it in use. First I’ll create table SOURCE_DATA to mimic some randomised incoming data feed



SQL> exec dbms_random.seed(0)

PL/SQL procedure successfully completed.

SQL> drop table source_data purge;
drop table source_data purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> create table source_data  as
  2      select d.*
  3      from dba_objects d
  4      where object_id is not null
  5      order by dbms_random.random;

Table created.

So let’s now populate (create) our table T with a straight copy of the data from SOURCE_DATA and index a column of interest


SQL> create table t as select * from source_data;

Table created.

SQL>
SQL> select object_id, owner from t where rownum < 50;

 OBJECT_ID OWNER
---------- ------------------------------
     37627 PUBLIC
     79081 ORDSYS
     26953 SYS
     74744 PUBLIC
     47975 PUBLIC
     94003 APEX_050000
     59014 SYS
     66409 PUBLIC
     36977 PUBLIC
     74886 PUBLIC
       565 SYS
     35456 SYS
     74656 SYS
     61451 PUBLIC
     11486 SYS
     61444 SYS
     44048 SYS
     24810 PUBLIC
     27578 SYS
     68871 PUBLIC
     22010 SYS
     31915 PUBLIC
     60762 SYS
     45858 SYS
     44413 PUBLIC
     61323 PUBLIC
     81339 ORDSYS
     31560 SYS
      8621 SYS
     42483 PUBLIC
     35172 SYS
     88978 APEX_040200
     67118 PUBLIC
     27550 SYS
      7244 SYS
     96985 APEX_050000
     80677 PUBLIC
     65032 SYS
     42454 SYS
     69067 PUBLIC
     15227 SYS
     29374 SYS
     19280 XDB
     45063 PUBLIC
     61815 PUBLIC
     94727 APEX_050000
     31122 SYS
     51869 PUBLIC
     39565 PUBLIC

49 rows selected.

SQL>
SQL> create index t_idx on t(object_id);

Index created.

You can see the data is in pseudo-random order on both OBJECT_ID and OWNER. (We only care about OBJECT_ID for the sake of this demo). Let us now see what the I/O of an index range scan query on OBJECT_ID costs us.


SQL> set autotrace traceonly stat
SQL> select /*+ index(t) */ * from t where object_id between 10000 and 15000;

4997 rows selected.


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5339  consistent gets
         12  physical reads
          0  redo size
     629403  bytes sent via SQL*Net to client
       4214  bytes received via SQL*Net from client
        335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       4997  rows processed

SQL> set autotrace off
SQL>
SQL>

Just under 5400 consistent gets, which is not dissimilar from the rows we fetched, because those rows from OBJECT_ID 10,000 to 15,000 they are scattered throughout the table. Let us now repeat the exercise, this time using the clustering facilities available under 12c.


SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t
  2  clustering by linear order(object_id)
  3  as select * from source_data;

Table created.

SQL>
SQL> select object_id, owner from t where rownum < 50;

 OBJECT_ID OWNER
---------- ------------------------------
         2 SYS
         3 SYS
         4 SYS
         5 SYS
         6 SYS
         7 SYS
         8 SYS
         9 SYS
        10 SYS
        11 SYS
        12 SYS
        13 SYS
        14 SYS
        15 SYS
        16 SYS
        17 SYS
        18 SYS
        19 SYS
        20 SYS
        21 SYS
        22 SYS
        23 SYS
        24 SYS
        25 SYS
        26 SYS
        27 SYS
        28 SYS
        29 SYS
        30 SYS
        31 SYS
        32 SYS
        33 SYS
        34 SYS
        35 SYS
        36 SYS
        37 SYS
        38 SYS
        39 SYS
        40 SYS
        41 SYS
        42 SYS
        43 SYS
        44 SYS
        45 SYS
        46 SYS
        47 SYS
        48 SYS
        49 SYS
        50 SYS

49 rows selected.

SQL>
SQL> create index t_idx on t(object_id);

Index created.

SQL>
SQL> set autotrace traceonly stat
SQL> select /*+ index(t) */ * from t where object_id between 10000 and 15000;

4997 rows selected.


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        756  consistent gets
         12  physical reads
          0  redo size
     629403  bytes sent via SQL*Net to client
       4214  bytes received via SQL*Net from client
        335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       4997  rows processed

SQL> set autotrace off
SQL>
SQL>

Before we even used the index, you could see from the sample of rows we queried from the table, the data has been stored in an ordered sequence. As a result, our index lookup is dramatically improved, dropping down to just ~750 consistent gets.  Clustering is covered in great detail in the Data Warehousing Guide, but I presented this example to help with my next blog post on the same topic (coming soon).

Some people have mentioned that Attribute Clustering is an engineered system feature only.  This is not correct – you’re free to Attribute Clustering in Enterprise Edition. It is only when you want to incorporate Zone Maps that licensing things get more complicated Smile

Performing a large correlated update

We had a question on AskTom recently asking for the best way to update a new column on a very large table.  Four in-place update options are at our disposal:

  • Serial update SQL
  • Parallel DML update SQL
  • DBMS_PARALLEL_EXECUTE
  • PL/SQL batching (as long as sensible restart-after-error logic is readily available)

all of which will achieve the required outcome but they have a significant redo and undo cost associated with them.  Also, depending on the size of the new column, such an update might create chaos with row migration, because whilst unlikely, there is definitely the potential for every row to grow beyond the available block space required to hold it in situ.

So a common approach to tackling a large “update” is to recast the problem to creating a new version (copy) of the table.  This can be done with a CREATE TABLE AS SELECT (CTAS).  Using this method we get the benefits of less resource consumption, but two issues can get in the way of this approach:

  1. It requires significant downtime whilst the new table is populated
  2. It requires careful planning to ensure all of the dependent objects (indexes, constraints, etc etc) are correctly recreated on the new table

But perhaps there is an alternative – it might not be 100% as fast as a pure CTAS, but what if it solves both the issues mentioned above ?  That alternative is DBMS_REDEFINITION

People often dismiss DBMS_REDEFINITION as purely for changing the structure of a table without changing the underlying data (for example, partitioning a table).  But they fail to take notice of the column mapping functionality that is available – and as long as our column mapping expressions are deterministic, then we can get more value out of the package, including a correlated update.

So here’s our example:

We will have a table called T1, which is a copy of DBA_OBJECTS and will be the table to which we will want a add a new column




SQL>
SQL> create table t1  as
  2  select owner, object_name, subobject_name,
  3       object_id, data_object_id, object_type,
  4       created, last_ddl_time, timestamp, status,
  5       temporary, generated, secondary
  6    from all_objects
  7    where object_id is not null;

Table created.

SQL>
SQL> alter table t1
  2    add constraint t1_pk
  3    primary key(object_id);

Table altered.

T2 is the table from which we will want to source the new data to apply to T1. In this case, T2 has a primary key of OBJECT_ID which can be used to source a new data element called FNAME. In our example, this is just owner and object name concatenated.


SQL>
SQL> create table t2 as select object_id, owner||'.'||object_name fname
  2  from all_objects
  3    where object_id is not null;

Table created.

SQL>
SQL> alter table t2
  2    add constraint t2_pk
  3    primary key(object_id);

Table altered.

So here is the requirement. T1 gets a new column called FULL_NAME. It must be populated via the FNAME column on T2 via the OBJECT_ID lookup. We can see that T1 starts with FULL_NAME being null, and we can see a sample of the values that should come from T2


SQL> alter table t1 add full_name varchar2(200);

Table altered.

SQL>
SQL> select object_id, owner, full_name from t1
  2  where rownum < 10;

 OBJECT_ID OWNER                          FULL_NAME
---------- ------------------------------ --------------------------------------------------
        30 SYS
        47 SYS
        32 SYS
        49 SYS
        17 SYS
         2 SYS
        29 SYS
        45 SYS
        10 SYS

9 rows selected.

SQL>
SQL> select object_id, fname from t2
  2  where rownum < 10;

 OBJECT_ID FNAME
---------- ---------------------------
        30 SYS.I_COBJ#
        47 SYS.I_USER2
        32 SYS.CCOL$
        49 SYS.I_COL2
        17 SYS.FILE$
         2 SYS.C_OBJ#
        29 SYS.C_COBJ#
        45 SYS.I_TS1
        10 SYS.C_USER#

We can’t do joins or subqueries in DBMS_REDEFINITION but we can do deterministic expressions. In our case, we’ll use a deterministic PL/SQL function to mimic the join


SQL>
SQL> create or replace
  2  function get_full(p_id int )  return varchar2 deterministic is
  3    f varchar2(100);
  4  begin
  5    select  fname
  6    into f
  7    from t2
  8    where object_id = p_id;
  9
 10    return f;
 11  end;
 12  /

Function created.

As per normal, we create an interim table to hold the converted data as we redefine the table.


SQL>
SQL> create table t_interim (
  2   owner                         varchar2(128),
  3   object_name                   varchar2(128),
  4   subobject_name                varchar2(128),
  5   object_id                     number,
  6   data_object_id                number,
  7   object_type                   varchar2(23),
  8   created                       date,
  9   last_ddl_time                 date,
 10   timestamp                     varchar2(19),
 11   status                        varchar2(7),
 12   temporary                     varchar2(1),
 13   generated                     varchar2(1),
 14   secondary                     varchar2(1),
 15   full_name                     varchar2(200)
 16  );

Table created.

And now we are ready to use DBMS_REDEFINITION. The critical part here is the COL_MAPPING parameter. We are using the call to GET_FULL to populate the new column that will ultimately end up on T1.


SQL>
SQL>
SQL> declare
  2      l_colmap varchar(512);
  3    begin
  4      l_colmap :=
  5            'OWNER
  6            ,OBJECT_NAME
  7            ,SUBOBJECT_NAME
  8            ,OBJECT_ID
  9            ,DATA_OBJECT_ID
 10            ,OBJECT_TYPE
 11            ,CREATED
 12            ,LAST_DDL_TIME
 13            ,TIMESTAMP
 14            ,STATUS
 15            ,TEMPORARY
 16            ,GENERATED
 17            ,SECONDARY
 18            ,get_full(OBJECT_ID) FULL_NAME';
 19
 20      dbms_redefinition.start_redef_table
 21      (  uname           => user,
 22         orig_table      => 'T1',
 23         int_table       => 'T_INTERIM',
 24         col_mapping   => l_colmap );
 25   end;
 26   /

PL/SQL procedure successfully completed.

And here is the nice part. By using DBMS_REDEFINITION, we dont need to worry about the dependencies – we can let COPY_TABLE_DEPENDENTS do the heavy lifting.


SQL>
SQL> variable nerrors number
SQL> begin
  2    dbms_redefinition.copy_table_dependents
  3      ( user, 'T1', 'T_INTERIM',
  4        copy_indexes => dbms_redefinition.cons_orig_params,
  5        num_errors => :nerrors );
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> print nerrors

   NERRORS
----------
         0

SQL> begin
  2    dbms_redefinition.finish_redef_table
  3      ( user, 'T1', 'T_INTERIM' );
  4  end;
  5  /

PL/SQL procedure successfully completed.

And we’re done ! A correlated update using DBMS_REDEFINITION.


SQL>
SQL> select object_id, owner, full_name from t1
  2  where rownum < 10;

 OBJECT_ID OWNER                          FULL_NAME
---------- ------------------------------ --------------------------------------------------
        30 SYS                            SYS.I_COBJ#
        47 SYS                            SYS.I_USER2
        32 SYS                            SYS.CCOL$
        49 SYS                            SYS.I_COL2
        17 SYS                            SYS.FILE$
         2 SYS                            SYS.C_OBJ#
        29 SYS                            SYS.C_COBJ#
        45 SYS                            SYS.I_TS1
        10 SYS                            SYS.C_USER#

9 rows selected.

SQL>
SQL>

You might be thinking “But we’ll be slow because we’re calling that PL/SQL millions of times”.  Yes, this will be slower than a pure CTAS operation, but by using DBMS_REDEFINITION the process is online with nearly zero service disruption.  Hence the overall execution time is not nearly so critical anymore as a CTAS where applications are unavailable during the activity.