oracle中ddl中enable,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.

打赏

微信扫一扫，打赏作者吧～