Blog

Tables Transportables

Tables et pas Tablespaces ! Avec Oracle 11g, vous pouvez n’exporter que les tables qui vous intéressent d’un tablespace afin les accrocher à une autre base de données. La copie des données se fait toujours par copie physique des fichiers. Quoiqu’il en soit voilà comment faire…

Avant de commencer

On supposera que EMP est une table du schéma SCOTT et que vous avez créé un objet « directory » comme celui ci-dessous :

!mkdir -p /u01/app/oracle/export/dpdump
create directory dp_exp
      as '/u01/app/oracle/export/dpdump';

Pour pouvoir faire un export TRANSPORTABLE de votre table, il faut que le tablespace qui la contient soit en READ-ONLY :

alter tablespace users read only;

On utilisera donc surtout cette fonctionnalité avec des partitions de type range par date dans le cadre d’un data warehouse pour permettre de passer les tablespaces en lecture seule le temps de l’export… A moins d’utiliser RMAN pour faire un TSPITR avant de faire l’export.

Exécuter un export « TRANSPORTABLE » de table

Rien de bien sorcier :

expdp "/ as sysdba" 
      dumpfile=dp_exp:t_table.dmp 
      TABLES=scott.emp  
      TRANSPORTABLE=always

Export: Release 11.2.0.2.0 - Production on Tue Oct 5 11:16:42 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" dumpfile=dp_exp:t_table.dmp TABLES=scott.emp TRANSPORTABLE=always 
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/export/dpdump/t_table.dmp
******************************************************************************
Datafiles required for transportable tablespace USERS:
  /u01/app/oracle/oradata/BLACK/users01.dbf
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 11:17:23

Une fois que c’est fait, vous pouvez copier le(s) fichier(s) qui contien(nen)t la table sur l’environnement cible. Il est bien sur possible de les renommer. Pour les besoins de l’exemple, j’ai copié le fichier users01.dbf. Le nouveau fichier s’appelle copy.dbf sur le même environnement.

L’import TRANSPORTABLE n’existe pas

La clause transportable de la commande impdp est un faux amis. En fait un import « TRANSPORTABLE » n’existe pas ; seul l’export est « TRANSPORTABLE ». Alors à quoi sert la clause TRANSPORTABLE de impdp vous demandez-vous ? Et bien à faire un export TRANSPORTABLE quand l’import et l’export sont faits en une seule opération via un DBLink (et le mot clé NETWORK_LINK)

Comment fait-on un import d’une table exportée avec TRANSPORTABLE ?

Import d’un export TRANSPORTABLE de tables

Vous allez importer la table en ajoutant simplement un nouveau tablespace. La syntaxe d’import utilise le paramètre « transport_datafiles » comme ci-dessous…

Notes :

  • L’option la plus sage est d’importer la table dans un nouvel utilisateur ou de supprimer la table d’origine et tous les objets dépendants avant de la réimporter. C’est le sujet du premier script
  • L’option sans doute moins sage, quoique vous ne risquez normalement pas de tout détruire est d’importer la table en la renommant avec le paramètre REMAP_TABLE dans le même utilisateur. Vous pouvez alors au choix, soit préalablement renommer tous les objets dependants de la table d’origine, soit ne pas importer les contraintes, index, triggers ou autres MV Logs avec le paramètre exclude. C’est le sujet du second script

Exemple 1: Reimporter la table dans un autre schéma

Vous noterez que j’ai exclu les contraintes référentielles de l’import dans la mesure où la table DEMO.DEPT n’existe pas :

sqlplus / as sysdba

create user demo 
  identified by demo
  default tablespace users
  temporary tablespace temp;

grant connect, resource to demo;

exit;

cp /u01/app/oracle/oradata/BLACK/users01.dbf 
        /u01/app/oracle/oradata/BLACK/copy01.dbf

impdp "/ as sysdba"    
    dumpfile=dp_exp:t_table.dmp   
    TABLES=scott.emp  
    transport_datafiles='/u01/app/oracle/oradata/BLACK/copy01.dbf' 
    exclude=ref_constraint 
    remap_schema=scott:demo remap_tablespace=users:copy

Import: Release 11.2.0.2.0 - Production on Tue Oct 5 11:51:12 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" dumpfile=dp_exp:t_table.dmp TABLES=scott.emp transport_datafiles=/u01/app/oracle/oradata/BLACK/copy01.dbf exclude=ref_constraint remap_schema=scott:demo remap_tablespace=users:copy 
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 11:51:19

Exemple 2 Import dans le même schéma

sqlplus / as sysdba
drop tablespace copy including contents;
drop user demo cascade;

select constraint_name 
  from dba_constraints 
 where owner='SCOTT' 
   and table_name='EMP';

CONSTRAINT_NAME
------------------------------
FK_DEPTNO
PK_EMP

alter table scott.emp 
  rename constraint FK_DEPTNO to FK_DEPTNO_ORIG;

alter table scott.emp 
  rename constraint PK_EMP to PK_EMP_ORIG;

select owner||'.'||index_name idx
  from dba_indexes 
 where table_owner='SCOTT' and table_name='EMP';

IDX
------------
SCOTT.PK_EMP

alter index SCOTT.PK_EMP 
  rename to PK_EMP_ORIG;

exit

impdp "/ as sysdba"    
    dumpfile=dp_exp:t_table.dmp   
    TABLES=scott.emp  
    transport_datafiles='/u01/app/oracle/oradata/BLACK/copy01.dbf' 
    remap_table=emp:emp2 remap_tablespace=users:copy

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" dumpfile=dp_exp:t_table.dmp TABLES=scott.emp transport_datafiles=/u01/app/oracle/oradata/BLACK/copy01.dbf remap_table=emp:emp2 remap_tablespace=users:copy 
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TABLE_01" succes
sfully completed at 11:36:07

Le tablespace importé est en lecture seule; vous pouvez repasser les 2 tablespaces en lecture/écriture :

sqlplus / as sysdba
alter tablespace users read write;
alter tablespace copy read write;
exit

Conclusion

Si vous utilisez les tablespaces transportables, il s’agit simplement d’une toute petite variation ou vous remplacez le mot TABLESPACES par le mot TABLES. Un peu de nettoyage et c’est fini :

sqlplus / as sysdba
drop tablespace copy including contents and datafiles;
exit
Thomas Bordet
Thomas Bordet
Voir tous ses articles
Articles récents

Mentions légales & Politique de confidentialité