Partitioning: Divide and Conquer - Pro Oracle Database 12c Administration - page 310

F_SALES

partition:
Y11
(values less
than
20120101)

partition:
Y12
(values less
than
20130101)

partition:
Y13
(values less
than
20140101)

tablespace:
p1_tbsp

tablespace:
p2_tbsp

tablespace:
p3_tbsp

data file(s):
t1_df1..N

data file(s):
t2_df1..N

data file(s):
t3_df1..N

Figure 12-3. Partitions stored in separate tablespaces

An advantage of placing partitions in separate tablespaces is that you can back up and recover partitions
independently (by backing up individual tablespaces). Also, if you have a partition that isn't being modified, you
can change its tablespace to read-only and instruct utilities such as RMAN to skip backing up such tablespaces, thus
increasing backup performance. Additionally, creating each partition in its own tablespace makes it easier to move
data from OLTP databases to decision support system (DSS) databases, and it lets you place specific tablespaces and
corresponding data files on separate storage devices to improve scalability and performance.
Also keep in mind that when you specify a tablespace for a partition, you can also specify any other storage
settings (per tablespace). The next example explicitly sets the PCTFREE , PCTUSED , and NOLOGGING storage clauses for the
tablespaces:

create table f_sales (
sales_amt number
,d_date_id number)
tablespace p1_tbsp
partition by range(d_date_id)(
partition y11 values less than (20120101)
tablespace p1_tbsp pctfree 5 pctused 90 nologging
,partition y12 values less than (20130101)
tablespace p2_tbsp pctfree 5 pctused 90 nologging
,partition y13 values less than (20140101)
tablespace p3_tbsp pctfree 5 pctused 90 nologging);