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

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.

3.  There are 65534 blocks for emp table for cluster key DEPNO=3 which has total 19420289 records.

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from emp where DEPTNO=3;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
 65534


SQL> select DEPTNO,count(*) from emp group by DEPTNO order by DEPTNO desc FETCH FIRST 5 ROWS ONLY;

 DEPTNO COUNT(*)
---------- ----------
 3 19420289

4.  For index cluster, the identifier to identify rows (ROWID) reserves 2 bytes for blocks associated with a single index entry.  After filling a cluster with 65534 blocks for the same key , ORA-2475 error is returned .

 

5.  We have to move cluster tables onto bigger block size,  like 16K tablespace to resolve this problem.

a. Add parameter db_16k_cache_size 
   SQL>alter system set db_16k_cache_size = 100M scope=spfile;
b.  Export cluster and tables 
    ...
    INCLUDE=CLUSTER:"IN ('EMP_DEPT')"
    INCLUDE=TABLE:"IN ('EMP','DEPT')"
    .....
c. Keep reference constraints definitions.

   select * from dba_constraints where ( r_owner , r_constraint_name) in (  select owner, constraint_name from dba_constraints where table_name in ('EMP','DEPT') and constraint_type = 'P' );

   select dbms_metadata.get_ddl('REF_CONSTRAINT','ref_constraint_name','owner') from dual;

d. Drop cluster and tables. SQL>drop cluster EMP_DEPT; SQL>drop table emp; SQL>drop table dept; e. Drop tablespace users. f. Create tablespace with 16k blocksize SQL> create tablespace users_new datafile '+DATA" size 500M blocksize 16384; Tablespace created. g. import cluster and tables. h. Add back reference constraints, which was dropped while cluster tables sere dropped.
Advertisements
This entry was posted in ORA- ERRORS and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s