EXPORT ORACLE 10G/11G

1 OBJETIVOS

Data Pump Export es la utilidad de Oracle que se encarga de copiar los datos a un fichero de volcado con una extensión .dmp en la base de datos ORACLE.
El objetivo de este documento es ver cómo utilizar la utilidad expdp tanto para la exportación de la base de datos completa, como para exportar esquemas, tablas y tablespaces comparándolo con la antigua utilidad de Oracle exp.

2 DATAPUMP EXPORT

Para trabajar con expdp se necesitan los permisos adecuados en el usuario con el que vayamos a realizar la exportación. Cualquier usuario puede exportar objetos de su propiedad o su esquema al completo pero los usuarios no privilegiados deben tener permiso WRITE en el objeto directorio.
Para poder hacer uso de esta utilidad se debe especificar el directorio que va a ser utilizado por ORACLE11g Export/Import. Dependiendo de la exportación, se deberá actualizar cada uno de los diferentes permisos.

Ejemplo de creación de directorios

Existen diversas formas de crear un directorio aquí se muestra una de ellas:

SQL> CONNECT system/******;

Se crea el mapeo del directorio export a '/backup/exports/datapump'

SQL> CREATE OR REPLACE DIRECTORY export AS '/backup/exports/datapump';

Se crea el mapeo del directorio log a '/backup/exports/datapump/log'

SQL> CREATE OR REPLACE DIRECTORY log AS '/backup/exports/datapump/log';

Nota: Estos directorios ya deben estar creados en disco, ya que la sentencia CREATE DIRECTORY define un directorio como objeto en la base de datos, pero no lo crea físicamente. Hay que comprobar que el directorio sea válido y que existan los permisos necesarios para acceder a él en los directorios físicos definidos como directorio de bd.
Nota2: Para crear un directorio es necesario tener el rol DBA o el privilegio CREATE ANY DIRECTORY.

Se otorgan permisos de escritura y lectura sobre los directorios al usuario system, porque en este caso es el encargado de realizar la exportación.

SQL> GRANT read, write ON DIRECTORY export TO SYSTEM;

 

SQL> GRANT read, write ON DIRECTORY log TO system;

Para consultar todos los directorios definidos en la bd y su equivalencia con sistemas de ficheros de s.o.:

    SQL> SELECT * FROM DBA_DIRECTORIES;

Nota:
A la hora de realizar el export se debe indicar el directorio de destino o, en su defecto, ser un usuario privilegiado. En el caso de los usuarios privilegiados, el fichero de volcado .dmp se genera en el directorio indicado por DATA_PUMP_DIR (En los ejemplos anteriores sería el usuario system, verificando que este usuario tenga los permisos adecuados para exportar, explicados posteriormente).

Directory: Este parámetro especifica la localización en la cual Export DataPump or Import DataPump pueden escribir el “dump file “, “log file”, “SQL file”.

2.1 Exportación completa (Full Export Mode)

Una exportación completa se especifica con el parámetro FULL. Para ello el usuario que exporta debe tener el rol DATAPUMP_EXP_FULL_DATABASE (El usuario system tiene este rol).

Se verifica que el usuario que va a realizar la exportación tiene los roles necesarios:

SELECT * FROM SYS.DBA_ROLES

Con esta vista se ven todos los roles del usuario system.

SELECT * FROM DBA_ROLE_PRIVS WHERE granted_role='DATAPUMP_EXP_FULL_DATABASE';

En Oracle 9 lo hacíamos de la siguiente forma (exp):

Ejemplo

    $ exp system/***** file=/backup/exports/todaBD.dmp full=Y log=/backup/exports/logs/exptodaBD.log buffer=100000

exp:  comando con el que se realiza la exportación.
system/*****: usuario y contraseña con el que realizo la  exportación.
file=/backup/exports/todaBD.dmp: ubicación en la que se genera el archivo de volcado con toda la información que voy a exportar.
full=Y: parámetro que indica la exportación de la base de datos completa. (Todos los esquemas de la base de datos,  sus datos,  permisos...)
log=/backup/exports/logs/exptodaBD.log: fichero que contiene el log de la exportación.
buffer=100000: reservamos buffer para la operación.

Nota: El usuario siempre tiene que tener permisos sobre los directorios especificados.

A partir de oracle10 (expdp):

Ejemplo

$ expdp system/***** full=Y DUMPFILE=export:DB10G.dmp LOGFILE=log:expdpDB10G.log;

expdp: comando con el que se realiza la exportación Data Pump
system/*****: usuario y contraseña con el que realizo la  exportación Data Pump  .
full=Y: parámetro que indica la exportación de la base de datos completa.
DUMPFILE=export:DB10G.dmp:  Se pone el nombre del fichero mediante DUMPFILE indicándole el directorio que se había creado y dado permisos para el efecto.
LOGFILE=log:expdpDB10G.log: Indica el nombre del fichero log mediante LOGFILE indicándole el directorio creado para ubicar estos archivos(log)

Nota: Export y log son asignados en el pto. 2

2.2 Exportación de un esquema (Schema Mode)

La exportación de un esquema se especifica usando el parámetro SCHEMAS. El parámetro OWNER de exp (Oracle 9) ha sido reemplazado por dicho parámetro, el cual es usado para especificar el esquema que es exportado. Se debe tener el rol DATAPUMP_EXP_FULL_DATABASE para especificar esquemas que no son el propio.

Para excluir un usuario y todos sus objetos puede emplearse el filtro: EXCLUDE=SCHEMA:”=’<esquema>’”. También puede emplearse este filtro para excluir únicamente alguno de los objetos de dicho usuario: EXCLUDE=TABLE:”IN(‘<tabla>’)”.
Nota: Este parámetro podría ser interesante en exportaciones de toda la bd.

En Oracle 9 lo hacíamos de la siguiente forma (exp):

Copio el esquema de un usuario completo

Ejemplo:

$ exp system/reco1 file=/backup/exports/Esqreco2.dmp log=/backup/exports/logs/expEsqreco2.log owner=reco2 buffer=1000000

exp:  comando con el que se realiza la exportación.
system/reco1: usuario y contraseña con el que realizo la  exportación.
file=/backup/exports/ Esqreco2.dmp: ubicación en la que se genera el archivo de volcado que contendrá la información que voy a exportar.
log=/backup/exports/logs/ expEsqreco2.log: fichero que contiene el log de la exportación.
Owner=reco2: usuario del que se realiza la copia.
buffer=100000: reservamos buffer para la operación.

Nota: El usuario siempre tiene que tener permisos sobre los directorios especificados.

Nota2: ORACLE11g no exporta las tablas con rows=0, ya que no tienen una entrada en la vista DBA_SEGMENTS. Debido a un nuevo feature llamado “deferred segment creation”.

Otra forma de copiar el esquema de un usuario completo:

$ exp reco2/***** file=/backup/exports/Esqreco2.dmp log=/backup/exports/logs/expEsqreco2.log buffer=1000000

exp:  comando con el que se realiza la exportación.
Reco2/*****: usuario y contraseña con el que realizo la  exportación.
file=/backup/exports/ Esqreco2.dmp: ubicación en la que se genera el archivo de volcado que contendrá la información que voy a exportar.
log=/backup/exports/logs/ expEsqreco2.log: fichero que contiene el log de la exportación.
buffer=100000: reservamos buffer para la operación.

Nota: El usuario siempre tiene que tener permisos sobre los directorios especificados.

Nota2:ORACLE11g no exporta las tablas con rows=0, ya que no tienen una entrada en la vista DBA_SEGMENTS. Debido a un nuevo feature llamado “deferred segment creation”.


A partir de Oracle10 (expdp):

Copio el esquema de un usuario completo

Ejemplo:

$ expdp system/***** schemas=reco2 DUMPFILE=export:RECO2.dmp LOGFILE=log:expdpRECO2.log;

expdp comando con el que se realiza la exportación DataPump
system/*****: usuario y contraseña con el que realizo la  exportación.
schemas=reco2: parámetro que indica el esquema a exportar.
DUMPFILE=export:RECO2.dmp:  Se pone el nombre del fichero mediante DUMPFILE indicándole el directorio que se había creado y dado permisos para el efecto.
LOGFILE=log:impdpRECO2.log: Indica el nombre del fichero log mediante LOGFILE indicándole el directorio creado para ubicar estos archivos(log)

Nota: Export y log son asignados en el pto. 2
Reco2 es un usuario que ya tengo creado y tiene permiso sobre los directorios.
El usuario que realiza la exportación debe tener permisos de lectura y escritura sobre el directorio.

Ejemplo de cómo dar permisos sobre un directorio a un usuario

SQL> GRANT READ, WRITE ON DIRECTORY log TO reco2;

 

SQL> GRANT READ, WRITE ON DIRECTORY export TO reco2;

Otra forma de copiar el esquema de un usuario completo:

Ejemplo

$ expdp reco2/***** DUMPFILE=export:RECO2.dmp LOGFILE=log:expdpRECO2.log;

expdp comando con el que se realiza la exportación DataPump
reco2/*****: usuario y contraseña con el que realizo la  exportación.
DUMPFILE=export:RECO2.dmp:  Se pone el nombre del fichero mediante DUMPFILE indicándole el directorio que se había creado y dado permisos para el efecto.
LOGFILE=log:impdpRECO2.log: Indica el nombre del fichero log mediante LOGFILE indicándole el directorio creado para ubicar estos archivos(log)

Nota: Export y log son asignados en el pto. 2
Reco2 es un usuario que ya tengo creado y tiene permiso sobre los directorios.
El usuario que realiza la exportación debe tener permisos de lectura y escritura sobre el directorio.

2.3 Exportación de una tabla (Table Mode)

El parámetro TABLES se usa para especificar  las tablas que son exportadas. Se debe tener el rol DATAPUMP_EXP_FULL_DATABASE para especificar tablas que no son pertenecientes a tu propio esquema. Esta exportación se puede realizar a partir de una exportación de la base de datos completa, de un esquema, de un tablespace o de una tabla.

En Oracle 9 lo hacíamos de la siguiente forma (exp):

Copio tablas específicas de un usuario

Ejemplo

$  exp system/***** file=/backup/exports/Esqreco2.dmp log=/backup/exports/logs/expEsqreco2.log tables=reco2.T2UNIAS rows=y buffer=1000000

Exp: comando con el que se realiza la exportación.
system/*****: usuario y contraseña con el que realizo la  exportación.
file=/backup/exports/ Esqreco2.dmp: ubicación en la que se genera el archivo de volcado que contendrá la información que voy a exportar.
log=/backup/exports/logs/expEsqreco2.log: fichero que contiene el log de la exportación.
Tables=reco2.T2UNIAS: relación de tablas a exportar (modo tabla).
Rows=y: parámetro para exportar o no los datos de las tablas (es opcional).
buffer=100000: reservamos buffer para la operación.

Nota: El usuario siempre tiene que tener permisos sobre los directorios especificados.


Copio tablas de un usuario con una condición específica

Ejemplo

$ exp reco2/***** file=/backup/exports/Esqreco2.dmp tables=T2UNIAS log=/backup/exports/logs/expEsqreco2.log query=\"WHERE an_fab='2001'\" statistics=none 

Exp: comando con el que se realiza la exportación.
Reco2/*****: usuario y contraseña con el que realizo la  exportación.
file=/backup/exports/ Esqreco2.dmp: ubicación en la que se genera el archivo de volcado que contendrá la información que voy a exportar.
Tables=T2UNIAS: relación de tablas a exportar (modo tabla).
log=/backup/exports/logs/expEsqreco2.log: fichero que contiene el log de la exportación.
Query=\”WHERE an_fab=’2001’\”: parámetro que permite seleccionar las filas a exporter según condiciones a cumplir.
Statistics=none: parámetro para evitar que aparezcan todas las estadísticas.

Nota: El usuario siempre tiene que tener permisos sobre los directorios especificados.

A partir de oracle10 ( expdp )

Copio tablas específicas de un usuario

Ejemplo:

$ expdp system/***** tables=reco2.T2UNIAS DUMPFILE=export:T2UNIAS.dmp LOGFILE=log:expdpT2UNIAS.log

expdp comando con el que se realiza la exportación DataPump
system/reco1: usuario y contraseña con el que realizo la  exportación.
tables=T2UNIAS: parámetro que indica la(s)  tabla(s) a exportar.
DUMPFILE=export: T2UNIAS.dmp: Se pone el nombre del fichero mediante DUMPFILE indicándole el directorio que se había creado y dado permisos para el efecto.
LOGFILE=log: expdpT2UNIAS.log: Indica el nombre del fichero log mediante LOGFILE indicándole el directorio creado para ubicar estos archivos(log)

Nota: Export y log son asignados en el pto. 2


Copio tablas de un usuario con una condición específica

Ejemplo

$ expdp reco2/***** DUMPFILE=export:RECO2.dmp tables=reco2.t2COCHE LOGFILE=log:expt2coche.log query=reco2.t2COCHE:\”WHERE an_fab=’2011’\”;

expdp comando con el que se realiza la exportación DataPump
reco2/*****: usuario y contraseña con el que realizo la  exportación.
DUMPFILE=export:RECO2.dmp:  Se pone el nombre del fichero mediante DUMPFILE indicándole el directorio que se había creado y dado permisos para el efecto.
tables=reco2.t2COCHE: parámetro que indica la(s)  tabla(s) a exportar.
LOGFILE=log: expdPt2coche.log: Indica el nombre del fichero log mediante LOGFILE indicándole el directorio creado para ubicar estos archivos (log).
Query=reco2.t2COCHE:\”WHERE an_fab=’2011’\”: parámetro que permite seleccionar las filas a exportar según condiciones a cumplir.

Nota: Export y log son asignados en el pto. 2

2.4 Exportación de un tablespace (Tablespace Mode)

La exportación de un tablespace se realiza usando el parámetro TABLESPACE. Esta exportación se puede realizar a partir de una exportación de la base de datos completa, de un esquema, de un tablespace o de una tabla.

A partir de oracle10 (expdp)

Ejemplo
Se realiza la exportación de un tablespaces llamado prueba:

$ expdp system/***** DUMPFILE=export:expTOTALtablespace.dmp LOGFILE=log:expdPTOTALtablespace1.log TABLESPACES=prueba

expdp comando con el que se realiza la exportación DataPump
system/*****: usuario y contraseña con el que realizo la  exportación.
DUMPFILE=export:expTOTALtablespace.dmp:  Se pone el nombre del fichero mediante DUMPFILE indicándole el directorio que se había creado y dado permisos para el efecto.
LOGFILE=log: expdPTOTALtablespace1.log: Indica el nombre del fichero log mediante LOGFILE indicándole el directorio creado para ubicar estos archivos(log)
TABLESPACES=prueba: parámetro que indica el tablespace(s) a exportar.


Nota: Export y log son asignados en el pto. 2.