Unable to Truncate Table / Drop Storage and Slow access. Why?

[Azrael Noor]

Problem 1:
Unable to truncate following table.
Getting following error:

Error: ORA-02266: unique/primary keys in table referenced by enabled foreign keys
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";

Following is table structure

CREATE TABLE "REQUESTLOG"
(
	"PKREQUEST" NUMBER NOT NULL ENABLE,
	"SERVICEID"        NUMBER NOT NULL ENABLE,
	"USERID"           NUMBER,
	"REQUESTID"           VARCHAR2(100 BYTE),
	"TYPE"         VARCHAR2(100 BYTE),
	"SUBTYPE"      VARCHAR2(100 BYTE),
	"ACTION"       VARCHAR2(100 BYTE),
	"DATE" DATE,
	"RECEIVEDDATE" DATE NOT NULL ENABLE,
	"DATA" CLOB,
	"DATARESPONSE" CLOB,
	"NAME"      VARCHAR2(100 BYTE),
	"ENTITYID"        VARCHAR2(100 BYTE),
	"TRANSACTIONID"   VARCHAR2(100 BYTE),
	"LOGIN"       VARCHAR2(20 BYTE),
	"IPADDRESS"  VARCHAR2(20 BYTE),
	"MACADDRESS" VARCHAR2(100 BYTE),
	"STARTDATE" DATE,
	"ENDDATE" DATE,
	"STATUSID" NUMBER NOT NULL ENABLE,
	"REMARKS"      VARCHAR2(500 BYTE),
	"FLAG"   NUMBER NOT NULL ENABLE,
	"FILENAME"           VARCHAR2(100 BYTE),
	"OLDSTATUSID"          NUMBER,
	"PARENTREQUESTID" NUMBER,
	"USERNAME"      VARCHAR2(50 BYTE),
	"REASON"         VARCHAR2(500 BYTE),
	PRIMARY KEY ("PKREQUEST")
	FOREIGN KEY ("USERID") REFERENCES "USERINFO" ("USERID") ENABLE,
	FOREIGN KEY ("SERVICEID") REFERENCES "SERVICEINFO" ("SERVICEID") ENABLE,
	FOREIGN KEY ("STATUSID") REFERENCES "STATUSINFO" ("PSTATUSID") ENABLE,
	FOREIGN KEY ("OLDSTATUSID") REFERENCES "STATUSINFO" ("STATUSID") ENABLE
)

Problem 2:
Very Slow Access, like if i select this table it takes 29, 30 seconds to access but other table in same schema select works out in fractions of seconds only

please guide where i am lacking?

[Ashwin Sridhar]

Problem 1

You have references from this table. So before deleting, those references needs to be deleted. In simple terms, before removing a department, you need to remove employees tagged to that department.

Problem 2

There is no guarantee that all tables in a schema should take equal time to fetch data. It all depends on the amount of data present, indexes present in tables. To improve query time, create index for your table.

[Martin Vajsar]

Ashwin Sridhar wrote:Problem 1

You have references from this table. So before deleting, those references needs to be deleted. In simple terms, before removing a department, you need to remove employees tagged to that department.

Not quite true. This error will occur even if the employees table is empty. What you wrote is true for delete operations, but truncate is a DDL operation, not a DML operation, and works differently.

The description of the error which Azrael himself posted tells exactly what needs to be done. All foreign key constrains that refer to the table being truncated must be dropped or disabled. They can be recreated or enabled after the truncate completes. This is simply how it works in Oracle.