Oracle – Exportación de Datos con Data Pump Export [expdp]

0. Índice de contenidos.

• 1. Entorno
• 2. Introducción
• 3. Preparación del entorno
• 4. Comando expdp
• 5. Algunos ejemplos
• 6. 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

A partir de la versión 10g, Oracle incluye nuevas utilidades para importar y exportar datos mucho más potente que los anteriores comandos imp y exp.

En este tutorial quiero revisar la herramienta de exportación Oracle Data Pump Export y sus parámetros más comunes. Con esta herramienta podemos realizar exportaciones de datos y metadatos de nuestras bases de datos Oracle, permitiéndonos establecer filtros muy finos respecto a los objetos y datos a exportar.

Hay que tener en cuenta que, aunque se trate de una evolución de la anterior herramienta de exportación exp, son comandos totalmente distintos. Si generamos la exportación con el comando exp, tendremos que emplear el comando imp y si la generamos con expdp, lo tendremos que importar con impdp.

3. Preparación del entorno

Para los ejemplos de este tutorial vamos a crear un usuario en la base de datos y a generar dos tablas con sus correspondientes datos. El script de creación es el siguiente:

La operación de exportación la realizará el motor de Oracle, por lo que es necesario que el usuario de Oracle con el que vamos a realizarla, demoexp, tenga acceso al directorio del sistema de ficheros destino. Para ello, deberemos crear un objeto DIRECTORY que apunte al directorio destino y asignar permisos de READ y WRITE sobre el directorio para el usuario.

Por simplificar el proceso, crearemos el directorio con el usuario demoexp directamente de forma que, al ser creador del objeto DIRECTORY, ya tenga los privilegios de lectura y escritura.

4. Comando expdp

Como comentamos en la introducción Data Pump Export es la utilidad de Oracle para la exportación de datos y metadatos.

El comando asociado es el ‘expdp’ y recibe una serie de parámetros para especificar de forma detallada qué datos y metadamos queremos exportar.

Estos parámetros pueden ser proporcionados de tres formas:

• Por línea de comandos, pasados como parámetros al comado expdp
• Mediante un fichero de parámetros
• Mediante un interfaz de comandos interactivo

En este tutorial nos vamos a centrar en el segundo modo, estableciendo un fichero de parámetros, por ser el modo recomendado, sobre todo si se desea establecer filtros. Para indicarle a expdp el fichero con los parámetros emplearemos el parámetro parfile:

Así, crearemos un fichero export.par en el directorio de exportación c:/tmp/demo/export

El contenido básico del fichero será:

Donde:

• USERID será el usuario que realizará la exportación. En nuestro caso será demoexp
• 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

Otros parámetros que podemos emplear en la exportación son los siguientes:

• Métodos de Exportación: expdp soporta los siguientes métodos de exportación:
  • Full export mode: Este método exporta la base de datos completa. Requiere que el usuario tenga el rol EXP_FULL_DATABASE. Para establecer este modo, habrá que incluir el siguiente parámetro al fichero:
    
    Shell

    FULL=Y

    1

    FULL=Y

  • Schema mode: Éste es el modo por defecto. Con este modo el usuario podrá especificar uno o varios esquemas a exportar. Si el usuario desea exportar un esquema distinto del suyo, deberá disponer del rol EXP_FULL_DATABASE. Para establecer este módo, se indicará en el fichero de propiedades con el siguiente parámetro:
    
    Shell

    SCHEMAS=esquema01 [, ...]

    1	SCHEMAS=esquema01 [, ...]

  • Table mode: En este caso, se especificará un conjunto de tablas a exportar. Se exportarán tanto las tablas como sus objetos dependientes: particiones, índices, triggers, constraints, etc. Al igual que en los otros modos, si se desea exportar tablas de esquemas distintos al del usuario, éste deberá disponer del rol EXP_FULL_DATABASE. Para especificar las tablas a exportar, estableceremos el siguiente parámetro en el fichero de configuración:
    
    Shell

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

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

    Todas las tablas deben pertenecer al mismo esquema. Si no se indica el esquema, se tomará por defecto el del usuario que realiza la exportación.

    Oracle permite establecer nombres de tablas con comodines. Sería válido por ejemplo especificar

    Shell

    TABLES=tabla%

    1	TABLES=tabla%

    exportando así tanto la tabla01, como la tabla02.

  • Tablespace mode: En este caso se exportarán todas las tablas pertenecientes a un determinado tablespace así como todos sus objetos dependientes. El usuario deberá disponer dle rol EXP_FULL_DATABASE para pdoer exportar tablas pertenecientes a otros esquemas, si no, únicamente podrá exportar las tablas de su esquema que estén asociadas al tablespace indicado. Se especificará de la siguiente manera:
    Shell

    TABLESPACES=tbs01 [,...]

    1	TABLESPACES=tbs01 [,...]

• CONTENT: Medienta el parámetro CONTENT podremos especificar si queremos exportar únicamente los metadatos, los datos o ambas cosas:
  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 exportar los objetos. Esto permite realizar una exportación en una base de datos Oracle 11g indicándole una version=10.0 para luego importar el resultado en una base de datos Oracle 10g. La Versión se establecerá de la siguiente manera:
  Shell

  VERSION={ COMPATIBLE | LATEST | version_string }

  1	VERSION={ COMPATIBLE | LATEST | version_string }

  donde:

  • COMPATIBLE: es el valor por defecto. Estableciendo esta versión, se tomará el valor indicado en base de datos en el parámetro de oracle COMPATIBLE. Para conocer este valor podemos ejecutar la siguiente query:
    
    Shell

    SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';

    1	SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';

  • LATEST: con esta opción los metadatos se generarán con la versión de la base de datos.
  • version_string: También se puede establecer directamente el número de versión con que queramos que sean generados los metadatos.

  Nota: Algunos parámetros no están soportados en determinadas versiones de oracle y requieren establecer una versión de compatibilidad mínima.

• SAMPLE: Con el parámetro SAMPLE podremos exportar únicamente un determinado porcentaje de bloques de datos de cada una de las tablas. Este parámetro podría ser útil para aquellos casos en que realizamos la exportación de un entorno de producción para pasarlo al entorno de desarrollo y queremos tener algunos datos de orientación. Este parámetro se especifica de la siguiente manera:
  
  Shell

  SAMPLE=[[esquema.]tabla:]porcentaje

  1	SAMPLE=[[esquema.]tabla:]porcentaje

  
  En caso de no especificarse ninguna tabla, se aplicará el parámetro a todas las tabals exportadas.

• REUSE_DUMPFILES: con este parámetro podemos indicar si queremos sobreescribir o no ficheros dmp anteriores con igual nombre que el indicado en el parámetro DUMPFILE. Por defecto no se reutilizan.

  Shell

  REUSE_DUMPFILES={Y|N}

  1	REUSE_DUMPFILES={Y|N}

• ESTIMATE_ONLY: Si establecemos este parámetro, expdp estimará el espacio que la exportación puede consumir, pero no realizará la operación de exportación en sí.

  Shell

  ESTIMATE_ONLY= { Y | N }

  1	ESTIMATE_ONLY= { Y | N }

  Hay que tener en cuenta que cuando establecemos el parámetro ESTIMATE_ONLY no debemos indicar un DUMPFILE, ya que no es compatible, ni podemos filtrar los datos a exportar. Por defecto la estimación se realiza por bloques. Si se quiere basar en estadísticas, habrá que especificar el parámetro ESTIMATE=statistics

• ESTIMATE: mediante este parámetro indicaremos si las estimaciones se han de realizar por bloques o por estadísticas:

  • Estimación por bloques: La estimación se calcula multiplicando el número de bloques de base de datos empleados por los objetos orígenes por el tamaño de bloque apropiado.
  • Estimación por estadística: La estimación se realiza empleando las estadísticas de cada tabla, por lo que es recomendable que las estadísticas de las tablas estén lo más actualizadas posibles.

  Así, el formtao de este parámetro es el siguiente:

  Shell

  ESTIMATE= { BLOCKS | STATISTICS }

  1	ESTIMATE= { BLOCKS | STATISTICS }

• INCLUDE y EXCLUDE:

  Mediante estos parámetro podremos establecer los objetos a incluir o excluir de la exportación. Son parámetros excluyentes, con lo que no pueden emplearse ambos a la vez. Para evitar sorpresas y conseguir exportar exactamente lo que queremos, debemos tener en mente que expdp siempre exporta todos los objetos dependientes, de forma que si estamos exportando un índice, también serán incluidas en la exportación sus estadísticas, o si excluimos una tabla, también estaremos excluyendo sus objetos asociados: índices, particiones, constraints, grants y triggers. De igual forma, si establecemos varios filtros sobre un mismo objeto de la base de datos, entoneces se aplicarán todos como si estuvieran unidos por el operador lógico AND.

  Los objetos que pueden ser filtrados dependerán del modo de exportación establecido. Podemos ver los posibles objetos mediante las siguientes queries:

  • Full Mode:
    
    Shell

    SQL> SELECT object_path, comments FROM database_export_objects ;

    1	SQL> SELECT object_path, comments FROM database_export_objects ;

  • Schema Mode:
    
    Shell

    SQL> SELECT object_path, comments FROM schema_export_objects;

    1	SQL> SELECT object_path, comments FROM schema_export_objects;

  • Table Mode:
    
    Shell

    SQL>SELECT object_path, comments FROM table_export_objects ;

    1	SQL>SELECT object_path, comments FROM table_export_objects ;

  La sintaxis de los parámetros es la siguiente:

  Shell

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

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

  La claúsula name_clause es opcional y permite establecer un filtro mucho más fino permitiendo especificar objetos del tipo indicado. Irá separada del tipo de objeto por un : y se establecerá con doble comillas, ya que los objetos irán especificados con comida simple. En la claúsula podremos emplear operadores IN, LIKE y comodines. Aquí van algunos ejemplos válidos:

  Shell

  INCLUDE=TABLE:" IN ('TABLA01', 'TABLA02')" INCLUDE=INDEX:"LIKE '%_IDX'"

  1 2	INCLUDE=TABLE:" IN ('TABLA01', 'TABLA02')"      INCLUDE=INDEX:"LIKE '%_IDX'"

  El funcionamiento es idéntico con EXCLUDE

• 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"' QUERY='"WHERE ROWNUM < 10"'

  1 2	QUERY='tabla01:"WHERE campo1 < 3"'      QUERY='"WHERE ROWNUM < 10"'

5. Algunos Ejemplos.

Una vez tenemos el modelo de datos y el directorio preparado y hemos revisado el comando, veamos un par de ejemplos.

Si lo que queremos es exportar el esquema de DEMOEXP completo, estableceremos el siguiente fichero de parámetros:

Ejecutamos el comando con el fichero de parámetros:

Si quisiéramos tomar únicamente la tabla01 (excluyendo la tabla02) y de esa tabla tomar únicamente las tres primeras filas, por ejemplo, tendríamos que añadir los siguientes parámetros:

Establecemos también el parámetro REUSE_DUMPFILES para que no nos dé problemas si existe previamente el fichero indicado en DUMPFILE.

Ejecutando de nuevo el comando

tendremos:

Como se puede ver en las trazas, en este caso únicamente se ha exportado la tabla01 y de las 8 filas que tenía se han exportado únicamente 3.

Si lo que queremos es estimar el tamaño de la exportación, ejecutaremos el comando con los siguientes parámetros:

Esto nos dará la estimación por bloques:

Si la queremos realizar por estadística, habría que añadir el parámetro:

Obteniendo este resultado:

Para que sea un poco más exacta la estimación, actualizamos las analíticas de las tablas:

Y una vez actualizadas, volvemos a ejecutar el expdp con unos resultados distintos:

Estos los resultados de estimación por estadísticas son mucho superiores que los de estimación por bloque debido al tamaño tan pequeño de las tablas de prueba. En tablas de mayor tamaño, en entornos productivos, las estimaciones son más ajustadas

6. Más información.

Exdp dispone de otros parámetros que permiten, entre otras cosas, poder establecer compresión y encriptación, pero sólo están disponibles en las versiones Enterprise de Oracle. También existe la posibilidad de especificar varios ficheros de exportación indicando el tamaño máximo de los mismos e incluso realizar la exportación empleando varios jobs en paralelo. Podéis verlos en detalle en la documentación oficial de Oracle:

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