| Script hangs when deleting from table [message #457460] |
Mon, 24 May 2010 10:45  |
Telbin
Messages: 7
Registered: May 2010
|
Junior Member |
|
|
I'm testing a procedure which loads data into my database, and after each test I want to empty some of the tables and reset the sequences. I have this script to do that...
DELETE FROM COM_MERGE;
COMMIT;
DELETE FROM COM_TITLE;
COMMIT;
DELETE FROM COM_ISSUE;
COMMIT;
DELETE FROM COM_PAGE_ELEMENT;
COMMIT;
DELETE FROM COM_ELEMENT;
COMMIT;
DELETE FROM COM_STORY_TITLE;
COMMIT;
BEGIN
COM_RESET_SEQUENCES;
END;
Today I added the call to the sequences procedure to my script, but I have been using the script to delete from tables for a number of days without problem.
However today I am finding that when I run the script it works ok the first couple of times, but when I try running it for a third time, it hangs after the second delete (in other words it stops when it gets to the delete from COM_ISSUE). After this happened the first couple of times I stopped the db and restarted it, then the script was ok twice, but again I'm finding that the script hangs. There is no error message, but the script fails to complete.
I didn't know if it was because originally I had one commit at the end of the script, so I added commits after each delete but that didn't solve it.
I am using SQL Developer but I have found the same problem when running the script from SQL Plus.
This is the definition of the COM_ISSUE table (just in case the table is the source of the problem).
There is only one row in COM_ISSUE.
CREATE TABLE "BILL"."COM_ISSUE"
(
"CI_ID" NUMBER NOT NULL ENABLE,
"CI_TITLE" NUMBER NOT NULL ENABLE,
"CI_DATE" NUMBER NOT NULL ENABLE,
"CI_PRICE" NUMBER NOT NULL ENABLE,
"CI_PUBLISHER" NUMBER NOT NULL ENABLE,
CONSTRAINT "COM_ISSUE_PK" PRIMARY KEY ("CI_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE,
CONSTRAINT "COM_ISSUE_UK1" UNIQUE ("CI_TITLE", "CI_DATE", "CI_PRICE") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE,
CONSTRAINT "COM_ISSUE_COM_COVER_DATE_FK1" FOREIGN KEY ("CI_DATE") REFERENCES "BILL"."COM_COVER_DATE" ("CCD_ID") ON
DELETE CASCADE ENABLE,
CONSTRAINT "COM_ISSUE_COM_PRICE_FK1" FOREIGN KEY ("CI_PRICE") REFERENCES "BILL"."COM_PRICE" ("CP_ID") ON
DELETE CASCADE ENABLE,
CONSTRAINT "COM_ISSUE_COM_PUBLISHER_FK1" FOREIGN KEY ("CI_PUBLISHER") REFERENCES "BILL"."COM_PUBLISHER" ("CP_ID") ON
DELETE CASCADE ENABLE
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "USERS" ;
CREATE UNIQUE INDEX "BILL"."COM_ISSUE_PK" ON "BILL"."COM_ISSUE"
(
"CI_ID"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "USERS" ;
CREATE UNIQUE INDEX "BILL"."COM_ISSUE_UK1" ON "BILL"."COM_ISSUE"
(
"CI_TITLE", "CI_DATE", "CI_PRICE"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "USERS" ;
CREATE OR REPLACE TRIGGER "BILL"."COM_ISSUE_ID_TRG" BEFORE
INSERT ON COM_ISSUE FOR EACH ROW BEGIN
SELECT bill.com_issue_seq.nextval INTO :new.ci_id FROM dual;
END;
/
ALTER TRIGGER "BILL"."COM_ISSUE_ID_TRG" ENABLE;
I'm totally puzzled by this, any help would be appreciated.
Thanks.
[Updated on: Mon, 24 May 2010 11:09] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Script hangs when deleting from table [message #457464 is a reply to message #457462] |
Mon, 24 May 2010 10:56  |
Telbin
Messages: 7
Registered: May 2010
|
Junior Member |
|
|
I just tried TRUNCATE but I get message
Error starting at line 3 in command:
TRUNCATE table COM_TITLE
Error report:
SQL 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";
Surely I should be able to just DELETE from the tables? In fact I know I can because it works sometimes. That's what's puzzling.
[Updated on: Mon, 24 May 2010 10:58] Report message to a moderator |
|
|
|
| Re: Script hangs when deleting from table [message #457469 is a reply to message #457464] |
Mon, 24 May 2010 11:12 |
cookiemonster
Messages: 13968
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Deletes never really hang, but they may take far longer than expected to complete.
To know where all the time is being spent you will need to trace the session.
Most likely problem is that you are trying to delete a record that is locked by another session.
Next time it gets stuck try running this SQL:
select
(select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
That'll tell you if any session is blocking any other session.
EDIT: typo
[Updated on: Mon, 24 May 2010 11:12] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Script hangs when deleting from table [message #457495 is a reply to message #457460] |
Mon, 24 May 2010 17:19 |
Telbin
Messages: 7
Registered: May 2010
|
Junior Member |
|
|
So much for my bold statement above. I got a 'hang' and ran cookiemonster's statement and got
BLOCKER SID 'ISBLOCKING'
------------------------------ ---------- ------------
BLOCKEE SID
------------------------------ ----------
BILL 159 is blocking
BILL 144
so I am blocking myself. Does this mean my data load procedure is not terminating cleanly? I'll check that it's committing all inserts. Is there anything else that I should check for?
btw I checked and all foreign key columns are indexed.
|
|
|
|
|
|
| Re: Script hangs when deleting from table [message #457497 is a reply to message #457496] |
Mon, 24 May 2010 17:49 |
Telbin
Messages: 7
Registered: May 2010
|
Junior Member |
|
|
Thanks for the input folks.
I found an unCOMMITed insert in my procedure. After fixing that, the problem seems to have gone away, I have now run the procedure seven times, each time followed by a successful execution of my delete script (and it's also fixed a problem I had where the procedure wasn't inserting all the rows I expected).
Cheers
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
