ANBOB™

首先提几个问题：

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.

打赏

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