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

[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个字符,便于目前工具的使用以及维护的需要.