Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Making a tablespace read-only - identifying blocking tx

RE: Making a tablespace read-only - identifying blocking tx

From: Rachel Carmichael <>
Date: Fri, 11 Oct 2002 11:05:03 -0800
Message-ID: <>

thanks :)

it sort of makes sense that you can offline a tablespace with an active transaction in it. You can offline a rollback segment with an active transaction in it, no new transactions can start in that rbs but the one(s) there will finish.

With the tablespace, if I remember what I read in the docs correctly (and if the docs are correct), when you offline a tablespace, the active transactions in it continue to process as long as they do not try to modify a block in that tablespace. Once they try that, the transaction dies.

Hm, that doesn't sound right...... maybe it's if the blocks from that tablespace are still in the buffer cache?

Okay, found it in the 9ir2 Concepts manual, quoted below:

When a tablespace goes offline, Oracle does not permit any subsequent SQL statements to reference objects contained in that tablespace. Active transactions with completed statements that refer to data in that tablespace are not affected at the transaction level. Oracle saves rollback data corresponding to those completed statements in a deferred rollback segment in the SYSTEM tablespace. When the tablespace is brought back online, Oracle applies the rollback data to the tablespace, if needed.

When a tablespace goes offline or comes back online, this is recorded in the data dictionary in the SYSTEM tablespace. If a tablespace is offline when you shut down a database, the tablespace remains offline when the database is subsequently mounted and reopened.

You can bring a tablespace online only in the database in which it was created because the necessary data dictionary information is maintained in the SYSTEM tablespace of that database. An offline tablespace cannot be read or edited by any utility other than Oracle. Thus, offline tablespaces cannot be transposed to other databases.

Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
Please see the official ORACLE-L FAQ:
Author: Rachel Carmichael

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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 Oct 11 2002 - 14:05:03 CDT

Original text of this message