[20180329]删除带斜线的索引.txt
--//上午看链接:http://www.itpub.net/thread-2100824-1-1.html
--//开始没仔细看,实际上已经恢复了,索引名带斜线,看看如何删除.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table t (id number,pad varchar2(200));
alter table t pctfree 0;
create unique index pk_t on t(id);
alter table t add constraint pk_t primary key (id) enable validate;
insert into t(id) select rownum from dual connect by level<=734;
commit ;
drop table t;
flashback table t;
SCOTT@book> flashback table t to before drop ;
Flashback complete.
SCOTT@book> select index_name,table_name from user_ind_columns;
INDEX_NAME TABLE_NAME
------------------------------ ----------
BIN$aINvQyb0cR3gU05kqMD44Q/=$0 T
PK_DEPT DEPT
PK_EMP EMP
SCOTT@book> alter index "SCOTT"."BIN$aIkFIu0geuTgU05kqMDjWw==$0" rename to "BIN/A";
Index altered.
--//这样建立一个带斜线的索引.看看如何删除.
2.测试:
SCOTT@book> drop index 'BIN/A';
drop index 'BIN/A'
*
ERROR at line 1:
ORA-00953: missing or invalid index name
SCOTT@book> drop index "BIN/A";
drop index "BIN/A"
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
--//注意看提示,这样已经正确了.但是因为建立的索引是主键.必须取消约束.
SCOTT@book> ALTER TABLE T MODIFY CONSTRAINT "BIN$aIkFIu0feuTgU05kqMDjWw==$0" DISABLE KEEP INDEX;
Table altered.
SCOTT@book> drop index "BIN/A";
Index dropped.
--//索引删除.使用双引号就ok了.实际上使用单引号是错误的.
SCOTT@book> create unique index pk_t on t(id);
Index created.
SCOTT@book> drop index 'PK_T';
drop index 'PK_T'
*
ERROR at line 1:
ORA-00953: missing or invalid index name
