Releasing LOB space in Oracle databases

You can release the large object (LOB) storage in the Oracle databases.

Before you begin

Plan downtime for upgrading HCL DevOps Deploy (Deploy) and releasing LOB space.

About this task

Run the following database commands to release LOB storage in Oracle databases. Releasing LOB storage in Oracle database requires different commands for basicfile and securefile file type.

You can also refer to the Oracle instructions for reclaiming unused space here: https://oracle-base.com/articles/misc/reclaiming-unused-space.

Procedure

Set the versioned_config.upgrade.enabled=true parameter in the conf/server/installed.properties file.
Restart the Deploy server.
Check on the progress of the conversion:
```
select count(*) from vc_persistent_record where persistent_data is not null
```
The compression process is complete when the row count reported by the query is 0.

Check the database size:

select sum(bytes)/1024/1024 size_in_mb from dba_segments;

Release LOB storage by running the following commands for the appropriate Oracle file type.

For securefile LOBs use these commands:

select sum(bytes)/1024/1024 size_in_mb from dba_segments;
ALTER TABLE vc_persistent_record MOVE TABLESPACE users;
ALTER INDEX VC_PERSISTENT_REC_PATH_VER REBUILD TABLESPACE users;
ALTER INDEX VC_PERSISTENT_RECORD_DIRECTORY REBUILD TABLESPACE users;
ALTER INDEX SYS_XXXXXXX* REBUILD TABLESPACE users;
ALTER INDEX VC_PERSIST_REC_COMMIT_PATH REBUILD TABLESPACE users;
ALTER INDEX VC_PERSISTENT_RECORD_VERSION REBUILD TABLESPACE users;
ALTER TABLE vc_persistent_record MOVE LOB(persistent_data) STORE AS (TABLESPACE users);
select sum(bytes)/1024/1024 size_in_mb from dba_segments;

For basicfile LOBs use these commands:

select sum(bytes)/1024/1024 size_in_mb from dba_segments;
ALTER TABLE vc_persistent_record MOVE TABLESPACE users;
ALTER INDEX VC_PERSISTENT_REC_PATH_VER REBUILD TABLESPACE users;
ALTER INDEX VC_PERSISTENT_RECORD_DIRECTORY REBUILD TABLESPACE users;
ALTER INDEX SYS_XXXXXXX* REBUILD TABLESPACE users;
ALTER INDEX VC_PERSIST_REC_COMMIT_PATH REBUILD TABLESPACE users;
ALTER INDEX VC_PERSISTENT_RECORD_VERSION REBUILD TABLESPACE users;
ALTER TABLE VC_PERSISTENT_RECORD MODIFY LOB (persistent_data) (SHRINK SPACE);
select sum(bytes)/1024/1024 size_in_mb from dba_segments;

Note: The database generates a SYS_XXXXXXX index file. Check the indexes in the vc_persistent_record table for the index name.

Optional: Free the unused space so that it can be reused. Note that the process might not reduce the size of the underlying data files. To start the process of reclaiming disc storage space (if wanted), a database administrator must complete database maintenance tasks.
```
ALTER TABLE vc_persistent_record ENABLE ROW MOVEMENT;
ALTER TABLE vc_persistent_record SHRINK SPACE;
ALTER TABLE vc_persistent_record DISABLE ROW MOVEMENT;
```

What to do next

Verify that the space is reduced:
```
select sum(bytes)/1024/1024 size_in_mb from dba_segments;
```
Set the versioned_config.upgrade.enabled parameter back to false in the conf/server/installed.properties file.