Here's a little puzzle to get your mind working Smile

It is generally well known that there is a limit to the length of an index key in the Oracle database, the length being related to the block size for your database, or the block size of the tablespace where you are trying to create that index.

Just like many long terms DBA's are familiar the numbers the represent the old extent limits (505 for 8k, 249 for 4k and 121 for 2k), the numbers of index key limits are often easy to recall as well, but here's a couple of quick demos to prove I'm not just pulling numbers out of thin air. In both cases, I'm trying to create a composite index on 2x3500 byte columns which cannot be done in the block sizes I'm attempting:

Blocksize = 8k

 SQL> select username, default_tablespace   2  from   all_users   3  where  username = sys_context('USERENV','CURRENT_SCHEMA');  USERNAME             DEFAULT_TABLESPACE -------------------- ------------------------------ SCOTT                TS8K  SQL> select block_size   2  from   dba_tablespaces   3  where  tablespace_name = 'TS8K';  BLOCK_SIZE ----------       8192  SQL> create table t (x varchar2(3500),y varchar2(3500));  Table created.  SQL> create index ix on t ( x,y); create index ix on t ( x,y)                    * ERROR at line 1: ORA-01450: maximum key length (6398) exceeded

Blocksize = 4k

 SQL> select username, default_tablespace   2  from   all_users   3  where  username = sys_context('USERENV','CURRENT_SCHEMA');  USERNAME             DEFAULT_TABLESPACE -------------------- ------------------------------ SCOTT                TS4K  SQL> select block_size   2  from   dba_tablespaces   3  where  tablespace_name = 'TS4K';  BLOCK_SIZE ----------       4096  SQL> create table t (x varchar2(3500),y varchar2(3500));  Table created.  SQL> create index ix on t ( x,y); create index ix on t ( x,y)                    * ERROR at line 1: ORA-01450: maximum key length (3118) exceeded

Thus we are capped at approximately 75% of the block's size for the index key length.

So here's the puzzle for you. Under what scenario can you be trying to create an index in 8k blocksize tablespace yet still get the error saying your index key exceeds the 4k limit?

 SQL> select username, default_tablespace   2  from   all_users   3  where  username = sys_context('USERENV','CURRENT_SCHEMA');  USERNAME             DEFAULT_TABLESPACE -------------------- ------------------------------ SCOTT                TS8K  SQL> select block_size   2  from   dba_tablespaces   3  where  tablespace_name = 'TS8K';  BLOCK_SIZE ----------       8192  SQL> create table t (x varchar2(3500),y varchar2(3500));  Table created.  SQL> create index ix on t ( x,y); create index ix on t ( x,y)                    * ERROR at line 1: ORA-01450: maximum key length (3118) exceeded  < ===== ??????

Answer posted soon, but if you solve it, feel free to post a comment!