Releasing the VC LOB space#

On upgrading to HCL™ Launch 7.0.5, the changes in the vc_persistent_record table reduces the amount of LOB storage space used by HCL Launch. You can optionally release the underlying LOB storage space. If you don't, the space will be reused when new records are added.

Plan downtime for upgrading HCL Launch and releasing LOB space.

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

  1. Set experimental.vcUpgrade.enabled=true in the conf/server/installed.properties file.

  2. Start the HCL Launch upgrade and make sure the HCL Launch server is up and running. See Upgrading the Server in Interactive Mode for more information.

  3. Check on the progress of the conversion.

    select count(*) from vc_persistent_record where persistent_data is not null

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

    • For securefile LOBs use:

      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); TRUNCATE TABLE vc_persistent_record; select sum(bytes)/1024/1024 size_in_mb from dba_segments;

    • For basicfile LOBs use:

      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); TRUNCATE TABLE vc_persistent_record; select sum(bytes)/1024/1024 size_in_mb from dba_segments;

    Note: A SYS_XXXXXXX index file is generated by the database. Check the indexes in the vc_persistent_record table for the index name.

  5. Free the unused space so that it can be reused. Note that it may not reduce the size of the underlying data files. The process of reclaiming disc storage space (if desired) requires database maintenance tasks to be performed by a DBA.

    ALTER TABLE vc_persistent_record ENABLE ROW MOVEMENT; ALTER TABLE vc_persistent_record SHRINK SPACE; ALTER TABLE vc_persistent_record DISABLE ROW MOVEMENT;

Verify that the space has been reduced.

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

Oracle has published instructions for reclaiming unused space here: https://oracle-base.com/articles/misc/reclaiming-unused-space.

Parent topic: Upgrading the server

Related information

Upgrading the server in interactive mode