Fix ORA-02032: clustered tables cannot be used before the cluster index is built

advertisements

_____________________________________________________________________________________________________________________

Error Description:

Insert SQL statement fails with following error message.

SQL> insert into emp values (7369, 'smith', 'clerk', 7902,to_date('17-dec-1980', 'dd-mon-yyyy'), 800, null, 20);

insert into emp values (7499, 'allen', 'salesman', 7698,to_date('20-feb-1981', 'dd-mon-yyyy'), 1600, 300, 30);

insert into emp values (7369, 'smith', 'clerk', 7902,to_date('17-dec-1980', 'dd-mon-yyyy'), 800, null, 20)

            *

ERROR at line 1:

ORA-02032: clustered tables cannot be used before the cluster index is built

Solution Description:

The error is because you are trying to insert the rows into a clustered table and for the corresponding cluster index is not created.

It is must to create the cluster index before inserting any rows to the clustered tables. Otherwise you will experience this error.

See example below.

CREATE CLUSTER emp_dept (deptno number(2));

Cluster created.

create table emp

(

empno number(4) not null,

ename varchar2(10),

job varchar2(9),

mgr number(4),

hiredate date,

sal number(7, 2),

comm number(7, 2),

deptno number(2)

)

cluster emp_dept (deptno);

Table created.

create table dept

(

deptno number(2),

dname varchar2(14),

loc varchar2(13)

)

cluster emp_dept (deptno);

Table created.

insert into emp values (7369, 'smith', 'clerk', 7902,to_date('17-dec-1980', 'dd-mon-yyyy'), 800, null, 20)

            *

ERROR at line 1:

ORA-02032: clustered tables cannot be used before the cluster index is built

SQL> create index idx_emp_dept on cluster emp_dept;

Index created.

SQL>  insert into emp values (7369, 'smith', 'clerk', 7902,to_date('17-dec-1980', 'dd-mon-yyyy'), 800, null, 20);

1 row created.

SQL> commit;

Commit complete.

_____________________________________________________________________________________________________________________

Website Stats