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 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>

2 comments:

uhesse.com 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,

Andrew