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:

  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 ... ');

  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');

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.

  C:>mkdir c:/tmp/demo
  C:>mkdir c:/tmp/demo/export
  C:>sqlplus demoexp/demoexp
    SQL>CREATE DIRECTORY dir_demoexp_dmp as 'c:/tmp/demo/export/';

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:

  C:/tmp/demo>expdp -parfile export.par

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

El contenido básico del fichero será:

USERID=demoexp
DIRECTORY=dir_demoexp_dmp
DUMPFILE=export_demo.dmp
LOGFILE=export.log

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:

            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:

            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:

            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

            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:

            TABLESPACES=tbs01 [,...]
          

• CONTENT: Medienta el parámetro CONTENT podremos especificar si queremos exportar únicamente los metadatos, los datos o ambas cosas:

        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:

        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:

          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:

         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.

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

         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:

        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:

              SQL> SELECT object_path, comments FROM database_export_objects ;
            

  • Schema Mode:

              SQL> SELECT object_path, comments FROM schema_export_objects;
            

  • Table Mode:

              SQL>SELECT object_path, comments FROM table_export_objects ;
            

  La sintaxis de los parámetros es la siguiente:

        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:

       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:

      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"'
       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:

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

Ejecutamos el comando con el fichero de parámetros:

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

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:

    EXCLUDE=TABLE:'' IN ('TABLA02')''
    QUERY='TABLA01:"WHERE ROWNUM < 4 "'
    REUSE_DUMPFILES=Y
  

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

    expdp -parfile export.par 
  

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:

      USERID=demoexp
      DIRECTORY=dir_demoexp_dmp
      LOGFILE=log_export.log
      SCHEMAS=DEMOEXP
      VERSION=11.2
      ESTIMATE_ONLY=Y
  

Esto nos dará la estimación por bloques:

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

      ESTIMATE=STATISTICS
    

Obteniendo este resultado:

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

    SQL> analyze table tabla01 compute statistics;
    SQL> analyze table tabla02 compute statistics;
  

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