Oracle LOB存储转换为安全文件

我有一个旧的应用程序，刚刚迁移到Oracle 12.2。应用程序有一个大的间隔分区表，其中分区列是日期。此表包含LOB列，有些使用BASICFILE存储，其他列使用SECUREFILE存储。我在网上看到一个帖子，似乎表明有一种方法可以修改表，以便当前使用BASICFILE的现有分区的LOB列将在创建新的间隔分区时开始使用SECUREFILE的LOB。这对于该表是理想的，因为随着时间的推移，较旧的分区将被删除，最终该表将具有所有SECUREFILE lob，而无需迁移和移动所有数据。如果这可能，你能建议吗？如果是这样，对于父级被范围分区的引用分区表也可以这样做。

您可以在创建分区时提名它

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
复制

对于一个间隔，只需更改 “db_securefile” 就足够了，例如

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
复制