Can't convert a Table Space into Read Only Mode

Posted By Sagar Patil

Reason : Active x’actions running on the table space

Locate SQL_TEXT and Session Address running alter Tablspace Command

SELECT SQL_TEXT, SADDR
FROM V$SQLAREA,V$SESSION
WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
AND lower(SQL_TEXT) LIKE ‘alter tablespace%’;

V$transaction will show active X’actions in System

SELECT SES_ADDR, START_SCNB,used_urec,used_ublk
FROM V$TRANSACTION
ORDER BY START_SCNB;

v$transaction View
Important fields of v$transaction are used_ublk and used_urec. They tell of how many blocks and records the undo for a transaction consists. In order to find out the name of the corresponding rollback segment, join the xidusn field with the usn field of v$rollname. This is demonstrated in

List of Sessions blocking tablepsace in write mode

select saddr, SID,Serial#,username,Status, Machine,SQL_ID
from v$session where saddr in
(select SES_ADDR FROM V$TRANSACTION where used_urec is not null
and used_ublk is not null) ;

SQL clarifying Commands fired by Session.

select b.SQL_TEXT,saddr, SID,Serial#,username,Status,Machine,a.SQL_ID
from v$session a ,v$SQL b
where a.saddr in (select SES_ADDR FROM V$TRANSACTION
where used_urec is not null and used_ublk is not null )
and a.sql_id=b.sql_id

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu