首页 » ORACLE » oracle 禁用表的DDL 实现真正的READ ONLY

oracle 禁用表的DDL 实现真正的READ ONLY

首先提几个问题:

1,只读表空间可不可以后来创建表

2,只读表空间里先创建的表能不能删除

3,read only  是不是只禁用dml

下面看我的实验,环境oracle 1og R2

SQL> create tablespace tbsread datafile '/oradata/orcl/tbsread.dbf' size 10m ;

Tablespace created.

SQL> alter tablespace tbsread read only;

Tablespace altered.

SQL> conn anbob/anbob
Connected.
SQL> create table testread (id int) tablespace tbsread;
create table testread (id int) tablespace tbsread
*
ERROR at line 1:
ORA-01647: tablespace 'TBSREAD' is read only, cannot allocate space in it

SQL> conn system/oracle
Connected.
SQL> alter tablespace tbsread read write;

Tablespace altered.

SQL> conn anbob/anbob
Connected.
SQL> create table testread (id int) tablespace tbsread;

Table created.

SQL> insert into testread values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> conn system/oracle
Connected.
SQL> alter tablespace tbsread read only;

Tablespace altered.

SQL> conn anbob/anbob
Connected.
SQL> delete table testread;
SP2-0544: Command "delete" disabled in Product User Profile

--为什么会报这个错,请查看http://www.anbob.com/?p=944 很有意思的方法

SQL> conn system/oracle
Connected.

SQL> delete from product_user_profile;

5 rows deleted.

SQL> commit;

Commit complete.

SQL> conn anbob/anbob
Connected.

SQL> delete from testread;
delete from testread
            *
ERROR at line 1:
ORA-00372: file 18 cannot be modified at this time
ORA-01110: data file 18: '/oradata/orcl/tbsread.dbf'

SQL> drop table testread;

Table dropped.

SQL> conn system/oracle
Connected.

SQL> alter tablespace tbsread read write;

Tablespace altered.

SQL> create table anbob.testread (id int) tablespace tbsread;

Table created.

SQL> conn anbob/anbob
Connected.
SQL> alter table  testread disable table lock;

Table altered.

SQL> insert into testread values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table testread;
drop table testread
           *
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for TESTREAD

SQL> alter table  testread enable table lock;

Table altered.

SQL> drop table testread;

Table dropped.
打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Derick Imbier | #1
    2011-12-21 at 05:43

    I would just say one thing to you and that is, “FANTASTIC”!! Keep it up and wish to get more details from your blog.