| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: understanding rollback segments
Hi,
thanks for replies so far about why I appear to need a big rollback segment when copying a large table using PL/SQL, even when I'm committing my inserts after ever 1000 records. I summarise below:
Kelly Young <young_at_maricopa.edu> says:
.. Your rollback tablespace is probably not big enough. When you do a commit, Oracle assigns you another rollback segment for your next transaction. So you are continually going from one rollback segment to another. And as you do this, each rollback segment is using a certain amount of storage in the rollback tablespace. The total space used by all your rollback segements is more space than you have allocated to your tablespace.
Me: Fine, but why, when switching back to the previous RBS, doesn't
Oracle overwrite the original transaction data - after all, its been committed. Why keep growing the RBS? Besides, this can't be the whole story, since its possible ( but not recommended ) to have a database with a single rollback segment (SYSTEM) providing there are no other tablespaces.
Ed Poorbaugh <poorbaugh_at_norcross.mcs.slb.com> says:
I think that the rollback maintains the consistency of the cursor so that your cursor select statement "sees" the database as it existed at the moment you opened the cursor. Since your cursor will not be closed until the end of your loop this keeps the rollback active and things tend to grow too large.
Me: Ok, but the open cursor is for a select against data which is
not changing - I'm inserting into a completely different table, and I've got no open selects on that.
Tim Witort <trw_@_medicalert.org> offered a different programming solution which depends on having a monotonically increasing key which I can use to keep restarting new selects on. Sadly, I can't use that solution in this case.
andrew.nelson_at_astramerck.com says:
If you are using Oracle 7.2 or above, than you can use
CREATE TABLE FOO AS SELECT * FROM BAR UNRECOVERABLE. This will avoid any potential rollback seg problems and not generate any redo log entries. It will also run much quicker.
me: Yes, that will work. Thank you. But I'm not yet on Oracle 7.3, and
it still doesn't help me understand whats wrong with my solution.
"Phil Cook" <pcook_at_iamerica.net> says:
The inserts into foo may be causing the rollback segment to run out of extents, look at v$rollstat and v$rollname for extents, wraps, and extends. Look at dba_rollback_segs for the max extents.
This query may help to see what rollback segment you are using and it's stats.....
select t.start_time, u.usename,r.name,s.wraps,s.extends,s.extents from v$transaction t, v$rollstat s, v$rollname r, v$session u where r.usn = s.usn and s.usn = t.xidusn and t.status = 'ACTIVE' and u.taddr = t.addr
me: ( Phil has also sent me some follow-up ). I'm applying his
diagnostic SQL to try to find out in more detail what's going on.
Also, thank you to everyone who explained that "max extents exceeded" means that I need to increase MAXEXTENTS, or who provided alternative solutions. However, I'm not looking for a fix ( I'd already found a solution before posting the article ), but for a deeper understanding into how the rollback segments work.
Dave. Received on Fri Apr 25 1997 - 00:00:00 CDT
 |
 |