Yesterday my production toad session blocked the alter tablespace read only operation. . My session just did a explain plan on some sql statement through toad and so the plan_table was on the public schema(system tablespace) and this transaction was not committed as toad doesn’t autocommit by default.Looks like due to a bug any uncommitted transaction in any tablespace could block a alter tablespace readonly operation. This information is FYI.
| Bug No: | 2227223 | ||
| Filed | 14-FEB-2002 | Updated | 13-AUG-2009 |
| Product | Oracle Server – Enterprise Edition | Product Version | 8.1.7.2 |
| Platform | Microsoft Windows 2000 | Platform Version | SP 2 |
| Database Version | 8.1.7.2 | Affects Platforms | Generic |
| Severity | Severe Loss of Service | Status | Development to Q/A |
| Base Bug | N/A | Fixed in Product Version | 11.2 |
Problem statement:
ALTER TABLESPACE READ ONLY HANGING WHEN THERE ARE ACTIVE TX IN ANY TABLESPACE
*** 02/14/02 03:53 pm ***
TAR:
—-
1988689.995
.
PROBLEM:
——–
alter tablespace < ts name> read only hangs when there is active transaction
in
any tablespace till all active transactions commit or rollback.
.
Customer considers this is a serious limitation for 24*7 oracle database or
mission critical databases. It is understandable that Oracle expects no active
transaction in the tablespace that is alterred. But why need not to have
active transactions in any other user tablespaces.
.
DIAGNOSTIC ANALYSIS:
——————–
Testcase
=========
The steps to reproduce the problem:
1. insert a row into foo table and don’t commit the transaction. foo table
resides in USERS tablespace.
.
SQL> connect julia
SQL> desc foo
Name Null? Type
—————————————– ——– —————-
ABC NUMBER(38)
DEFNOT NULL NUMBER(38)
GHI NUMBER(38)
.
SQL> insert into foo values (1,2,3);
.
1 row created.
.
.
SQL> alter tablespace users2 read only;
.
Tablespace altered.
.
This session is hanging till the insert transaction commits and users2
tablespace was alterred immediately.
.
Analysis
======
SQL> !ps -ef |grep internal
osupport 29996 29886 0 13:23:54 pts/2 0:00 sqlplus internal
osupport 30800 31330 2 14:33:00 pts/2 0:00 grep internal
osupport 31152 25016 0 14:28:18 pts/0 0:00 sqlplus internal
osupport 31330 29996 1 14:33:00 pts/2 0:00 -bin/csh -c ps -ef |grep
internal
.
.
SQL> !ps -ef |grep 31152
osupport 25686 31152 0 14:28:18 – 0:00 oracle817c
(DESCRIPTION=(LOCAL=YE
S)(ADDRESS=(PROTOCOL=beq)))
osupport 28604 31334 1 14:33:32 pts/2 0:00 grep 31152
osupport 31152 25016 0 14:28:18 pts/0 0:00 sqlplus internal
osupport 31334 29996 2 14:33:32 pts/20:00 -bin/csh -c ps -ef |grep 31152
.
SQL> select sid from v$session, v$process
2 where spid=25686 and addr=paddr;
.
SID
———-
10
.
SQL> select event,p1,p2,p3,wait_time from v$session_wait where sid=10;
.
EVENT P1
—————————————————————- ———-
P2 P3 WAIT_TIME
———- ———- ———-
enqueue 1415053316
589835 5004 0
.
.
SQL> alter session set events ‘immediate trace name systemstate level 10’;
.
Session altered.
.
SQL> alter session set events ‘immediate trace name systemstate level 10′;
.
Session altered.
.
SQL> select p1raw from v$session_wait where sid=10;
.
P1RAW
——–
54580004
.
SQL> select * from v$lock where type=’TX’;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
——– ——– ———- — ———- ———- ———- ———-
CTIME BLOCK
———- ———-
201456A8 20145774 9 TX 589835 5004 6 0
778 2
.
201456A8 20145774 10 TX 524361 5145 6 0
664 2
.
340864F8 34086508 10 TX 589835 5004 0 4
664 2
.
SQL> select username from v$session where sid=9;
.
USERNAME
——————————
JULIA
.
You can see session id 10 is requesting TX 589835 5004 in shared mode
that is held by session id 9 in exclusive mode. session 9 is doing insert
statement. session 10 is altering tablespace users2 read only.
.
WORKAROUND:
———–
N/A
.
RELATED BUGS:
————-
No
.
REPRODUCIBILITY:
—————-
Yes. reproducible at will.
.
TEST CASE:
———-
Please see the above
.
STACK TRACE:
————
N/A
.
SUPPORTING INFORMATION:
———————–
Will upload couple of systemstate dumps and alert logs to ess30.
.
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
—————————————-
.
DIAL-IN INFORMATION:
——————–
.
IMPACT DATE:
————
.
*** 02/14/02 03:53 pm *** (CHG: Sta->16)
*** 02/14/02 04:10 pm ***
files are on ess30:/bug/bug2227223
*** 02/15/02 05:09 pm *** (CHG: Asg->NEW OWNER)
*** 02/15/02 05:33 pm *** (CHG: Sta->32)
*** 02/15/02 05:33 pm ***
However, this is expected behaviour. Pls see explanation in bug 2106117 and
(ie, "making a tablespace read only" in Ch 11 of the Server Administrators
guide. ).
.
To quote the note :
.
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 write
operations (DML statements) are allowed against the tablespace. Existing
transactions that modified the tablespace are allowed to commit or rollback.
Once all transactions (in the database) have completed, the tablespace becomes
read-only.
.
.
The significant line here is ‘Once all transactions (in the database) have
completed, the tablespace becomes read-only.’ This means all transactions and
not just transactions in that tablespace. Thanks
*** 02/25/02 01:41 pm *** (CHG: Sta->16)
*** 02/25/02 01:41 pm ***
*** 02/25/02 01:50 pm *** (CHG: Sta->32)
*** 02/25/02 01:50 pm ***
*** 04/22/05 12:36 am *** (CHG: Sta->92)
*** 02/13/06 08:02 pm *** (CHG: SubComp->UNKNOWN)
*** 02/13/06 08:02 pm ***
*** 12/04/07 06:19 pm *** (CHG: Sta->11 Asg->NEW OWNER)
*** 12/04/07 06:19 pm ***
*** 12/07/07 05:38 pm ***
RELEASE NOTES:
]]Hang occurs during ‘alter tablespace read only’ due to unrelated active trans
]]actions.
REDISCOVERY INFORMATION:
Hang occurs during ‘alter tablespace read only’ due to unrelated active transac
tions.
WORKAROUND:
None
*** 12/09/07 09:29 pm *** (CHG: Asg->NEW OWNER)
*** 12/09/07 09:29 pm *** (CHG: Fixed->11.1.0.7)
*** 12/09/07 09:29 pm *** (CHG: Sta->80)
*** 12/10/07 07:21 am ***
*** 12/10/07 08:30 am ***
*** 12/10/07 10:38 am ***
*** 12/11/07 07:31 am ***
*** 12/16/07 11:49 pm ***
*** 12/20/07 05:53 pm ***
*** 01/16/08 01:30 am ***
*** 01/16/08 08:23 pm ***
*** 01/16/08 10:46 pm ***
*** 01/27/08 04:44 pm ***
*** 03/03/08 05:20 pm ***
*** 03/26/08 04:08 pm *** (CHG: Fixed->11.2)
*** 04/01/08 01:52 pm *** (CHG: Asg->NEW OWNER)
*** 04/01/08 01:52 pm ***
*** 05/17/08 11:53 am ***
*** 05/27/08 02:27 am ***
*** 05/27/08 02:27 am ***
*** 05/27/08 10:09 pm ***
*** 05/28/08 11:24 pm ***
*** 05/29/08 11:43 pm ***
*** 07/01/08 07:13 pm ***
*** 07/03/08 01:19 pm ***
*** 07/08/08 06:13 pm ***
*** 07/09/08 02:29 am ***
*** 07/22/08 03:33 pm ***
*** 07/22/08 03:34 pm ***
*** 07/22/08 03:34 pm ***
*** 07/23/08 08:48 pm ***
*** 07/25/08 10:24 am ***
*** 07/25/08 10:28 am ***
*** 07/25/08 10:59 am ***
*** 07/31/08 02:21 pm ***
*** 07/31/08 02:25 pm ***
*** 07/31/08 05:19 pm ***
*** 07/31/08 05:22 pm ***
*** 08/04/08 02:45 pm ***
*** 08/04/08 02:45 pm ***
*** 08/04/08 10:36 pm ***
*** 08/07/08 09:51 pm ***
*** 08/07/08 09:51 pm ***
*** 08/27/08 08:06 am ***
*** 09/01/08 11:03 pm ***
*** 09/01/08 11:04 pm ***
*** 09/01/08 11:05 pm ***
*** 09/02/08 01:33 pm ***
*** 09/07/08 09:36 pm ***
*** 09/07/08 09:40 pm ***
*** 09/09/08 10:24 pm ***
*** 09/10/08 07:25 pm ***
*** 09/12/08 11:59 pm ***
*** 09/15/08 05:24 pm ***
*** 09/18/08 11:55 am ***
*** 09/22/08 09:09 am ***
*** 09/22/08 04:30 pm ***
*** 09/22/08 04:30 pm ***
*** 10/03/08 12:53 pm ***
*** 02/05/09 08:36 am ***
*** 02/05/09 09:39 am ***
*** 04/13/09 02:58 pm ***
*** 04/29/09 07:04 am ***
*** 04/30/09 06:06 am ***
*** 04/30/09 06:09 am ***
*** 05/01/09 03:55 am ***
*** 05/06/09 04:48 pm ***
*** 05/13/09 05:13 pm ***
*** 05/15/09 12:24 am ***
*** 05/15/09 12:33 am ***
*** 05/26/09 11:43 pm ***
*** 06/16/09 06:37 am ***
bug 6404447 bug 6795880 bug 8210889 bug 7462072
*** 06/23/09 05:33 am ***
*** 06/25/09 10:12 am ***
*** 06/25/09 10:18 am ***
*** 06/30/09 09:25 pm ***
*** 06/30/09 09:42 pm ***
bug 6327692 bug 7238163 bug 7462072 bug 6904068 bug 8210889
*** 07/14/09 06:39 pm ***
*** 08/13/09 09:52 am ***
bug 6954722 bug 6053134 bug 6638558 bug 6870994 bug 6454456 bug 2227223
.
