如果INDEX表空间满了，数据是否可以插入

2010-3-2
如果数据和INDEX分开来存放，那如果INDEX的表空间满了，那是否可以插入新的数据。
创建表空间。

SQL> create tablespace test3 datafile '/oradata11/hr9dba/test03.dbf' size 100m; create tablespace test4 datafile '/oradata11/hr9dba/test04.dbf' size 2m; Tablespace created. Tablespace created. SQL> create table t10 tablespace test3 as select * from all_objects where 1=0; Table created. SQL> desc t10  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  OWNER                                     NOT NULL VARCHAR2(30)  OBJECT_NAME                               NOT NULL VARCHAR2(30)  SUBOBJECT_NAME                                     VARCHAR2(30)  OBJECT_ID                                 NOT NULL NUMBER  DATA_OBJECT_ID                                     NUMBER  OBJECT_TYPE                                        VARCHAR2(19 CHAR)  CREATED                                   NOT NULL DATE  LAST_DDL_TIME                             NOT NULL DATE  TIMESTAMP                                          VARCHAR2(19 CHAR)  STATUS                                             VARCHAR2(7 CHAR)  TEMPORARY                                          VARCHAR2(1 CHAR)  GENERATED                                          VARCHAR2(1 CHAR)  SECONDARY                                          VARCHAR2(1 CHAR) SQL> create index i_t10  on t10(object_id) tablespace test4   2  ; Index created. SQL> insert into t10 select * from all_objects; insert into t10 select * from all_objects             * ERROR at line 1: ORA-01654: unable to extend index SYS.I_T10 by 128 in tablespace TEST4 SQL> select count(*) from t10;   COUNT(*) ----------          0

数据无法插入。

SQL> insert into t10 select * from all_objects where rownum < 100; 99 rows created. SQL> commit; Commit complete. SQL> select count(*) from t10;   COUNT(*) ----------         99

插入小量的数据，是可以正常插入的。
再次插入大量的数据，发现还是报错。

SQL> insert into t10 select * from all_objects; insert into t10 select * from all_objects             * ERROR at line 1: ORA-01654: unable to extend index SYS.I_T10 by 128 in tablespace TEST4 SQL> select count(*) from t10;   COUNT(*) ----------         99

-END -

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/40239/viewspace-628421/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/40239/viewspace-628421/