Oracle – Importación de Datos con Data Pump Import [impdp]

0. Índice de contenidos.

• 1. Entorno
• 2. Introducción
• 3. Preparación del entorno
• 4. Comando impdp
• 5. Más información

1. Entorno

Para realizar este tutorial se ha empleado el siguiente entorno de desarrollo:

• Hardware: Mac Book Pro 15″ Intel Core i7 2,8 GHz, 16 GB RAM
• Sistema Operativo: Mac OS X Yosemite
• Máquina virtual VirtualBox versión 4.3.20: Sistema operativo: Windows 7 Ultimate 32bits 2GB RAM
• Oracle Database 11g Release 11.2.0.1.0

2. Introducción

En esta segunda parte del tutorial vamos a ver la herramienta de importación impdp de Oracle.

En la siguiente URL puedes ver la primera parte del tutorial, relativa a la exportación:

http://www.adictosaltrabajo.com/tutoriales/tutoriales.php?pagina=tutorialExpdp

03. Preparar el entorno.

Para realizar este tutorial vamos a realizar primero un export del esquema demoexp creado en el tutorial http://www.adictosaltrabajo.com/tutoriales/tutoriales.php?pagina=tutorialExpdp

Los pasos para crear el usuario y el modelo de datos son los siguientes:

  C:>sqlplus system/****

  SQL> CREATE TABLESPACE demoexp_tbs DATAFILE 'c:/oracle/oradata/orcl/demoexp.dbf' size 10M;

  SQL> CREATE USER demoexp IDENTIFIED BY demoexp DEFAULT TABLESPACE demoexp_tbs

  SQL> GRANT ALL PRIVILEGES TO demoexp;

  SQL> connect demoexp/demoexp

  SQL> CREATE TABLE TABLA01 (
                 CAMPO1_1 NUMBER(10),
                 CAMPO1_2 VARCHAR2(10),
                 CAMPO1_3 TIMESTAMP,
                 CAMPO1_4 CLOB);

  SQL> CREATE UNIQUE INDEX tabla01_idx ON TABLA01(CAMPO1_1);


  SQL> CREATE TABLE TABLA2 (
                 CAMPO2_1 NUMBER(10),
                 CAMPO2_2 VARCHAR2(10));

  SQL> INSERT INTO TABLA01 VALUES(1, 'Prueba 01', SYSDATE, 'Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - Campo CLOB muy muy largo - ');

  SQL> INSERT INTO TABLA01 VALUES(2, 'Prueba 02', SYSDATE, 'Campo CLOB 2');
  SQL> INSERT INTO TABLA01 VALUES(3, 'Prueba 03', SYSDATE, 'Campo CLOB 3');
  SQL> INSERT INTO TABLA01 VALUES(4, 'Prueba 04', SYSDATE, 'Campo CLOB 4');
  SQL> INSERT INTO TABLA01 VALUES(5, 'Prueba 05', SYSDATE, 'Campo CLOB 5');
  SQL> INSERT INTO TABLA01 VALUES(6, 'Prueba 06', SYSDATE, 'Campo CLOB 6');
  SQL> INSERT INTO TABLA01 VALUES(7, 'Prueba 07', SYSDATE, 'Campo CLOB 7');
  SQL> INSERT INTO TABLA01 VALUES(8, 'Prueba 08', SYSDATE, 'Campo CLOB 8');


  SQL> INSERT INTO TABLA02 VALUES (1, 'Prueba 21');
  SQL> INSERT INTO TABLA02 VALUES (2, 'Prueba 22');
  SQL> INSERT INTO TABLA02 VALUES (3, 'Prueba 23');
  SQL> INSERT INTO TABLA02 VALUES (4, 'Prueba 24');
  SQL> INSERT INTO TABLA02 VALUES (5, 'Prueba 25');
  SQL> INSERT INTO TABLA02 VALUES (6, 'Prueba 26');

  SQL>CREATE DIRECTORY dir_demoexp_dmp as 'c:/tmp/demo/export/';

Una vez tenemos el modelo y hemos creado el directorio de exportación, realizamos la exportación del esquema completo sobre la que vamos a trabajar en este tutorial. Para ello prepararemos el fichero de parámetros para expdp c:\tmp\demo\export\export.par:

      USERID=demoexp
      DIRECTORY=dir_demoexp_dmp
      DUMPFILE=export_demo.dmp
      LOGFILE=log_export.log
      SCHEMAS=DEMOEXP
  

Y generaremos el fichero de exportación export_demo.dmp con todo el esquema:

  C:>cd c:\tmp\demo\export
  C:\tmp\demo\export>expdp -parfile export.par

Para realizar la importación, crearemos un directorio de importación donde copiaremos el fichero dmp y se generarán los logs:

   C:> mkdir c:\demo\import
   C:> copy c:\demo\export\EXPORT_DEMO.DMP c:\demo\import\

Por último, creamos el usuario demoimp que vamos a emplear para realizar la importación y creamos el objeto DIRECTORY de Oracle para tener acceso al directorio que acabamos de crear:

  C:> sqlplus system/*****
  SQL> CREATE USER demoimp IDENTIFIED BY demoimp;
  SQL> GRANT ALL PRIVILEGES TO demoimp;
  SQL> CREATE DIRECTORY dir_demoimp_dmp as 'c:/tmp/demo/import/';

Una vez preparado el entorno, revisemos el comando de importación impdp.

4. Comando impdp

El funcionamiento general del comando de importación impdp es análogo al del expdp. Podemos parametrizarlo por tres vías:

• Línea de comando, pasandolos directamente al comando.
• Fichero de parámetros.
• Interfaz interactiva.

Al igual que en el tutorial anterior, nos centraremos en el fichero de parámetros, al ser el más recomendado, sobre todo si queremos especificar filtros.

Los parámetros generales compartidos con expdp son los siguientes:

• USERID: será el usuario que realizará la exportación

• DIRECTORY: será el objeto DIRECTORY que hemos creado previamente en oracle y que apunta al directorio de exportación.

• DUMPFILE: definirá el nombre del fichero de exportación.

• LOGFILE: definirá el fichero de trazas con el detalle de la exportación

• FULL: especifica el modo de importación completa. Tratará de importar el fichero dmp completo.

          FULL=Y
        

• SCHEMAS: se importará el esquema o los esquemas indicados. El formato es el siguiente:

        SCHEMAS=esquema01 [, ...] 
      

• TABLES: En este caso, se especificarán las tablas que se desean importar. Tened en cuenta que importará tanto las tablas como todos sus objetos dependientes si exisitieran: estadísticas, índices, etc.

          TABLES=[esquema.]tabla[:particion] [, ...]
        

• TABLESPACES En este caso importará todos los objetos asociados al tablespace indicado, así como sus objetos dependientes:

          TABLESPACES=tbs01 [,...]
        

• CONTENT: permite especificar si queremos importar únicamente los metadatos, los datos o ambos:

        CONTENT={ ALL | DATA_ONLY | METADATA_ONLY }
      

• VERSION: Con VERSION podremos especificar la versión de oracle que se empleará a la hora de importar los objetos.La Versión se establecerá de la siguiente manera:

        VERSION={ COMPATIBLE | LATEST | version_string }
      

  A diferencia con el export, al especificar una versión concreta en el import lo que le indicamos a Oracle es la versión destino, de forma que cualquier objeto que no sea compatible con esa versión no será importado.

• INCLUDE y EXCLUDE: el funcionamiento es análogo al funcionamiento en el export, permitiendo incluir o excluir objetos de determinados tipos. Podéis ver la descripción detallada de los parámetros en el tutorial de exportación.

        INCLUDE=object_type[:name_clause] [, ... ]
        EXCLUDE=object_type[:name_clause] [, ... ]
      

• QUERY: este parámetro permite filtrar los datos de las tablas a exportar. Se trata de una cláusula WHERE. Su sintaxis es:

      QUERY='[esquema.][tabla:] "query_clause"''
    

  Si no se especifica tabla, se aplicará la clausula WHERE a todas las tablas. Por ejemplo:

       QUERY='tabla01:"WHERE campo1 < 3"' -- Aplicará unicamente a la exportación de la tabla01
       QUERY='"WHERE ROWNUM < 10"' -- Aplicará a la exportación de cada una de las tablas.
    

Además de estos parámetros comunes con la herramienta de exportación, impdp dispone de los siguientes parámetros propios:

• DATA_OPTIONS: en la importación únicamente tiene un valor disponible SKIP_CONSTRAINT_ERRORS. Si se establece esta opción, oracle continuará con la importación aunque se produzca una violación de referencia inmediata, una non-referred constraint violation. La violación se mostrará en el log, pero se continuará con la importación. Si no se indica este valor, si se produce cualquier tipo de violación de referencia, se realizará automáticamente un rollback.

        DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS
    

• REMAP_SCHEMA: permite establecer un esquema distinto para la importación.

    
      REMAP_SCHEMA=source_schema:target_schema
    

  Hay que tener en cuenta que hay determinadas situaciones en que impdp es incapaz de realizar el cambio de esquema. Por ejemplo, no será capaz de detectar el esquema en el body de la definición de tipos, vistas, procedimientos y paquetes.

  Si el esquema no existe en la base de datos destinos, impdp intentará crear tanto el usuario como el esquema, siempre que existan los datos necesarios en el fichero de importación y que el usuario que realiza la importación tiene los privilegios suficientes.
  Si finalmente crea el esquema, una vez finalizada la importación, tendrás que aplicarle una nueva password al usuario propietario para tener acceso al mismo. Así, con un usuario administrador, deberás ejecutar la siguiente sentencia:

          SQL> ALTER USER schema_name IDENTIFIED BY new_password 
      

• REMAP_DATA: este parámetro permite realizar mapeos de datos durante la importación. Un uso muy típico es el recálculo de una PK para evitar posibles conflictos con los datos existentes. La sintaxis es:

      REMAP_DATA=[schema.]tablename.column_name:[schema.]package.function
    

  Si se establece este parámetro, se recalculará cada uno de los valores de la columna indicada. Para ello, llamará para cada valor a la función indicada como segundo parámetro.
  Por ejemplo, si quisieramos transformar el identificador de la tabla01 de manera que se incrementen todos los ids en 10 valores, crearíamos un paquete en el esquema de demoimp de la siguiente manera:

      CREATE OR REPLACE PACKAGE demo AS
        FUNCTION recalcula_id (OLD_ID NUMBER) RETURN NUMBER;
      END demo;
  
      CREATE OR REPLACE PACKAGE BODY DEMO AS
        FUNCTION recalcula_id (OLD_ID NUMBER) RETURN NUMBER IS
          BEGIN
            RETURN (OLD_ID + 10);
        END recalcula_id;
      END DEMO;
      

  Y estableceremos los siguientes parámetros de importación:

        USERID=demoimp
        DIRECTORY=dir_demoimp_dmp
        DUMPFILE=export_demo.dmp
        LOGFILE=log_import.log
        SCHEMAS=DEMOEXP
        REMAP_SCHEMA=demoexp:demoimp
        REMAP_DATA=TABLA01.CAMPO1_1:DEMO.recalcula_id
      

  Vemos que nos importa todo el esquema de demoexp, y además, en la tabla01 ejecuta para cada resultado la aplicación que hemos indicado en REMAP_DATA, aumentando los ids en 100:
  

• REMAP_DATAFILE: mediante este parámetro podemos remapear un datafile que esté referencia en CREATE LIBRARY, CREATE TABLESPACE o CREATE DIRECTORY.

      REMAP_DATAFILE source_datafile:target_datafile
    

  Tanto source_datafile como target_datafile deberán estar especificados exactamente como queremos que se establezcan en las sentencias sql donde se referencian. Se recomienda establecer los nombres de los datafiles entre comillas para evitar ambigüedades de plataforma.
  Para establecer este parámetro el usuario que realiza la importación deberá tener el rol IMP_FULL_DATABASE.

• REMAP_TABLE: en este caso permitirá renombrar una determinada tabla:

          REMAP_TABLE=source_table:target:table
      

  Hay que tener en cuenta que sólo se renombrarán tablas que se creen durante la importación. Si se trata de una actualización de una tabla existente, no se podrá renombrar.

• REMAP_TABLESPACE: Permitirá especificar el tablespace destino a partir del origen.

          REMAP_TABLEPACE=source_tablespace:target:tablespace
      

  Para realizar la prueba, primero eliminamos las tablas y creamos un tablespace para demoimp:

        C:>sqlplus demoimp/demoimp
        SQL>drop table tabla01;
        SQL>drop table tabla02;
        SQL> CREATE TABLESPACE demoimp_tbs DATAFILE 'c:/oracle/oradata/orcl/demoimp.dbf' size 10M;
      

  Establecemos los parámetros de importación:

      USERID=demoimp
      DIRECTORY=dir_demoimp_dmp
      DUMPFILE=export_demo.dmp
      LOGFILE=log_import.log
      SCHEMAS=DEMOEXP
      REMAP_SCHEMA=demoexp:demoimp
      REMAP_TABLESPACE=demoexp_tbs:demoimp_tbs
      TABLE_EXISTS_ACTION=REPLACE
    

  Realizamos la importación y comprobamos que las tablas se han creado en el nuevo tablespace:

      SQL> SELECT table_name, tablespace_name FROM user_tables;
  
      TABLE_NAME                     TABLESPACE_NAME
      ------------------------------ ------------------------------
      TABLA02                        DEMOIMP_TBS
      TABLA01                        DEMOIMP_TBS
    

• SQLFILE: Estableciendo este parámetro, no se llevará a cabo la importación en la base de datos, sino que se almacenará en el fichero especificado todas las operaciones del DLL que iban a ser importadas.

      SQLFILE=[directory_object:]file_name
    

  Hay que tener en cuenta que nunca se incluirán las passwords en el fichero. Así, si existiera una sentencia CONNECT en el DLL, ésta se escribiría en el SQLFile únicamente con el nombre del usuario, pero no con la password, y comentado:

      -- CONNECT demoexp
    

  Otro punto a tener en cuenta es que estableciendo este parámetro, se ignorará el parámetro CONTENT pasando automáticamente a METADATA_ONLY.

• TABLE_EXISTS_ACTION: especifica la acción a realizar en caso de que exista una tabla incluida en la importación:

      TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}
    

  Siendo:

  • SKIP: deja la tabla tal cual y continúa con la importación. Esta opción no es válida si el parámetro CONTENT está establecido a DATA_ONLY.

    Si por ejemplo ejecutamos de nuevo la misma importación anterior, indicando SKIP, el resultado será el siguiente:

        USERID=demoimp
        DIRECTORY=dir_demoimp_dmp
        DUMPFILE=export_demo.dmp
        LOGFILE=log_import.log
        SCHEMAS=DEMOEXP
        REMAP_SCHEMA=demoexp:demoimp
        REMAP_DATA=TABLA01.CAMPO1_1:DEMO.recalcula_id
        TABLE_EXISTS_ACTION=SKIP
      

    

    Como se puede ver en las trazas, ninguna de las tablas se ha importado al existir previamente.

  • APPEND: añade los datos de la importación manteniendo los existentes previamente.

    Probemos a volvemos a importar las tablas, pero esta vez sin aplicar la función de modificación de ids:

        USERID=demoimp
        DIRECTORY=dir_demoimp_dmp
        DUMPFILE=export_demo.dmp
        LOGFILE=log_import.log
        SCHEMAS=DEMOEXP
        REMAP_SCHEMA=demoexp:demoimp
        TABLE_EXISTS_ACTION=APPEND
      

    

    En este caso, la importación avisa de que las tablas existen y que va a añadir los nuevos registros en las tablas. Al tener distintos ids, no hay problema.

    Si volvemos a realizar la misma importación, intentando importar registros ya existentes en la tabla01, Oracle dará un error de restricción única, por la primary key de la tabla:

    

  • TRUNCATE: elimina los datos previos (con DELETE) e importa los nuevos.
    Si realizamos la importación anterior modificando el parámetro a TRUNCATE, no dará ningún problema de PK, aunque se seguirá quejando porque existan las tablas. Eliminará TODOS los datos de la tabla e importará los nuevos.

    

  • REPLACE: elimina la tabla y la vuelve a crear con los datos de la importación.Opción no válida si se ha establecido CONTENT=DATA_ONLY ya que impdp no dispondría de información para volver a crear la tabla.

    Si volvemos a realizar la misma importación anterior, con este valor de parámetro, veremos que elimina TODOS los datos, pero esta vez eliminando la tabla, con lo que no dará error por tabla existente:

    

  Otras consideraciones a tener en cuenta con este parámetro son:

  • Si se aplica TRUNCATE o REPLACE y existe alguna foreign key a los datos que se van a eliminar se producirá un error y no se llevará a cabo la operación.

  • Si se aplica APPEND o TRUNCATE, antes de realizar ninguna acción, se realizarán automáticamente los chequeos necesarios para comprobar que los datos son compatibles con la tabla destino. En caso de que no lo sean, no se realizará ninguna acción. Si existen triggers o constraints activos y se da algún problema de integridad se realizará un rollback y se cancelará la operación a menos que se haya establecido el parámetro DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS

  • Si se aplica SKIP, APPEND o TRUNCATE no se realizará ninguna operación sobre los objetos dependientes de la tabla (índices, constraints, etc.), pero si se aplica REPLACE, se eliminarán todos ellos y volverán a crearse siempre y cuando estén definidos en el fichero de importación y no hayan sido excluidos de la importación mediante los parámetros INCLUDE o EXCLUDE.

5. Más información.

Al igual que exdp, impdp dispone de otros parámetros que permiten, entre otras cosas, poder establecer compresión y encriptación. Podéis verlos en detalle en la documentación oficial de Oracle:

http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_import.htm