Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Krishnaprasad.

Asked: February 16, 2018 - 11:36 am UTC

Last updated: February 22, 2018 - 2:02 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi Team,

we have situation where we have two partition table (range partition ) ,example table A1 ,A1_history .


application team want to archive all the partition except last 3 partitions , from Current production table to archival table .

we have recommending to use exchange partition to use below action plan .


kindly create temporary table which will store partition data. using below command data will be shifted to temporary table .

use below syntax for the same :-
===============================
ALTER TABLE <SOURCE_TABLE> EXCHANGE PARTITION <PARTITION_NAME> WITH TABLE <TEMPORARY TABLE> update indexes INDEXES <with | without> VALIDATION


The second form swaps a table for a partition:
==================================================
ALTER TABLE target_table EXCHANGE PARTITION <PARTITION_NAME> WITH TABLE <TEMPORARY TABLE> update indexes <with | without> VALIDATION




my question is : exchange partition from table to temp table will be done , but from temp table to archival partition is command is correct since ,it seems that again it is exchanging my temp data ,not archival partition data .

here i mean is from
command 1 : A1 to temp ===> ok
command 2: Temp to A1_history or A1_history to Temp ??? since command 1 and 2 look similar it should be something
like temp to table partition

Request you to review my command ,surely i will test in uat before moving ahead with prod and suggest us in case if we have some other method which is more better that this one .


and Connor said...

Here's an example where we move data

- from SRC to TMP
- from TMP to ARCH

SQL> create table src ( x int, y int )
  2  partition by list ( x )
  3  (
  4    partition p1src values (1),
  5    partition p2src values (2)
  6  );

Table created.

SQL>
SQL> create table arch ( x int, y int )
  2  partition by list ( x )
  3  (
  4    partition p1arch values (1),
  5    partition p2arch values (2)
  6  );

Table created.

SQL>
SQL> create table tmp ( x int, y int );

Table created.

SQL>
SQL> insert into src values (1,1);

1 row created.

SQL> insert into src values (2,2);

1 row created.

SQL>
SQL> alter table src exchange partition p1src with table tmp;

Table altered.

SQL> select * from tmp;

         X          Y
---------- ----------
         1          1

SQL> alter table arch exchange partition p1arch with table tmp;

Table altered.

SQL> select * from arch;

         X          Y
---------- ----------
         1          1


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Krishnaprasad Yadav, February 21, 2018 - 12:13 pm UTC

Hi Team,

After table partition movement , does it affect indexes of both table , do we need to rebuilt it ???
Connor McDonald
February 22, 2018 - 2:02 am UTC

It is likely that your indexes (or partitions thereof) will be set to unusable depending on the operation you do

SQL> create table src ( x int, y int )
  2      partition by list ( x )
  3      (
  4        partition p1src values (1),
  5        partition p2src values (2)
  6      );

Table created.

SQL>
SQL> create index src_glob on src ( y ) ;

Index created.

SQL> create index src_loc on src ( x ) local;

Index created.

SQL>
SQL> create table arch ( x int, y int )
  2      partition by list ( x )
  3      (
  4        partition p1arch values (1),
  5        partition p2arch values (2)
  6      );

Table created.

SQL>
SQL> create index arch_glob on arch ( y ) ;

Index created.

SQL> create index arch_loc on arch ( x ) local;

Index created.

SQL>
SQL> create table tmp ( x int, y int );

Table created.

SQL> create index tmp_loc on tmp ( x );

Index created.

SQL>
SQL> insert into src values (1,1);

1 row created.

SQL> insert into src values (2,2);

1 row created.

SQL>
SQL>
SQL>
SQL> alter table src exchange partition p1src with table tmp;

Table altered.

SQL>
SQL> select * from tmp;

         X          Y
---------- ----------
         1          1

1 row selected.

SQL> select index_name, partition_name, status from user_ind_partitions where substr(index_name,1,3) in ('SRC','ARC','TMP')
  2  union all
  3  select index_name, null, status from user_indexes where substr(index_name,1,3) in ('SRC','ARC','TMP') ;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
ARCH_LOC                       P1ARCH                         USABLE
ARCH_LOC                       P2ARCH                         USABLE
SRC_LOC                        P1SRC                          UNUSABLE
SRC_LOC                        P2SRC                          USABLE
ARCH_GLOB                                                     VALID
ARCH_LOC                                                      N/A
SRC_GLOB                                                      UNUSABLE
SRC_LOC                                                       N/A
TMP_LOC                                                       UNUSABLE

9 rows selected.

SQL>
SQL> alter table arch exchange partition p1arch with table tmp;

Table altered.

SQL>
SQL> select index_name, partition_name, status from user_ind_partitions where substr(index_name,1,3) in ('SRC','ARC','TMP')
  2  union all
  3  select index_name, null, status from user_indexes where substr(index_name,1,3) in ('SRC','ARC','TMP') ;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
ARCH_LOC                       P1ARCH                         UNUSABLE
ARCH_LOC                       P2ARCH                         USABLE
SRC_LOC                        P1SRC                          UNUSABLE
SRC_LOC                        P2SRC                          USABLE
ARCH_GLOB                                                     UNUSABLE
ARCH_LOC                                                      N/A
SRC_GLOB                                                      UNUSABLE
SRC_LOC                                                       N/A
TMP_LOC                                                       UNUSABLE

9 rows selected.

SQL>
SQL> select * from arch;

         X          Y
---------- ----------
         1          1

1 row selected.

SQL>
SQL>

SQL> create table src ( x int, y int )
  2      partition by list ( x )
  3      (
  4        partition p1src values (1),
  5        partition p2src values (2)
  6      );

Table created.

SQL>
SQL> create index src_glob on src ( y ) ;

Index created.

SQL> create index src_loc on src ( x ) local;

Index created.

SQL>
SQL> create table arch ( x int, y int )
  2      partition by list ( x )
  3      (
  4        partition p1arch values (1),
  5        partition p2arch values (2)
  6      );

Table created.

SQL>
SQL> create index arch_glob on arch ( y ) ;

Index created.

SQL> create index arch_loc on arch ( x ) local;

Index created.

SQL>
SQL> create table tmp ( x int, y int );

Table created.

SQL> create index tmp_loc on tmp ( x );

Index created.

SQL>
SQL> insert into src values (1,1);

1 row created.

SQL> insert into src values (2,2);

1 row created.

SQL>
SQL>
SQL>
SQL> alter table src exchange partition p1src with table tmp including indexes update indexes;

Table altered.

SQL> select * from tmp;

         X          Y
---------- ----------
         1          1

1 row selected.

SQL> select index_name, partition_name, status from user_ind_partitions where substr(index_name,1,3) in ('SRC','ARC','TMP')
  2  union all
  3  select index_name, null, status from user_indexes where substr(index_name,1,3) in ('SRC','ARC','TMP') ;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
ARCH_LOC                       P1ARCH                         USABLE
ARCH_LOC                       P2ARCH                         USABLE
SRC_LOC                        P1SRC                          USABLE
SRC_LOC                        P2SRC                          USABLE
ARCH_GLOB                                                     VALID
ARCH_LOC                                                      N/A
SRC_GLOB                                                      VALID
SRC_LOC                                                       N/A
TMP_LOC                                                       VALID

9 rows selected.

SQL>
SQL> alter table arch exchange partition p1arch with table tmp including indexes update indexes;

Table altered.

SQL>
SQL> select index_name, partition_name, status from user_ind_partitions where substr(index_name,1,3) in ('SRC','ARC','TMP')
  2  union all
  3  select index_name, null, status from user_indexes where substr(index_name,1,3) in ('SRC','ARC','TMP') ;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
ARCH_LOC                       P1ARCH                         USABLE
ARCH_LOC                       P2ARCH                         USABLE
SRC_LOC                        P1SRC                          USABLE
SRC_LOC                        P2SRC                          USABLE
ARCH_GLOB                                                     VALID
ARCH_LOC                                                      N/A
SRC_GLOB                                                      VALID
SRC_LOC                                                       N/A
TMP_LOC                                                       VALID

9 rows selected.

SQL>
SQL> select * from arch;

         X          Y
---------- ----------
         1          1

1 row selected.

SQL>


In 12.2 and 18c, there are a number of improvements in this area, which allow many partition operations to be done online.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.