12c2

LOBs from afar

Connor McDonald , ,

This has always been a nuisance.  There you are – getting all the bells and whistles with LOBs…until a database link enters the room Smile


--
-- Database: DB11
--
SQL> create table t ( id int, c clob );

Table created.

SQL> insert into t values (1,rpad('x',32000,'x'));

1 row created.

SQL> select * 
  2  from   t;

        ID C
---------- --------------------------------------------
         1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

--
-- Database: anywhere except DB11
--
SQL> select * 
  2  from   t@db11;

ERROR:
ORA-22992: cannot use LOB locators selected from remote tables

 

Well that sucks. But look what has snuck its way into 12.2 !


SQL> select * 
  2  from   t@db122;

        ID C
---------- --------------------------------------------
         1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

 

There is a whole chapter on it here in the official docs.  Very nice indeed.

Interval partitioning just got better

Connor McDonald , , ,

Interval partitioning was a great feature when it arrived in version 11, because we no longer had to worry so much about ensuring partitions were available for new data when it arrived.  Partitions would just be created on the fly as required.  I’m not going to talk about interval partition in detail because there’s plenty of good content already out there.  But one key element for interval partitioning is that the intervals have to start from somewhere, which is why you always have to define a table with at least one partition.

 

image

 

So what if I want to drop that partition that is the conceptual “starting point”.  Well…I get problems Smile


SQL> create table sales
  2  ( tstamp       timestamp    not null,
  3    empno        number(10)   not null,
  4    ename        varchar2(10) not null,
  5    deptno       varchar2(10) not null
  6  )
  7  partition by range (tstamp)
  8  interval( numtoyminterval(1,'YEAR'))
  9  (
 10    partition p00 values less than (timestamp '2010-01-01 00:00:00')
 11  );

Table created.

SQL>
SQL> insert into sales values ( timestamp '2011-01-01 00:00:00' , 0,0,0);

1 row created.

SQL> insert into sales values ( timestamp '2012-01-01 00:00:00' , 0,0,0);

1 row created.

SQL> insert into sales values ( timestamp '2013-01-01 00:00:00' , 0,0,0);

1 row created.

SQL>
SQL> col high_value format a36
SQL> col pname format a10
SQL>
SQL> select
  2    partition_name pname,
  3    partition_position pos,
  4    high_value,
  5    interval
  6  from   user_tab_partitions
  7  where  table_name = 'SALES';

PNAME             POS HIGH_VALUE                           INT
---------- ---------- ------------------------------------ ---
P00                 1 TIMESTAMP' 2010-01-01 00:00:00'      NO
SYS_P121            2 TIMESTAMP' 2012-01-01 00:00:00'      YES
SYS_P122            3 TIMESTAMP' 2013-01-01 00:00:00'      YES
SYS_P123            4 TIMESTAMP' 2014-01-01 00:00:00'      YES

4 rows selected.

SQL>
SQL> alter table sales drop partition p00;
alter table sales drop partition p00
                                 *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped

There is a simple workaround for that issue. If you re-issue the INTERVAL definition for the table, all of the existing partitions will be “upgraded” (or should be it downgraded…I dunno) to being range partitions. Hence we will now have “moved” the starting point, and can then drop the problem partition.


SQL> alter table sales set interval( numtoyminterval(1,'YEAR'));

Table altered.

SQL> select
  2    partition_name pname,
  3    partition_position pos,
  4    high_value,
  5    interval
  6  from   user_tab_partitions
  7  where  table_name = 'SALES';

PNAME             POS HIGH_VALUE                           INT
---------- ---------- ------------------------------------ ---
P00                 1 TIMESTAMP' 2010-01-01 00:00:00'      NO
SYS_P121            2 TIMESTAMP' 2012-01-01 00:00:00'      NO
SYS_P122            3 TIMESTAMP' 2013-01-01 00:00:00'      NO
SYS_P123            4 TIMESTAMP' 2014-01-01 00:00:00'      NO

4 rows selected.

SQL> alter table sales drop partition p00;

Table altered.

So that’s all pretty easy, but of course, you must now run this “re-interval” command all the time to be sure that you will always be able to drop any partition you want.

Unless of course….. you’re on 12.2 ! Let’s repeat the demo on 12.2


SQL> create table sales
  2  ( tstamp       timestamp    not null,
  3    empno        number(10)   not null,
  4    ename        varchar2(10) not null,
  5    deptno       varchar2(10) not null
  6  )
  7  partition by range (tstamp)
  8  interval( numtoyminterval(1,'YEAR'))
  9  (
 10    partition p00 values less than (timestamp '2010-01-01 00:00:00')
 11  );

Table created.

SQL>
SQL> insert into sales values ( timestamp '2011-01-01 00:00:00' , 0,0,0);

1 row created.

SQL> insert into sales values ( timestamp '2012-01-01 00:00:00' , 0,0,0);

1 row created.

SQL> insert into sales values ( timestamp '2013-01-01 00:00:00' , 0,0,0);

1 row created.

SQL>
SQL> col high_value format a36
SQL> col pname format a10
SQL>
SQL> select
  2    partition_name pname,
  3    partition_position pos,
  4    high_value,
  5    interval
  6  from   user_tab_partitions
  7  where  table_name = 'SALES';

PNAME             POS HIGH_VALUE                           INT
---------- ---------- ------------------------------------ ---
P00                 1 TIMESTAMP' 2010-01-01 00:00:00'      NO
SYS_P3415           2 TIMESTAMP' 2012-01-01 00:00:00'      YES
SYS_P3416           3 TIMESTAMP' 2013-01-01 00:00:00'      YES
SYS_P3417           4 TIMESTAMP' 2014-01-01 00:00:00'      YES

4 rows selected.

SQL>
SQL> alter table sales drop partition p00;

Table altered.

SQL>
SQL> select
  2    partition_name pname,
  3    partition_position pos,
  4    high_value,
  5    interval
  6  from   user_tab_partitions
  7  where  table_name = 'SALES';

PNAME             POS HIGH_VALUE                           INT
---------- ---------- ------------------------------------ ---
SYS_P3415           1 TIMESTAMP' 2012-01-01 00:00:00'      NO
SYS_P3416           2 TIMESTAMP' 2013-01-01 00:00:00'      YES
SYS_P3417           3 TIMESTAMP' 2014-01-01 00:00:00'      YES

3 rows selected.

How cool is that! We now automatically modify one of the interval partitions to being a range partition, so you’ll not get the error.

The little features are often the coolest Smile

12c Release 2 – Transparent Data Encryption online !

Connor McDonald , , , ,

It doesn’t take a rocket scientist to know that even if you have incredibly stringent controls on user authentication, user authorisation etc, that wont save you if your data on disk is not encrypted.  All you need is an errant tape, a missing disk, a misplaced flash stick…and kersplat, someone has a copy of your datafiles from your Oracle database.

Data at rest should be encrypted, but that often meant taking applications offline to do so.

I’ve put my Speed Racer hat on Smile and here’s a video on a new 12c Release 2 feature covered in 60 seconds !

Oracle Database 12c Release 2 is here !

Connor McDonald ,

image

Since OpenWorld 2016 when we first saw some of the cool features in Oracle Database 12c Release 2, many IT professionals out there have been exploring the release via our various cloud offerings, but if your organization has not yet embraced the cloud, then March 2017 is a great month for you !  Because you can now download the latest and greatest release of our database from the usual downloads page, and run it on your own servers in your own data centre.

Of course, there’s a difference between downloading and installing the software, and being up to speed with all of the great features that have come in 12c and 12c Release 2 so keep an eye on my blog, and on my YouTube channel.

Over the coming weeks I’ll be talking about lots of the new features in 12.2 in a short easy to digest videos to help you appreciate all of the goodness that is 12c Release 2 Smile

To whet your appetite, how cool is this simple enhancement to SQL Plus – command history !



SQL> history
  1  select * from dba_users where username = 'SYSTEM';
  2  select count(*)
     from dba_tables
     where table_Name like 'A%';
  3  select * from tab;
  4  show sga
  5  set timing off
  6  select * from dba_tablespaces;

SQL> history 4 run

Total System Global Area 1048576000 bytes
Fixed Size                  8795840 bytes
Variable Size             251660608 bytes
Database Buffers          784334848 bytes
Redo Buffers                3784704 bytes

A 12.2 treat for the festive season

Connor McDonald , , , ,

We’re being asked to store more and more data, yet keep backup windows, query performance and the like unchanged, no matter how much we store. As a result, more and more database shops are needing to partition their data. The problem is – partitioning data is a significant restructure of the data, which thus incurs a large outage and the accompanying planning and coordination.

Unless you’re on 12.2.

Here’s a demo where we can take an existing table and

  • convert it to partitioned on a range scheme, also using intervals so we don’t need to worry about ongoing range creation maintenance, and
  • take one of the existing indexes, and convert it to a local index to mirror the table partitioning scheme, and
  • take the other existing index, and globally partition it.

Oh…and of course, we will do the whole thing online without service disruption

Oh…and of course, we will do it with a single SQL command.

Ab…So….Lute….Ly awesome !



SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 22 09:53:37 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Dec 19 2016 13:38:54 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production


SQL> create table T as
  2  select d.*
  3  from dba_Objects d,
  4       ( select 1 from dual
  5         connect by level <= 20 )   6  where d.object_id is not null; Table created. SQL> create index IX on t ( object_id );

Index created.

SQL> create index IX2 on t ( created, object_name );

Index created.

SQL> alter table T modify
  2  partition by range (object_id) interval (10000)
  3  (
  4    partition p1 values less than (20000)
  5  ) online
  6  update indexes
  7  ( ix  local,
  8    ix2 global partition by range (created)
  9    (
 10     partition ix2_p1 values less than (date '2016-08-01'),
 11     partition ix2_p2 values less than (maxvalue)
 12   )
 13  );

Table altered.

SQL> select partition_name
  2  from   user_tab_partitions
  3  where  table_name = 'T';

PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
P1
SYS_P1145
SYS_P1146
SYS_P1147
SYS_P1148
SYS_P1149
SYS_P1150

7 rows selected.

SQL> select index_name, partition_name
  2  from   user_ind_partitions
  3  where  index_name like 'IX%';

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
IX                             P1
IX                             SYS_P1145
IX                             SYS_P1146
IX                             SYS_P1147
IX                             SYS_P1148
IX                             SYS_P1149
IX                             SYS_P1150
IX2                            IX2_P1
IX2                            IX2_P2

9 rows selected.

SQL>