Thanks for the question, Brian.

Asked: September 28, 2018 - 5:35 pm UTC

Last updated: October 16, 2023 - 1:40 am UTC

Version: 12.2

Viewed 10K+ times! This question is

You Asked

I have an old application that was just migrated to Oracle 12.2. The application has a large interval partitioned table where the partitioning column is a DATE. This table contains LOB columns, some use BASICFILE storage and other columns use SECUREFILE storage. I saw a posting on the web that seemed to indicate there is a way to modify the table so that a LOB column that currently uses BASICFILE for existing partitions will start using SECUREFILE for the LOB as new interval partitions are created. This would be ideal for this table because over time the older partitions are dropped, and eventually the table would have all SECUREFILE LOBS without having to migrate and move all the data. If this is possible, can you advise? If so, can this also be done for reference partitioned tables where the parent is range partitioned.

and Connor said...

You can nominate it as you create the partitions

SQL> create table t ( x int, y clob )
  2  partition by range (x)
  3  ( partition p1 values less than (10) lob ( y ) store as basicfile,
  4    partition p2 values less than (20) lob ( y ) store as basicfile
  5  );

Table created.

SQL>
SQL> insert into t values ( 5,'clob');

1 row created.

SQL> insert into t values ( 15,'clob');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> ALTER TABLE t add partition p3 values less than ( 30 ) lob ( y ) store as securefile;

Table altered.

SQL> insert into t values ( 25,'clob');

1 row created.

SQL> commit;

Commit complete.

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

For an INTERVAL, just changing 'db_securefile' should be sufficient, eg

SQL> create table t ( x int, y clob )
  2  lob ( y ) store as basicfile
  3  partition by range (x)
  4  interval ( 10 )
  5  ( partition p1 values less than (10) lob ( y ) store as basicfile
  6  );

Table created.

SQL>
SQL> insert into t values ( 5,'clob');

1 row created.

SQL> insert into t values ( 15,'clob');

1 row created.

SQL> commit;

Commit complete.

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

PARTITION_NAME                 SEC
------------------------------ ---
P1                             NO
SYS_P3703                      NO

SQL>
SQL> alter session set db_securefile = preferred;

Session altered.

SQL> insert into t values ( 25,'clob');

1 row created.

SQL> commit;

Commit complete.

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

PARTITION_NAME                 SEC
------------------------------ ---
P1                             NO
SYS_P3703                      NO
SYS_P3706                      YES

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Enabling lob compression once all partitions are securefile

Virgile, December 20, 2022 - 7:47 am UTC

Once all partitions are securefile, I did not find a way to enable lob compression without doing a DBMS_REDEFINITION.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

SQL> column TABLE_NAME format A30
SQL> column PARTITION_NAME format A30

SQL> create table t ( x int, y clob )
lob ( y ) store as basicfile
partition by range (x)
interval ( 10 ) ( partition p1 values less than (10) lob ( y ) store as basicfile );

Table created.

SQL> select table_name, securefile
from dba_lobs
where owner = 'EP2'
and table_name in ('T');

TABLE_NAME SEC
------------------------------ ---
T NO

SQL> select table_name, partition_name, securefile
from dba_lob_partitions
where table_owner = 'EP2'
and table_name in ('T');

TABLE_NAME PARTITION_NAME SEC
------------------------------ ------------------------------ ---
T P1 NO

SQL> insert into t values (25, 'clob');

1 row created.

SQL> alter table t drop partition P1;

Table altered.

SQL> select table_name, securefile
from dba_lobs
where owner = 'EP2'
and table_name in ('T');

TABLE_NAME SEC
------------------------------ ---
T NO

SQL> select table_name, partition_name, securefile
from dba_lob_partitions
where table_owner = 'EP2'
and table_name in ('T');

TABLE_NAME PARTITION_NAME SEC
------------------------------ ------------------------------ ---
T SYS_P654041 YES

SQL> ALTER TABLE t MOVE LOB(y) STORE AS securefile;
ALTER TABLE t MOVE LOB(y) STORE AS securefile
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object

SQL> alter table t modify default attributes lob(y)(compress low);
alter table t modify default attributes lob(y)(compress low)
*
ERROR at line 1:
ORA-43856: Unsupported LOB type for SECUREFILE LOB operation

October 16, 2023 - 1:40 am UTC

SQL> create table t ( x int, c clob )
  2  lob ( c ) store as securefile ( compress low )
  3  partition by range (x)
  4* interval ( 10 ) ( partition p1 values less than (10) );

Table T created.

SQL>
SQL> alter table t modify default attributes lob ( c ) ( compress high );

Table T altered.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Enabling lob compression once all partitions are securefile

More to Explore

Administration

LOB storage conversion to SECUREFILE

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Enabling lob compression once all partitions are securefile

More to Explore

Administration