Oracle分區(qū)之四：分區(qū)維護和管理

一 分區(qū)表的相關實驗 創(chuàng)建一個列表分區(qū)表 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 分區(qū)) 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 分區(qū)) 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 實驗五( 向分區(qū)某個分區(qū)里增加個分區(qū)列值) SQL alter table t modify partition p add values( ZQ ); Table altered 二 分區(qū)索引的相關實驗 實驗六(創(chuàng)建索引分區(qū)) 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

三 分區(qū)表交換的相關實驗 Exchange partition提供了一種方式 讓你在表與表或分區(qū)與分區(qū)之間遷移數據 注意不是將表轉換成分區(qū)或非分區(qū)的形式 而僅只是遷移表中數據(互相遷移) 由于其號稱是采用了更改數據字典的方式 因此效率最高(幾乎不涉及io操作) Exchange partition適用于所有分區(qū)格式 你可以將數據從分區(qū)表遷移到非分區(qū)表 也可以從非分區(qū)表遷移至分區(qū)表 或者從hash partition到range partition諸如此類 其語法 alter table tbname exchange partition/subpartition ptname with table tbname ; 注意 在將未分區(qū)表的數據遷移到分區(qū)表中時 可能出現ora 的錯誤 雖然可以用without validation去解決 但是此時進入分區(qū)表的數據可能不符合分區(qū)規(guī) 則 所以without validation一定要慎用 a 涉及交換的兩表之間表結構必須一致 除非附加with validation子句; b 如果是從非分區(qū)表向分區(qū)表做交換 非分區(qū)表中的數據必須符合分區(qū)表中指定分區(qū)的規(guī)則 除非附加without validation子句; c 如果從分區(qū)表向分區(qū)表做交換 被交換的分區(qū)的數據必須符合分區(qū)規(guī)則 除非附加without validation子句; d Global索引或涉及到數據改動了的global索引分區(qū)會被置為unusable 除非附加update indexes子句 注意 一旦附加了without validation子句 則表示不再驗證數據有效性 因此指定該子句時務必慎重

創(chuàng)建一個交換分區(qū)的普通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 分區(qū)有records SQL select count(*) from t partition (p ); COUNT(*) 下面是分區(qū)表和普通HEAP表交換 alter table t exchange partition p with table exchange_t including indexes without validation; 驗證下數據 和上面的P 分區(qū)數據一致 SQL select count(*) from exchange_t ; COUNT(*) SQL select distinct city from exchange_t ; CITY TJ BJ HB 四 一個實際應用的例子的相關實驗 創(chuàng)建一個分區(qū)表 只保留最近 年的財務數據 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) ); 創(chuàng)建索引 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_ 分區(qū)放入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_ 分區(qū) 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語句及注意事項 對于分區(qū)索引 不能整體進行重建 只能對單個分區(qū)進行重建(也就是物理存在的分區(qū)) 語法如下 Alter index idx_name rebuild partition index_partition_name [online nologging] Alter Index IndexName Rebuild Partition P_Name; 有子分區(qū)的本地索引 不能重建某分區(qū) 只能對每個子分區(qū)進行重建 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 ( ); 如果有子分區(qū) 且定義了子分區(qū)模板 所有的子分區(qū)會自動添加 新加分區(qū)后 該區(qū)沒有統(tǒng)計信息 全是空 如果表級不是global_satus 則表級的統(tǒng)計信息也會空 新加分區(qū)后 如果表級統(tǒng)計是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可對有子分區(qū)的分區(qū)進行 ALTER TABLE TEST truncate Partition P_ ; ALTER TABLE TEST Drop Partition P_ ; 它們會導致globl index的某些分區(qū)不可用 必須這樣做 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 有子分區(qū)的分區(qū)不能move 只能move每個子分區(qū)(也就是物理分區(qū)) 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]; 可以對有子分區(qū)的分區(qū)進行 自動split子分區(qū) 由于rowid變了 新分區(qū)和global index都變?yōu)閡nusable alter table t merge partitions p _ p _ into partition p ; 合并range分區(qū) ALTER TABLE Test_Tab Merge Partitions P_ P_ Into Partition P_ [Update [global] Indexes]; 該分區(qū)有子分區(qū) 有子分區(qū) 也可以單獨合并子分區(qū)merge subpartition

lishixinzhi/Article/program/Oracle/201311/19037

Oracle分區(qū)表怎么建唯

1、一般分區(qū)表都會很大，所以可以先創(chuàng)建表空間，為了讓分區(qū)表存放到單獨的表空間，否則默認會存放到USERS表空間

2、創(chuàng)建TABLESPACE TS1：

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

3、創(chuàng)建分區(qū)表，需要確定按什么分區(qū)，比如按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、繼續(xù)增加分區(qū)

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、查詢表及分區(qū)數量

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

oracle具體怎么分區(qū)

你說的是oracle分區(qū)表吧，一般數據量大的表格采用到此功能，一個表上千萬數據以上。以下例子，分區(qū)創(chuàng)建需要有條件，如時間，語法可詳見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創(chuàng)建分區(qū)表操作

1、創(chuàng)建語句

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、添加分區(qū)

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

3、清除分區(qū)數據

alter table p trunc partition p1;

4、刪除分區(qū)

alter table p drop partition p1;

oracle如何創(chuàng)建組合分區(qū)

oracle組合分區(qū)

由于Interval分區(qū)是針對range的，11g-12.1版本，目前只有Interval—*一共3種Interval的復合分區(qū)

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')))

注意：每個分區(qū)有2個子分區(qū)，分區(qū)的子分區(qū)需要手動添加

Interval分區(qū)目前測試，只支持range先分區(qū)的方式

range-hash方法：每個分區(qū)10個子hash分區(qū)(可以指定每個子分區(qū)到單獨的表空間)

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')))

注意：這種方式可以實現完全自動的分區(qū)，非常適合自增長特性的10位數以上的大表

hash分區(qū)最好是2的次方個，不然數據會分布不均

range-range方法:子分區(qū)需要手動添加，這種分區(qū)需要一般比較少見

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分區(qū)怎么做的 oracle如何加分區(qū)
網址分享：http://chinadenli.net/article44/hgcihe.html

成都網站建設公司_創(chuàng)新互聯(lián)，為您提供、虛擬主機、面包屑導航、商城網站、網站排名、定制開發(fā)

廣告

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)