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!
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.
Altering Indexes
To rename an index, issue this statement: ALTER INDEX index_name RENAME TO new_name;
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
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;
SQL >
Table created
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
TEST_TB_PK TEST_TAB
Index altered
TEST_TAB_PK TEST_TAB
ORACLE-BASE - Oracle9i Renaming columns and constraints
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!
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 ...
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
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.
I don't think that uses would be in a hung state or something if you are going to rename the index .
Aman....
"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
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
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.
Thank you Hemant for more clarification.
as shown by Hemant Chitale's demonstration the index rename operation:
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.
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.
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.
That would be this one:
DDL | Oracle Scratchpad