oracle三种分区方式,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.