Oracle Transportable Table Partitions

Transportable Table Partitions

By Caleb Small, Bsc, ISP

Transportable Tablespaces are the most efficient way to move large amounts of data from one Oracle database to another. When combined with a properly designed table partitioning scheme this feature provides an ideal method of refreshing a data warehouse or archiving transactional data quickly and with minimum interruption.

To review, the basic premise of Transportable Tablespace is copying the actual Oracle datafile(s) from one database to another, then exporting metadata from the source database and importing it into the target, thus "plugging in" a tablespace. The export includes descriptions of the tables and other objects contained in the tablespace set, but no actual data, making it much faster than a conventional export. The source database requires the Enterprise Edition license.

In this scenario we simulate a transactional banking system which is required to keep three years of data online, and archive older data in another database. We'll start by creating a transaction table, range partitioned on transaction date. There will be one partition for each year's worth of business. Furthermore, we'll store each partition in its own tablespace to facilitate easy transport when we're ready to archive. The data will be indexed to enforce the primary key constraint. For efficiency, the indexes will be LOCAL PARTITIONED, meaning that each table partition will have its own corresponding index partition in its own tablespace. Individual table and index partitions can then be treated as one. Just to make life interesting the source DB will be Linux the target will be Windows, both 10g.

PART 1 - Design and create the source table

Start by creating five tablespaces for five years worth of data, and five more for indexes. Note the small initial size and use of AUTOEXTEND with MAXSIZE. We don't want to transport files larger than they need to be.

CREATE TABLESPACE ttsdat1 DATAFILE '/u01/app/oracle/oratrans/ttsdat1.dbf'

SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

CREATE TABLESPACE ttsidx1 DATAFILE '/u01/app/oracle/oratrans/ttsidx1.dbf'

SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

...

SELECT tablespace_name, status, plugged_in FROM dba_tablespaces;

save ts

Now, create the range partitioned table using transaction date as the partition key. Storage parameters, such as tablespace, can be different for each partition.

CREATE TABLE txns (

trans_id NUMBER(12),

trans_dt DATE,

from_acct CHAR(10),

to_acct CHAR(10),

amount NUMBER(12,2))

PARTITION BY RANGE (trans_dt)

( PARTITION fy2001 VALUES LESS THAN (to_date('2002-01-01','yyyy-mm-dd') )

TABLESPACE ttsdat1,

PARTITION fy2002 VALUES LESS THAN (to_date('2003-01-01','yyyy-mm-dd') )

TABLESPACE ttsdat2,

PARTITION fy2003 VALUES LESS THAN (to_date('2004-01-01','yyyy-mm-dd') )

TABLESPACE ttsdat3,

PARTITION fy2004 VALUES LESS THAN (to_date('2005-01-01','yyyy-mm-dd') )

TABLESPACE ttsdat4,

PARTITION fy2005 VALUES LESS THAN (to_date('2006-01-01','yyyy-mm-dd') )

TABLESPACE ttsdat5 );

Create a unique index to enforce the primary key constraint. For manageability we want a LOCAL PARTITIONED index so that removing or adding a partition will not require an index rebuild in either source or target databases. However, this requires the partitioning key be included in the index, which makes one consider how much additional storage and/or processing overhead this may incur. Again each index partition has its own storage parameters.

CREATE UNIQUE INDEX txns_pk_idx ON txns(trans_id,trans_dt) LOCAL

( PARTITION fy2001 TABLESPACE ttsidx1,

PARTITION fy2002 TABLESPACE ttsidx2,

PARTITION fy2003 TABLESPACE ttsidx3,

PARTITION fy2004 TABLESPACE ttsidx4,

PARTITION fy2005 TABLESPACE ttsidx5 );

Once the index is created we add the primary key constraint to the whole table. Remember this will also add a NOT NULL constraint to the two indexed columns.

ALTER TABLE txns ADD CONSTRAINT txns_pk PRIMARY KEY (trans_id, trans_dt)

USING INDEX LOCAL

( PARTITION fy2001,

PARTITION fy2002,

PARTITION fy2003,

PARTITION fy2004,

PARTITION fy2005 );

descr txns

Now it's time to create some test data. This little procedure will create a bunch of rows that look like bank transaction data, and will spread it evenly over the last four years starting from today. Select a few rows from the table to convince yourself.

CREATE SEQUENCE trans_id_seq;

BEGIN

FOR i IN 1..25000 LOOP

INSERT INTO txns SELECT

trans_id_seq.nextval,

SYSDATE-ABS(dbms_random.random)/power(2,31)*365*4,

SUBSTR(TO_CHAR(dbms_random.random,'000000000000'),-10,10),

TO_CHAR(ABS(dbms_random.random)/100000,'999999999.00') FROM dual;

COMMIT;

END LOOP;

END;

SELECT * FROM txns WHERE rownum < 25;

SELECT trunc(trans_dt,'YEAR'), count(*)

FROM txns GROUP BY trunc(trans_dt,'YEAR');

It's time to check some data dictionary views to see what we've got. Save these queries for later use as we make changes to the table.

ANALYZE TABLE txns COMPUTE STATISTICS;

ANALYZE INDEX txns_pk_idx COMPUTE STATISTICS;

-- Number of rows in each table partition

SELECT partition_name, num_rows FROM user_tab_partitions;

save tabpartrows

-- Number of rows in each index partition

SELECT partition_name, num_rows, status FROM user_ind_partitions;

save indpartrows

-- Number of rows in the entire table

SELECT table_name, num_rows FROM user_Tables;

save tabrows

-- Size of each segment

SELECT segment_type, segment_name, partition_name, tablespace_name, blocks, extents

FROM user_segments ORDER BY segment_Type, segment_name, partition_name;

save segsize

PART 2 - Unplug the oldest partition

Our "production" table is complete and contains at least one partition that's old enough to archive. The first thing to do is create a discrete table that is identical to the partition we want to remove. It must have the same columns, data types, constraints and indexes, and exist in the same tablespace as the partition. The index must also exist in the corresponding index tablespace.

CREATE TABLE txns_fy2001 TABLESPACE ttsdat1 AS SELECT * FROM txns WHERE 1=0;

ALTER TABLE txns_fy2001 ADD CONSTRAINT txns_fy2001_pk PRIMARY KEY (trans_id, trans_dt)

USING INDEX TABLESPACE ttsidx1;

The new table contains no rows, and the index is empty. When we EXCHANGE a partition with a discrete table, we literally exchange data segments between the two. That's why they must match in structure and location. No data is actually moved, only internal pointers are changed.

ALTER TABLE txns EXCHANGE PARTITION fy2001

WITH TABLE txns_fy2001 INCLUDING INDEXES;

Count the rows in the new table and in the partitioned table before and after the EXCHANGE to convince yourself that the data did actually "move", check the size of the data segments, and the status of the indexes.

SELECT COUNT(*) FROM txns PARTITION (fy2001);

SELECT COUNT(*) FROM txns_fy2001;

@segsize

We no longer need the old partition as it is now empty, so we can drop it. If your goal was to simply transport this partition without removing it, such as in a data warehouse scenario, you could exchange the table back into the partition after transporting the tablespace.

ALTER TABLE txns DROP PARTITION fy2001;

To get ready make the tablespaces we are transporting (data and index) READ ONLY to ensure there are no active transactions, and that none start during transport. The rest of the partitions are unaffected.

ALTER TABLESPACE ttsdat1 READ ONLY;

ALTER TABLESPACE ttsidx1 READ ONLY;

Use the DBMS_TTS utility to verify the tablespace set is self contained. That is, there are no objects within the set that reference (foreign key, constraint indexes, etc) any object outside of the set. The set can be one or more tablespaces that will be transported together.

EXECUTE sys.DBMS_TTS.TRANSPORT_SET_CHECK('ttsdat1,ttsidx1', TRUE);

SELECT * FROM sys.TRANSPORT_SET_VIOLATIONS;

We're going to use data pump to perform the export, so make sure a named directory exists within the database and any other users have necessary permissions on it. Data pump cannot read or write arbitrarily to any file system directory like the old export/import.

CREATE OR REPLACE DIRECTORY trans_dir AS '/u01/app/oracle/oratrans';

GRANT READ, WRITE ON DIRECTORY trans_dir TO ...;

Bounce out to the OS and perform the metadata export. This will run quickly and consistently regardless of the partition size as it contains data descriptions only, no actual data.

cd /u01/app/oracle/oratrans

expdp bank/bank@FRED \

DUMPFILE=ttsfy1.dmp \

DIRECTORY=trans_dir \

TRANSPORT_TABLESPACES = ttsdat1,ttsidx1

ls -al

The second part of transportation is copying the datafile(s) for the tablespace(s) we are moving. If there is more than one datafile per tablespace remember to get them all. Remember to take all tablespaces that make up the complete transport set. Copying can be performed by simple OS commands if you are moving to a similar platform. If you are using ASM or if endian conversion is necessary then use RMAN to perform the copy (and parallelize it!). You can list supported platforms and their endian format by querying V$TRANSPORTABLE_PLATFORM.

rman target fred

CONVERT TABLESPACE ttsdat1, ttsidx1

TO PLATFORM 'Microsoft Windows IA (32-bit)'

FORMAT='$ORACLE_BASE/oratrans/%N_win.dbf';

exit

ls -al

After transport is complete we can drop the source tablespaces. This is optional based on your business requirement. You could go back to the source database and simply put them back into READ WRITE mode and continue business as usual. Discrete tables can be easily EXCHANGED back into the original partition they came from.

DROP TABLESPACE ttsdat1 INCLUDING CONTENTS;

DROP TABLESPACE ttsidx1 INCLUDING CONTENTS;

In preparation for the next year's business data we need to create new data and index partitions, again in their own tablespaces to facilitate future transport.

CREATE TABLESPACE ttsdat6 DATAFILE '/u01/app/oracle/oratrans/ttsdat6.dbf'

SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

CREATE TABLESPACE ttsidx6 DATAFILE '/u01/app/oracle/oratrans/ttsidx6.dbf'

SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

ALTER TABLE txns ADD PARTITION fy2006

VALUES LESS THAN (to_date('2007-01-01','yyyy-mm-dd') )

TABLESPACE ttsdat6

UPDATE INDEXES (txns_pk_idx (PARTITION fy2006 TABLESPACE ttsidx6));

@indpartrows

PART 3 - Plug in to the archive database

Copy the data and index tablespace datafiles and the export file to the target system. Connect to the target system and verify the database character sets match the source database. This is a very important restriction of Transportable Tablespaces!

CONNECT arch/arch@BING

SELECT * FROM v$nls_parameters WHERE parameter IN ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

Ensure an Oracle directory with necessary permissions exists for data pump.

CREATE OR REPLACE DIRECTORY trans_dir AS 'd:/oradata/oratrans';

GRANT READ, WRITE ON DIRECTORY trans_dir TO ...;

Import the metadata into the target system. Again this is fast because we only import data definitions; the data already exists in the named datafiles. Note use of REMAP_SCHEMA if a different user will own the objects in the target database.

impdp arch/arch@BING parfile=ttsfy1.par

DUMPFILE=ttsfy1.dmp

DIRECTORY=trans_dir

REMAP_SCHEMA=bank:arch

TRANSPORT_DATAFILES='d:\oradata\oratrans\TTSDAT1_WIN.dbf',

'd:\oradata\oratrans\TTSIDX1_WIN.dbf'

By default the new tablespaces will be READ ONLY. In order to create the table partition and EXCHANGE it with the existing discrete table we will have to make them READ WRITE.

ALTER TABLESPACE ttsdat1 READ WRITE;

ALTER TABLESPACE ttsidx1 READ WRITE;

Because this is the first time we are transporting a partition we must create the partitioned table with its first partition. Remember, it must match exactly the source table, and exist in the same tablespace.

CREATE TABLE arch

PARTITION BY RANGE (trans_dt)

( PARTITION fy2001 VALUES LESS THAN (to_date('2002-01-01','yyyy-mm-dd') )

TABLESPACE ttsdat1 )

AS SELECT * FROM txns_fy2001 WHERE 1=0;

Again, the new partition must match exactly including indexes and constraints. Note the tablespace clause to ensure the index is located in the same tablespace.

CREATE UNIQUE INDEX arch_pk_idx ON arch(trans_id,trans_dt) LOCAL

( PARTITION fy2001 TABLESPACE ttsidx1 );

ALTER TABLE arch ADD CONSTRAINT arch_pk PRIMARY KEY (trans_id, trans_dt)

USING INDEX LOCAL ( PARTITION fy2001 );

descr arch

We now have a partitioned table with one empty partition, and our discrete table with data that was transported. To complete the process, simply EXCHANGE data segments between the empty partition and discrete table. Then, count the number of rows, check the size of data segments and status of indexes again.

ALTER TABLE arch EXCHANGE PARTITION fy2001

WITH TABLE txns_fy2001 INCLUDING INDEXES;

@segsize

@indpartrows

When you are convinced the exchange has occurred properly and the partitioned table is functional, drop the now empty discrete table and you are done for this year.

DROP TABLE txns_fy2001 PURGE;

For subsequent years, transport of the next partition would be identical. Then, instead of creating the partitioned table in the target database, we simply ADD another partition and EXCHANGE it with the discrete table again.

ALTER TABLE arch ADD PARTITION fy2002

VALUES LESS THAN (to_date('2003-01-01','yyyy-mm-dd') )

TABLESPACE ttsdat2

UPDATE INDEXES (arch_pk_idx (PARTITION fy2002 TABLESPACE ttsidx2));

ALTER TABLE arch EXCHANGE PARTITION fy2002

WITH TABLE txns_fy2002 INCLUDING INDEXES;

DROP TABLE txns_fy2002 PURGE;

Both Transportable Tablespaces and Table Partitioning are complex options of the Oracle database. They can greatly enhance the performance and manageability of a production system. However, before relying on these features it is imperative to spend some time setting up a test bed and evaluating your specific scenario. There are many other caveats and restrictions. This article demonstrates one simple scenario that can be used as a guide, but is no replacement for reading and understanding the Oracle documentation.

This article and more information on Transportable Tablespaces is available on my website at www.caleb.com/dba

Caleb Small is an independent consultant currently teaching Oracle evening courses at Camosun College in Victoria, BC.

He can be contacted at caleb@caleb.com