Sans doute l’avez-vous remarqué: il est possible d’échanger une partition Oracle avec une table qui n’est pas dans le même schéma. J’ai eu une discussion à ce sujet hier et j’ai donc construit un exemple rapide pour illustrer cette fonctionnalité. C’est très utile lorsque vous utilisez Oracle Partitioning pour faire de l’archivage et que, pour des raisons évidentes, vous ne voulez pas laisser les données archivées dans le schéma de production… et vice versa si vous voulez raccrocher les données archivées dans le schéma de production.
Pour être tout à fait honnête et rendre à Christo ce qui lui appartient, je dois dire que cet article est très largement inspiré d’un article de mon ancien collègue Canadien, à moitié Bulgare, qui joue peut être encore avec mon drum set et qui s’intitule « How to move a table to another schema?« . Dans cet article, Christo va un pas plus loin et utilise cette technique pour déplacer une table d’un schéma à un autre en utilisant que des ordres DDL… A condition que vous puissiez utiliser le partitioning. Mais revenons à notre exemple…
Le schéma ci-dessous illustre assez bien ce je vous  propose de faire:

Vous noterez que la table de départ et la table de destination peuvent également avoir des noms différents.

Etape 1 : Créer les schémas d’exemple et les tables associées

Dans cette première partie, il s’agit de créer les conditions de l’exemple. On créera donc:

• Un schéma LIVE et un schéma HISTORY pour stocker nos tables

connect / as sysdba
create user LIVE
  identified by LIVE
  default tablespace USERS
  temporary tablespace TEMP;
create user HISTORY
  identified by HISTORY
  default tablespace USERS
  temporary tablespace TEMP;
grant connect, resource to LIVE;
grant connect, resource to HISTORY;

• Une table VENTES partitionnée et quelques données associées; on donnera les droits à HISTORY de manipuler cette table

connect LIVE/LIVE
create table ventes(jour    DATE,
                    produit VARCHAR2(80),
                    montant NUMBER)
     partition by range (jour)
     (partition ventes2005
        values less than (to_date('01/01/2006','DD/MM/YYYY')),
     partition ventes2006
        values less than (to_date('01/01/2007','DD/MM/YYYY')),
     partition ventes2007
        values less than (to_date('01/01/2008','DD/MM/YYYY')),
     partition ventes2008
        values less than (to_date('01/01/2009','DD/MM/YYYY')),
     partition ventes2009
        values less than (to_date('01/01/2010','DD/MM/YYYY')),
     partition ventes2010
        values less than (to_date('01/01/2011','DD/MM/YYYY')),
     partition ventesfutur
        values less than (MAXVALUE));
begin
  for i in 1..55 loop
    insert into ventes
      values
     (add_months(to_date('01/08/2009','DD/MM/YYYY'), -i),
      'Oracle SOA Suite',
       mod(i*10, 90));
  end loop;
end;
/
commit;
create index ventes_idx on ventes(jour,produit) local;
grant alter, select on ventes to history;
select count(*) from ventes partition (ventes2005);
  COUNT(*)
----------
        12

• Une table VENTES_HISTORY également partitionnée mais vide; pour faciliter, la lecture j’ai choisi un nom différent

connect HISTORY/HISTORY
create table ventes_history(
                     jour    DATE,
                     produit VARCHAR2(80),
                     montant NUMBER)
      partition by range (jour)
      (partition ventes2004 values less than (to_date('01/01/2005','DD/MM/YYYY')));
select count(*) from history.ventes_history;
  COUNT(*)
----------
         0

Nota Bene:
Pour enrichir un peu l’exemple, les 2 tables ne sont pas indexées de la même façon

Etape 2 : Déplacer la partition VENTES2005 dans le schéma HISTORY

Dans cette seconde étape, il s’agit en fait de créer une table VENTES2005 dans le schéma HISTORY et de l’échanger avec la partition VENTES2005 de la table LIVE.VENTES:

connect HISTORY/HISTORY
create table ventes2005(
                    jour    DATE,
                    produit VARCHAR2(80),
                    montant NUMBER);
create index ventes2005 on ventes2005(jour, produit);
select count(*) from ventes2005;
  COUNT(*)
----------
         0
alter table live.ventes
   exchange partition ventes2005
   with table history.ventes2005
   including indexes without validation;
select index_name, status
  from user_indexes
 where table_name='VENTES2005';
INDEX_NAME   STATUS
------------ ------
VENTES2005   VALID
select count(*) from ventes2005;
  COUNT(*)
----------
         12
connect LIVE/LIVE
col index_name format a10
col partition_name format a11
col status format a6
select index_name, partition_name, status
  from user_ind_partitions
 where index_name='VENTES_IDX'
  order by partition_position;
INDEX_NAME PARTITION_N STATUS
---------- ----------- ------
VENTES_IDX VENTES2005  USABLE
VENTES_IDX VENTES2006  USABLE
VENTES_IDX VENTES2007  USABLE
VENTES_IDX VENTES2008  USABLE
VENTES_IDX VENTES2009  USABLE
VENTES_IDX VENTES2010  USABLE
VENTES_IDX VENTESFUTUR USABLE
select count(*)
  from live.ventes partition (ventes2005);
  COUNT(*)
----------
        0
alter table live.ventes
  drop partition ventes2005;

Etape 3 : Ajouter la table VENTES2005 à la table HISTORY.VENTES_HISTORY

Il s’agit en réalité de créer une partition vide VENTES2005 à la table HISTORY.VENTES_HISTORY et de l’échanger avec la table VENTES2005 créée à l’étape précédente:

connect HISTORY/HISTORY
drop index VENTES2005;
alter table ventes_history
   add partition ventes2005
   values less than (to_date('01/01/2006','DD/MM/YYYY'));
select count(*)
  from history.ventes_history partition (ventes2005);
COUNT(*)
----------
        0
alter table ventes_history
   exchange partition ventes2005
   with table ventes2005
   including indexes without validation;
select count(*)
  from history.ventes_history partition (ventes2005);
  COUNT(*)
----------
        12
select count(*)
  from ventes2005;
  COUNT(*)
----------
         0
drop table ventes2005 purge;

Etape 4: Nettoyer l’environnement

Comme toujours, remettez la base de données dans le même état que vous l’avez trouvée (à condition que vous ayez créé les schéma bien sur).

connect / as sysdba
drop user live cascade;
drop user history cascade;

Conclusion

Vous me direz, c’est loin d’être fini! Il faudra peut-être déplacer la partition dans un nouveau tablespace, la compresser, exporter avec Data Pump et/ou un tablespace transportables, passer le tablespace en lecture seule, enlever les tablespaces des sauvegardes; il faut bien en garder pour la suite, non?