Home » SQL & PL/SQL » SQL & PL/SQL » alter tablespace test_tbs read only hangs (Oracle DB 10.2.0.1 SUN SPARC OS)
alter tablespace test_tbs read only hangs [message #320863] Fri, 16 May 2008 08:43 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I created a partitioned test tablespace 'test_tbs' to test out transportable tablespaces. However, when I issue :
SQL> alter tablespace test_tbs read only;


My session just hangs. I had this running overnight and it did not complete this morning. There were no corresponding messages in the alert.log either. There are no active transactions affecting this tablespace, why would this seemingly simple task hang?
Thanks.
Re: alter tablespace test_tbs read only hangs [message #320866 is a reply to message #320863] Fri, 16 May 2008 08:51 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> why would this seemingly simple task hang?
I would suspect that if there was uncommitted changes pending against an object in that tablespace,
Oracle would wait until COMMIT or ROLLBACK was issued before making it READ ONLY.
Re: alter tablespace test_tbs read only hangs [message #320875 is a reply to message #320866] Fri, 16 May 2008 09:32 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
That's interesting. However, the only objects in this tablespace are my test table partitions:

  1* select segment_name, segment_type, bytes/1024/1024 from dba_segments where tablespace_name = 'TEST_TBS'
SQL> /

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024
------------------------------ ------------------ ---------------
SENT_TEST                      TABLE PARTITION               3392
SENT_TEST                      TABLE PARTITION               3200
SENT_TEST                      TABLE PARTITION               3200


I've issued commits and also exited/re-entered the session. Shouldn't that commit any uncommitted transactions? Could it have anything to do with the fact that during the load of my test data, I set the tablespace to 'no logging'?
Re: alter tablespace test_tbs read only hangs [message #320876 is a reply to message #320863] Fri, 16 May 2008 09:39 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
GOOGLE is your friend but only if you actually use it.

http://www.jlcomp.demon.co.uk/faq/ts_readonly.html
Re: alter tablespace test_tbs read only hangs [message #320891 is a reply to message #320876] Fri, 16 May 2008 10:44 Go to previous message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Yes, I read that article as well. I analyzed the objects with this test schema and that still does not resolve the issue of altering this tablespace to 'read-only'.
I'll keep looking around, thanks for your help.
Previous Topic: take the digit out of the string
Next Topic: alter session fails
Goto Forum:
  


Current Time: Wed Dec 07 22:45:04 CST 2016

Total time taken to generate the page: 0.10409 seconds