lob reorganisation

by

oracle Lob Reorganisation

The method below describes the steps to redefine the DDL of a table in whole or in part eg refine the lob definition only.

it works online, and performs an exact copy of the data from the source to the destination object, obeying the destination objects changed DDL where valid.
the only offline period, is a few seconds at the end of the task, where the tables are switched around, with the destination table name switching to the source and vice versa.

TIP: this time period can be reduced by running a synchronise process prior to running the “finish_redef” command.

Steps

1) if you wish to recover space from a datafile, you can only recover the space above the High Water Mark. For this reason, for a large LOB reorganisation, it may be best advised to move the lob and (optionally), it’s parent table into a different tablespace – perhaps a new one.

CREATE TABLESPACE PR_ARCHIVELOBDATA DATAFILE  SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE 32767M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

2) check the average size distribution of the lob – this will aid you in setting the chunk size. (note this is irrelevant when using Securefiles) and retained for posterity only. the code below will give an indication of how many rows will be stored inline (below 4k) and how many out of line – ie stored into a separate log segment)

eg
SELECT 'pritemdataversion' table_name, COUNT(*), size_cat FROM (
SELECT
  CASE
    WHEN dbms_lob.getLength(item_data) <= 4096 THEN 'inline'
    WHEN dbms_lob.getLength(item_data) >  4096 AND dbms_lob.getLength(item_data) <= 32768 THEN 'small'
    WHEN dbms_lob.getLength(item_data) > 32768 AND dbms_lob.getLength(item_data) <= 65536 THEN 'medium'
    ELSE 'large'
  END AS size_cat
    FROM aesuser.pritemdataversion
) GROUP BY rollup(size_cat)

3) create destination table containing new ddl.
4) check the redefinition is valid

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('[schemaname]', '[objectname]');

TIP – no output will be given if the redefinition can go ahead – ie only error messages are displayed

4) run the following sql to start, synchronise and complete the redefinition.

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('[schemaname]', '[source_objectname]', '[destination_objectname]' );
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('[schemaname]', '[source_objectname]', '[destination_objectname]' );
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('[schemaname]', 'source_objectname]', '[destination_objectname]' );

5) check the table is changed as required.