Oracle分区之四：分区维护和管理

一 分区表的相关实验 创建一个列表分区表 create table t (id number city varchar ( )) partition by list(city) ( partition p values ( SH JS ZJ ) partition p values ( BJ TJ HB ) partition p values ( GZ SZ ) partition p_others values (default) ); create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i SH ; end loop; end; / exec proc create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i JS ; end loop; end; / exec proc create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i ZJ ; end loop; end; / exec proc create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i BJ ; end loop; end; / exec proc create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i TJ ; end loop; end; / exec proc create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i GZ ; end loop; end; / exec proc create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i HB ; end loop; end; / exec proc create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i SZ ; end loop; end; / exec proc create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i AH ; end loop; end; / exec proc SQL SET linesize SQL select TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT from dba_tab_partitions where table_name= T ; TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT T HR P T HR P T HR P T HR P_OTHERS

10年积累的成都网站制作、成都做网站经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先做网站设计后付款的网站建设流程，更有交口免费网站建设让你可以放心的选择与我们合作。

实验一(SPLIT 分区) alter table t split partition p values ( JS ) into (partition p _ partition p _ ); SQL select TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT from dba_tab_partitions where table_name= T ; TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT T HR P _ T HR P _ T HR P T HR P T HR P_OTHERS

实验二(merge 分区) alter table t merge partitions p _ p _ into partition p ; SQL select TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT from dba_tab_partitions where table_name= T ; TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT T HR P T HR P T HR P T HR P_OTHERS 实验三 alter table t split partition p values ( BJ TJ ) into (partition p _ partition p _ ); SQL select TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT from dba_tab_partitions where table_name= T ; TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT T HR P T HR P _ T HR P _ T HR P T HR P_OTHERS

实验四 alter table t merge partitions p _ p _ into partition p ; SQL select TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT from dba_tab_partitions where table_name= T ; TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT T HR P T HR P T HR P T HR P_OTHERS 实验五( 向分区某个分区里增加个分区列值) SQL alter table t modify partition p add values( ZQ ); Table altered 二 分区索引的相关实验 实验六(创建索引分区) create index idx_t on t (id) global partition by range(id) ( partition p values less than ( ) partition p values less than (maxvalue) ); drop index idx_ create index idx_t on t (id) global partition by hash(id) partitions ; create table tt (id number createdate date) partition by range(createdate) subpartition by hash(id) subpartitions ( partition p values less than (to_date( yyyy mm dd )) partition p values less than (to_date( yyyy mm dd )) ); create table tt (id number name varchar ( )) partition by range(name) ( partition p values less than ( h ) partition p values less than ( o ) ); create index idx_tt on tt (id) local; drop indexe idx_tt ; create index idx_tt on tt (id) global partition by range(id) ( partition p values less than ( ) partition p values less than (maxvalue) ); SQL SET LINESIZE SQL select INDEX_OWNER INDEX_NAME PARTITION_NAME FROM dba_Ind_Partitions where index_name= IDX_TT ; INDEX_OWNER INDEX_NAME PARTITION_NAME HR IDX_TT P HR IDX_TT P

alter index idx_tt split partition p at ( ) into (partition p partition p_max); SQL select INDEX_OWNER INDEX_NAME PARTITION_NAME FROM dba_Ind_Partitions where index_name= IDX_TT ; INDEX_OWNER INDEX_NAME PARTITION_NAME HR IDX_TT P HR IDX_TT P HR IDX_TT P_MAX

三 分区表交换的相关实验 Exchange partition提供了一种方式 让你在表与表或分区与分区之间迁移数据 注意不是将表转换成分区或非分区的形式 而仅只是迁移表中数据(互相迁移) 由于其号称是采用了更改数据字典的方式 因此效率最高(几乎不涉及io操作) Exchange partition适用于所有分区格式 你可以将数据从分区表迁移到非分区表 也可以从非分区表迁移至分区表 或者从hash partition到range partition诸如此类 其语法 alter table tbname exchange partition/subpartition ptname with table tbname ; 注意 在将未分区表的数据迁移到分区表中时 可能出现ora 的错误 虽然可以用without validation去解决 但是此时进入分区表的数据可能不符合分区规 则 所以without validation一定要慎用 a 涉及交换的两表之间表结构必须一致 除非附加with validation子句; b 如果是从非分区表向分区表做交换 非分区表中的数据必须符合分区表中指定分区的规则 除非附加without validation子句; c 如果从分区表向分区表做交换 被交换的分区的数据必须符合分区规则 除非附加without validation子句; d Global索引或涉及到数据改动了的global索引分区会被置为unusable 除非附加update indexes子句 注意 一旦附加了without validation子句 则表示不再验证数据有效性 因此指定该子句时务必慎重

创建一个交换分区的普通heap表 SQL create table exchange_t (id number city varchar ( )); Table created SQL select distinct city from t partition (p ); CITY TJ BJ HB 查看下P 分区有records SQL select count(*) from t partition (p ); COUNT(*) 下面是分区表和普通HEAP表交换 alter table t exchange partition p with table exchange_t including indexes without validation; 验证下数据 和上面的P 分区数据一致 SQL select count(*) from exchange_t ; COUNT(*) SQL select distinct city from exchange_t ; CITY TJ BJ HB 四 一个实际应用的例子的相关实验 创建一个分区表 只保留最近 年的财务数据 create table ware(wareyear varchar ( ) id number) partition by range (wareyear) ( partition p_ values less than( ) partition p_ values less than( ) partition p_max values less than(maxvalue) ); 创建索引 create index idx_ware_id on ware(id) global partition by range(id) ( partition p_id_ values less than( ) partition p_id_max values less than(maxvalue) ); create index idx_ware_wareyear on ware(wareyear) local; 插入测试数据 insert into ware select object_id from dba_objects; insert into ware select object_id from dba_objects; mit; 年终 归档最早的数据 并加入新财年的数据 create table ware_ (wareyear varchar ( ) id number); create index idx_ware_ on ware_ (wareyear); insert into ware_ select object_id from dba_objects; mit; alter table ware split partition p_max at ( ) into (partition p_ partition p_max); 将p_ 分区放入ware_ 表里 alter table ware exchange partition p_ with table ware_ including indexes without validation; create table ware_ (wareyear varchar ( ) id number); create index idx_ware_ on ware_ (wareyear); alter table ware exchange partition p_ with table ware_ including indexes without validation; 删除p_ 分区 alter table ware drop partition p_ ; 导出做归档 [oracle@even admin]$ exp hr/hr@test file=/home/oracle/ware_ dmp tables=ware_ press=n Export: Release Production on Fri Jan : : Copyright (c) Oracle All rights reserved

Connected to: Oracle Database g Enterprise Edition Release Production With the Partitioning Oracle Label Security OLAP and Data Mining options Export done in US ASCII character set and AL UTF NCHAR character set server uses AL UTF character set (possible charset conversion) About to export specified tables via Conventional Path exporting table WARE_ rows exported Export terminated successfully without warnings 然后删除表 drop table ware_ ;

五 表和索引的维护的常见SQL语句及注意事项 对于分区索引 不能整体进行重建 只能对单个分区进行重建(也就是物理存在的分区) 语法如下 Alter index idx_name rebuild partition index_partition_name [online nologging] Alter Index IndexName Rebuild Partition P_Name; 有子分区的本地索引 不能重建某分区 只能对每个子分区进行重建 Alter Index Index_Name Rebuild subPartition P_Sub_Name; 脚本 重建所有unUsable的索引 Select alter index || Index_Name || rebuild; From User_Indexes Where Status = UNUSABLE union Select alter index || Index_Name || rebuild Partition ||Partition_Name || ; From User_Ind_Partitions Where Status = UNUSABLE union Select alter index || Index_Name || rebuild subPartition ||subPartition_Name || ; From User_Ind_subPartitions Where Status = UNUSABLE ;

add parttion Alter Table TestTab Add Partition P Values Less Than ( ); 如果有子分区 且定义了子分区模板 所有的子分区会自动添加 新加分区后 该区没有统计信息 全是空 如果表级不是global_satus 则表级的统计信息也会空 新加分区后 如果表级统计是global_satus 还会出现out of range的问题（CBO估算的选择率很低） 解决 问题的方法是 copy_table_stats exec dbms_stats copy_table_stats(user tabname = TEST_TAB srcpartname = P_ dstpartname = P_ );

tuncate and drop partition truncate和drop可对有子分区的分区进行 ALTER TABLE TEST truncate Partition P_ ; ALTER TABLE TEST Drop Partition P_ ; 它们会导致globl index的某些分区不可用 必须这样做 ALTER TABLE TEST truncate Partition P_ update indexes; ALTER TABLE TEST truncate Partition P_ update global indexes; ALTER TABLE TEST Drop Partition P_ update indexes; ALTER TABLE TEST Drop Partition P_ update global indexes;

move partition 有子分区的分区不能move 只能move每个子分区(也就是物理分区) Alter Table TEST Move Partition P_ ; 由于rowid变了 会导致所有相关索引unusable 必须这样做 Alter Table TEST Move subPartition P_ _P update indexes; Alter Table TEST Move subPartition P_ _P update global indexes; Local Index没有更新 split partion 语法 alter table table_name split partition partition_name at (value) into (partition partition_name partition partition_name) [update [global] indexes]; 可以对有子分区的分区进行 自动split子分区 由于rowid变了 新分区和global index都变为unusable alter table t merge partitions p _ p _ into partition p ; 合并range分区 ALTER TABLE Test_Tab Merge Partitions P_ P_ Into Partition P_ [Update [global] Indexes]; 该分区有子分区 有子分区 也可以单独合并子分区merge subpartition

lishixinzhi/Article/program/Oracle/201311/19037

Oracle分区表怎么建唯

1、一般分区表都会很大，所以可以先创建表空间，为了让分区表存放到单独的表空间，否则默认会存放到USERS表空间

2、创建TABLESPACE TS1：

CREATE TABLESPACE TS1 DATAFILE '/data1/oracle/test.dbf' SIZE 512M AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED；

3、创建分区表，需要确定按什么分区，比如按id或按时间段：

CREATE TABLE test_201602

(

ID NUMBER(10) NOT NULL,

CREATE_TIME DATE,

)

TABLESPACE TS1

PARTITION BY LIST (ID)

(PARTITION PT_1001 VALUES (1001) TABLESPACE TS1)；

4、继续增加分区

ALTER TABLE test_201602 ADD PARTITION "PT_1003" VALUES (1003) LOGGING NOCOMPRESS;

ALTER TABLE test_201602 ADD PARTITION "PT_1004" VALUES (1004) LOGGING NOCOMPRESS;

5、查询表及分区数量

select TABLE_NAME,PARTITION_COUNT,DEF_TABLESPACE_NAME from USER_PART_TABLES order by DEF_TABLESPACE_NAME;

oracle具体怎么分区

你说的是oracle分区表吧，一般数据量大的表格采用到此功能，一个表上千万数据以上。以下例子，分区创建需要有条件，如时间，语法可详见metalink

CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE)

PARTITION BY RANGE (TIME)

(PARTITION P1 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),

PARTITION P2 VALUES LESS THAN (MAXVALUE));

Oracle创建分区表操作

1、创建语句

create table p(id number)

partition by range(id)

(partition p1 values less than(100) tablespace t1,

partition p2 values less than(200) tablespace t2,

partition p3 values less than(300) tablespace t3);

2、添加分区

alter table p add partition p4 values less than (400) tablespace t4;

3、清除分区数据

alter table p trunc partition p1;

4、删除分区

alter table p drop partition p1;

oracle如何创建组合分区

oracle组合分区

由于Interval分区是针对range的，11g-12.1版本，目前只有Interval—*一共3种Interval的复合分区

range-list方法：

partition by range (updatetime)

interval (numtodsinterval(1,'day'))

subpartition by list(PSNCODE)

SUBPARTITION TEMPLATE(

SUBPARTITION REGION_1 values('038716'),

SUBPARTITION REGION_2 values('270719') )

(partition p1 values less than(to_date('2014-05-01','yyyy-mm-dd')))

注意：每个分区有2个子分区，分区的子分区需要手动添加

Interval分区目前测试，只支持range先分区的方式

range-hash方法：每个分区10个子hash分区(可以指定每个子分区到单独的表空间)

partition by range (updatetime)

interval(numtodsinterval(1,'day'))

subpartition by hash(PSNCODE)

SUBPARTITION template

( SUBPARTITION p1 TABLESPACE ts1

, SUBPARTITION p2 TABLESPACE ts2

, SUBPARTITION p3 TABLESPACE ts3

, SUBPARTITION P4 TABLESPACE ts4

)

(partition p1 values less than(to_date('2014-05-01','yyyy-mm-dd')))

注意：这种方式可以实现完全自动的分区，非常适合自增长特性的10位数以上的大表

hash分区最好是2的次方个，不然数据会分布不均

range-range方法:子分区需要手动添加，这种分区需要一般比较少见

PARTITION BY RANGE (time_id)

INTERVAL (NUMTODSINTERVAL(1,'DAY'))

SUBPARTITION BY RANGE(amount_sold)

SUBPARTITION TEMPLATE

( SUBPARTITION p_low VALUES LESS THAN (1000)

, SUBPARTITION p_medium VALUES LESS THAN (4000)

, SUBPARTITION p_high VALUES LESS THAN (8000)

, SUBPARTITION p_ultimate VALUES LESS THAN (maxvalue)

)

(PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy')) )

本文标题：oracle分区怎么做的 oracle如何加分区
当前网址：http://chengdu.cdxwcx.cn/article/hgcihe.html