[20180329]刪除帶斜線的索引.txt

[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