Thursday, October 18, 2012

Another Stolen Idea

I read this in a comment by Uwe Hesse on Tanel Poder's blog and tested it on Oracle 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
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
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

1 comment: