By any chance is your compatible parameter set to lower than 8.1 (!)?
As MOS note 33402.1 states:
You do not have to wait for transactions to complete before issuing the
ALTER TABLESPACE...READ ONLY statement. When the statement is issued,
the target tablespace goes into a transitional read-only mode in which no further
DML statements are allowed, though existing transactions that modified the
tablespace will be allowed to commit or rollback. Once this occurs, the
tablespace is quiesced, with respect to active transactions.
Note: This transitional read-only state only occurs if the value of the
initialization parameter COMPATIBLE is 8.1.0 or greater. For parameter
values less than 8.1.0, the ALTER TABLESPACE...READ ONLY statement fails
if any active transactions exist. If I have an uncommitted insert to a table in session 1:
create tablespace tbsp datafile 'tbsp.dbf' size 1M;
create table t (
x int
) tablespace tbsp;
insert into t values (1);
and session 2 I try and make corresponding tablespace read only, it is blocked:
alter tablespace tbsp read only;
Until I commit/rollback the insert.
You can check for open transactions by querying v$transaction. You can use this to identify those that may be stopping you making it read only. From MOS note 33402.1 again
If you find it is taking a long time for the tablespace to quiesce, it is possible to
identify the transactions which are preventing the read-only state from taking effect.
The owners of these transactions can be notified and a decision can be made to
terminate the transactions, if necessary. The following example illustrates how you
might identify the blocking transactions.
Identify the transaction entry for the ALTER TABLESPACE...READ ONLY statement.
SELECT sql_text, saddr
FROM v$sqlarea,v$session
WHERE v$sqlarea.address = v$session.sql_address
AND sql_text like 'alter tablespace%';
SQL_TEXT SADDR
---------------------------------------- --------
alter tablespace tbs1 read only 80034AF0
The start SCN of each active transaction is stored in the V$TRANSACTION view.
Displaying this view sorted by ascending start SCN lists the transactions in
execution order. Knowing the transaction entry for the read-only statement, it
can be located in the V$TRANSACTION view. All transactions with lesser or
equal start SCN can potentially hold up the quiesce and subsequent read-only
state of the tablespace.
SELECT ses_addr, start_scnb
FROM v$transaction
ORDER BY start_scnb;
SES_ADDR START_SCNB
-------- ----------
800352A0 3621 --> waiting on this txn
80035A50 3623 --> waiting on this txn
80034AF0 3628 --> this is the ALTER TABLESPACE statement
80037910 3629 --> don't care about this txnIf this doesn't help, please give us more details about what you're doing that includes the exact statement that is leading to your error. And ideally a complete test case (including create table, tablespace, etc.) we can copy to reproduce the problem.