This article demonstrates how to reproduce ORA-02475 error , and explains why it happens and how to resolve this issue.
- 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.
Make DBA Life Easy 