Oracle 为物联网指定表空间

我有两个表空间分别命名为USERS和INDX。当前用户的dufault表空间是用户。我创建了一个物联网表，它的名字是tb_zxp。由于不需要为IOT指定存储数据的表空间，所以我想将tb_zxp的整个索引放在表空间INDX上:

create table tb_zxp (customer_id integer ,
                     store_id integer,
                     trans_date date,
                     amt number,
                     goods_name varchar2(20),
                     rate number(8,1),
                     quantity integer,
                     constraint pk_zxp primary key (customer_id,store_id,trans_date))
                     organization index including amt overflow
                     tablespace indx;

insert into tb_zxp values (11,21,date '2021-04-10',500,'Cocacola',2,250);

insert into tb_zxp values (11,25,date '2021-04-11',760,'Tower',3.8,200);

insert into tb_zxp values (24,9,date '2021-05-11',5200,'Washing machine',5200,1);

commit;

但是，通过此查询，我们可以发现索引仍在默认表空间用户上分配:

select tablespace_name from user_extents where segment_name in (select 'TB_ZXP' c from dual union select index_name from user_indexes where table_name='TB_ZXP');

TABLESPACE_NAME
------------------------------------------------------------------------------------------
USERS

然后，我从表创建语句中删除包含溢出子句，然后重试:

create table tb_zxp (customer_id integer ,
                     store_id integer,
                     trans_date date,
                     amt number,
                     goods_name varchar2(20),
                     rate number(8,1),
                     quantity integer,
                     constraint pk_zxp primary key (customer_id,store_id,trans_date))
                     organization index
                     tablespace indx;
 
insert into tb_zxp values (11,21,date '2021-04-10',500,'Cocacola',2,250);

commit;

这一次，索引按预期落在表空间INDX上:

select tablespace_name from user_extents where segment_name in (select 'TB_ZXP' c from dual union select index_name from user_indexes where table_name='TB_ZXP');

TABLESPACE_NAME
------------------------------------------------------------------------------------------
INDX

任何专家都可以解释为什么删除包含溢出可以为我们提供所需的结果吗？

当您有溢出子句时，溢出区域是表段，而不是索引段。它具有系统生成的名称，因此不会由user_extents上的那些查询返回。

有了这些属性:

organization index including amt 
overflow tablespace indx

表空间子句仅适用于溢出段。要说明索引的表空间，请在溢出之前放置另一个表空间子句:

organization index 
tablespace TBLSP
including amt 
overflow tablespace TBLSP

例如:

create table tb_zxp (customer_id integer ,
                     store_id integer,
                     trans_date date,
                     amt number,
                     goods_name varchar2(20),
                     rate number(8,1),
                     quantity integer,
                     constraint pk_zxp primary key (customer_id,store_id,trans_date))
                     organization index including amt overflow
                     tablespace TBLSP;

insert into tb_zxp values (11,21,date '2021-04-10',500,'Cocacola',2,250);
insert into tb_zxp values (11,25,date '2021-04-11',760,'Tower',3.8,200);
insert into tb_zxp values (24,9,date '2021-05-11',5200,'Washing machine',5200,1);

commit;

select segment_name, segment_type, tablespace_name 
from   user_extents where segment_name in (
  select object_name from user_objects
  where  created > sysdate - 1
);

SEGMENT_NAME          SEGMENT_TYPE   TABLESPACE_NAME   
SYS_IOT_OVER_151150   TABLE          TBLSP              
PK_ZXP                INDEX          USERS     

drop table tb_zxp
  cascade constraints purge;
  
create table tb_zxp (customer_id integer ,
                     store_id integer,
                     trans_date date,
                     amt number,
                     goods_name varchar2(20),
                     rate number(8,1),
                     quantity integer,
                     constraint pk_zxp primary key (customer_id,store_id,trans_date))
                     organization index 
                     tablespace TBLSP;

insert into tb_zxp values (11,21,date '2021-04-10',500,'Cocacola',2,250);
insert into tb_zxp values (11,25,date '2021-04-11',760,'Tower',3.8,200);
insert into tb_zxp values (24,9,date '2021-05-11',5200,'Washing machine',5200,1);

commit;

select segment_name, segment_type, tablespace_name 
from   user_extents where segment_name in (
  select object_name from user_objects
  where  created > sysdate - 1
);

SEGMENT_NAME   SEGMENT_TYPE   TABLESPACE_NAME   
PK_ZXP         INDEX          TBLSP 

drop table tb_zxp
  cascade constraints purge;
  
create table tb_zxp (customer_id integer ,
                     store_id integer,
                     trans_date date,
                     amt number,
                     goods_name varchar2(20),
                     rate number(8,1),
                     quantity integer,
                     constraint pk_zxp primary key (customer_id,store_id,trans_date))
                     organization index 
                     tablespace TBLSP
                     including amt overflow
                     tablespace TBLSP;

insert into tb_zxp values (11,21,date '2021-04-10',500,'Cocacola',2,250);
insert into tb_zxp values (11,25,date '2021-04-11',760,'Tower',3.8,200);
insert into tb_zxp values (24,9,date '2021-05-11',5200,'Washing machine',5200,1);

commit;

select segment_name, segment_type, tablespace_name 
from   user_extents where segment_name in (
  select object_name from user_objects
  where  created > sysdate - 1
);

SEGMENT_NAME          SEGMENT_TYPE   TABLESPACE_NAME   
SYS_IOT_OVER_151155   TABLE          TBLSP              
PK_ZXP                INDEX          TBLSP