Rao_S
asked on
How to delete large number of rows from a table.?
I have to delete about 200000 old rows and when i run a script with a commit point at 100, i get a tablespace error: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS_01'.
when i run the script at 10 or 50 commit point, i get long runing script error:
ORA-01555: snapshot too old: rollback segment number with name "" too small
What should i do next?
when i run the script at 10 or 50 commit point, i get long runing script error:
ORA-01555: snapshot too old: rollback segment number with name "" too small
What should i do next?
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
i tried this:
select * from dba_free_space where tablespace_name = 'SEH_ER_LT' order by bytes desc;
but i get nothing, could be access issues, is something wrong with my select?
select * from dba_free_space where tablespace_name = 'SEH_ER_LT' order by bytes desc;
but i get nothing, could be access issues, is something wrong with my select?
dba_free_space is actually 'free' unallocated space. Deleting rows doesn't release the space to 'free'. The space remains allocated to the table. If frees up space in the blocks allocated.
After running stats check out the empty_blocks column from user_tables.
After running stats check out the empty_blocks column from user_tables.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
thank you so much for the temp table solution! that is a great solution!
for now, i changed the commit to 100 and i got in touch with my dba, he ran stats and increased the space on the undo tablespace and reran the script. it worked fine!
on the qa database i had to delete 1080499 rows out of a table of 2714962. this one failed on the same 01555 error, he increases the space on redo log and will submitt the job several more times.
for now, i changed the commit to 100 and i got in touch with my dba, he ran stats and increased the space on the undo tablespace and reran the script. it worked fine!
on the qa database i had to delete 1080499 rows out of a table of 2714962. this one failed on the same 01555 error, he increases the space on redo log and will submitt the job several more times.
>>This creates a fetch across commit situation and will definitely cause an ORA-01555.
Excellent point! I keep forgetting about this one. I shouldn't, you post it often enough! :)
Excellent point! I keep forgetting about this one. I shouldn't, you post it often enough! :)
ASKER
successfully deleted large number of rows from both the tables.
the query to get tablespace data is very good! as well as the idea to create temp table.
the query to get tablespace data is very good! as well as the idea to create temp table.
Unfortunately, I run into the fetch across commits so many times, I cannot forget about it. I wish that developers (and vendors) would learn how to write code for massive updates and deletes against very large (multi-terabyte) databases. I end up rewriting so many of these I lost track a very long time ago.
Oracle Database
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
how do i check, how much free space is available? the table data has 2 clob fields. i thought the inconsistant read/transaction could be due to an app writing to it constantly, but for the last 5 mnts, there has been no change to the total count.