Error: Invalid spaceKey on retrieving a related space config.

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