Dear Jonathan,
How are you my friend? Hope all is well with you and
your family in the UK. When we spoke to James Morle
last evening, he drew our attention to a B-52 flying
over his home. Interesting times we live in!
Yes, you are right in your observation, that "in
general" one does not have to do it. I remember
helping out a customer on a Saturday afternoon with
this problem a year or so ago, hence wanted to share
the experience. My memory fails me in retrieving the
exact version number where the problem was
encountered.
At any rate, it was one of those "delayed block clean
out" gotchas. In the specific case, the customer had
nobody on the system, after a shutdown, single user,
single session and was pulling his hair out with a
ORA-1555. Turns out the previous night, the objects in
the original READ-ONLY tablespace, was loaded with new
data, and the status was changed back to READ-ONLY.
Due to whatever reason (I could not gather whether
there (were/were not) any long-running queries), the
clean out did not occur on some of the objects, and
this caused the ORA-1555, as his current query was
trying to build a read-consistent image from the
rollback segment and could not find the before
image(obviously). We got around the problem by doing
what I had suggested in my posting. You are right in
that the relevant indexes should also be touched and
cleaned by some query.
Cheers,
Gaja
- Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
wrote:
>
> In general, you don't need to do this in
> recent versions of Oracle. Oracle knows
> that all the data in the tablespace MUST
> have been committed before the tablespace
> was switched to read-only (you can only
> switch a tablspace to readonly when there
> are no active transactions that started before
> the call to do the switch), and can use
> the 'read-only' SCN as the upper bound
> commit for the data in the tablespace.
>
> The only problem would come if you had a
> long-running query that started before a
> tablespace was made readonly - and Oracle
> needed to know whether some data had
> changed before or after the query started -
> AND you had managed to overwrite the
> undo that contained the relevant older
> version of the transaction table.
>
> If you do need to do this, don't forget
> that you also have to worry about indexes -
> count(*) could be satisfied from an index,
> and the 1555 problem could be down to
> an index block anyway - so the "SQL to
> write SQL" has to be a little clever.
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick
> Jr
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
> ____UK_______April 8th
> ____UK_______April 22nd
> ____Denmark__May 21-23rd
> ____USA_(FL)_May 2nd
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK_(Manchester)_May
> ____Estonia___June (provisional)
> ____USA_(CA, TX)_August
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com>
> Sent: 04 April 2003 19:53
>
>
> >
> > 1) Set those tablespaces back to READ-WRITE mode
> > 2) Write a sql-generating-sql script that performs
> a
> > select count(*) on all the objects in the
> tablespace
> > 3) Set the tablespaces back to READ-ONLY.
> >
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Jonathan Lewis
> INET: jonathan_at_jlcomp.demon.co.uk
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gaja Krishna Vaidyanatha
INET: oraperfman_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri Apr 04 2003 - 17:43:47 CST
