Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-01644 -- tablespace 'USERS' is already read only

Prashant DixitJul 12 2012 — edited Jul 12 2012
Hi,

After executing DBMS_TTS.TRANSPORT_SET_CHECK Procedure I've switched the USERS Tablespace to READ ONLY mode and since then I'm not able to change it back to ONLINE mode.

SQL> alter tablespace users online;

Tablespace altered.

SQL> select TABLESPACE_NAME, STATUS from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
USERS READ ONLY
EXAMPLE ONLINE
MUSIC ONLINE
TEMP00 ONLINE
UNDOTBS0007 ONLINE

8 rows selected.

SQL> alter tablespace users read only;
alter tablespace users read only
*
ERROR at line 1:
ORA-01644: tablespace 'USERS' is already read only


Please assist.

Thanks
Prashant D

Comments

Srini Chavali-Oracle
Pl post details of OS and database versions. Are you wanting to change the tablespace mode to READ WRITE ? ONLINE is the opposite of OFFLINE

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_3002.htm#i2154547

HTH
Srini
1 person found this helpful
Prashant Dixit
Thanks for reply Sir.

OS Details:

[oracle@localhost ~]$ uname -a
Linux localhost.localdomain 2.6.18-164.el5 #1 SMP Tue Aug 18 15:51:54 EDT 2009 i686 i686 i386 GNU/Linux

DB Details:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


Yes, all i want is to switch the tablespace back to Online Mode: Read Write. I tried to restart the database to check the status and still the issue endures.
Still reflecting 'READ ONLY' as mode for Tbs USERS.


thanks
Prashant D
vlethakula
I think by mistake, you are issuing read only command

SQL> alter tablespace users read only;
alter tablespace users read only
*
ERROR at line 1:
ORA-01644: tablespace 'USERS' is already read only
Prashant Dixit
Sir,

It's fixed:
Altered tablespace in Read and Write bring back the Tablespace to ONLINE mode.

SQL> alter tablespace users read write;

Tablespace altered.

SQL> select TABLESPACE_NAME, STATUS from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
USERS ONLINE
EXAMPLE ONLINE
MUSIC ONLINE
TEMP00 ONLINE
UNDOTBS0007 ONLINE

8 rows selected.

SQL>

Thanks for your support and assistance.

- Prashant
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 9 2012
Added on Jul 12 2012
#general-database-discussions
4 comments
467 views