Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Is renaming an index an online operation?

DoevelaarFeb 26 2014 — edited Feb 28 2014

Hello all,

I need to rename an index on a busy and large table and I was wondering if this would be an online operation: can users coninue work when I'm doing this? I can't find the answer in the docs.

Kind regards.

This post has been answered by Hemant K Chitale on Feb 26 2014
Jump to Answer

Comments

Sergei Krasnoslobodtsev

Altering Indexes

Renaming an Index

To rename an index, issue this statement:

ALTER INDEX index_name RENAME TO new_name; 

For example :

drop table test_tab;

create table test_tab (x int constraint test_tb_pk primary key );

select i.INDEX_NAME,i.TABLE_NAME from user_indexes i where i.TABLE_NAME = 'TEST_TAB';

alter index test_tb_pk rename to test_tab_pk;

select i.INDEX_NAME,i.TABLE_NAME from user_indexes i where i.TABLE_NAME = 'TEST_TAB';

SQL >

Table created

INDEX_NAME                     TABLE_NAME

------------------------------ ------------------------------

TEST_TB_PK                     TEST_TAB

Index altered

INDEX_NAME                     TABLE_NAME

------------------------------ ------------------------------

TEST_TAB_PK                    TEST_TAB

ORACLE-BASE - Oracle9i Renaming columns and constraints

carajandb

Simple test with Oracle 11gR2:

update table set col1 = 'xxx';
(col1 is indexed and no commit)

alter index idxcol1 rename to newindex;

it works so I can assume that rename index is an online operation!

Martin Preiss

I don't see a reason why it should not be an online operation: it's just a small change in metadata. But I hope you did not use index hints containing the index name ...

Mohamed Houri

And I hope he didn't use a SPM baseline with that index name

http://hourim.wordpress.com/2014/01/02/sql-plan-management-what-matters-the-index-name-or-the-indexed-columns/

Best regards

Mohamed Houri

tvCa-Oracle

Keep in mind there are 2 levels : when the DBA says it's an online action, it means it can be done while the database is running in normal mode.

Real online is to be viewed from the application side : does the application continue to work normally - on that specific index - while such an action is going on.

Aman....

I don't think that uses would be in a hung state or something if you are going to rename the index .

Aman....

Hemant K Chitale
Answer

"online" means that the command can be issued.  Whether it will be successful depends on whether there is active DML

Demo :

Session 1:

SQL>create table hkc_test_10 (id_column number, data_col varchar2(15));

Table created.

SQL>create index hkc_test_10_ndx on hkc_test_10(id_column);

Index created.

SQL>insert into hkc_test_10
  2  select rownum, 'Row:' || to_char(rownum)
  3  from dual
  4  connect by level < 1000;

999 rows created.

SQL>

Session 2 when Session 1 hasn't committed :

AnotherSQLSessn>select index_name from user_indexes where table_name = 'HKC_TEST_10';

INDEX_NAME
------------------------------
HKC_TEST_10_NDX

1 row selected.

AnotherSQLSessn>alter index hkc_test_10_ndx rename to ndx_hkc_test_10;
alter index hkc_test_10_ndx rename to ndx_hkc_test_10
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


AnotherSQLSessn>

Hemant K Chitale

Marked as Answer by Doevelaar · Sep 27 2020
Girish Sharma

The example showed by Hemant tells us, that if there are active DML or pending transaction on the table then you can not drop/rename the index in another session, you can drop/rename in only that session who have started the transaction.  So, if you gets "Index altered." message in your session, it means there were not any active DML at the time of your drop/rename operation.

Regards

Girish Sharma

Hemant K Chitale

If you issue an ALTER in the same session as having the active DML, the first thing the ALTER does is that it issues a COMMIT.   So the transaction is committed, resulting in the DML being not active any more.  That is why the ALTER INDEX ... RENAME goes through -- because it causes a COMMIT of the active DML in the same session first.

*ALWAYS* be wary of the "side-effect" that DML in the same session is committed when you issue DDL.

Hemant K Chitale

Girish Sharma

Thank you Hemant for more clarification.

Regards

Girish Sharma

Martin Preiss

as shown by Hemant Chitale's demonstration the index rename operation:

  • will not block other transactions - so other users will not be disturbed
  • may be unsuccessfull (with ora-00054) if there are concurrent transactions doing DML on the table

On a table with much DML activity this could make the renaming operation quite difficult and afford a lot of attempts. But since 11.1 it is possible to use a session parameter ddl_lock_timeout to define a period in which the renaming session would wait for concurrent DML to complete before getting ora-00054: http://www.oracle-base.com/articles/11g/ddl-lock-timeout-11gr1.php.

Hemant K Chitale

Or a script written by Jonathan Lewis which catches the ORA-54 and retries after a sleep interval --- running continuously in a loop and exiting when the DDL (the ALTER) is successful.

Hemant K Chitale


Doevelaar

That is what I meant indeed: can the index be renamed when there is DDL going on on the table, and will the application continue to function normally during the rename.

Hoek

That would be this one:

DDL | Oracle Scratchpad

1 - 14
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 28 2014
Added on Feb 26 2014
14 comments
968 views