[20221010]使用toad管理索引改名问题.txt

--//今天在优化sql语句时发现的问题.做一个记录.

1.环境:
SYS@192.168.100.235:1521/orcl> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.问题:
SYS@192.168.100.235:1521/orcl> @ 6q67qzn442k68.sql9_0
PL/SQL procedure successfully completed.
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  aac9ayg7mv1x5, child number 0
-------------------------------------
select item_id from com_item_inst where Allow_Null=1 and
inst_id=:inst_id
Plan hash value: 4227739359
-------------------------------------------------------------------------------------------------------------------------------------------------
|Id|Operation                           |Name                           |Starts|E-Rows|E-Bytes|Cost (%CPU)|E-Time  |A-Rows|   A-Time   |Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT                    |                               |     1|      |       |   81 (100)|        |     0|00:00:00.01 |     72 |
|*1| TABLE ACCESS BY INDEX ROWID BATCHED|COM_ITEM_INST                  |     1|     2|    24 |   81   (0)|00:00:01|     0|00:00:00.01 |     72 |
|*2|  INDEX RANGE SCAN                  |I_COM_ITEM_INST_INST_ID_ITEM_ID|     1|    97|       |    2   (0)|00:00:01|    97|00:00:00.01 |      2 |
-------------------------------------------------------------------------------------------------------------------------------------------------
--//当我在toad界面查看索引时发现看到的索引名字为I_COM_ITEM_INST_INST_ID_ITEM_I.很明显,toad看到的是错误的.
--//12c支持更长字符的索引名以及表名,最大128个字符.
--//I_COM_ITEM_INST_INST_ID_ITEM_I 正好30个字符. 很明显我使用toad版本可能有点低(12.6.0.53),仅仅显示30个字符.
--//实际上在toad下建立索引名字超过30个字符是没有问题的,但是显示仅仅30个字符.
--//补充说明:我的测试在toad修改索引大于30个字符也是没有问题的.

SYS@192.168.100.235:1521/orcl> select index_name from dba_indexes where length(index_name)>30;
INDEX_NAME
-------------------------------
I_COM_ITEM_INST_INST_ID_ITEM_ID

--//由于这个原因,修改索引名字长度(大于30个字符)在toad下也是不行的,因为toad界面下仅仅看到前面30个字符,只能在sqlplus下修改.

SYS@192.168.100.235:1521/orcl> rename lis.I_COM_ITEM_INST_INST_ID_ITEM_ID to lis.I_COM_ITEM_INST_INST_ID_ITEM_I;
rename lis.I_COM_ITEM_INST_INST_ID_ITEM_ID to lis.I_COM_ITEM_INST_INST_ID_ITEM_I
       *
ERROR at line 1:
ORA-01765: specifying owner's name of the table is not allowed

SYS@192.168.100.235:1521/orcl> @ cs lis
alter session set current_schema=lis
Session altered.

SYS@192.168.100.235:1521/orcl> rename I_COM_ITEM_INST_INST_ID_ITEM_ID to I_COM_ITEM_INST_INST_ID_ITEM_I;
rename I_COM_ITEM_INST_INST_ID_ITEM_ID to I_COM_ITEM_INST_INST_ID_ITEM_I
*
ERROR at line 1:
ORA-04043: object I_COM_ITEM_INST_INST_ID_ITEM_ID does not exist

SYS@192.168.100.235:1521/orcl> alter index I_COM_ITEM_INST_INST_ID_ITEM_ID rename to I_COM_ITEM_INST_INST_ID_ITEM_I;
Index altered.

SYS@192.168.100.235:1521/orcl> @ cs sys
alter session set current_schema=sys
Session altered.

--//另外我发现在toad下改名超过30个字符是可以的,但是反过来无法修改回来.除非采用上面的命令方式.
--//这给维护运维提供一个注意,建立索引以及表做好还是不要超过30个字符,便于目前工具的使用以及维护的需要.