Friday Oct 18, 2013

Oracle Database 12c New Partition Maintenance Features by Gwen Lazenby

One of my favourite new features in Oracle Database 12c is the ability to perform partition maintenance operations on multiple partitions. This means we can now add, drop, truncate and merge multiple partitions in one operation, and can split a single partition into more than two partitions also in just one command. This would certainly have made my life slightly easier had it been available when I administered a data warehouse at Oracle 9i.

To demonstrate this new functionality and syntax, I am going to create two tables, ORDERS and ORDERS_ITEMS which have a parent-child relationship. ORDERS is to be partitioned using range partitioning on the ORDER_DATE column, and ORDER_ITEMS is going to partitioned using reference partitioning and its foreign key relationship with the ORDERS table. This form of partitioning was a new feature in 11g and means that any partition maintenance operations performed on the ORDERS table will also take place on the ORDER_ITEMS table as well.

First create the ORDERS table -

SQL> CREATE TABLE orders
      ( order_id NUMBER(12),
        order_date TIMESTAMP,
        order_mode VARCHAR2(8),
        customer_id NUMBER(6),
        order_status NUMBER(2),
        order_total NUMBER(8,2),
        sales_rep_id NUMBER(6),
        promotion_id NUMBER(6),
       CONSTRAINT orders_pk PRIMARY KEY(order_id)
     )
    PARTITION BY RANGE(order_date)
   (PARTITION Q1_2007 VALUES LESS THAN (TO_DATE('01-APR-2007','DD-MON-YYYY')),
    PARTITION Q2_2007 VALUES LESS THAN (TO_DATE('01-JUL-2007','DD-MON-YYYY')),
    PARTITION Q3_2007 VALUES LESS THAN (TO_DATE('01-OCT-2007','DD-MON-YYYY')),
    PARTITION Q4_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY'))
    );

Table created.

Now the ORDER_ITEMS table

SQL> CREATE TABLE order_items
     ( order_id NUMBER(12) NOT NULL,
       line_item_id NUMBER(3) NOT NULL,
       product_id NUMBER(6) NOT NULL,
       unit_price NUMBER(8,2),
       quantity NUMBER(8),
       CONSTRAINT order_items_fk
       FOREIGN KEY(order_id) REFERENCES orders(order_id) on delete cascade)  
       PARTITION BY REFERENCE(order_items_fk) tablespace example;

Table created.

Now look at DBA_TAB_PARTITIONS to get details of what partitions we have in the two tables –

SQL>  select table_name,partition_name,
     partition_position position, high_value
     from dba_tab_partitions
     where table_owner='SH' and
     table_name  like 'ORDER_%'
     order by partition_position, table_name;

TABLE_NAME    PARTITION_NAME   POSITION HIGH_VALUE
-------------- --------------- -------- -------------------------
ORDERS         Q1_2007                1 TIMESTAMP' 2007-04-01 00:00:00'
ORDER_ITEMS    Q1_2007                1
ORDERS         Q2_2007                2 TIMESTAMP' 2007-07-01 00:00:00'
ORDER_ITEMS    Q2_2007                2
ORDERS         Q3_2007                3 TIMESTAMP' 2007-10-01 00:00:00'
ORDER_ITEMS    Q3_2007                3
ORDERS         Q4_2007                4 TIMESTAMP' 2008-01-01 00:00:00'
ORDER_ITEMS    Q4_2007                4

Just as an aside it is also now possible in 12c to use interval partitioning on reference partitioned tables. In 11g it was not possible to combine these two new partitioning features.

For our first example of the new 12cfunctionality, let us add all the partitions necessary for 2008 to the tables using one command. Notice that the partition specification part of the add command is identical in format to the partition specification part of the create command as shown above -

SQL> alter table orders add
PARTITION Q1_2008 VALUES LESS THAN (TO_DATE('01-APR-2008','DD-MON-YYYY')),
PARTITION Q2_2008 VALUES LESS THAN (TO_DATE('01-JUL-2008','DD-MON-YYYY')),
PARTITION Q3_2008 VALUES LESS THAN (TO_DATE('01-OCT-2008','DD-MON-YYYY')),
PARTITION Q4_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'));

Table altered.

Now look at DBA_TAB_PARTITIONS and we can see that the 4 new partitions have been added to both tables –

SQL> select table_name,partition_name,
     partition_position position, high_value
     from dba_tab_partitions
     where table_owner='SH' and
     table_name  like 'ORDER_%'
     order by partition_position, table_name;

TABLE_NAME    PARTITION_NAME   POSITION HIGH_VALUE
-------------- --------------- -------- -------------------------
ORDERS         Q1_2007                1 TIMESTAMP' 2007-04-01 00:00:00'
ORDER_ITEMS    Q1_2007                1
ORDERS         Q2_2007                2 TIMESTAMP' 2007-07-01 00:00:00'
ORDER_ITEMS    Q2_2007                2
ORDERS         Q3_2007                3 TIMESTAMP' 2007-10-01 00:00:00'
ORDER_ITEMS    Q3_2007                3
ORDERS         Q4_2007                4 TIMESTAMP' 2008-01-01 00:00:00'
ORDER_ITEMS    Q4_2007                4
ORDERS         Q1_2008                5 TIMESTAMP' 2008-04-01 00:00:00'
ORDER_ITEMS    Q1_2008                5
ORDERS         Q2_2008                6 TIMESTAMP' 2008-07-01 00:00:00'
ORDER_ITEM     Q2_2008                6
ORDERS         Q3_2008                7 TIMESTAMP' 2008-10-01 00:00:00'
ORDER_ITEMS    Q3_2008                7
ORDERS         Q4_2008                8 TIMESTAMP' 2009-01-01 00:00:00'
ORDER_ITEMS    Q4_2008                8

Next, we can drop or truncate multiple partitions by giving a comma separated list in the alter table command. Note the use of the plural ‘partitions’ in the command as opposed to the singular ‘partition’ prior to 12c

SQL> alter table orders drop partitions Q3_2008,Q2_2008,Q1_2008;

Table altered.

Now look at DBA_TAB_PARTITIONS and we can see that the 3 partitions have been dropped in both the two tables –

TABLE_NAME    PARTITION_NAME   POSITION HIGH_VALUE
-------------- --------------- -------- -------------------------
ORDERS         Q1_2007                1 TIMESTAMP' 2007-04-01 00:00:00'
ORDER_ITEMS    Q1_2007                1
ORDERS         Q2_2007                2 TIMESTAMP' 2007-07-01 00:00:00'
ORDER_ITEMS    Q2_2007                2
ORDERS         Q3_2007                3 TIMESTAMP' 2007-10-01 00:00:00'
ORDER_ITEMS    Q3_2007                3
ORDERS         Q4_2007                4 TIMESTAMP' 2008-01-01 00:00:00'
ORDER_ITEMS    Q4_2007                4
ORDERS         Q4_2008                5 TIMESTAMP' 2009-01-01 00:00:00'
ORDER_ITEMS    Q4_2008                5

Now let us merge all the 2007 partitions together to form one single partition –

SQL> alter table orders merge partitions
   Q1_2005, Q2_2005, Q3_2005, Q4_2005
   into partition Y_2007;

Table altered.

TABLE_NAME    PARTITION_NAME   POSITION HIGH_VALUE
-------------- --------------- -------- -------------------------
ORDERS         Y_2007                 1 TIMESTAMP' 2008-01-01 00:00:00'
ORDER_ITEMS    Y_2007                 1
ORDERS         Q4_2008                2 TIMESTAMP' 2009-01-01 00:00:00'
ORDER_ITEMS    Q4_2008                2

Splitting partitions is a slightly more involved. In the case of range partitioning one of the new partitions must have no high value defined, and in list partitioning one of the new partitions must have no list of values defined. I call these partitions the ‘everything else’ partitions, and will contain any rows contained in the original partition that are not contained in the any of the other new partitions.

For example, let us split the Y_2007 partition back into 4 quarterly partitions –

SQL> alter table orders split partition Y_2007 into 
(PARTITION Q1_2007 VALUES LESS THAN (TO_DATE('01-APR-2007','DD-MON-YYYY')),
PARTITION Q2_2007 VALUES LESS THAN (TO_DATE('01-JUL-2007','DD-MON-YYYY')),
PARTITION Q3_2007 VALUES LESS THAN (TO_DATE('01-OCT-2007','DD-MON-YYYY')),
PARTITION Q4_2007);

Now look at DBA_TAB_PARTITIONS to get details of the new partitions –


TABLE_NAME    PARTITION_NAME   POSITION HIGH_VALUE
-------------- --------------- -------- -------------------------
ORDERS         Q1_2007                1 TIMESTAMP' 2007-04-01 00:00:00'
ORDER_ITEMS    Q1_2007                1
ORDERS         Q2_2007                2 TIMESTAMP' 2007-07-01 00:00:00'
ORDER_ITEMS    Q2_2007                2
ORDERS         Q3_2007                3 TIMESTAMP' 2007-10-01 00:00:00'
ORDER_ITEMS    Q3_2007                3
ORDERS         Q4_2007                4 TIMESTAMP' 2008-01-01 00:00:00'
ORDER_ITEMS    Q4_2007                4
ORDERS         Q4_2008                5 TIMESTAMP' 2009-01-01 00:00:00'
ORDER_ITEMS    Q4_2008                5

Partition Q4_2007 has a high value equal to the high value of the original Y_2007 partition, and so has inherited its upper boundary from the partition that was split.

As for a list partitioning example let look at the following another table, SALES_PAR_LIST, which has 2 partitions, Americas and Europe and a partitioning key of country_name.

SQL> select table_name,partition_name,
   high_value
   from dba_tab_partitions
   where table_owner='SH' and
   table_name = 'SALES_PAR_LIST';

TABLE_NAME      PARTITION_NAME   HIGH_VALUE
--------------  ---------------  -----------------------------
SALES_PAR_LIST  AMERICAS         'Argentina', 'Canada', 'Peru',
                                 'USA', 'Honduras', 'Brazil', 'Nicaragua'
SALES_PAR_LIST  EUROPE           'France', 'Spain', 'Ireland', 'Germany',
                                 'Belgium', 'Portugal', 'Denmark'

Now split the Americas partition into 3 partitions –

SQL> alter table sales_par_list split partition americas into
   (partition south_america values ('Argentina','Peru','Brazil'),
   partition north_america values('Canada','USA'),
   partition central_america);

Table altered.

Note that no list of values was given for the ‘Central America’ partition. However it should have inherited any values in the original ‘Americas’ partition that were not assigned to either the ‘North America’ or ‘South America’ partitions. We can confirm this by looking at the DBA_TAB_PARTITIONS view.

SQL> select table_name,partition_name,
   high_value
   from dba_tab_partitions
   where table_owner='SH' and
   table_name = 'SALES_PAR_LIST';

TABLE_NAME      PARTITION_NAME   HIGH_VALUE
--------------- ---------------  --------------------------------
SALES_PAR_LIST  SOUTH_AMERICA    'Argentina', 'Peru', 'Brazil'
SALES_PAR_LIST  NORTH_AMERICA    'Canada', 'USA'
SALES_PAR_LIST  CENTRAL_AMERICA  'Honduras', 'Nicaragua'
SALES_PAR_LIST  EUROPE           'France', 'Spain', 'Ireland', 'Germany',
                                 'Belgium', 'Portugal', 'Denmark'

In conclusion, I hope that DBA’s whose work involves maintaining partitions will find the operations a bit more straight forward to carry out once they have upgraded to Oracle Database 12c.

Gwen Lazenby

Gwen Lazenby is a Principal Training Consultant at Oracle.

She is part of Oracle University's Core Technology delivery team based in the UK, teaching Database Administration and Linux courses. Her specialist topics include using Oracle Partitioning and Parallelism in Data Warehouse environments, as well as Oracle Spatial and RMAN.

Wednesday Feb 27, 2013

Transporting a Single Partition of a Table

Untitled Document

A feature of the Oracle database that appeared in 11gR1 but seems to have gone largely unnoticed is the ability to transport single (or multiple) partitions of a table to another database. This is particularly useful when archiving out older data held in older partitions of a table.

In previous releases of the Oracle database, it was only possible to transport entire tables, which meant every partition in that table. Therefore the standard methodology used to transport a single partition was to perform a partition exchange at the source database, making the partition a standalone table, and then to transport this table using the normal Transportable Tablespace method to the target database. The result is a standalone table in the target database. The Oracle 11.2 documentation still states that you cannot use the Transportable Tablespace feature to transport a single partition of a table, however we are going to use a different technique, known as ‘datafile copy’ to do exactly that.

The datafile copy technique is actually part of Data Pump. In a similar way to transporting tablespaces, Data Pump is used to export out the metadata describing the objects to be copied to the new database. The datafiles that contain these objects are then copied to the new database, and Data Pump is used to import the metadata. These objects can now be seen by the new database.

This sounds exactly like transporting a tablespace, but there is a difference. In the datafile copy technique we might only be interested in a subset of what is in the datafiles, not the entire contents. This is achieved by invoking Data Pump export in the table mode, rather than tablespace mode. The datafile then effectively becomes an alternative to a dumpfile containing the exported data.

If the only contents of the tablespace in question are a single partition (or subpartition) of a table, then the final result appears to be identical to that achieved using the partition exchange and transportable tablespace technique traditionally used.

Now look at a worked example:
First create a tablespace TRANS to hold the partition to be transported

SQL> CREATE TABLE "OE"."ORDERS_PART"
( "ORDER_ID" NUMBER(12),
"ORDER_DATE" TIMESTAMP(6) WITH local TIME ZONE CONSTRAINT "ORDER_PART_DATE_NN" NOT NULL ,
"ORDER_MODE" VARCHAR2(8),
"CUSTOMER_ID" NUMBER(6) CONSTRAINT "ORDER_PART_CUST_ID_NN" NOT NULL ,
"ORDER_STATUS" NUMBER(2),
"ORDER_TOTAL" NUMBER(8, 2),
"SALES_REP_ID" NUMBER(6),
"PROMOTION_ID" NUMBER(6))
TABLESPACE "EXAMPLE"
PARTITION BY RANGE ("ORDER_DATE")
(PARTITION "H1_2004" VALUES LESS THAN (TIMESTAMP'2004-07-01 00:00:00 +0:00') TABLESPACE TRANS,
PARTITION "H2_2004" VALUES LESS THAN (TIMESTAMP'2005-01-01 00:00:00 +0:00') TABLESPACE TRANS,
PARTITION "H1_2005" VALUES LESS THAN (TIMESTAMP'2005-07-01 00:00:00 +0:00'),
PARTITION "H2_2005" VALUES LESS THAN (TIMESTAMP'2006-01-01 00:00:00 +0:00'),
PARTITION "H1_2006" VALUES LESS THAN (TIMESTAMP'2006-07-01 00:00:00 +0:00'),
PARTITION "H2_2006" VALUES LESS THAN (MAXVALUE));
Table created

And then populate it:

SQL> insert into oe.orders_part select * from oe.orders;
105 rows created.
SQL> commit;

Look at the segments that are contained inside this tablespace:

SQL> select owner, segment_name,partition_name from dba_segments where tablespace_name='TRANS'

OWNER SEGMENT_NAME PARTITION_NAME -------- --------------------- --------------
OE ORDERS_PART H1_2004 OE ORDERS_PART H2_2004

Just like when transporting tablespaces any tablespaces whose datafiles we want to copy must first be made read only.

SQL> alter tablespace trans read only;
Tablespace altered

Now the Data Pump export can be performed. The ‘table’ option is used to indicate which partitions we want to export, and also the ‘transportable=always’ to indicate that the generated dumpfile is to contain the table/partition metadata only as the datafiles containing segments involved will be copied to the new database.

expdp system/oracle tables=oe.orders_part:H1_2004 transportable=always dumpfile=ordersH1_2004.dmp reuse_dumpfiles=y

Export: Release 11.2.0.2.0 - Production on Thu Feb 21 16:20:31 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** tables=oe.orders_part:H1_2004 transportable=always dumpfile=ordersH1_2004.dmp reuse_dumpf
s=y
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  D:\DATAPUMP\ORDERSH1_2004.DMP
******************************************************************************
Datafiles required for transportable tablespace TRANS:
  D:\ORADATA\TRANS.DBF
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 16:20:53

Now the datafile is copied to its new location and the TRANS tablespace is made read-write again.

copy d:\oradata\trans.dbf d:\oradata\trans.cpy
        1 file(s) copied.

(If necessary, RMAN can now be invoked to convert the datafile to different platform format.)

SQL> alter tablespace trans read write;

Tablespace altered.

Now move over to the second database and import the partition’s metadata into the database. Use the option ‘partition_options=departition’ which will import the partition as a standalone table. In this example the schema owner is also being changed.

impdp system/oracle partition_options=departition dumpfile=ordersH1_2004.dmp transport_datafiles='d:/oradata/trans.cpy' remap_schema=OE:SH

Import: Release 11.2.0.2.0 - Production on Thu Feb 21 16:21:28 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** partition_options=departition dumpfile=ordersH1_2004.dmp transport_datafiles='d:/
data/trans.cpy' remap_schema=OE:SH
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 16:21:34

Look for the newly imported table

SQL> select table_name, tablespace_name  from dba_tables
  2  where table_name like 'ORDERS%';

TABLE_NAME			TABLESPACE_NAME
------------------------------ --------------------------------------
ORDERS_PART_H1_2004		TRANS
ORDERS_QUEUETABLE 		EXAMPLE
ORDERS				EXAMPLE
ORDERS				EXAMPLE

Its new name is the name of the original table suffixed with the name of the partition. If this was the only segment that was originally stored in this datafile we would now be finished but, although this partition is the only segment whose metadata was imported into this database, there are actually other segments in this datafile of which this 'new' database does not know anything about.

SQL> select owner, segment_name, partition_name from dba_segments where tablespace_name='TRANS'

OWNER    SEGMENT_NAME         PARTITION_NAME
------------------------------ --------------
SH       ORDERS_PART_H1_2004          

So move the new table to a new tablespace enabling the TRANS tablespace to be dropped.

SQL> alter table sh.orders_part_h1_2004 move tablespace example;

Table altered.

SQL> drop tablespace trans;

Tablespace dropped.

Datafile copying as a means to transport partitions is one of the many features covered in the Oracle Database 11g : Implement Partitioning course.

About the Author:

Gwen Lazenby



Gwen Lazenby is Principal Training Consultant at Oracle. She is part of the UK and IE Core Tech Delivery team, teaching database administration, Spatial and Linux courses. Gwen is also a member of the OU EMEA SME team, with a special responsibility for Oracle Spatial.

About

Expert trainers from Oracle University share tips and tricks and answer questions that come up in a classroom.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today