oracle11g中一个新的分区方式

interval分区，和范围分区类似，不过是自动按照范围分配。

oracle中原有的几个分区：hash分区、范围分区、列表分区和混合分区。

今天碰到一个问题：

Hi Ming,

As discussed with you over chat we're testing an Oracle 11g feature called Interval Partitioning. The table partitions are fine and seems to be working as expected, the issue is in the local index when new data is added after the index is created. I've provided some scripts to demonstrate:

-- create sample table on daily interval-hash partition and spread to 4 tablespaces create table test_int_part

( cust_id number

, time_id date

)

partition by range (time_id) interval (numtodsinterval(1, 'DAY')) store in (BIGFILE_DATA01, BIGFILE_DATA02, BIGFILE_DATA03, BIGFILE_DATA04) subpartition by hash (cust_id) subpartitions 4 store in (BIGFILE_DATA01, BIGFILE_DATA02, BIGFILE_DATA03, BIGFILE_DATA04) ( partition oct31_2011 values less than (to_date('01-NOV-2011 00:00:00', 'DD-MON-YYYY HH24:MI:SS')) tablespace BIGFILE_DATA01 ) parallel;

-- insert sample data

declare

dt date := to_date('31-OCT-2011 03:23:10', 'DD-MON-YYYY HH24:MI:SS'); begin

for i in 1 .. 7 loop

for j in 1 .. 10000 loop

insert into test_int_part (time_id, cust_id) values (dt, trunc(dbms_random.value(1,9)) * 100);

end loop;

commit;

dt := dt + 1;

end loop;

end;

/

-- All good at this point, partitions seems to be going to the right tbs create index test_int_part_idx on test_int_part (time_id, cust_id) local store in (BIGFILE_INDEX01, BIGFILE_INDEX02, BIGFILE_INDEX03, BIGFILE_INDEX04)

select * from user_tab_partitions where table_name like 'TEST_INT_PART';

select * from user_tab_subpartitions where table_name like 'TEST_INT_PART';

select * from user_ind_partitions where index_name like 'TEST_INT_PART_IDX';

select * from user_ind_subpartitions where index_name like 'TEST_INT_PART_IDX';

select min(time_id), max(time_id) from test_int_part;

-- ORA-14523: Cannot co-locate [sub]partition of local index with table [sub]partition because local index block size [16384] does not match table block size [8192] insert into test_int_part (time_id, cust_id) values (to_date('07-NOV-2011 03:23:10', 'DD-MON-YYYY HH24:MI:SS'), 200);

I suspect the new index entry is being added to the data tablespace, not in the index tablespace.

解决方法：

我没有自己试，就把解决方法给他发过去了。

ORA-14523: Cannot co-locate [sub]partition of string string with table [sub]partition because string block size [string] does not match table block size [string]

Cause: A DDL statement was issued that would require a partition/subpartition of a local index/LOB column to be co-located with the corresponding partition/subpartition of the base table. However, this is not possible because the block sizes of the table and the LOB column/local index are different.

Action: Either (1) Specify an object-level default tablespace (or partition-level default tablespace for the appropriate partition, if composite partitioning is used) for the partitioned local index/LOB column and then retry the DDL command, OR (2) Ensure that tablespaces of the correct block size are specified for all new partitions/subpartitions being created. Also ensure that neither of TABLESPACE DEFAULT and STORE IN (DEFAULT) is specified for a local index whose block size does not match that of the base table.

[@more@]

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

转载于:http://blog.itpub.net/41451/viewspace-1056647/