ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option Cause: An option other than INITRANS, MAXTRANS,or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW statement.
Action: Specify only legal options. ENABLE和DISABLE只针对函数索引。ENABLE applies only to a function-based index that has been disabled because a user-defined function used by the index was dropped or replaced. This clause enables such an index if these conditions are true: * The function is currently valid * The signature of the current function matches the signature of the function when the index was created * The function is currently ed as DETERMINISTIC Restriction on Enabling Function-based Indexes You cannot specify any other clauses of ALTER INDEX in the same statement with ENABLE.DISABLE ClauseDISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.楼主试试： alter index xx unusable;UNUSABLE Clause Specify UNUSABLE to the index or index partition(s) or index subpartition(s) UNUSABLE. An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is ed UNUSABLE, the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it.20分就是20分. 不要写100分.
因为索引上有相应的约束存在 SQL> create table t1(id number primary key,name varchar2(20));Table created.SQL> select CONSTRAINT_NAME, TABLE_NAME, INDEX_NAME 2 from user_constraints where table_name = 'T1';CONSTRAINT_NAME TABLE_NAME INDEX_NAME -------------------- -------------------- ------------------------------ SYS_C0022920 T1 SYS_C0022920SQL> alter index SYS_C0022920 disable; alter index SYS_C0022920 disable * ERROR at line 1: ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option SQL> alter table t1 drop constraint SYS_C0022920;Table altered.SQL> alter index SYS_C0022920 disable; alter index SYS_C0022920 disable * ERROR at line 1: ORA-01418: specified index does not exist SQL> select CONSTRAINT_NAME, TABLE_NAME, INDEX_NAME 2 from user_constraints where table_name = 'T1';no rows selected
to wh62592855： 我的索引不是出主键形成的。
create table ta(id int,name varchar2(10)); create index idx_ta on ta(id); --不能用disable禁用 alter index idx_ta disable; --需要用unusable禁用 alter index idx_ta unusable;
恩 就像上面朋友说的 ENABLE和DISABLE适用于FUNCTION-BASED INDEX 如果普通索引的话 你就用unusable 而不是disable
请问alter index idx_ta unusable; 后如何再启用索引
An unusable index must be rebuilt, or dropped and re-created, before it can be used.
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
Cause: An option other than INITRANS, MAXTRANS,or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW statement.
Action: Specify only legal options. ENABLE和DISABLE只针对函数索引。ENABLE applies only to a function-based index that has been disabled because a user-defined function used by the index was dropped or replaced. This clause enables such an index if these conditions are true: * The function is currently valid
* The signature of the current function matches the signature of the function when the index was created
* The function is currently ed as DETERMINISTIC
Restriction on Enabling Function-based Indexes
You cannot specify any other clauses of ALTER INDEX in the same statement with ENABLE.DISABLE ClauseDISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.楼主试试：
alter index xx unusable;UNUSABLE Clause Specify UNUSABLE to the index or index partition(s) or index subpartition(s) UNUSABLE. An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is ed UNUSABLE, the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it.20分就是20分. 不要写100分.
SQL> create table t1(id number primary key,name varchar2(20));Table created.SQL> select CONSTRAINT_NAME, TABLE_NAME, INDEX_NAME
2 from user_constraints where table_name = 'T1';CONSTRAINT_NAME TABLE_NAME INDEX_NAME
-------------------- -------------------- ------------------------------
SYS_C0022920 T1 SYS_C0022920SQL> alter index SYS_C0022920 disable;
alter index SYS_C0022920 disable
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
SQL> alter table t1 drop constraint SYS_C0022920;Table altered.SQL> alter index SYS_C0022920 disable;
alter index SYS_C0022920 disable
*
ERROR at line 1:
ORA-01418: specified index does not exist
SQL> select CONSTRAINT_NAME, TABLE_NAME, INDEX_NAME
2 from user_constraints where table_name = 'T1';no rows selected
我的索引不是出主键形成的。
create index idx_ta on ta(id);
--不能用disable禁用
alter index idx_ta disable;
--需要用unusable禁用
alter index idx_ta unusable;
ENABLE和DISABLE适用于FUNCTION-BASED INDEX
如果普通索引的话
你就用unusable 而不是disable
后如何再启用索引
如果你已经把一个索引置为unusable了
那么只能通过如下两种方法把它再次变为"usabel"1
重建
2
删除 重新建一个索引