如果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.

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/，如需轉載，請註明出處，否則將追究法律責任。

如果INDEX表空間滿了，資料是否可以插入

相關文章