securefile

LOBs and tiny typos

Connor McDonald , , , ,

This one caught me out – I was racing around looking for bugs, or parameter files, or hidden settings that stopped SECUREFILE lobs from being created.  Here was my incredibly simple test case – create a securefile LOB, and then make sure it’s a securefile.


SQL> create table t1 ( b blob ) lob ( b ) store as securfile;

Table created.

SQL> select securefile
  2  from   user_lobs
  3  where  table_name = 'T1';

SEC
---
NO

That had me bamboozled, but it is a trivial explanation.  Notice that I did not spell  “SECUREFILE” correctly.  As a result, the syntax is interpreted as being the name of the LOB segment in the data dictionary, rather than the specification of how the LOB should be stored.


SQL> select segment_name
  2  from   user_lobs
  3  where  table_name = 'T1';

SEGMENT_NAME
------------------------------
SECURFILE

All it takes is the correct “e” in “securefile” and normal service was resumed Smile


SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 ( b blob ) lob ( b ) store as securefile;

Table created.

SQL> select securefile
  2  from   user_lobs
  3  where  table_name = 'T1';

SEC
---
YES

So why blog about it such a silly mistake ?  Because this is a common issue with all of us as developers.  We see something unusual and our first assumption is that it must be some fundamental flaw in the product we’re using.  That mindset has been around as long computer programming has existed, but but 20 years ago, it wasn’t so easy to make a fool of yourself by bleating about it on social media Smile.  I remember when I first started programming, one of my mentors told me: “Remember, the COBOL compiler is not broken”, and of course, we can insert any programming language into that sentence.  So before you jump on to Twitter … just take a moment to re-check that script, or get a colleague to give it a “once over”.  You’ll either save yourself some embarrassment, or you add additional rigour to your test case – it’s win-win.

Securefile in 12c – part 2

Connor McDonald , , ,

In a previous post, I noted that the parameter db_securefile changes from PERMITTED in 11g, to PREFERRED in 12c.  Jonathan Lewis raised the interesting question of what happens when you upgrade to 12c from 11g, where a partitioned table may already have some basicfile LOBs defined.  This blog post explores that.

We’ll start with “PERMITTED” mode, and create a table with two partitions. We can see that at table level and partition level, the LOBs are stored as BASICFILE because we have explicitly create them as such. Can we add a new partition but opt to have SECUREFILE for that partition ?



SQL> alter session set db_securefile = permitted;

Session altered.

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table T ( x int, y clob )
  2  lob(y) store as basicfile
  3  partition by list ( x )
  4  (
  5    partition p1 values (1),
  6    partition p2 values (2)
  7  )
  8  /

Table created.

SQL> select securefile
  2  from user_lobs
  3  where table_name = 'T';

SEC
---
NO

1 row selected.

SQL>
SQL> select partition_name, securefile
  2  from user_lob_partitions
  3  where table_name = 'T';

PARTITION_NAME       SEC
-------------------- ---
P1                   NO
P2                   NO

2 rows selected.

SQL>
SQL> alter table T
  2   add partition p3 values (3)
  3   lob(y) store as securefile
  4  /

Table altered.

SQL>
SQL> select partition_name, securefile
  2  from user_lob_partitions
  3  where table_name = 'T';

PARTITION_NAME       SEC
-------------------- ---
P1                   NO
P2                   NO
P3                   YES

3 rows selected.

OK…no dramas there. We can see that the new partition will be SECUREFILE whilst the older ones are left untouched. Let’s now reset out db_securefile parameter



SQL> alter session set db_securefile = preferred;

Session altered.

SQL> alter table T
  2   add partition p4 values (4)
  3  /

Table altered.

SQL> select securefile
  2  from user_lobs
  3  where table_name = 'T';

SEC
---
NO

1 row selected.

SQL> select partition_name, securefile
  2  from user_lob_partitions
  3  where table_name = 'T';

PARTITION_NAME       SEC
-------------------- ---
P1                   NO
P2                   NO
P3                   YES
P4                   YES

We can see that with the parameter set to “preferred” (the default in 12c) that any new partitions will be defined as SECUREFILE even though the table level default remains at BASICFILE. This is alluded to within the reference documentation for db_securefile

PREFERRED

All LOBs are created as SecureFiles unless BASICFILE is explicitly specified in the LOB storage clause or the tablespace is a Manual Segment Space Management tablespace. When PREFERRED is set, cases where BASICFILE would otherwise be inherited from the partition or column level LOB storage are ignored; the LOBs will be created as SecureFiles instead.

One interesting aspect of this, is that even at table creation time, the partition creation can be conceptualised as “create table, then add each partition”, because look what happens in that case (with our default setting of “preferred”)


SQL> alter session set db_securefile = preferred;

Session altered.

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table T ( x int, y clob )
  2  lob(y) store as basicfile
  3  partition by list ( x )
  4  (
  5    partition p1 values (1),
  6    partition p2 values (2)
  7  )
  8  /

Table created.

SQL>
SQL> select securefile
  2  from user_lobs
  3  where table_name = 'T';

SEC
---
NO

1 row selected.

SQL>
SQL> select partition_name, securefile
  2  from user_lob_partitions
  3  where table_name = 'T';

PARTITION_NAME       SEC
-------------------- ---
P1                   YES
P2                   YES

2 rows selected.

Even those partitions nominated at table creation time are still defaulted to SECUREFILE.

CLOBS, from 11g to 12c

Connor McDonald , , ,

If you are a regular user of LOB’s in the database, take care when you switch from 11g to 12c.  Notice the subtle difference:

In 11g, you are permitted to, and recommended to, use SECUREFILE lobs, but they are not the default


SQL> select name, value
  2  from v$parameter
  3  where  name = 'db_securefile';

NAME                                     VALUE
---------------------------------------- ---------------
db_securefile                            PERMITTED

In12c, SECUREFILE lobs will be the default.



SQL> select name, value
  2  from v$parameter
  3  where  name = 'db_securefile';

NAME                                     VALUE
---------------------------------------- ---------------
db_securefile                            PREFERRED

This change should be beneficial, because SECUREFILE lobs are the newer and more functional implementation, but just be aware that the default has changed between versions.