oracle 分區表放入不同的資料表空間__oracle

oracle 分區表放入不同的資料表空間
引用：http://hougbin.iteye.com/blog/1470505 部落格分類：  資料庫  

  查詢分區:Select *From user_extents WHERE partition_name='分區名';

1）建立資料表空間

create tablespace HRPM0

datafile '/oradata/misdb/HRPM0.DBF' size 5m autoextend on next 10m maxsize unlimited

2）刪除資料表空間（同時把資料檔案也刪除）

DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

   如果不想刪除資料檔案：

           Drop tablespace tablespace_name;

3) 修改資料表空間大小

alter database datafile '/path/NADDate05.dbf' resize 100M

4)添加資料檔案（在建立資料表空間時，若是約束了資料表空間的大小，那麼一段時間後，這個資料表空間就會被裝滿，無法再添加其他對象。則需要給資料表空間添加資料檔案）:

Alter tablespace tablespace_name add datafile’ '/path/NADDate06.dbf’ size 100M;

4) 備忘：

4.1).--.禁止undo tablespace自動成長

alter database datafile 'full_path\undotbs01.dbf' autoextend off;

4.2).-- 建立一個新的小空間的undo tablespace

create undo tablespace undotBS2 datafile 'full_path\UNDOTBS02.DBF' size 100m;

4.3).-- 設定新的資料表空間為系統undo_tablespace

alter system set undo_tablespace=undotBS2;

4.4).-- Drop 舊的資料表空間

drop tablespace undotbs1 including contents;

4.5).--查看所有資料表空間的情況

select * from dba_tablespaces

5)查到一個最好用的表：dict

5.1)select *from dict where table_name like '%PART%'

5.2)ALL_TAB_PARTITIONS:可以查出表所對應的分區內容；

5.3)dab_tab_partitons ：與上2);

5.4)dba_ind_partitons:查詢分區的索引；

5.5)子分區也是一樣的(dba_tab_subpartitons,dba_ind_partitons)

一、使用分區的優點：

    1、增強可用性：如果表的某個分區出現故障，表在其他分區的資料仍然可用；

    2、維護方便：如果表的某個分區出現故障，需要修複資料，只修複該分區即可；

    3、均衡I/O：可以把不同的分區映射到磁碟以平衡I/O，改善整個系統效能；

4、改善查詢效能：對分區對象的查詢可以僅搜尋自己關心的分區，提高檢索速度。

二、Oracle資料庫提供對錶或索引的分區方法有幾種（收集到四種）：

        1、定界分割

        2、列表分區

        3、Hash分區（散列分區）

        4、複合分區

三、詳描述分區執行個體：

    1）下面將以執行個體的方式分別對這三種分區方法來說明分區表的使用。為了測試方便，我們先建三個資料表空間。

create tablespace dinya_space01 datafile 'C:\資料表空間\dinya01.dbf' size 5M; createtablespace dinya_space02 datafile 'C:\資料表空間\dinya02.dbf'SIZE 5M; create tablespacedinya_space03 datafile 'C:\資料表空間\dinya03.dbf' SIZE 5M;

select * from user_tablespaces

 

　　　　　　　　　　　　　　　　　　　<資料表空間->三個>

1.1）定界分割

    定界分割就是對資料表中的某個值的範圍進行分區，根據某個值的範圍，決定將該資料存放區在哪個分區上。如根據序號分區，根據業務記錄的建立日期進行分區等。

    需求描述：有一個物料交易表，表名：material_transactions。該表將來可能有千萬級的資料記錄數。要求在建該表的時候使用分區表。這時候我們可以使用序號分區三個區，每個區中預計儲存三千萬的資料，也可以使用日期分區，如每五年的資料存放區在一個分區上。

根據交易記錄的序號分區建表：----為了測試需要做以下修改；

create table dinya_test

   (

        transaction_id number primary key,

        item_id number(8) not null,

        item_description varchar2(300),

        transaction_date date not null

    )

    partition by range (transaction_id)

    (

       partition part_01 values less than(2) tablespace dinya_space01,-----2條以下的交易在此分區上：part_01

      partition part_02 values less than(3) tablespace dinya_space02,-----等於+大於2而小於3的交易在此分區：part_02

      partition part_03 values less than(maxvalue) tablespace dinya_space03----大於3的交易在此分區：part_03

-----------------以上在pl/sql測試成功；

---------------- 以下沒有在pl/sql測試。

根據交易日期分區建表：

 

SQL> create table dinya_test

   (

        transaction_id number primary key,

        item_id number(8) not null,

       item_description varchar2(300),

       transaction_date date not null  

 )

    partition by range (transaction_date)

    (

   partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')) tablespace dinya_space01,

   partition part_02 values less than(to_date('2010-01-01','yyyy-mm-dd')) tablespace dinya_space02,

 partition part_03 values less than(maxvalue) tablespace dinya_space03

 );

這樣我們就分別建了以交易序號和交易日期來分區的分區表。每次插入資料的時候，系統將根據指定的欄位的值來自動將記錄儲存到制定的分區（資料表空間）中。

當然，我們還可以根據需求，使用兩個欄位的範圍分布來分區，如partition by range ( transaction_id ,transaction_date),分區條件中的值也做相應的改變，請讀者自行測試。

---------------------------------以上沒有在pl/sql測試。

1.2) 定界分割建立成功之後的相關操作測試；

 a)向表添加測試資料：

insert into dinya_test values(1,12,'BOOKS',sysdate);

insert into dinya_test values(2,12, 'BOOKS',sysdate+30);

insert into dinya_test values(3,12, 'BOOKS',to_date('2006-05-30','yyyy-mm-dd'));

insert into dinya_test values(4,12, 'BOOKS',to_date('2007-06-23','yyyy-mm-dd'));

insert into dinya_test values(5,12, 'BOOKS',to_date('2011-02-26','yyyy-mm-dd'));

insert into dinya_test values(6,12, 'BOOKS',to_date('2011-04-30','yyyy-mm-dd'));

b)查詢

   b.1)如果查詢全表資料

select * from dinya_test;如下圖：

 

                                < 全表資料>

select * from dinya_test partition(part_01);如下圖:

 

                              <Part_01分區的資料>

select * from dinya_test partition(part_02);如下圖:

 

                               <Part_02分區的資料>

select * from dinya_test partition(part_03);如下圖:

 

                               <Part_03分區的資料>

update dinya_test partition(part_01) t set t.item_description='DESK' wheret.transaction_id=1;

select * from dinya_test partition(part_01);BOOKS->DESK(發生變化)

 

　　　　　　　　　　　　　　　　　　

select * from dinya_test（此結果就不用查看了，肯定變了）；

---刪除part_03分區中transaction_id=4的記錄：

delete from dinya_test partition(part_03) t where t.transaction_id=4;

select * from dinya_test partition(part_03)

 

　　　　　　　　　　　　　少了transaction_id=4的記錄（與上圖對比）

c)索引的建立：

　　　c.1)局部索引的建立：

create index dinya_idx_t on dinya_test(item_id)

 local

   (

     partition idx_1 tablespace dinya_space01,---分區名為：idx_1

     partition idx_2 tablespace dinya_space02, ---分區名為：idx_2

     partition idx_3 tablespace dinya_space03---分區名為：idx_3

 );　　---pl/sql測試成功

註：

select *from ALL_TAB_PARTITIONS where table_name ='DINYA_TEST'

 

　　　　select *From dba_ind_partitions where partition_name=