Partitioning Oracle Table with LOBs

Because of the way that both partitioning and LOBs can divide a table into several physical sections, it took a little longer for Oracle to support LOB data types in a partitioned table. Oracle has jumped that hurdle, and now you can create a partitioned table that contains LOB data types.
When specifying the partitions in the CREATE TABLE command, you also specify where the LOB is stored for each partition. This gives you maximum flexibility in storing the partitions and the LOBs. Move the large LOB data to a separate tablespace, so that queries not involving the LOB data are more efficient.
In the following simulation, you will create a partitioned thable that contains LOB data.
Partitioning Table with Lob Data
It is easy to see how you might expand this kind of partitioning into more partitions. For example, in the simulation above, you might add ten-year sections for the 80s, 90s and the new millennium. The next lesson covers how to enable row movement in a partitioned table.

Unstructured data (such as images and documents) which is stored in a LOB column in the database can also be partitioned. When a table is partitioned, all the columns will reside in the tablespace for that partition, with the exception of LOB columns, which can be stored in their own tablespace. This technique is very useful when a table is comprised of large LOBs because they can be stored separately from the main data. This can be beneficial if the main data is being frequently updated but the LOB data isn't. For example, an employee record may contain a photo which is unlikely to change frequently. However, the employee personnel details (such as address, department, manager, and so on) could change. This approach also means that cheaper storage can be used for storing the LOB data and more expensive, faster storage used for the employee record.

LOBs are supported in RANGE partitioned, LIST partitioned, and HASH partitioned tables. Composite heap-organized tables can also have LOBs. You can partition a table containing LOB columns using the following techniques:

1. When the table is created using the PARTITION BY ... clause of the CREATE TABLE statement.
2. Adding a partition to an existing table using the ALTER TABLE ... ADD PARTITION clause.
3. Exchanging partitions with a table that already has partitioned LOB columns using the ALTER TABLE ... EXCHANGE PARTITION clause. Note that EXCHANGE PARTITION can only be used when both tables have the same storage attributes, for example, both tables store LOBs out-of-line.

Creating LOB partitions at the same time you create the table (in the CREATE TABLE statement) is recommended. If you create partitions on a LOB column when the table is created, then the column can hold LOBs stored either inline or out-of-line LOBs.
After a table is created, new LOB partitions can only be created on LOB columns that are stored out-of-line. Also, partition maintenance operations, SPLIT PARTITION and MERGE PARTITIONS, will only work on LOB columns that store LOBs out-of-line.

Click the link below to read about partitioning tables.
Partition Table using LobData