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

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

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;
/

Execute the p_change_LOB_storage procedure with appropriate parameter values.
The storage option is changed.

Converting LOB storage

To change the storage option

Comments