[20191219]索引名裡帶回車符.txt

[20191219]索引名裡帶回車符.txt

--//連結http://www.itpub.net/thread-2123029-1-1.html提到的問題,索引名裡帶回車符,自己測試一下,如何改名:

1.環境:
SCOTT@book> @ 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

SCOTT@book> create table t as select rownum id ,lpad('x',100,'x') name ,lpad('1',1) flag from dual connect by level<=1e5;
Table created.

2.首先遇到的問題,怎麼建立1個索引名裡面帶回車符:

--//在toad下輸入如下:
 create index "i_t_
 id" on t(id);

--//實際上在sqlplus也可以:
SCOTT@book> create index "i_t_
  2  name " on t(name);
Index created.

SCOTT@book> select dump(index_name,16) c50 ,index_name,index_type,blevel,leaf_blocks,distinct_keys from dba_indexes where owner=user and table_name='T';
C50                                        INDEX_NAME INDEX_TYPE BLEVEL LEAF_BLOCKS DISTINCT_KEYS
------------------------------------------ ---------- ---------- ------ ----------- -------------
Typ=1 Len=8: 69,5f,74,5f,a,20,69,64        i_t_       NORMAL          1         222        100000
                                            id

Typ=1 Len=10: 69,5f,74,5f,a,6e,61,6d,65,20 i_t_       NORMAL          2        1563             1
                                           name



--//確定使用instr(index_name,chr(10))>0.
SCOTT@book> select dump(index_name,16) c50 ,index_name,index_type,blevel,leaf_blocks,distinct_keys from dba_indexes where owner=user  and instr(index_name,chr(10))>0;
C50                                        INDEX_NAME INDEX_TYPE BLEVEL LEAF_BLOCKS DISTINCT_KEYS
------------------------------------------ ---------- ---------- ------ ----------- -------------
Typ=1 Len=8: 69,5f,74,5f,a,20,69,64        i_t_       NORMAL          1         222        100000
                                            id

Typ=1 Len=10: 69,5f,74,5f,a,6e,61,6d,65,20 i_t_       NORMAL          2        1563             1
                                           name

3.改名:
--//如果不多,直接在toad下修改,跟蹤實際上執行如下:
alter index "SCOTT"."i_t_
 id" rename to I_T_ID

--//實際上如果不多解決並不太複雜.