Posted by 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 
Good news.
It’s easy to envisage people switching preferences to get new partitions as securefiles then take time to rebuild the old partitions one at a time.
Regards
Jonathan Lewis
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
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.
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.
Hi ,
I am facing a problem and appreciate if someone can help on this.
DB – 19C
TABLESPACE – ASSM
Create table clob_test
(
sale_id number,
product_id number,
price number,
CLB1 CLOB
)
PARTITION BY RANGE(sale_id) (
partition s1 values less than (10) tablespace A,
partition s2 values less than (20) tablespace A,
partition s3 values less than (MAXVALUE) tablespace A
);
alter session set db_securefile = PREFEREED;
ALTER TABLE CLOB_TEST ADD CLB2 CLOB;
SELECT COLUMN_NAME, SECUREFILE FROM DBA_LOBS WHERE TABLE_NAME = ‘CLOB_TEST’;
—————————————–
CLB1 YES
CLB2 NO
So, the issue is when I set PREFEREED as the mode and add a new clob to the table, it is defaulting to basicfile. However, it works fine when I set the mode to FORCE
I can replicate that – I’ll log a bug because that does not seem correct.