我现在遇到一个奇怪的问题
我把表test 从A表空间move到B 表空间,但是我在往表中插入符合新分区规则的数据时 提示我找不到以前的A表空间。
我已经修改改了分区表的属性:alter table test modify default attributes tablespace system;
以下是真实的数据
SQL> alter table sdb_retail_sale modify default attributes tablespace DTCODSPRD01_DATA_TS_00;
Table altered
SQL> select partition_name,tablespace_name from user_tab_partitions where table_name='SDB_RETAIL_SALE';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P1 DTCODSPRD01_DATA_TS_00
SYS_P102 DTCODSPRD01_DATA_TS_00
SYS_P10689 DTCODSPRD01_DATA_TS_00
SYS_P11709 DTCODSPRD01_DATA_TS_00
SYS_P12409 DTCODSPRD01_DATA_TS_00
SYS_P13169 DTCODSPRD01_DATA_TS_00
SYS_P2065 DTCODSPRD01_DATA_TS_00
SYS_P242 DTCODSPRD01_DATA_TS_00
SYS_P2765 DTCODSPRD01_DATA_TS_00
SYS_P3330 DTCODSPRD01_DATA_TS_00
SYS_P3829 DTCODSPRD01_DATA_TS_00
SYS_P4529 DTCODSPRD01_DATA_TS_00
SYS_P5229 DTCODSPRD01_DATA_TS_00
SYS_P62 DTCODSPRD01_DATA_TS_00
SYS_P6229 DTCODSPRD01_DATA_TS_00
SYS_P6909 DTCODSPRD01_DATA_TS_00
SYS_P7649 DTCODSPRD01_DATA_TS_00
SYS_P8349 DTCODSPRD01_DATA_TS_00
SYS_P9109 DTCODSPRD01_DATA_TS_00
SYS_P922 DTCODSPRD01_DATA_TS_00
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_P93894 DTCODSPRD02_DATA_TS_01
SYS_P9849 DTCODSPRD01_DATA_TS_00
22 rows selected
SQL> alter table sdb_retail_sale modify default attributes tablespace DTCODSPRD01_DATA_TS_00;
Table altered
SQL> insert into sdb_retail_sale_f(retail_id,etl_date ) values('82EB49E90AA330CCE0430A01006530CC11',sysdate+32);
insert into sdb_retail_sale_f(retail_id,etl_date ) values('82EB49E90AA330CCE0430A01006530CC11',sysdate+32)
ORA-00942: table or view does not exist
SQL> insert into sdb_retail_sale(retail_id,etl_date ) values('82EB49E90AA330CCE0430A01006530CC11',sysdate+32);
1 row inserted
SQL> select partition_name,tablespace_name from user_tab_partitions where table_name='SDB_RETAIL_SALE' order by 1;
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P1 DTCODSPRD01_DATA_TS_00
SYS_P102 DTCODSPRD01_DATA_TS_00
SYS_P10689 DTCODSPRD01_DATA_TS_00
SYS_P11709 DTCODSPRD01_DATA_TS_00
SYS_P12409 DTCODSPRD01_DATA_TS_00
SYS_P13169 DTCODSPRD01_DATA_TS_00
SYS_P2065 DTCODSPRD01_DATA_TS_00
SYS_P242 DTCODSPRD01_DATA_TS_00
SYS_P2765 DTCODSPRD01_DATA_TS_00
SYS_P3330 DTCODSPRD01_DATA_TS_00
SYS_P3829 DTCODSPRD01_DATA_TS_00
SYS_P4529 DTCODSPRD01_DATA_TS_00
SYS_P5229 DTCODSPRD01_DATA_TS_00
SYS_P62 DTCODSPRD01_DATA_TS_00
SYS_P6229 DTCODSPRD01_DATA_TS_00
SYS_P6909 DTCODSPRD01_DATA_TS_00
SYS_P7649 DTCODSPRD01_DATA_TS_00
SYS_P8349 DTCODSPRD01_DATA_TS_00
SYS_P9109 DTCODSPRD01_DATA_TS_00
SYS_P922 DTCODSPRD01_DATA_TS_00
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_P93894 DTCODSPRD02_DATA_TS_01
SYS_P93895 DTCODSPRD02_DATA_TS_01
SYS_P9849 DTCODSPRD01_DATA_TS_00
23 rows selected
SQL>
可以看到,我新添加的分区 又到了以前的表空间当中了,如果把以前的表空间删除掉,就会报找不到该表空间。