tablespace read only 时DDL 不解

显示全部楼层 · 2008-2-13 12:43:03

[php]
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME
STATUS
------------------------------ ---------
SYSTEM
ONLINE
UNDOTBS1
ONLINE
SYSAUX
ONLINE
TEMP
ONLINE
USERS
ONLINE
YXYUP
ONLINE
TEST
ONLINE
7 rows selected.
SQL> conn yxyup
Enter password:
Connected.
SQL> create table t12 (id number ) tablespace yxyup;
Table created.
SQL> insert into t12 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> conn /as sysdba
Connected.
SQL> alter tablespace yxyup read only;
Tablespace altered.
SQL>select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME
STATUS
------------------------------ ---------
SYSTEM
ONLINE
UNDOTBS1
ONLINE
SYSAUX
ONLINE
TEMP
ONLINE
USERS
ONLINE
YXYUP
READ ONLY
TEST
ONLINE
7 rows selected.
SQL> conn yxyup
Enter password:
Connected.
SQL> insert into t12 values(1);
insert into t12 values(1)

*
ERROR at line 1:
ORA-00372: file 11 cannot be modified at this time
ORA-01110: data file 11: '/opt/oracle/oradata/yxyup/yxyup03.dbf'
SQL> delete t12;
delete t12
*
ERROR at line 1:
ORA-00372: file 11 cannot be modified at this time
ORA-01110: data file 11: '/opt/oracle/oradata/yxyup/yxyup03.dbf'

SQL> select * from t12;
ID
----------
1
SQL> create table t13(id number);
create table t13(id number)
*
ERROR at line 1:
ORA-01647: tablespace 'YXYUP' is read only, cannot allocate space in it

SQL> drop table t12;
Table dropped.
SQL>

SYS@yxyup>alter tablespace yxyup read write;
Tablespace altered.
SYS@yxyup>create table yxyup.t12(id number) tablespace yxyup;
Table created.
SYS@yxyup>alter tablespace yxyup read only;
Tablespace altered.
SYS@yxyup>alter table yxyup.t12 add (name char(8));
Table altered.
SYS@yxyup>alter table yxyup.t12 drop column name;
alter table yxyup.t12 drop column name
*
ERROR at line 1:
ORA-12985: tablespace 'YXYUP' is read only, cannot drop column

SYS@yxyup>alter table yxyup.t12 add (remark date default sysdate);
Table altered.

SYS@yxyup>alter table yxyup.t12 add (dt date default sysdate not null) ;
Table altered.
SYS@yxyup>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE10.2.0.3.0Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SYS@yxyup>select file_name from dba_data_files where tablespace_name='YXYUP';
FILE_NAME
------------------------------------------------------------
/opt/oracle/oradata/yxyup/yxyup01.dbf
/opt/oracle/oradata/yxyup/yxyup02.dbf
/opt/oracle/oradata/yxyup/yxyup03.dbf
SYS@yxyup>alter table yxyup.t12 add (name1 varchar2(30) default 'ytk' not null);
Table altered.
SYS@yxyup>conn yxyup
Enter password:
Connected.
YXYUP@yxyup>alter table t12 add (name2 varchar2(30) default 'ytk' not null);
Table altered.
YXYUP@yxyup>conn /as sysdba
Connected.
SYS@yxyup>
SYS@yxyup>
SYS@yxyup>select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAMESTATUS
-------------------- ---------
SYSTEM
ONLINE
UNDOTBS1
ONLINE
SYSAUX
ONLINE
TEMP
ONLINE
USERS
ONLINE
YXYUP
READ ONLY
TEST
ONLINE
7 rows selected.
SYS@yxyup>

SYS@yxyup>alter table yxyup.t12 drop column name2;
alter table yxyup.t12 drop column name2
*
ERROR at line 1:
ORA-12985: tablespace 'YXYUP' is read only, cannot drop column
[/php]

问题
1.为什么可以drop table ?
2.为什么可以add column?
3.为什么不可以drop column ?
请指点,谢谢

千问 · 2008-2-13 12:43:03

呵呵，同问。浏览了下，确实我也不解。

千问 · 2008-2-13 12:43:03

因为DDL是对数据字典操作

千问 · 2008-2-13 12:43:03

测试表明,
一个表空间如果read only,对这个表空间中的对像所做的DDL操作是否能够成功和这个表中是否存在数据有关,见下面测试,
[php]
SYS@yxyup>select * from yxyup.t12;
no rows selected
SYS@yxyup>
SYS@yxyup>alter tablespace yxyup read write;
Tablespace altered.
SYS@yxyup>desc yxyup.t12;
Name
Null?Type
------------------------------------------------------------------------ -------- -------------------------------------------------
ID
NUMBER
NAME
CHAR(8)
REMARK
DATE
DT
NOT NULL DATE
NAME1
NOT NULL VARCHAR2(30)
NAME2
NOT NULL VARCHAR2(30)
SYS@yxyup>
SYS@yxyup>
SYS@yxyup>insert into yxyup.t12 values(1,'yxyup',sysdate,sysdate,'a','b');
1 row created.
SYS@yxyup>commit;
Commit complete.
SYS@yxyup>alter tablespace yxyup read only;
Tablespace altered.
SYS@yxyup>alter table yxyup.t12 add (name3 varchar2(30) default 'ytk' not null);
alter table yxyup.t12 add (name3 varchar2(30) default 'ytk' not null)
*
ERROR at line 1:
ORA-00372: file 11 cannot be modified at this time
ORA-01110: data file 11: '/opt/oracle/oradata/yxyup/yxyup03.dbf'

SYS@yxyup>alter tablespace yxyup read write;
Tablespace altered.
SYS@yxyup>delete yxyup.t12;
1 row deleted.
SYS@yxyup>commit;
Commit complete.
SYS@yxyup>alter tablespace yxyup read only;
Tablespace altered.
SYS@yxyup>alter table yxyup.t12 add (name3 varchar2(30) default 'ytk' not null);
Table altered.
SYS@yxyup>
[/php]
[ 本帖最后由 yxyup 于 2007-12-25 12:19 编辑 ]

千问 · 2008-2-13 12:43:03

name2 column有defult值存放在YXYUP表空间，应该不能drop
update:sorry,说的有问题

[ 本帖最后由 waityou81 于 2007-12-25 12:31 编辑 ]

千问 · 2008-2-13 12:43:03

SQL> drop table t12;
Table dropped.
SQL>
是DDL操作,是对基表进程delete操作

千问 · 2008-2-13 12:43:03

是不是可以查一下，你的所有表默认的表空间

千问 · 2008-2-13 12:43:03

原帖由 foxmile 于 2007-12-25 12:24 发表
是不是可以查一下，你的所有表默认的表空间
[php]
SYS@yxyup>select owner,table_name,tablespace_name from dba_tables where table_name='T12';
OWNER TABLE_NAME
TABLESPACE_NAME
--------------- ------------------------------ --------------------
YXYUP T12
YXYUP
SYS@yxyup>
[/php]

千问 · 2008-2-13 12:43:03

那就不明白了。看来是不是我们理解错了read only的定义了。
要不你到专题深入讨论里面贴一下，问下Yong Huang,

千问 · 2008-2-13 12:43:03

原帖由 yxyup 于 2007-12-25 12:12 发表
测试表明,
一个表空间如果read only,对这个表空间中的对像所做的DDL操作是否能够成功和这个表中是否存在数据有关,见下面测试,

也不完全对吧，drop table在有数据时不也能成功吗？