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.
Learning is not a spectator sport 
Leave a reply to kapil kumar varshney Cancel reply