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

2 comments: said...

Funny, isn't it? :-)

Andrew Reid said...

Dear Uwe,

Thank you for taking the time to comment on my blog. I found out today that you have written a book so I have advertised it at the end of the post.

Kind regards,