Securefile in 12c – part 2

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.

4 thoughts on “Securefile in 12c – part 2

  1. Hi Connor,

    Thanks. Is that Advanced compression license is needed for setting the db_securefile parameter to preferred?

    We recently upgraded all our databases ( with Oracle EBS R12.1.3) to 12.1.0.2 and i see the parameter set is PERMITTED.

    Baskar.l

  2. No, securefiles can be compressed, but do not *have* to be compressed.

    In terms of the default, from the docs:

    Default value:
    – PERMITTED if the COMPATIBLE initialization parameter is set to 11.2.0.1, 11.2.0.2, or 11.2.0.3, or
    – PREFERRED if the COMPATIBLE initialization parameter is set to 12.0.0.0 or higher

    Hope this helps.

  3. Thanks a lot Connor.

    i see we havent set the right parameter.

    SQL> show parameter db_securefile
    db_securefile string PERMITTED
    SQL> show parameter compatible

    NAME TYPE VALUE
    ———————————— —————————— ——————————
    compatible string 12.1.0

    We need to modify the parameter setting to make it work for lobs created in future to be create by default as secure files.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s