oracle11g 分区表的5种类型,oracle11g中一个新的分区方式

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

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

今天碰到一个问题：

Hi Ming,

As discussed with you over chatwe'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_idnumber

, time_iddate

)

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

indexwith 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@]