Converting LOB storage

If your BMC Remedy AR System is currently using out-row storage, you can convert it to use in-row storage, and vice versa. The following Oracle PL/SQL procedure changes the in-row or out-row option of all the large object (LOB) columns in an Oracle schema.

The following case studies describe how to convert LOB storage:

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.