SQL> create table sale_data
2 (sale_id number(5), salesman_name varchar2(30),sales_date date)
3 partition by range(sales_date)
4 (
5 partition sales_01 values less than (to_date('01/02/2012','DD/MM/YYYY')) tablespace tbs_sale01,
6 partition sales_02 values less than (to_date('01/03/2012','DD/MM/YYYY')) tablespace tbs_sale02,
7 partition sales_03 values less than (to_date('01/04/2012','DD/MM/YYYY')) tablespace tbs_sale03,
8 partition sales_04 values less than (to_date('01/05/2012','DD/MM/YYYY')) tablespace tbs_sale04,
9 partition sales_05 values less than (to_date('01/06/2012','DD/MM/YYYY')) tablespace tbs_sale05,
10 partition sales_06 values less than (to_date('01/07/2012','DD/MM/YYYY')) tablespace tbs_sale06,
11 partition sales_07 values less than (to_date('01/08/2012','DD/MM/YYYY')) tablespace tbs_sale07,
12 partition sales_08 values less than (to_date('01/09/2012','DD/MM/YYYY')) tablespace tbs_sale08,
13 partition sales_09 values less than (to_date('01/10/2012','DD/MM/YYYY')) tablespace tbs_sale09,
14 partition sales_10 values less than (to_date('01/11/2012','DD/MM/YYYY')) tablespace tbs_sale10,
15 partition sales_11 values less than (to_date('01/12/2012','DD/MM/YYYY')) tablespace tbs_sale11,
16* partition sales_12 values less than (to_date('31/12/2012','DD/MM/YYYY')) tablespace tbs_sale12)
Table created. SQL> select owner,partitioning_type,partition_count,status from dba_part_tables where table_name='SALE_DATE'; OWNER PARTITI PARTITION_COUNT STATUS
------------------------------ ------- --------------- --------
SALE RANGE 12 VALID SQL> create index ind_sale_data_date on sale_data(sale_id) local
2 (
3 partition sales_01 tablespace tbs_sale01,
4 partition sales_02 tablespace tbs_sale02,
5 partition sales_03 tablespace tbs_sale03,
6 partition sales_04 tablespace tbs_sale04,
7 partition sales_05 tablespace tbs_sale05,
8 partition sales_06 tablespace tbs_sale06,
9 partition sales_07 tablespace tbs_sale07,
10 partition sales_08 tablespace tbs_sale08,
11 partition sales_09 tablespace tbs_sale09,
12 partition sales_10 tablespace tbs_sale10,
13 partition sales_11 tablespace tbs_sale11,
14* partition sales_12 tablespace tbs_sale12)
Index created. SQL> select segment_name,partition_name,tablespace_name from user_segments where segment_name in ('SALE_DATA','IND_SALE_DATA_DATE'); SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- ------------------------------ --------------------
SALE_DATA SALES_01 TBS_SALE01
SALE_DATA SALES_02 TBS_SALE02
SALE_DATA SALES_03 TBS_SALE03
SALE_DATA SALES_04 TBS_SALE04
SALE_DATA SALES_05 TBS_SALE05
SALE_DATA SALES_06 TBS_SALE06
SALE_DATA SALES_07 TBS_SALE07
SALE_DATA SALES_08 TBS_SALE08
SALE_DATA SALES_09 TBS_SALE09
SALE_DATA SALES_10 TBS_SALE10
SALE_DATA SALES_11 TBS_SALE11 SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- ------------------------------ --------------------
SALE_DATA SALES_12 TBS_SALE12
IND_SALE_DATA_DATE SALES_01 TBS_SALE01
IND_SALE_DATA_DATE SALES_02 TBS_SALE02
IND_SALE_DATA_DATE SALES_03 TBS_SALE03
IND_SALE_DATA_DATE SALES_04 TBS_SALE04
IND_SALE_DATA_DATE SALES_05 TBS_SALE05
IND_SALE_DATA_DATE SALES_06 TBS_SALE06
IND_SALE_DATA_DATE SALES_07 TBS_SALE07
IND_SALE_DATA_DATE SALES_08 TBS_SALE08
IND_SALE_DATA_DATE SALES_09 TBS_SALE09
IND_SALE_DATA_DATE SALES_10 TBS_SALE10 SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- ------------------------------ --------------------
IND_SALE_DATA_DATE SALES_11 TBS_SALE11
IND_SALE_DATA_DATE SALES_12 TBS_SALE12 |
