ORA-02475: maximum cluster chain block count of 65534 has been exceeded

For 8k block size tablespace, maximum cluster chain block count is 65534. Otherwise bigger block size tablespace is recommended.

This article demonstrates how to reproduce  ORA-02475  error , and explains why it happens and how to resolve this issue.

  1. Create cluster tables and indexes.
SQL> CREATE CLUSTER emp_dept (deptno NUMBER(3)) TABLESPACE users;

Cluster created.

SQL> CREATE TABLE dept 
     (
        deptno NUMBER(3) PRIMARY KEY,
        deptname VARCHAR2(15) NOT NULL
     )
     CLUSTER emp_dept (deptno);

Table created.

SQL> CREATE TABLE emp 
     (
        empno NUMBER(10) PRIMARY KEY,
        ename VARCHAR2(15) NOT NULL,
       deptno NUMBER(3) REFERENCES dept
     )
     CLUSTER emp_dept (deptno);

Table created.

SQL> CREATE INDEX emp_dept_index
         ON CLUSTER emp_dept
         TABLESPACE users ;

Index created.

SQL> insert into dept values ( 1,'DEPT1 1');

1 row created.

SQL> insert into dept values (2,'DEPT1 2');

1 row created.

SQL> insert into dept values (3, 'DEPT1 3');

1 row created.

SQL> commit;

Commit complete.

2.  Insert records into cluster table EMP until gets ORA-02475 error.

SQL> set serveroutput on;
begin
    for i in 1 .. 1000000000
    loop
       begin
           insert into emp values (i,'emp '||i, 3);
           commit;
       exception
       when others
       then
           dbms_output.put_line( SQLERRM );
          exit;
       end;
   end loop;
end;
/

ORA-02475: maximum cluster chain block count of 65534 has been exceeded

PL/SQL procedure successfully completed.

Subscribe to get access

Read more of this content when you subscribe today.