oracle exchange partit…

ORACLE限制每个分区表最大分区数量在64000个

oracle exchange partition
--创建分区表 t1
create tablespace tsa datafile 'c:\tsa.dbf' size 10m;
create tablespace tsa_p0 datafile 'c:\tsa_p0.dbf' size 40m;
create tablespace tsa_p1 datafile 'c:\tsa_p1.dbf' size 40m;
create tablespace tsa_p2 datafile 'c:\tsa_p2.dbf' size 40m; 
--drop table t1;
--truncate table t1;
create table t1 partition by list (object_type)
(partition p1 values ('SYNONYM') tablespace tsa_p1,
partition p2 values ('JAVA CLASS') tablespace tsa_p2,
partition p0 values (default) tablespace tsa_p0
) as select * from dba_objects where 1=2;

 

--创建普通表 t2
create tablespace tsb datafile 'c:\tsb01.dbf' size 100m;
--drop table t2;
create table t2 tablespace tsb as select * from dba_objects where 1=2;
--truncate table t2;
declare
  i number;
begin

-- 灌入数据 30万条 34M
  for i in 1 .. 15 loop
    insert into t2 select * from dba_objects where object_type='SYNONYM';
      commit;
  end loop;
end;

/

 

-- 检查当前使用情况

select owner,segment_name,partition_name,segment_type,tablespace_name,bytes

from dba_segments where owner='A' order by 1,2,3 ;

--交换分区

alter table t1 exchange partition p1 with table t2;

 

--再查看

select owner,segment_name,partition_name,segment_type,tablespace_name,bytes

from dba_segments where owner='A' order by 1,2,3 ;

--再执行一次exchange 又会回复原先的表空间使用情况。