why deletion taking time and creating lock in oracle?

Question

I have a table with around 3,00,000 records, like

CREATE TABLE xxx 
   (    xxx_PK NUMBER(10,0), 
    CREATEDTIME TIMESTAMP (6), 
    MODIFIEDTIME TIMESTAMP (6), 
    CREATOR NUMBER(10,0), 
    LASTMODIFIER_FK NUMBER(10,0), 
    a_FK NUMBER(10,0), 
    b_FK NUMBER(10,0), 
    c_FK NUMBER(10,0), 
    d_FK NUMBER(10,0), 
    e_FK NUMBER(10,0), 
    f NUMBER(10,0), 
     PRIMARY KEY (xxx_PK),
CONSTRAINT FOREIGN KEY (LASTMODIFIER_FK)
      REFERENCES USERS (USERID_PK) ENABLE NOVALIDATE, 
CONSTRAINT FOREIGN KEY (a_FK)
      REFERENCES a (a_PK) ENABLE NOVALIDATE, 
CONSTRAINT FOREIGN KEY (b_FK)
      REFERENCES b (b_PK) ENABLE NOVALIDATE, 
CONSTRAINT FOREIGN KEY (c_FK)
      REFERENCES c (c_PK) ENABLE NOVALIDATE, 
CONSTRAINT FOREIGN KEY (d_FK)
      REFERENCES d (d_PK) ENABLE NOVALIDATE, 
CONSTRAINT FOREIGN KEY (e_FK)
      REFERENCES e (e_PK) ENABLE NOVALIDATE
);
CREATE INDEX f_INDEX ON xxx (f) ;

When I am trying to truncate, getting error -

ora 02266
02266, 00000, "unique/primary keys in table referenced by enabled foreign keys"
*Cause: An attempt was made to truncate a table with unique or
primary keys referenced by foreign keys enabled in another table.
Other operations not allowed are dropping/truncating a partition of a
partitioned table or an ALTER TABLE EXCHANGE PARTITION.
*Action: Before performing the above operations the table, disable the
foreign key constraints in other tables. You can see what
constraints are referencing a table by issuing the following
command:
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

We also table yyy and zzz which contains reference of table xxx. Table yyy and zzz is empty.

EDIT - When I disabled constraint of his child table yyy and zzz, it was successfully truncated.

Please suggest what should I do.

Disable foreign key constraints first, some tables that have data are still references — Muhammad Muazzam, Dec 01 '15 at 08:12
@MuhammadMuazzam I forgot to mention that when I disabled constraint, it was successfully truncated. Is there any other way, I can resolve? — Aamir, Dec 01 '15 at 08:13
Possible duplicate of: http://stackoverflow.com/questions/1327190/oracle-delete-query-taking-too-much-time http://stackoverflow.com/questions/10101484/delete-statement-was-very-slow-in-oracle — Rene, Dec 01 '15 at 08:31
no this is not duplicate, its different case. Let me elaborate more in Edit part. — Aamir, Dec 04 '15 at 15:34

score 0 · Answer 1 · answered Dec 01 '15 at 08:48

0

You need Foreign Keys with cascade delete

in linked tables.

answered Dec 01 '15 at 08:48

Muhammad Muazzam

2,810
6
33
62

why deletion taking time and creating lock in oracle?

1 Answers1