SYmaxskiВот поэтому я спросил тут про то, что есть в доке, но не работает.
Так тебе и ответили. Для поля LOB (LOB/CLOB/XMLTYPE...) можно указывать и соответственно менять табличное пространство. Для поля ANYDATA табличное пространство указать нельзя ....
SY.
Похоже, тут сказочники завелись.
Создаю такую же таблицу с другим табличным пространством для LOB partitions (USERS и USERS2):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
CREATE TABLE "DATA"."HIST_FLD_HISTORY2"
( "ID_HIST" NUMBER(*,0) NOT NULL ENABLE,
"ID_FIELD" NUMBER(*,0) NOT NULL ENABLE,
"CHANGE_TIME" DATE NOT NULL ENABLE,
"ID_OBJ" NUMBER(*,0) NOT NULL ENABLE,
"ID_SERVER" NUMBER(*,0),
"OLD_VALUE" "SYS"."ANYDATA" ,
"NEW_VALUE" "SYS"."ANYDATA" ,
"OLDVAL_STR" VARCHAR2(4000) GENERATED ALWAYS AS ("DATA"."GETVARCHAR2FROMANYDATA"("OLD_VALUE")) VIRTUAL VISIBLE ,
"NEWVAL_STR" VARCHAR2(4000) GENERATED ALWAYS AS ("DATA"."GETVARCHAR2FROMANYDATA"("NEW_VALUE")) VIRTUAL VISIBLE ,
"OLDVAL_NUM" VARCHAR2(4000) GENERATED ALWAYS AS ("DATA"."GETNUMFROMANYDATA"("OLD_VALUE")) VIRTUAL VISIBLE ,
"NEWVAL_NUM" VARCHAR2(4000) GENERATED ALWAYS AS ("DATA"."GETNUMFROMANYDATA"("NEW_VALUE")) VIRTUAL VISIBLE ,
CONSTRAINT "PK_HIST_FLD_HISTORY2" PRIMARY KEY ("ID_HIST")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE, FOREIGN KEY ("ID_FIELD") REFERENCES "DATA"."HIST_FIELDS" ("ID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 65536 BUFFER_POOL KEEP FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
OPAQUE TYPE "OLD_VALUE" STORE AS BASICFILE LOB ( ENABLE STORAGE IN ROW CHUNK 8192 RETENTION CACHE STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
OPAQUE TYPE "NEW_VALUE" STORE AS BASICFILE LOB ( ENABLE STORAGE IN ROW CHUNK 8192 RETENTION CACHE STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
PARTITION BY RANGE ("CHANGE_TIME") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION "P00" VALUES LESS THAN (TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"
LOB ("OLD_VALUE") STORE AS BASICFILE ( tablespace users2 ENABLE STORAGE IN ROW CHUNK 8192 RETENTION CACHE STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
LOB ("NEW_VALUE") STORE AS BASICFILE ( tablespace users2 ENABLE STORAGE IN ROW CHUNK 8192 RETENTION CACHE STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) );
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
15:46:16 SQL> @create_table_hist2.sql
Table created.
Elapsed: 00:00:00.04
15:46:31 SQL> select LOB_NAME, COLUMN_NAME, PARTITION_NAME, LOB_PARTITION_NAME, TABLESPACE_NAME , INITIAL_EXTENT, NEXT_EXTENT from dba_lob_partitions where table_name = 'HIST_FLD_HISTORY2';
LOB_NAME COLUMN_NAME PARTITION_NAME LOB_PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
INITIAL_EXTENT NEXT_EXTENT
---------------------------------------- ----------------------------------------
SYS_LOB0001169844C00006$$ OLD_VALUE P00 SYS_LOB_P172044 USERS2
65536 65536
SYS_LOB0001169844C00007$$ NEW_VALUE P00 SYS_LOB_P172046 USERS2
65536 65536
Elapsed: 00:00:00.00
15:46:35 SQL> select tablespace_name from dba_segments where segment_name = 'HIST_FLD_HISTORY2';
TABLESPACE_NAME
------------------------------
USERS
Elapsed: 00:00:00.09
15:46:50 SQL> select tablespace_name from dba_segments where segment_name = 'SYS_LOB0001169844C00006$$';
TABLESPACE_NAME
------------------------------
USERS2
Elapsed: 00:00:00.08
15:47:13 SQL> alter table data.HIST_FLD_HISTORY2 move partition P00 lob ("OLD_VALUE") store as (tablespace users );
alter table data.HIST_FLD_HISTORY2 move partition P00 lob ("OLD_VALUE") store as (tablespace users )
*
ERROR at line 1:
ORA-00904: "OLD_VALUE": invalid identifier
Elapsed: 00:00:00.02
Вставляю строки из первой таблицы.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
15:48:04 SQL> insert into data.HIST_FLD_HISTORY2 ( ID_HIST, ID_FIELD, CHANGE_TIME,ID_OBJ,ID_SERVER,OLD_VALUE,NEW_VALUE) select ID_HIST,ID_FIELD,CHANGE_TIME,ID_OBJ,ID_SERVER,OLD_VALUE,NEW_VALUE from data.HIST_FLD_HISTORY partition (SYS_P171053);
10408 rows created.
Elapsed: 00:00:00.26
15:48:26 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
15:48:31 SQL> select LOB_NAME, COLUMN_NAME, PARTITION_NAME, LOB_PARTITION_NAME, TABLESPACE_NAME , INITIAL_EXTENT, NEXT_EXTENT from dba_lob_partitions where table_name = 'HIST_FLD_HISTORY2';
LOB_NAME COLUMN_NAME PARTITION_NAME LOB_PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
INITIAL_EXTENT NEXT_EXTENT
---------------------------------------- ----------------------------------------
SYS_LOB0001169844C00007$$ NEW_VALUE SYS_P172048 SYS_LOB_P172051 USERS
8388608 1048576
SYS_LOB0001169844C00006$$ OLD_VALUE P00 SYS_LOB_P172044 USERS2
65536 65536
SYS_LOB0001169844C00007$$ NEW_VALUE P00 SYS_LOB_P172046 USERS2
65536 65536
SYS_LOB0001169844C00006$$ OLD_VALUE SYS_P172048 SYS_LOB_P172049 USERS
8388608 1048576
Elapsed: 00:00:00.00
Пытаюсь сменить ТС и получаю ошибку:
1.
2.
3.
4.
5.
6.
7.
8.
9.
15:48:36 SQL> alter table data.HIST_FLD_HISTORY2 move partition SYS_P172048 lob ("OLD_VALUE") store as (tablespace users2 );
alter table data.HIST_FLD_HISTORY2 move partition SYS_P172048 lob ("OLD_VALUE") store as (tablespace users2 )
*
ERROR at line 1:
ORA-00904: "OLD_VALUE": invalid identifier
Elapsed: 00:00:00.02
При этом если СLOB, то все перемещается.
Я это делал еще в 2005 году без проблем.
Единственное, где нашел про ограничения для отдельных типов данных, тут:
https://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_tables.htm#ADLOB510
"Relational and object partitioned index-organized tables (partitioned by range, hash, or list) can hold LOBs stored as follows; however, partition maintenance operations, such as MOVE, SPLIT, and MERGE are not supported with:
VARRAY datatypes stored as LOB datatypes
Abstract datatypes with LOB attributes
Nested tables with LOB types"
но это не мой случай.
может dbms_redefinition?
