[20210506]]關於ORA-01450.txt

[20210506]]關於ORA-01450.txt

--//參考連結：



--//按照連結介紹：
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

--//參考連結

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)之類的欄位
--//型別。