How to Change Retention for Securefile LOB Segments in Oracle

Managing Large Object (LOB) segments in Oracle databases can be a nuanced task, especially when it comes to Securefile LOBs. If you've ever wondered how to tweak the retention settings for these segments, you're in the right place. This post will guide you through the process with clarity.

Understanding LOB Retention 

For Securefile LOBs, Oracle allows you to specify the RETENTION parameter. It's important to note that you can specify either PCTVERSION or RETENTION for Basicfile LOBs, but not both.

Checking the current UNDO settings 

Before making changes, it’s good practice to check your current undo settings. You can do this using the following command:

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

temp_undo_enabled                    boolean     FALSE

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

Create a table with Securefile LOB and view the retention 

Let's create a test table with a Securefile LOB and view its default settings :

CREATE TABLE test.employee_int (id NUMBER, c_lob CLOB) LOB (c_lob) STORE AS SECUREFILE;

set linesize 300

col owner for a20 

col TABLE_NAME for a20

col COLUMN_NAME for a20

col RETENTION_TYPE for a15

SELECT owner, TABLE_NAME, COLUMN_NAME, PCTVERSION, RETENTION, SECUREFILE, RETENTION_TYPE, RETENTION_VALUE 

FROM dba_lobs 

WHERE table_name = 'EMPLOYEE_INT';

OWNER                TABLE_NAME           COLUMN_NAME          PCTVERSION  RETENTION SEC RETENTION_TYPE  RETENTION_VALUE

-------------------- -------------------- -------------------- ---------- ---------- --- --------------- ---------------

EMPLOYEE_INT         CUST_INT             C_LOB                                      YES DEFAULT                        

Modifying Undo Retention & Changing LOB Retention 

alter system set undo_retention=1500;

alter table  modify lob () (retention min );  ie.  =1500 

alter table employee_int modify lob (c_lob) (retention sec 1500);

OWNER                TABLE_NAME           COLUMN_NAME          PCTVERSION  RETENTION SEC RETENTION_TYPE  RETENTION_VALUE
-------------------- -------------------- -------------------- ---------- ---------- --- --------------- ---------------
EMPLOYEE_INT         CUST_INT             C_LOB                                      YES MIN                        1500

You should see that the RETENTION_TYPE is now set to MIN with the specified retention value.

 

Resetting to default

If you need to reset the retention to the default setting:

ALTER TABLE test.cust_int MODIFY LOB (c_lob) (retention default);

Summary 

Managing Securefile LOB retention in Oracle is straightforward once you know the right commands. By following the steps outlined in this guide, you can easily check and modify LOB retention settings to optimize your database's performance and manageability.

Remember, whether you are changing retention settings to accommodate a temporary workload or resetting to default for everyday operations, Oracle provides the flexibility you need to manage your LOB data efficiently.

Happy database managing!