This documentation supports the 9.0 version of Remedy Action Request System.

To view the latest version, select the version from the Product version menu.

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.
Was this page helpful? Yes No Submitting... Thank you

Comments