oracle date 去掉斜线,[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