Another Stolen Idea

I read this in a comment by Uwe Hesse on Tanel Poder's blog and tested it on Oracle 11.2.0.2.7. With deferred segment creation, you can create a table in a read only tablespace. You do not get an error until you try to insert data into the table:

 

SQL> conn / as sysdba

Connected.

SQL> create user andrew

  2  identified by reid

  3  default tablespace users

  4  quota unlimited on users

  5  /

 

User created.

 

SQL> grant

  2  create session,

  3  create table,

  4  select any dictionary to andrew

  5  /

 

Grant succeeded.

 

SQL> alter tablespace users read only

  2  /

 

Tablespace altered.

 

SQL> conn andrew/reid

Connected.

SQL> create table t1

  2  segment creation deferred

  3  as select * from dba_tables

  4  where 1 = 2

  5  /

 

Table created.

 

SQL> insert into t1

  2  select * from dba_tables

  3  /

insert into t1

            *

ERROR at line 1:

ORA-01647: tablespace 'USERS' is read-only, cannot

allocate space in it

 

SQL>