11g Partitioning Features

11g Partitioning Features :

What is Partitioning ?
Oracle Partitioning is the splitting of data sets usually into separate physical files using separate partition tablespaces.

Why is partitioning relevant to tuning ?
Partitioning can be used to break large tables into smaller subsets. Processing of smaller subsets of data separately and in parallel is petentially much faster than serial processing on very large data sets.

Different Partitioning methods :- (till 10g)
Partitions can be created on single or multiple columns of a table. A table can be devided into separate partitions based on three methods ( ranges of values , values in lists and hashing algorithms on columns).
1) Range Partition
2) List Partition
3) Hash Partition
4) Composite Partition (Range - Hash Partition and Range - List Partition)

The Optimizer can access individual partitions when processing SQL code. This process is termed pruning.

=== What is new with 11g ? =====

Extended Composite Partitions
Range top level
--Range-Hash (available since Oracle 8i)
--Range-List (available since Oracle 9.2)
--Range-Range
List top level
--List-List
--List-Hash
--List-Range
Interval top level
--Interval-Range
--Interval-List
--Interval-Hash

Enhanced Partitioning
1)Virtual Column based partitioning
2)Reference Partitioning
3)Interval Partitioning
4)System Partitioning

Enhanced Manageability
1)Partition Advisor

Virtual Column Based Partitioning :
It is purely virtual , meta-data only. Virtual columns can have statistics and they are also eligible for partitioning key. This enhances the performance and manageability.

Reference Partitioning :
Oracle 11g introduced Reference Partitioning. Child table inherits the partitioning strategy of parent table through PK-FK relationship. This enhances the performance and manageability.

Interval Partitioning :
Extention to range partition and these are created as metadata information only.Partition created when new data is added.
Used Functions:
Numtodsinterval - Convert a number into an interval day to second literal.
Numtoyminterval - Convert a number into an interval year to month literal.
Interval partitioning does not support subpartitions. Thus, you can create an interval partition on the main partition of a composite partitioned table, but the subpartition cannot be interval-partitioned.

System Partitioning:
Application-controlled partitioning and No partitioning keys.You must define which partition the data goes in when doing an insert of data.System partitioning gives you all the advantages partitioning, but leaves the decision of how the data is partitioned to the application layer.

Partitioning Advisor:
Considers entire query workload to improve query performance.

=====================================================================
DEMO DEMO DEMO DEMO DEMO DEMO DEMO
=====================================================================

Interval Partitioning:
======================

SQL> conn sh/sh
Connected.
SQL> create table daily_sales(
product_id number not null,
customer_id number not null,
sale_dt date not null ,
quantity_sold number(3) not null)
partition by range (sale_dt)interval (numtoyminterval(1,'MONTH'))
(
PARTITION P1 values less than (TO_DATE('1-1-2002','dd-mm-yyyy')),
PARTITION P2 values less than (TO_DATE('1-1-2003','dd-mm-yyyy')),
PARTITION P3 values less than (TO_DATE('1-1-2004','dd-mm-yyyy')));

Table created.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = 'DAILY_SALES'order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
DAILY_SALES          P1                   TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P2                   TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P3                   TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> INSERT INTO daily_sales VALUES (1, 3423, TO_DATE('16-OCT-2002', 'DD-MON-YYYY'), 45);

1 row created.

SQL> INSERT INTO daily_sales VALUES (2, 3426, TO_DATE('31-OCT-2002', 'DD-MON-YYYY'), 97);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats('SH', 'DAILY_SALES');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = 'DAILY_SALES'order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
DAILY_SALES          P1                   TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P2                   TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P3                   TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> INSERT INTO daily_sales VALUES (3, 34863, TO_DATE('16-NOV-2004', 'DD-MON-YYYY'), 29);

1 row created.

SQL> INSERT INTO daily_sales VALUES (4, 34586, TO_DATE('30-NOV-2004', 'DD-MON-YYYY'), 94);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats('SH', 'DAILY_SALES');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = 'DAILY_SALES'order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
DAILY_SALES          P1                   TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P2                   TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P3                   TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          SYS_P21              TO_DATE(' 2004-12-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> INSERT INTO daily_sales VALUES (5, 3403, TO_DATE('16-JAN-2005', 'DD-MON-YYYY'), 29);

1 row created.

SQL> INSERT INTO daily_sales VALUES (6, 3486, TO_DATE('31-JAN-2005', 'DD-MON-YYYY'), 94);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'DAILY_SALES');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_table_stats('SH', 'DAILY_SALES');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = 'DAILY_SALES'order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
DAILY_SALES          P1                   TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P2                   TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P3                   TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          SYS_P21              TO_DATE(' 2004-12-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          SYS_P22              TO_DATE(' 2005-02-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> alter table daily_sales rename partition SYS_P21 to P4;

Table altered.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = 'DAILY_SALES'order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
DAILY_SALES          P1                   TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P2                   TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P3                   TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P4                   TO_DATE(' 2004-12-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          SYS_P22              TO_DATE(' 2005-02-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> alter table daily_sales merge partitions for(to_date('01-JAN-2002','dd-MON-yyyy')) , for(to_date('01-JAN-2003','dd-MON-yyyy')) into partition P5;

Table altered.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = 'DAILY_SALES'order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
DAILY_SALES          P1                   TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P4                   TO_DATE(' 2004-12-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P5                   TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          SYS_P22              TO_DATE(' 2005-02-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Virtual Column Based Partitioning
=================================

SQL> sho user
USER is "SH"
SQL> CREATE TABLE employees
( employee_id number(6) not null,
emp_name varchar2(30),
first_letter VARCHAR2(1)
GENERATED ALWAYS AS
(
UPPER(SUBSTR(TRIM(emp_name), 1, 1))
) VIRTUAL
)
PARTITION BY LIST (first_letter)
(
PARTITION part_a_g VALUES ('A','B','C','D','E','F','G'),
PARTITION part_h_n VALUES ('H','I','J','K','L','M','N'),
PARTITION part_o_u VALUES ('O','P','Q','R','S','T','U'),
PARTITION part_v_z VALUES ('V','W','X','Y','Z')
);

Table created.

SQL> select column_name, data_default from   user_tab_columns where table_name = 'EMPLOYEES';

COLUMN_NAME                    DATA_DEFAULT
------------------------------ --------------------------------------------------
EMPLOYEE_ID
EMP_NAME
FIRST_LETTER                   UPPER(SUBSTR(TRIM("EMP_NAME"),1,1))

SQL> INSERT INTO employees (employee_id, emp_name) VALUES (1, 'Andy Pandy');
INSERT INTO employees (employee_id, emp_name) VALUES (1, 'Burty Basset');
INSERT INTO employees (employee_id, emp_name) VALUES (1, 'Harry Hill');
INSERT INTO employees (employee_id, emp_name) VALUES (1, 'Iggy Pop');
INSERT INTO employees (employee_id, emp_name) VALUES (1, 'Oliver Hardy');
INSERT INTO employees (employee_id, emp_name) VALUES (1, 'Peter Pervis');
INSERT INTO employees (employee_id, emp_name) VALUES (1, 'Veruca Salt');
INSERT INTO employees (employee_id, emp_name) VALUES (1, 'Wiley Cyote');
COMMIT;
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats('SH', 'EMPLOYEES');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rows from user_tab_partitions where table_name = 'EMPLOYEES' order by table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
EMPLOYEES                 PART_A_G             'A', 'B', 'C', 'D', 'E', 'F', 'G'                 2
EMPLOYEES                 PART_H_N             'H', 'I', 'J', 'K', 'L', 'M', 'N'                 2
EMPLOYEES                 PART_O_U             'O', 'P', 'Q', 'R', 'S', 'T', 'U'                 2
EMPLOYEES                 PART_V_Z             'V', 'W', 'X', 'Y', 'Z'                           2

SQL>

Reference Partitioning
======================

SQL> sho user
USER is "SH"
SQL> create table parent_tab(
customer_id number,
order_id number not null,
order_date date not null,
order_mode varchar2(8),
order_status varchar2(1))
partition by range (order_date)
(
partition p_before_jan_2008   values less than(to_date('01-JAN-2009','dd-MON-yyyy')),
partition p_before_jan_2009   values less than(to_date('01-DEC-2010','dd-MON-yyyy'))) parallel;

Table created.

SQL> alter table parent_tab add constraint parent_tab_pkprimary key (order_id);

Table altered.

SQL> INSERT INTO parent_tab(customer_id,order_id,order_date) VALUES (1, 100, SYSDATE);

1 row created.

SQL> INSERT INTO parent_tab(customer_id,order_id,order_date) VALUES (2, 101, SYSDATE);

1 row created.

SQL> INSERT INTO parent_tab(customer_id,order_id,order_date) VALUES (3, 102, ADD_MONTHS(SYSDATE,12));

1 row created.

SQL> create table child_tab(
order_id number not null,
product_id number not null,
quantity number not null,
sales_amount number not null,
constraint child_tab_fk
foreign key (order_id) references parent_tab(order_id) )
partition by reference (child_tab_fk)parallel;

Table created.

SQL> INSERT INTO child_tab(order_id,product_id,quantity,sales_amount) VALUES (101, 1, 4, 500);

1 row created.

SQL> INSERT INTO child_tab(order_id,product_id,quantity,sales_amount) VALUES (101, 3, 97, 450);

1 row created.

SQL> INSERT INTO child_tab(order_id,product_id,quantity,sales_amount) VALUES (102, 3, 47, 350);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats('SH', 'PARENT_TAB');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_table_stats('SH', 'CHILD_TAB');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name in ('PARENT_TAB','CHILD_TAB')order by table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
CHILD_TAB                 P_BEFORE_JAN_2008                                                      0
CHILD_TAB                 P_BEFORE_JAN_2009                                                      3
PARENT_TAB                P_BEFORE_JAN_2008    TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

PARENT_TAB                P_BEFORE_JAN_2009    TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-M          3
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> select table_name, partitioning_type, ref_ptn_constraint_name from user_part_tables where table_name in ('PARENT_TAB','CHILD_TAB');

TABLE_NAME                PARTITION REF_PTN_CONSTRAINT_NAME
------------------------- --------- ------------------------------
CHILD_TAB                 REFERENCE CHILD_TAB_FK
PARENT_TAB                RANGE

SQL> select table_name, partition_name, high_value from user_tab_partitions where table_name in ('PARENT_TAB','CHILD_TAB') order by partition_position, table_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE
------------------------- -------------------- ----------------------------------------
CHILD_TAB                 P_BEFORE_JAN_2008
PARENT_TAB                P_BEFORE_JAN_2008    TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

CHILD_TAB                 P_BEFORE_JAN_2009
PARENT_TAB                P_BEFORE_JAN_2009    TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> select up.table_name, up.partitioning_type, uc.table_name ref_table from user_part_tables up, (select r.table_name, r.constraint_name from user_constraints uc, user_constraints r where uc.constraint_name=r.constraint_name and uc.owner=r.owner) uc where up.ref_ptn_constraint_name = uc.constraint_name(+) and up.table_name in ('PARENT_TAB','CHILD_TAB');

TABLE_NAME                PARTITION REF_TABLE
------------------------- --------- ------------------------------
CHILD_TAB                 REFERENCE CHILD_TAB
PARENT_TAB                RANGE

SQL> alter table parent_tab add partition p_before_jan_20010 values less than (to_date('01-feb-2011','dd-mon-yyyy')) ;

Table altered.

SQL> select table_name, partitioning_type, ref_ptn_constraint_name from user_part_tables where table_name in ('PARENT_TAB','CHILD_TAB');

TABLE_NAME                PARTITION REF_PTN_CONSTRAINT_NAME
------------------------- --------- ------------------------------
CHILD_TAB                 REFERENCE CHILD_TAB_FK
PARENT_TAB                RANGE

SQL> select table_name, partition_name, high_value from user_tab_partitions where table_name in ('PARENT_TAB','CHILD_TAB') order by partition_position, table_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE
------------------------- -------------------- ----------------------------------------
CHILD_TAB                 P_BEFORE_JAN_2008
PARENT_TAB                P_BEFORE_JAN_2008    TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

CHILD_TAB                 P_BEFORE_JAN_2009
PARENT_TAB                P_BEFORE_JAN_2009    TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

CHILD_TAB                 P_BEFORE_JAN_20010
PARENT_TAB                P_BEFORE_JAN_20010   TO_DATE(' 2011-02-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

6 rows selected.

SQL> select up.table_name, up.partitioning_type, uc.table_name ref_table from user_part_tables up, (select r.table_name, r.constraint_name from user_constraints uc, user_constraints r where uc.constraint_name=r.constraint_name and uc.owner=r.owner) uc where up.ref_ptn_constraint_name = uc.constraint_name(+) and up.table_name in ('PARENT_TAB','CHILD_TAB');

TABLE_NAME                PARTITION REF_TABLE
------------------------- --------- ------------------------------
CHILD_TAB                 REFERENCE CHILD_TAB
PARENT_TAB                RANGE

SQL>

System Partitioning :
======================

SQL> sho user
USER is "SH"
SQL> CREATE TABLE systab (c1 integer, c2 integer)
PARTITION BY SYSTEM
(
PARTITION p1 TABLESPACE test1,
PARTITION p2 TABLESPACE test2,
PARTITION p3 TABLESPACE test1,
PARTITION p4 TABLESPACE test2
);

Table created.

SQL> INSERT INTO systab VALUES (4,5);
INSERT INTO systab VALUES (4,5)
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by
the System method

-- Insert row with partition-extended syntax

SQL> INSERT INTO systab PARTITION (p1) VALUES (4,5);

1 row created.

SQL> INSERT INTO systab PARTITION (p4) VALUES (2,7);

1 row created.

SQL> INSERT INTO systab PARTITION (p2) VALUES (3,5);

1 row created.

SQL> INSERT INTO systab PARTITION (p2) VALUES (1,9);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'SYSTAB');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rows from user_tab_partitions where table_name = 'SYSTAB' order by table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
SYSTAB                    P1                                                                     1
SYSTAB                    P2                                                                     2
SYSTAB                    P3                                                                     0
SYSTAB                    P4                                                                     1

SQL> UPDATE SYSTAB PARTITION (p2) SET c2 = 3 WHERE c1 = 2;

0 rows updated.

SQL> DELETE FROM SYSTAB PARTITION (p1) WHERE c1 = 2;

0 rows deleted.

SQL> select count(1) from systab where c1=2;

COUNT(1)
----------
1

SQL> DELETE FROM SYSTAB PARTITION (p4) WHERE c1 = 2;

1 row deleted.

SQL> select count(1) from systab where c1=2;

COUNT(1)
----------
0

SQL>

-- The PARTITION clause is optional for update and delete statements, but omitting this clause will force
all partitions to be scanned, since there is no way perform automatic partition pruning when the database
has no control over row placement. When the PARTITION clause is used, you must be sure to perform the
operation against the correct partition.

List-Hash
==========

SQL> sho user
USER is "SH"
SQL> CREATE TABLE list_hash_tab (
id           NUMBER,
code         VARCHAR2(10),
description VARCHAR2(50),
created_date DATE
)
PARTITION BY LIST (code)
SUBPARTITION BY HASH (id)
(
PARTITION part_aa values ('AA')
(
SUBPARTITION part_aa_01,
SUBPARTITION part_aa_02
),
partition part_bb values ('BB')
(
SUBPARTITION part_bb_01,
SUBPARTITION part_bb_02
)
);

Table created.

SQL> DECLARE
l_code VARCHAR2(10);
BEGIN
FOR i IN 1 .. 40 LOOP
IF MOD(i, 2) = 0 THEN
l_code := 'BB';
ELSE
l_code := 'AA';
END IF;

INSERT INTO list_hash_tab (id, code, description, created_date)
VALUES (i, l_code, 'Description for ' || i || ' ' || l_code, SYSDATE);
END LOOP;
COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'LIST_HASH_TAB', granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rows from user_tab_partitions where table_name = 'LIST_HASH_TAB' order by table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
LIST_HASH_TAB             PART_AA              'AA'                                             20
LIST_HASH_TAB             PART_BB              'BB'                                             20

SQL>

Single Partition Transportable for Oracle Data Pump :
=====================================================
1) create two tablespaces and assign quota to users
2) Create a partitioned table with some data and gather stats
3) Query the user_tab_partitions to see data
4) Make one tablespace as read only
5) Use expdp to export the partion
6) Drop the table and tablespace ( dont drop the datafiles of tablespace which is transported)
7) use impdp to import
8 ) Check the table data in user_tables

11g table Partitions Features

askMLabs