Problem can be reproduced on stardard sample scheme HR or with limited/shorter setup (from the same HR script)
drop TABLE DEPARTMENTS;
CREATE TABLE DEPARTMENTS
(
DEPARTMENT_ID NUMBER (4) NOT NULL ,
DEPARTMENT_NAME VARCHAR2 (30 BYTE) NOT NULL ,
MANAGER_ID NUMBER (6) ,
LOCATION_ID NUMBER (4)
) LOGGING tablespace users
;
CREATE INDEX DEPT_LOCATION_IX ON DEPARTMENTS
(
LOCATION_ID ASC
)
LOGGING
NOCOMPRESS
NOPARALLEL tablespace users
;
CREATE UNIQUE INDEX DEPT_ID_PKX ON DEPARTMENTS
(
DEPARTMENT_ID ASC
) tablespace users
;
ALTER TABLE DEPARTMENTS
ADD CONSTRAINT DEPT_ID_PK PRIMARY KEY ( DEPARTMENT_ID ) ;
drop TABLE EMPLOYEES;
CREATE TABLE EMPLOYEES
(
EMPLOYEE_ID NUMBER (6) NOT NULL ,
FIRST_NAME VARCHAR2 (20 BYTE) ,
LAST_NAME VARCHAR2 (25 BYTE) NOT NULL ,
EMAIL VARCHAR2 (25 BYTE) NOT NULL ,
PHONE_NUMBER VARCHAR2 (20 BYTE) ,
HIRE_DATE DATE NOT NULL ,
JOB_ID VARCHAR2 (10 BYTE) NOT NULL ,
SALARY NUMBER (8,2) ,
COMMISSION_PCT NUMBER (2,2) ,
MANAGER_ID NUMBER (6) ,
DEPARTMENT_ID NUMBER (4)
) LOGGING tablespace users
;
ALTER TABLE EMPLOYEES
ADD CONSTRAINT EMP_SALARY_MIN
CHECK ( salary > 0)
;
CREATE INDEX EMP_DEPARTMENT_IX ON EMPLOYEES
(
DEPARTMENT_ID ASC
)
LOGGING
NOCOMPRESS
NOPARALLEL tablespace users
;
CREATE INDEX EMP_JOB_IX ON EMPLOYEES
(
JOB_ID ASC
)
LOGGING
NOCOMPRESS
NOPARALLEL tablespace users
;
CREATE INDEX EMP_MANAGER_IX ON EMPLOYEES
(
MANAGER_ID ASC
)
LOGGING
NOCOMPRESS
NOPARALLEL tablespace users
;
CREATE INDEX EMP_NAME_IX ON EMPLOYEES
(
LAST_NAME ASC ,
FIRST_NAME ASC
)
LOGGING
NOCOMPRESS
NOPARALLEL tablespace users
;
CREATE UNIQUE INDEX EMP_EMP_ID_PKX ON EMPLOYEES
(
EMPLOYEE_ID ASC
) tablespace users
;
ALTER TABLE EMPLOYEES
ADD CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY ( EMPLOYEE_ID ) ;
ALTER TABLE EMPLOYEES
ADD CONSTRAINT EMP_EMAIL_UK UNIQUE ( EMAIL ) ;
Oracle mechanics 