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:

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:

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

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

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:

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.

  Shell

  FULL=Y

  1

  FULL=Y

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

  Shell

  SCHEMAS=esquema01 [, ...]

  1	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.

  Shell

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

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

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

  Shell

  TABLESPACES=tbs01 [,...]

  1	TABLESPACES=tbs01 [,...]

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

  Shell

  CONTENT={ ALL | DATA_ONLY | METADATA_ONLY }

  1	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:

  Shell

  VERSION={ COMPATIBLE | LATEST | version_string }

  1	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.
  
  Shell

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

  1 2	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:
  
  Shell

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

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

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

  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.

  1 2	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.
  
  Shell

  DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS

  1	DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS

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

  REMAP_SCHEMA=source_schema:target_schema

  1	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:

  Shell

  SQL> ALTER USER schema_name IDENTIFIED BY new_password

  1	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:
  Shell

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

  1	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:

  Shell

  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;

  1 2 3 4 5 6 7 8 9 10

  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:

  Shell

  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

  1 2 3 4 5 6 7

  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.

  Shell

  REMAP_DATAFILE source_datafile:target_datafile

  1	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:

  Shell

  REMAP_TABLE=source_table:target:table

  1	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.

  Shell

  REMAP_TABLEPACE=source_tablespace:target:tablespace

  1	REMAP_TABLEPACE=source_tablespace:target:tablespace

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

  Shell

  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;

  1 2 3 4

  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:

  Shell

  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

  1 2 3 4 5 6 7 8

  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:

  Shell

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

  1 2 3 4 5 6

  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.

  Shell

  SQLFILE=[directory_object:]file_name

  1	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:

  Shell

  -- CONNECT demoexp

  1	-- 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:

  Shell

  TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}

  1	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:

    Shell

    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

    1 2 3 4 5 6 7 8

    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:

    Shell

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

    1 2 3 4 5 6 7

    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