Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 12.10 ALTER TABLE… EXCHANGE PARTITION

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

12.10 ALTER TABLE… EXCHANGE PARTITION

Use the ALTER TABLE… EXCHANGE PARTITION command to exchange an existing table with a partition or subpartition. The command syntax is available in two forms:

      ALTER TABLE target_table
      EXCHANGE PARTITION target_partition
      WITH TABLE source_table
      [(WITH | WITHOUT) VALIDATION];

      and

      ALTER TABLE target_table
      EXCHANGE SUBPARTITION target_partition
      WITH TABLE source_table
      [(WITH | WITHOUT) VALIDATION];

Description

The ALTER TABLE…EXCHANGE PARTITION command swaps an existing table with a partition or subpartition. The structure of the source_table must match the structure of target_table (that is, both tables must have matching columns and data types). When this command completes, the data originally found in the target_partition can now be found in the source_table and the data originally found in the source_table can be found in the target_partition.

This command makes no distinction between a partition and a subpartition:

    ● You can exchange a partition with the EXCHANGE PARTITION or EXCHANGE SUBPARTITION clause.

    ● You can exchange a subpartition with EXCHANGE PARTITION or EXCHANGE SUBPARTITION clause.

Advanced Server accepts the WITHOUT VALIDATION clause, but ignores it; the new table is always validated.

You must own a table to invoke ALTER TABLEEXCHANGE PARTITION or ALTER TABLE EXCHANGE SUBPARTITION against that table.

Parameters

target_table

The name (optionally schema-qualified) of the table in which the partition resides.

target_partition

The name of the partition or subpartition to be replaced.

source_name

The name of the table that will replace the target_partition.

Example

The following command replaces the 1st_qtr partition of the sales table with the contents of the ytd_sales table:

      ALTER TABLE sales
      EXCHANGE PARTITION 1st_qtr
      WITH TABLE ytd_sales;

Previous PageTable Of ContentsNext Page