2012-10-30 11gR2 "ADMINISTRATOR'S GUIDE" page 397 - 446

 

 

Taking Tablespaces Offline -- 表空间下线

 

Making a Tablespace Read-Only -- 表空间只读
If you find it is taking a long time for the ALTER TABLESPACE statement to complete,
you can identify the transactions that are preventing the read-only state from taking
effect. You can then notify the owners of those transactions and decide whether to
terminate the transactions, if necessary. -- 如果在使表空间只读的过程中。你可以找出其他事务，
并决定是否终止这些事务。

 

1. 找出会话地址
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

 

2. 事务地址，在本事务之前的事务（看SCN号），就有可能是阻挡事务。
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 txn

3. 找出阻挡的嫌疑人（或事务），由你决定是提交还是回滚
SELECT T.SES_ADDR, S.USERNAME, S.MACHINE
FROM V$SESSION S, V$TRANSACTION T
WHERE T.SES_ADDR = S.SADDR
ORDER BY T.SES_ADDR
SES_ADDR USERNAME MACHINE
-------- -------------------- --------------------
800352A0 DAVIDB DAVIDBLAP --> Contact this user
80035A50 MIKEL LAB61 --> Contact this user
80034AF0 DBA01 STEVEFLAP
80037910 NICKD NICKDLAP

 

Tablespace Data Dictionary Views -- 与表空间相关的数据字典视图