Re: Rollback and what table's DML caused an ORA-1555?

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).