Converting LOB storage

If your AR System is currently using out-row storage, you can convert it to use in-row storage, and vice versa. See the following Oracle PL/SQL procedure to learn how to change the in-row or out-row option of all the large object (LOB) columns in an Oracle schema. Then, review the case studies that follow.

To change the storage option

1. From SQL*Plus or other tools, connect to the Oracle database server as the ARAdmin user.

2. Create the following PL/SQL p_change_LOB_storage procedure:

   create or replace procedure p_change_LOB_storage
   (
   p_in_row varchar2 default 'Yes',
   p_dest_tablespace varchar2 default 'ARSYSTEM',
   p_table_name varchar2 default '%',
   p_generate_SQL_only varchar2 default 'No'
   )
   as
   lv_block_size number;
   lv_in_row_clause varchar2(400);
   lv_chg_to_inrow varchar2(3) default 'NO';
   lv_chg_to_outrow varchar2(3) default 'NO';
   lv_no_inrow_outrow_chg varchar2(3) default 'NO';
   lv_sql_statement varchar2(4000 byte) default '';
   BEGIN
   -- check specified in_row option
   IF p_in_row is not null and upper(p_in_row) not in ('YES','NO','Y','N') then
   raise_application_error(-20001,'The first parameter(p_in_row) must be Yes,No or Null.');
   END IF;
   
   -- check p_generate_SQL_only
   IF upper(p_generate_SQL_only) not in ('YES','NO') then
    raise_application_error(-20001,'The parameter p_generate_SQL_only must be Yes or No.');
   END IF;
   
   -- three cases:
   select case when upper(p_in_row) in ('YES' ,'Y') then 'YES' else 'NO' end chg_to_inrow,
   case when upper(p_in_row) in ('NO' ,'N') then 'YES' else 'NO' end  chg_to_outrow,
   case when p_in_row is NULL then 'YES' else 'NO' end no_inrow_outrow_chg
   into lv_chg_to_inrow,
       lv_chg_to_outrow ,
       lv_no_inrow_outrow_chg
   from dual;
   
   
   lv_in_row_clause :='';
   select case when lv_chg_to_inrow='YES' then 'enable storage in row'
              when lv_chg_to_outrow ='YES' then 'disable storage in row'
              when lv_no_inrow_outrow_chg='YES' then ''
              else 'error:unknown cases'
         end in_row_clause
   into lv_in_row_clause
   from dual;
   
   
   -- get the block size of destination tablespace
   BEGIN
   select block_size into lv_block_size
   from user_tablespaces where TABLESPACE_NAME=p_dest_tablespace;
   EXCEPTION
   WHEN NO_DATA_FOUND THEN
   raise_application_error(-20002,'The tablespace '||p_dest_tablespace||'does not exist.');
   WHEN OTHERS THEN
   raise;
   END;
   -- use FOR loop and SQL to generate 'Alter table ... move LOB ...'SQL command
   FOR r IN (        
          select distinct l.TABLE_NAME,
          case when 'YES'= (select logging from user_tables t where t.table_name=l.table_name )
          then
              'Alter table '||l.table_name||' logging'
              else
              null
              end alt_logging_cmd
          from user_lobs l join user_tables t on l.table_name = t.table_name
          where l.table_name like p_table_name
            and (
            l.in_row in ( case when lv_chg_to_inrow='YES' then 'NO' else '' end,
                          case when lv_chg_to_outrow='YES' then 'YES' else '' end)                      
            or l.tablespace_name <> p_dest_tablespace
            )
   )
   LOOP
        lv_sql_statement := 'alter table '||r.table_name||' move ';
        for r_lob in (  
              select ' lob ('||column_name
                ||') store as (chunk '||to_char(lv_block_size)
                ||' tablespace '||p_dest_tablespace
                ||' '|| lv_in_row_clause ||' ) '
                mv_lob_clause
                from user_lobs l join user_tables t on l.table_name = t.table_name
                where l.table_name =r.table_name
                  and (
                  l.in_row in ( case when lv_chg_to_inrow='YES' then 'NO' else '' end,
                                case when lv_chg_to_outrow='YES' then 'YES' else '' end)                      
                  or l.tablespace_name <> p_dest_tablespace  
                      )
                  )
            LOOP   
            lv_sql_statement :=lv_sql_statement ||r_lob.mv_lob_clause;
            END loop;
      lv_sql_statement := lv_sql_statement ||' nologging';
      dbms_output.put_line (lv_sql_statement);
      if r.alt_logging_cmd is not null then
          dbms_output.put_line (r.alt_logging_cmd);
      end if;
            
    if upper(p_generate_SQL_only) <>'YES' THEN
      -- execute SQL
        execute immediate lv_sql_statement;
        if r.alt_logging_cmd is not null then
            execute immediate r.alt_logging_cmd;
        end if;
    end if;
    
    --
    IF upper(p_generate_SQL_only) ='YES' THEN
        FOR r1 IN (
          select 'Alter index '||index_name ||' rebuild nologging' sqlCmd,
          case when ind.logging='YES' then
              'Alter index '||ind.index_name||' logging'
              else null
              end alt_logging_cmd
          from user_indexes ind where table_name = r.table_name and index_type <>'LOB'
          )
          LOOP
            dbms_output.put_line (r1.sqlCmd);
            if r1.alt_logging_cmd is not null then
                dbms_output.put_line (r1.alt_logging_cmd);
            end if;
          END LOOP;
     END IF;
    
    IF upper(p_generate_SQL_only) <>'YES' THEN
          FOR r1 IN (
            select 'Alter index '||index_name ||' rebuild nologging' sqlCmd,
            case when ind.logging='YES' then
              'Alter index '||ind.index_name||' logging'
              else null
              end alt_logging_cmd
            from user_indexes ind
            where table_name = r.table_name and status = 'UNUSABLE'
            )
            LOOP
              dbms_output.put_line (r1.sqlCmd);
              execute immediate r1.sqlCmd;
              if r1.alt_logging_cmd is not null then
                dbms_output.put_line (r1.alt_logging_cmd);
                execute immediate r1.alt_logging_cmd;
              end if;
              
            END LOOP;
    END IF;
    
   END LOOP;
   END;
   /
   
3. Execute the p_change_LOB_storage procedure with appropriate parameter values.
    The storage option is changed.

Case 1—Applying changes to all LOBs

To apply changes to all LOBs, execute the procedure as specified in the following table.

Case 2—Applying changes to LOBs only in a specified table

To apply changes to LOBs only in specified tables, execute the procedure as specified in the following table.

Case 3—Displaying SQL statements only

The p_change_LOB_storage stored procedure runs SQL statements to apply changes to large objects (LOBs). To display those SQL statements without applying changes to the LOBs, execute the procedure as specified in the following table.