Can you create objects in a READ ONLY tablespace?

Hi Oracle geeks,

Last Friday, I’ve noticed a strange thing happening in my 11.2.0.3 Database: I’m able to create objects in a tablespace which is marked READ ONLY !!!

Don’t believe this?? See the below

SQL> create tablespace ro datafile ‘/xxx/RO.DBF’ size 10M AUTOEXTEND ON;
Tablespace created.

SQL> alter tablespace ro read only;
Tablespace altered.

SQL> select status from dba_tablespaces where tablespace_name=’RO’;
STATUS
———
READ ONLY

SQL> sho user
USER is “CSM“

SQL> create table csm(a number) tablespace ro;
Table created.

looking strange!!!

but connect as a ‘sys’ user now. and do the same exercise

SQL> conn sys/sys as sysdba
Connected.

SQL> sho user
USER is “SYS“

SQL> create table csm(a number) tablespace ro;
create table csm(a number) tablespace ro
*
ERROR at line 1:
ORA-01647: tablespace ‘RO’ is read-only, cannot allocate space in it

OMG!! it’s working as (un)expected…

After investigating a bit I found the reason behind this strange behavior(only in our perspective) is,

There is an option called ‘deferred_segment_creation’ starting from 11.2 which just adds an entry into the data dictionary but won’t actually create a segment.
So even in case of a locally managed tablespace, Oracle need not update the bitmaps in the current tablespace (RO in our case).
As it is just adding an entry into some of the data dictionary tables, Oracle doesn’t throw any errors.

You can’t even find a segment created in this tablespace

SQL> select segment_name,segment_type from dba_segments where tablespace_name=’RO’;
no rows selected

If you try to create a table by forcing it not to use the deferred_segment mode like this,

SQL> create table csm2(a number) segment creation IMMEDIATE tablespace ro;
create table csm2(a number) segment creation IMMEDIATE tablespace ro
*
ERROR at line 1:
ORA-01647: tablespace ‘RO’ is read-only, cannot allocate space in it

you’ll encounter the ORA-01647 error.

But the biggest question is, why a sys user was unable to do the same???
The answer is, differed segment option is not available for the objects in sys and system schema :)

Regards,
CSM

Comments

You can set uo some very odd examples. See this discussion,
http://www.orafaq.com/forum/mv/msg/154204/440027/148813/#msg_440027

Interesting observation!