OraFAQ Forum: SQL & PL/SQL » ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations [message #623896]

Tue, 16 September 2014 01:42

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi all,

I want to know create 2 index's on the same columns for that..

CREATE INDEX emp_empname ON  emp(empno,ename);

--drop index emp_empname

ALTER INDEX emp_empname INVISIBLE;

CREATE BITMAP INDEX emp_deptno  ON emp(empno,ename);

At ALTER getting below eror..

ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations

Report message to a moderator

Re: ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations [message #623898 is a reply to message #623896]

Tue, 16 September 2014 01:59

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Version (4 decimals)?

This works for me:

SQL> CREATE INDEX emp_empname ON  emp(empno,ename);

Index created.

SQL> ALTER INDEX emp_empname INVISIBLE;

Index altered.

Report message to a moderator

Re: ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations [message #623899 is a reply to message #623896]

Tue, 16 September 2014 02:00

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

So what's not clear with the error message?

Edit : Saw Michel's reply, I think OP is doing something else too, which he has not posted. I may be wrong. But it work for me too :

SQL> SELECT BANNER FROM V$VERSION WHERE ROWNUM = 1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL>
SQL> drop table emp1;

Table dropped.

SQL> CREATE TABLE EMP1 AS SELECT * FROM EMP;

Table created.

SQL>
SQL> CREATE INDEX emp_empname ON  emp1(empno,ename);

Index created.

SQL>
SQL> ALTER INDEX emp_empname INVISIBLE;

Index altered.

SQL>
SQL> CREATE BITMAP INDEX emp_deptno  ON emp1(empno,ename);

Index created.

[Updated on: Tue, 16 September 2014 02:20]

Report message to a moderator

Re: ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations [message #623902 is a reply to message #623899]

Tue, 16 September 2014 02:38

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

It is the Vision Version

Report message to a moderator

Re: ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations [message #623903 is a reply to message #623902]

Tue, 16 September 2014 02:47

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Did you execute in SQL*Plus or TOAD? As I found this over web.

Report message to a moderator

Re: ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations [message #623911 is a reply to message #623903]

Tue, 16 September 2014 03:15

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi Lalit,
Now i am getting ORA-01408: such column list already indexed when i was creating BITMAP INDEX on the same columns

SQL> drop index emp_empname
  2  /

Index dropped.

SQL> commit;

Commit complete.

SQL>  CREATE INDEX emp_empname ON  emp(empno,ename);

Index created.

SQL> ALTER INDEX emp_empname INVISIBLE;

Index altered.

SQL> CREATE BITMAP INDEX emp_deptno  ON emp(empno,ename);
CREATE BITMAP INDEX emp_deptno  ON emp(empno,ename)
                                       *
ERROR at line 1:
ORA-01408: such column list already indexed


SQL> SELECT BANNER FROM V$VERSION WHERE ROWNUM = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

SQL>

Thanks

Report message to a moderator

Re: ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations [message #623913 is a reply to message #623911]

Tue, 16 September 2014 03:45

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What was the root of the first error?

What don't you understand in "such column list already indexed" isn't what you did 2 statements before?

Report message to a moderator

Re: ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations [message #623914 is a reply to message #623911]

Tue, 16 September 2014 03:51

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

mist598 wrote on Tue, 16 September 2014 13:45

SQL> ALTER INDEX emp_empname INVISIBLE;

Index altered.

So was the culprit TOAD the previous time you got the error against ALTER index statement?

Report message to a moderator

Re: ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations [message #623917 is a reply to message #623914]

Tue, 16 September 2014 04:26

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Quote:

So was the culprit TOAD the previous time you got the error against ALTER index statement?

Yes.

And next time I did in SQL*PLUS got the below error..(creating the BITMAP INDEX on same column's)

SQL> CREATE BITMAP INDEX emp_deptno  ON emp(empno,ename);
CREATE BITMAP INDEX emp_deptno  ON emp(empno,ename)
                                       *
ERROR at line 1:
ORA-01408: such column list already indexed

Quote:

What don't you understand in "such column list already indexed" isn't what you did 2 statements before?

I am trying to do creating Index on same columns..
source from http://docs.oracle.com/database/121/ADMIN/indexes.htm#ADMIN11732

[Updated on: Tue, 16 September 2014 04:27]

Report message to a moderator

Re: ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations [message #623919 is a reply to message #623917]

Tue, 16 September 2014 04:35

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You are referring a 12c documentation but have a 11.1 version.
Read the documentation for your version.

Quote:

I am trying to do creating Index on same columns..

You can't in your version.

Report message to a moderator

Re: ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations [message #623954 is a reply to message #623917]

Tue, 16 September 2014 10:47

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

mist598 wrote on Tue, 16 September 2014 14:56

Quote:
So was the culprit TOAD the previous time you got the error against ALTER index statement?

Yes.

So it is another instance where 3rd party client tool failed and made us aware of the consequence.

Report message to a moderator

Re: ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations [message #623956 is a reply to message #623954]

Tue, 16 September 2014 11:23

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes, I always say, TOAD is b.......

Report message to a moderator

Previous Topic:	Oracle doesn't use a partial index
Next Topic:	Cursor Problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 22:00:59 CDT 2024