[20210506]]关于ORA-01450.txt

[20210506]]关于ORA-01450.txt --//参考链接： https://jonathanlewis.wordpress.com/2009/06/05/online-rebuild/ https://jonathanlewis.wordpress.com/2013/06/28/index-rebuild-12c/ --//按照链接https://jonathanlewis.wordpress.com/2013/06/28/index-rebuild-12c/介绍： Just one of those little snippets about 12c that might help someone. Further to an earlier post, online rebuild works in 12c even when the key is "too long". The internal code has changed completely, and there is no sign of the problematic journal table that caused the problem in earlier versions. --//视乎12c改变一些代码，导致下面的情况不会在出现ORA-01450错误。 $ oerr ora 01450 01450, 00000, "maximum key length (%s) exceeded" // *Cause: // *Action: 1.环境： TTT@XXXX> @ ver1 TTT@XXXX> @ prxx ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 18.0.0.0.0 BANNER                        : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 BANNER_LEGACY                 : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. TTT@XXXX> create table t1( v1  varchar2(4000), v2  varchar2(2387), v3  varchar2(100)) ; Table created. TTT@XXXX> create index i_t1_v1_v2 on t1(v1, v2); Index created. TTT@XXXX> alter index i_t1_v1_v2 rebuild; Index altered. TTT@XXXX> alter index i_t1_v1_v2 rebuild online; Index altered. TTT@XXXX> drop index i_t1_v1_v2; Index dropped. --//OK没有这样的问题。如果以sys用户登录重复相同的操作： SYS@XXXX> create table t1( v1  varchar2(4000), v2  varchar2(2387), v3  varchar2(100)) ; Table created. SYS@XXXX> create index i_t1_v1_v2 on t1(v1, v2); Index created. SYS@XXXX> alter index i_t1_v1_v2 rebuild; Index altered. SYS@XXXX> alter index i_t1_v1_v2 rebuild online; alter index i_t1_v1_v2 rebuild online * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01450: maximum key length (3215) exceeded --//出现ORA-01450错误,无法解析。 --//主要问题出在一些开发胡乱定义字段类型长度，导致在维护中有可能出现ORA-01450错误。 SYS@XXXX> drop index i_t1_v1_v2; Index dropped. SYS@XXXX> create index i_t1_v1_v2 on t1(v1, v2) tablespace users; Index created. SYS@XXXX> alter index i_t1_v1_v2 rebuild; Index altered. SYS@XXXX> alter index i_t1_v1_v2 rebuild online; alter index i_t1_v1_v2 rebuild online * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01450: maximum key length (3215) exceeded --//一样出现，与表空间类型无关。 3.如果你在11g上重复前面的操作，普通用户下不会出现ORA-01450错误。 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 create table t1( v1  varchar2(4000), v2  varchar2(2387), v3  varchar2(100)) ; create index i_t1_v1_v2 on t1(v1, v2); SCOTT@book> alter index i_t1_v1_v2 rebuild; Index altered. SCOTT@book> alter index i_t1_v1_v2 rebuild online; Index altered. --//ok. SCOTT@book> drop table t1 purge ; Table dropped. SCOTT@book> create table t1( v1  varchar2(4000), v2  varchar2(2388), v3  varchar2(100)) ; Table created. SCOTT@book> create index i_t1_v1_v2 on t1(v1, v2); create index i_t1_v1_v2 on t1(v1, v2)                            * ERROR at line 1: ORA-01450: maximum key length (6398) exceeded --//参考链接https://jonathanlewis.wordpress.com/2009/06/05/online-rebuild/ My key value is at the limit for an 8KB block size in Oracle 9i and later – which is roughly 80% of (block size – 190 bytes). In earlier versions of Oracle (prior to 9i) the limit was roughly half that (i.e. 40% rather than 80%). If you try to create a longer key you'll see Oracle error ORA-01450: "maximum key length (6398) exceeded".  (If you've built your indexes using a different blocksize the number in brackets will be different – and you will have to adjust the demo code for 16KB and 32KB block sizes). The difference between my declared column lengths and the error limit relates to the overheads in an index entry – but seems to "forget" the one byte extra for non-unique indexes. --//实际上面的测试主要有感而发，又遇到开发乱键字段长度的情况，没有根据实际的情况选择建立字段的长度，直接导致我根本不能建立索 --//引。我不可能建立更大的数据块来存放索引，无形增加维护的难度，大概这个也算一个理由不要顺便选择varchar2(1000)之类的字段 --//类型。