OCM实验-分区表

要求：创建一个分区表，共有4个分区，每个分区独立使用一个表空间，使用非标准块，块大小16k，创建一个全区索引，truncate掉一个分区数据并保证全局索引有效。

1 准备分区数据

create table t2 (itemid number(10),name varchar2(10),itemdate date);
create index idx_t2 on t2(itemid);
insert into t2 values (1,'apple1',to_date('2000-02-01','yyyy-mm-dd'));
insert into t2 values (2,'apple2',to_date('2000-03-01','yyyy-mm-dd'));
insert into t2 values (3,'apple3',to_date('2002-04-01','yyyy-mm-dd'));
insert into t2 values (4,'apple4',to_date('2002-05-01','yyyy-mm-dd'));
insert into t2 values (5,'apple5',to_date('2002-06-01','yyyy-mm-dd'));
insert into t2 values (6,'apple6',to_date('2010-07-01','yyyy-mm-dd'));
insert into t2 values (7,'apple7',to_date('2010-08-01','yyyy-mm-dd'));
insert into t2 values (8,'apple8',to_date('2012-09-01','yyyy-mm-dd'));
insert into t2 values (9,'apple9',to_date('2012-10-01','yyyy-mm-dd'));
insert into t2 values (10,'apple10',to_date('2013-11-01','yyyy-mm-dd'));

TSPORT@PROD>select * from t2;

    ITEMID NAME       ITEMDATE
---------- ---------- ---------
         1 apple1     01-FEB-00
         2 apple2     01-MAR-00
         3 apple3     01-APR-02
         4 apple4     01-MAY-02
         5 apple5     01-JUN-02
         6 apple6     01-JUL-10
         7 apple7     01-AUG-10
         8 apple8     01-SEP-12
         9 apple9     01-OCT-12
        10 apple10    01-NOV-13

10 rows selected.

2 创建非标准块的表空间

1）设置16k非标准块缓冲区

SYS@PROD>show parameter 16k;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 0

SYS@PROD>alter system set db_16k_cache_size=100M;

System altered.

2）创建16k非标准块表空间

SYS@PROD>create tablespace part1 datafile '/u01/app/oracle/oradata/PROD/disk1/part101.dbf' size 20M autoextend off blocksize 16k;

Tablespace created.

SYS@PROD>create tablespace part2 datafile '/u01/app/oracle/oradata/PROD/disk1/part201.dbf' size 20M autoextend off blocksize 16k;

Tablespace created.

SYS@PROD>create tablespace part3 datafile '/u01/app/oracle/oradata/PROD/disk1/part301.dbf' size 20M autoextend off blocksize 16k;

Tablespace created.

SYS@PROD>create tablespace part4 datafile '/u01/app/oracle/oradata/PROD/disk1/part401.dbf' size 20M autoextend off blocksize 16k;

Tablespace created.

3 创建分区表

TSPORT@PROD>create table t2_part partition by range (itemdate)
  2  (partition p1 values less than (to_date('2002-01-01','yyyy-mm-dd')) tablespace part1,
  3  partition p2 values less than (to_date('2010-01-01','yyyy-mm-dd')) tablespace part2,
  4  partition p3 values less than (to_date('2012-01-01','yyyy-mm-dd')) tablespace part3,
  5  partition p4 values less than (to_date('2013-01-01','yyyy-mm-dd')) tablespace part4,
  6  partition other values less than (maxvalue) tablespace part4)
  7  as select * from t2;

Table created.

TSPORT@PROD>
TSPORT@PROD>select * from t2_part;

    ITEMID NAME       ITEMDATE
---------- ---------- ---------
         1 apple1     01-FEB-00
         2 apple2     01-MAR-00
         3 apple3     01-APR-02
         4 apple4     01-MAY-02
         5 apple5     01-JUN-02
         6 apple6     01-JUL-10
         7 apple7     01-AUG-10
         8 apple8     01-SEP-12
         9 apple9     01-OCT-12
        10 apple10    01-NOV-13

10 rows selected.

TSPORT@PROD>select * from t2_part partition(p2);

    ITEMID NAME       ITEMDATE
---------- ---------- ---------
         3 apple3     01-APR-02
         4 apple4     01-MAY-02
         5 apple5     01-JUN-02

TSPORT@PROD>

4 创建全局分区索引

TSPORT@PROD>create unique index idx_t2_part on t2_part(name,itemid) global partition by hash(name) partitions 4 parallel 4;

Index created.

TSPORT@PROD>select index_name,index_type,table_name,STATUS from user_indexes where table_name='T2_PART';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     STATUS
------------------------------ --------------------------- ------------------------------ --------
IDX_T2_PART                    NORMAL                      T2_PART                        N/A

5 truncate一个分区并保证全局索引有效

TSPORT@PROD>select * from t2_part partition(p2);

    ITEMID NAME       ITEMDATE
---------- ---------- ---------
         3 apple3     01-APR-02
         4 apple4     01-MAY-02
         5 apple5     01-JUN-02

TSPORT@PROD>alter table t2_part truncate partition p2 update global indexes;

Table truncated.

TSPORT@PROD>select * from t2_part partition(p2);

no rows selected

TSPORT@PROD>select index_name,index_type,table_name,STATUS from user_indexes where table_name='T2_PART';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     STATUS
------------------------------ --------------------------- ------------------------------ --------
IDX_T2_PART                    NORMAL                      T2_PART                        N/A

TSPORT@PROD>select index_name,index_type,table_name,STATUS,partitioned from user_indexes where table_name='T2_PART';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     STATUS   PAR
------------------------------ --------------------------- ------------------------------ -------- ---
IDX_T2_PART                    NORMAL                      T2_PART                        N/A      YES

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11590946/viewspace-1074144/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11590946/viewspace-1074144/