Delayed block cleanout and changing automatic undo tablespace
- From: Tim Onions <tim.onions@xxxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 20 Jul 2004 10:52:39 +0100
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
