| Deletion script doesnt completes [message #291797] |
Sun, 06 January 2008 23:15  |
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
|
Senior Member |
|
|
Hi Experts,
I am deleting 100.000 records from a table.
I executed one script that doesn't completed from 1 day.
I hope that should execute in 1 hour.
I checked UNDOTBS1 tablespace and find that it is 99% used.
My guess is because of this my deletion script is not working.
Please tell me how i can reduce size of the UNDOTBS1?
Or please tell me any other option so that my deletion script will work.
Thanks in advance.
[Updated on: Sun, 06 January 2008 23:15] Report message to a moderator |
|
|
|
|
|
| Re: Deletion script doesnt completes [message #291825 is a reply to message #291812] |
Mon, 07 January 2008 00:17  |
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
|
Senior Member |
|
|
My script is
DECLARE
i NUMBER:=0;
BEGIN
FOR cid IN (
SELECT a.id FROM test a WHERE a.database_id =10136
)
LOOP
i:=i+1;
DELETE FROM test WHERE id = cid.id;
IF (MOD(i, 1000) = 0) THEN
COMMIT;
END IF;
END LOOP;
commit;
END;
select cursor query results 100.000 records.
Previously this script takes 1 hour to complete.
And when i seen the UNDO tablespace it is 99% complete.
I cannot increase size of the tablespace because of space problem.
Please tell me how can i reduce the size of it?
Thanks in advance.
[Updated on: Mon, 07 January 2008 00:18] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Deletion script doesnt completes [message #291895 is a reply to message #291888] |
Mon, 07 January 2008 02:44 |
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
|
Senior Member |
|
|
Currently i added 500MB in the undo tablespace.
Now 90% used space in undo tablespace.
And i started to delete records manually only 1000 records.
Then say commit;
So i think that there should not increase in the UNDO tablespace.
But the undo tablespace is incresing.
So i want to know that suppose again undo tablespace reaches to 99% will it cause problem while deleting?
Thanks in advance.
|
|
|
|
|
|
| Re: Deletion script doesnt completes [message #291908 is a reply to message #291898] |
Mon, 07 January 2008 03:38 |
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
|
Senior Member |
|
|
Thanks Michel,
I am agree with you that i am weak in DBA because i am new to DBA. I will learn the topics which you have mentioned.
But for the time being please just tell will my delete statemets run even undo tablespace will 99%.
Thanks.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
