創建range 分區：
創建測試的tablespace
[oracle@CICQAS sfisarch]$ export ORACLE_SID=spring01;
[oracle@CICQAS sfisarch]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.7.0 - Production on Wed Jun 1 01:34:48 2011
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
spring01
SQL> create tablespace tbspart01 datafile '/sfisdata/spring/spring01.dbf' size 200M;
Tablespace created.
SQL> create tablespace tbspart02 datafile '/sfisdata/spring/spring02.dbf' size 200M;
Tablespace created.
SQL> create tablespace tbspart03 datafile '/sfisdata/spring/spring03.dbf' size 200M;
Tablespace created.
SQL> create tablespace tbspart04 datafile '/sfisdata/spring/spring04.dbf' size 200M;
Tablespace created.
1 創建一個標準的range分區表：
  SQL>  create table t_partition_range (id number,name varchar2(50))
  2   partition by range(id)(
  3   partition t_range_p1 values less than (10) tablespace tbspart01,
  4   partition t_range_p2 values less than (20) tablespace tbspart02,
  5   partition t_range_p3 values less than (30) tablespace tbspart03,
  6   partition t_range_pmax values less than (maxvalue) tablespace tbspart04
  7   );
Table created
查詢分區表的信息，可以通過查詢user_part_tables,user_tab_partitions 兩個數據字典
user_part_tables:記錄分區表的信息；
user_tab_partitions:記錄表的分區的信息。
例如：
查看分區表的信息：
SQL> select table_name,partitioning_type,partition_count from user_part_tables where
table_name='T_PARTITION_RANGE';
 
TABLE_NAME                     PARTITIONING_TYPE PARTITION_COUNT
------------------------------ ----------------- ---------------
T_PARTITION_RANGE              RANGE                           4
查看表分區的信息：
SQL> select TABLE_NAME,PARTITION_NAME ,HIGH_VALUE ,TABLESPACE_NAME  from user_tab_partitions where 
  2  table_name='T_PARTITION_RANGE';
 
TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                    
                                 TABLESPACE_NAME
------------------------------ ------------------------------ ----------------------------------------------
---------------------------------- ------------------------------
T_PARTITION_RANGE              T_RANGE_P1                     10                                            
                                 TBSPART01
T_PARTITION_RANGE              T_RANGE_P2                     20                                            
                                 TBSPART02
T_PARTITION_RANGE              T_RANGE_P3                     30                                            
                                 TBSPART03
T_PARTITION_RANGE              T_RANGE_PMAX                   MAXVALUE
創建global索引range分區：
SQL> create index indx_parti_range_id on t_partition_range(id) global partition by range(id)
  2  (partition i_range_p1 values less than(10) tablespace tbspart01,
  3   partition i_range_p2 values less than(40) tablespace tbspart02,
  4   partition i_range_pmax values less than (maxvalue) tablespace tbspart03
  5  );
Index created
創建global 索引的分區和創建表的分區語句格式完全相同,而且其分區形式與索引所在表的分區形式沒有關聯關係。
查詢索引的分區信息可以通過user_part_indexes,user_ind_partitions 兩個數據字典：
例如：
    SQL> select INDEX_NAME ,table_name,PARTITIONING_TYPE  ,PARTITION_COUNT   from user_part_indexes 
  2  where table_name='T_PARTITION_RANGE' and index_name='INDX_PARTI_RANGE_ID';
 
INDEX_NAME                     TABLE_NAME                     PARTITIONING_TYPE PARTITION_COUNT
------------------------------ ------------------------------ ----------------- ---------------
INDX_PARTI_RANGE_ID            T_PARTITION_RANGE              RANGE                           3
SQL> select INDEX_NAME  ,PARTITION_NAME ,HIGH_VALUE ,TABLESPACE_NAME  from user_ind_partitions
  2  where index_name='INDX_PARTI_RANGE_ID';
 
INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE                                    
                                 TABLESPACE_NAME
------------------------------ ------------------------------ ----------------------------------------------
---------------------------------- ------------------------------
INDX_PARTI_RANGE_ID            I_RANGE_P1                     10                                            
                                 TBSPART01
INDX_PARTI_RANGE_ID            I_RANGE_P2                     40                                            
                                 TBSPART02
INDX_PARTI_RANGE_ID            I_RANGE_PMAX                   MAXVALUE
創建local 分區：
SQL> drop index INDX_PARTI_RANGE_ID;
 
Index dropped
 
SQL> create index INDX_PARTI_RANGE_ID on t_partition_range(id) local;
 
Index created
local索引的分區完全繼承表的分區的屬性，包括分區的類型，分區的範圍值即不需要指定也不能更改，就是前面說的：
local 索引的分區維護完全依賴與其索引所在的表。但是表空間可以不同。測試按照上面的進行。