Hi,

I am trying to change the name of an index using DBMS_METADATA ,but it seemingly does not works on the index name. Please let me know if I am doing something wrong.

Best Regards,
Manas


drop table a;

create table a (a clob);

drop table testmeonce;

create table testmeonce ( a number, b varchar2(10), constraint cns_1 unique (a) USING INDEX (create index idx_testmeonce on testmeonce (a)));

DECLARE
v_transform_handle NUMBER;
v_handle NUMBER;
v_table_query CLOB;
BEGIN
v_handle := DBMS_METADATA.OPEN(object_type => 'TABLE' );

DBMS_METADATA.SET_FILTER( v_handle
, 'SCHEMA'
, USER);

DBMS_METADATA.SET_FILTER( v_handle
, 'NAME'
, 'TESTMEONCE');

v_transform_handle := DBMS_METADATA.ADD_TRANSFORM( v_handle
, 'MODIFY');

DBMS_METADATA.SET_REMAP_PARAM( v_transform_handle
, 'REMAP_NAME'
, 'IDX_TESTMEONCE'
, 'CHANGED123');

v_table_query := DBMS_METADATA.FETCH_CLOB(v_handle);

insert into a values (v_table_query);
commit;

END;
/


The index name is not changed in my local environment. It remains as IDX_TESTMEONCE.

I can make changes using modification of code manually in Pl/sql but prefer if it can be done through dbms_metadata.

alter index idx_testmeonce rename to changed123;

create table a (a clob);
create table testmeonce ( 
  a number, b varchar2(10), 
  constraint cns_1 unique (a) USING INDEX (
    create index idx_testmeonce on testmeonce (a)
  )
);

DECLARE

v_transform_handle NUMBER;
v_handle NUMBER;
v_table_query CLOB;

BEGIN
  
v_handle := DBMS_METADATA.OPEN(object_type => 'INDEX' );

DBMS_METADATA.SET_FILTER( v_handle
, 'SCHEMA'
, USER);

DBMS_METADATA.SET_FILTER( v_handle
, 'NAME'
, 'IDX_TESTMEONCE');

v_transform_handle := DBMS_METADATA.ADD_TRANSFORM( v_handle
, 'MODIFY');

DBMS_METADATA.SET_REMAP_PARAM( v_transform_handle
, 'REMAP_NAME'
, 'IDX_TESTMEONCE'
, 'CHANGED123');

v_table_query := DBMS_METADATA.FETCH_CLOB(v_handle);
dbms_metadata.close(v_handle);
insert into a values (v_table_query);

commit; 

END;
/

select extractvalue(xmltype(a), '/ROWSET/ROW/INDEX_T/SCHEMA_OBJ/NAME') 
from   a;

EXTRACTVALUE(XMLTYPE(A),'/ROWSET/ROW/INDEX_T/SCHEMA_OBJ/NAME')  
CHANGED123