Partitioning & Transportable Tablespaces

Here an example how to use in an Oracle database partitioning together with transportable tablespaces:

Create Tablespaces

To create tablespaces, put the following lines into a file createTablespace.sql

CREATE SMALLFILE TABLESPACE "&1" DATAFILE '/opt/oracle/oradata/XYNADB/datafile/&1.dbf'
SIZE 100M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
exit;

and start the script via sqlplus with sqlplus system/pwd @ createTablespace.sql TTS01.
It will create a tablespace named TTS01. Repeat this step with TTS00 and TTS02.

Create Table

To create a partitioned table enter the follwing lines:

create table parttest (
id number, name varchar2(40), payload varchar2(40))
partition by range (id)
(partition part00 values less than (100) tablespace tts00,
partition part01 values less than (1000) tablespace tts01,
partition part02 values less than (10000) tablespace tts02);
exit;

Insert Data

Now put into the table parttest any data you want (the id has to be lower than 10000).
Just to verify, that the partitioning is working, use the following command:

select * from parttest partition (part01);

Adding new Partitions

To add new partitions, you have to create a new tablespace (s. abouve) and then enter the following command:

alter table parttest add partition part03 values less than (20000) tablespace tts03;

Drop an old partition

To drop an old partition, use the follwing command:

alter table parttest drop partition part00;

But with issueing this command your data in this partition is gone and there is no rollback!

Anything new?

Now we have a table partitioned over several tablespaces. We can add partitions and drop them.
The advantage is, that performance is better than with one unpartitioned table because the optimizer uses the partitioning to get faster access to the data.
But if you could move the partitions without dropping them, that would be a real enhancement.
Here the commands how you can:

Moving the data

First you have to create a table with the same structure as your paritioned table (but without the partitions) on the same tablespace where your data resides, which you want to move.
After that you can exchange the data of your partitioned table with the data of the new table.

alter table parttest exchange partition part01 with table parttestnew;

After that you can drop the partition on the original table parttest without loosing your data, which is still there in the table parttestnew. And if you want to can move the partition with the transportable tablespace feature from Oracle to another instance…

At GIP these features are used in the Xyna Service Warehouse (take a look at my guest post there!).