Practical Tips For Moving Indexes Between Tablespaces

From time to time, a simple request to reorganize space in a tablespace leads to a more complex solution. Here’s how we moved  indexes between tablespaces.

A client requested we reduce the size of an Automatic Storage Management (ASM) disk group which contained data files for an 11g database. The goal was to free up a number of disks and remove them from ASM. The tablespace in question contained indexes only. This tablespace was pretty big with lots of the segments at the end of each datafile, which meant that we could not simply shrink the data files. 

The obvious solution was to rebuild indexes to another tablespace. But the specificity of the request was that no new tablespaces could be created in this database. The client requested that we rebuild the indexes in a particular tablespace located in another disk group. Another challenge was that both the source and the target tablespace had a different block size (32K) to the database default (8K). When there are differing block sizes, in some situations we can get this type of error:

ORA-14520: Tablespace <TARGET_TABLESPACE> block size [32768] does not match existing object block size [8192]

The ORA-14520 is explained as:

Cause: A Data Definition Language (DDL )statement was issued that would require a tablespace of a block size different from the block size of the specified partitioned object to be assigned either: (1) As the object’s default tablespace (or one of the object’s partition-level default tablespaces, if composite partitioning is being used) OR (2) To one of the object’s partitions/subpartitions.

Solution: Specify a tablespace of the same block size as the partitioned object.

In this case, the approach was to move non-partitioned indexes as-is, using ‘alter index <index_name> rebuild tablespace <target_tablespace> [online]’. However, partitioned indexes are more complex objects and can only be rebuilt partition by partition.

So, how would you fix this?

There are  a number of ways to get a DDL command of an object in a database. For example, it can be done via expdp/impdp utilities. First, use expdp to get a datapump of index metadata, like this:

expdp dumpfile=DIRECTORY:ind_exp_file.dmp logfile=DIRECTORY:ind_exp.log schemas=SCHEMA_NAME CONTENT=METADATA_ONLY EXCLUDE=STATISTICS INCLUDE=INDEX:\"IN \(\'condition\')\"

Then, use impdp with the parameter SQLFILE to get a set of commands, for example:

impdp dumpfile=DIRECTORY:ind_exp_file.dmp logfile=DIRECTORY:ind_exp_imp.log SQLFILE=DIRECTORY:index_ddl.sql

The output can be like below:

-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
-- CONNECT SCHEMA_NAME
CREATE UNIQUE INDEX "SCHEMA_NAME"."INDEX_NAME" ON "SCHEMA_NAME"."TABLE_NAME" ("COL1", "COL2", "COL3", "COL4")
  PCTFREE 0 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE( MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOURCE_TABLESPACE"  LOCAL
 (PARTITION "P201711"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOURCE_TABLESPACE" ,
 PARTITION "P201712"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOURCE_TABLESPACE" ,
 PARTITION "P201801"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOURCE_TABLESPACE" ,
 PARTITION "P201802"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOURCE_TABLESPACE" ,
 PARTITION "P201803"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOURCE_TABLESPACE" ,
 PARTITION "P201804"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOURCE_TABLESPACE" ,
 PARTITION "P201805"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOURCE_TABLESPACE" ,
 PARTITION "P201806"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOURCE_TABLESPACE" ,
 PARTITION "PMAXVALUE"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOURCE_TABLESPACE" ) PARALLEL 1 ;

  ALTER INDEX "SCHEMA_NAME"."INDEX_NAME" NOPARALLEL;

There’s a lot of editing required in the resulting SQL file; unnecessary syntax entries need to be removed and the source tablespace name needs to be replaced with the target one, along with other tasks.

Another approach is to use DBMS_METADATA package as a powerful set of instruments to retrieve DDL-related information about database objects. There are several articles on  how to get a proper output, however I didn’t find a good summarized solution for my particular case.

The DBMS_METADATA package has a number of useful procedures, for example, SET_TRANSFORM_PARAM which helps to get a better formatting of the output. The most useful are:

DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', false); -- exclude segment attributes clauses from the DDL.
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', false); -- remove storage clauses in the DDL.
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'TABLESPACE', true); -- include tablespace clauses in the DDL (Ignored if SEGMENT_ATTRIBUTES is FALSE).
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); -- If TRUE, append a SQL terminator (; or /) to each DDL statement.
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true); -- format the output with indentation and line feeds.

And for remapping tablespaces on the fly:

DBMS_METADATA.set_remap_param (DBMS_METADATA.session_transform, 'REMAP_TABLESPACE', <source_tablespace>,<target_tablespace>);

However, the above-mentioned directive requires more steps by using DBMS_METADATA.ADD_TRANSFORM as shown below. Keeping the above in mind, the following script can be used to retrieve the DDL commands for a specific schema and a source tablespace as it limits the size of indexes. It can be useful for situations where you’re dealing with a very large volume of indexes. Therefore, we can divide our task into chunks:

cat index_recreate.sql

set trimspool off
set linesize 300
set long 20000000
set pagesize 0
column ddl format a500

set serveroutput on size unlimited

spool objects_ddl.lst

undef ts_owner
accept ts_owner char prompt 'Enter owner name: '
undef ts_name
accept ts_name char prompt 'Enter tablespace name where indexes are located: '
undef ts_name_target
accept ts_name_target char prompt 'Enter tablespace name where indexes should be moved to: '
undef obj_size
accept obj_size char prompt 'Enter maximum size of an object, Mb: '

declare
cursor c1 is
    select * from (
         select owner, segment_name, segment_type, round(sum(bytes/1024/1024),2) sum_size
                from dba_segments where TABLESPACE_NAME='&ts_name' and owner='&ts_owner'
                and SEGMENT_TYPE like 'INDEX%'
                group by owner, segment_name, segment_type order by 4 desc)
         where sum_size<&obj_size;
v_ddl clob;
hndl number;
th number;

 procedure print_clob( p_clob in clob ) is
  v_offset number default 1;
  v_chunk_size number := 10000;
   begin
    loop
      exit when v_offset > dbms_lob.getlength(p_clob);
      dbms_output.put_line( dbms_lob.substr( p_clob, v_chunk_size, v_offset ));
      v_offset := v_offset+v_chunk_size;
    end loop;
 end print_clob;

begin


for i in c1
loop
  dbms_output.put_line('drop index '||i.owner||'.'||i.segment_name||';');

  hndl:=DBMS_METADATA.OPEN('INDEX');
  DBMS_METADATA.SET_FILTER(hndl,'SCHEMA',i.owner);
  DBMS_METADATA.SET_FILTER(hndl,'NAME',i.segment_name);

  th:=DBMS_METADATA.ADD_TRANSFORM(hndl,'MODIFY');
  DBMS_METADATA.set_remap_param(th,'REMAP_TABLESPACE','&ts_name','&ts_name_target');

  th:= DBMS_METADATA.ADD_TRANSFORM(hndl,'DDL');
  DBMS_METADATA.set_transform_param(th,'STORAGE', false);
  DBMS_METADATA.set_transform_param(th,'TABLESPACE', true);
  DBMS_METADATA.set_transform_param(th,'PRETTY', true);
  DBMS_METADATA.set_transform_param(th,'SQLTERMINATOR', true);

  v_ddl:=dbms_metadata.FETCH_CLOB(hndl);
  print_clob(v_ddl);
  dbms_metadata.CLOSE (hndl);
end loop;
end;
/

spool off

The output we get now looks more compact:

drop index SCHEMA_NAME.INDEX_NAME;                                                                                                                                                                                                          

  CREATE UNIQUE INDEX "SCHEMA_NAME"."INDEX_NAME" ON "SCHEMA_NAME"."TABLE_NAME" ("COL1", "COL2", "COL3", "COL4")
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  TABLESPACE "TARGET_TABLESPACE"  LOCAL
 (PARTITION "P201711"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
TABLESPACE "TARGET_TABLESPACE" ,
 PARTITION "P201712"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  TABLESPACE "TARGET_TABLESPACE" ,
 PARTITION "P201801"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  TABLESPACE "TARGET_TABLESPACE" ,
 PARTITION "P201802"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  TABLESPACE "TARGET_TABLESPACE" ,
 PARTITION "P201803"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  TABLESPACE "TARGET_TABLESPACE" ,
 PARTITION "P201804"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  TABLESPACE "TARGET_TABLESPACE" ,
 PARTITION "P201805"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  TABLESPACE "TARGET_TABLESPACE" ,
 PARTITION "P201806"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  TABLESPACE "TARGET_TABLESPACE" ,
 PARTITION "PMAXVALUE"
  PCTFREE 0 INITRANS 2 MAXTRANS 255 NOLOGGING
  TABLESPACE "TARGET_TABLESPACE" ) ;

The only remaining step is to check and fix any syntax/formatting issues related to how the nested ‘print_clob’ procedure works.

We may get the following error when these indexes are in use as we’re rebuilding them:

ORA-14024: number of partitions of LOCAL index must equal that of the underlying table

If you see this,  the particular index should be created with no explicitly specified list of partitions, or the missing partition can be added to the index creation command and rerun.

As you can see, the script contains a number of changes that affect the output of the syntax and change the storage settings to the new characteristics. It can be extended to include the ability to extract specific objects types as required in different scenarios.

I hope you find this post useful. Feel free to leave questions in the comments and don’t forget to sign up for the next post.