 | | | Delayed block cleanout and changing automatic undo tablespace | Delayed block cleanout and changing automatic undo tablespace 2004-07-20 - By Tim Onions
Dear All
Windows 2003 (full patched)/Oracle9.2.0.5 EE
After a recent migration to 9i where data was copied from the 8i database
rather than the migration utility used I changed the automatic undo
tablespace from a large one (coz I was migrating a LOT of data) to a smaller
one (normal transactions are quite small). The DB was restarted in between
times to ensure the new UNDO tablesapce became active.
I want to drop the old large undo tablespace but cannot as when I offlined
it in preparation I started to get "data file cannot be read at this time "
errors in the application, where the datafiles in question were associated
with the old large undo tablesapce. I am pretty sure this is due to delayed
block cleanout with "rollback " in the old large tablespace still being
required for this period.
When using traditional rollback you could offline the rollbacks segments and
I believe this would force a cleanout (from what I have read). I can see no
way of offlining auto undo or forcing a cleanout. AskTom says "it happens
over time " as the blocks that need cleaning are accessed. I have done FTS
queries on all tables in the application and will, if I have to, run queries
that access all index blocks. However, I need to be sure data in the old
large undo tablespace is not still needed before I drop it or even risk
taking it offline again (the application errors were not nice!).
So eventually the questions:
- how can I tell if cleanout is still required?
or how can I tell if undo in the old large tablespace (the tablespaces that
is no longer being actively used by auto undo) is still required (for
cleanout)
Or how can I force cleanout aka offlining a rollback segment in 8i (without
having to run FTS and index scans on everything)
DBA_SEGMENTS shows 10 auto undo segments in the old large tablespace which
may be a clue but there is no info there to help me (they were there beofre
I ran the FTS queries and are still there now). They have been there for
some time and show no inclination to go.
Many thanks in anticipation.
T?
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
|