Cómo Copiar una Tabla en Oracle: Guía Definitiva para Replicar Datos con Precisión y Eficiencia

Table of Contents

Cómo Copiar una Tabla en Oracle: Guía Definitiva para Replicar Datos con Precisión y Eficiencia

Recuerdo con absoluta claridad aquella tarde de viernes, ya casi al cierre de la semana laboral. Un desarrollador de nuestro equipo, con la frente perlada de sudor y una expresión de pánico, se me acercó. Necesitaba copiar una tabla en Oracle para una prueba crítica que debía ejecutar antes del lunes. La tabla en cuestión era enorme, con millones de registros y una estructura compleja, llena de índices, restricciones y hasta algunos LOBs. La presión era palpable. Un simple INSERT INTO SELECT no iba a ser suficiente, y el tiempo apremiaba. Fue en ese momento cuando la importancia de conocer a fondo las diversas estrategias para replicar tablas en Oracle se hizo más evidente que nunca. No se trataba solo de mover datos, sino de hacerlo bien, preservando la integridad, el rendimiento y la metadata.

La tarea de duplicar una tabla en Oracle, ya sea por motivos de desarrollo, pruebas, respaldo, migración o auditoría, es una operación común pero que esconde múltiples matices. No hay una única «mejor» forma; la elección depende enteramente del contexto: ¿Necesitas solo los datos? ¿También la estructura completa, incluyendo índices y restricciones? ¿Es una tabla pequeña o gigantesca? ¿Está la tabla en el mismo esquema o en otro? ¿O quizás en una base de datos completamente diferente? Este artículo desglosará las metodologías más efectivas y comunes, ofreciendo una visión profunda para que puedas abordar esta tarea con confianza y maestría.

Desde las sentencias SQL más directas hasta las utilidades de exportación e importación, pasando por el manejo de metadatos, exploraremos cada opción. Mi objetivo es proporcionarte una guía práctica y exhaustiva que no solo te muestre el «cómo», sino también el «cuándo» y el «por qué» de cada enfoque, cimentada en años de experiencia trabajando con Oracle. Vamos a desentrañar el arte de copiar tablas en este robusto sistema de gestión de bases de datos.

Métodos Fundamentales para Copiar una Tabla en Oracle

Cuando hablamos de copiar una tabla en Oracle, nos referimos a varias técnicas que varían en su alcance y complejidad. A grandes rasgos, podemos categorizarlas en métodos SQL directos y métodos que involucran utilidades del sistema. Aquí te presento las principales vías:

CREATE TABLE AS SELECT (CTAS): La opción más popular y versátil para crear una nueva tabla y poblarla con datos de otra.
INSERT INTO SELECT: Para copiar datos de una tabla existente a otra que ya ha sido creada previamente.
DBMS_METADATA: Una potente utilidad PL/SQL para extraer la definición completa (DDL) de objetos de base de datos, ideal para replicar estructuras complejas.
Data Pump (EXPDP/IMPDP): Las utilidades de exportación e importación de Oracle, esenciales para mover grandes volúmenes de datos y esquemas completos, especialmente entre diferentes bases de datos o servidores.
Comando COPY de SQL*Plus: Una opción más antigua y con limitaciones, pero que es útil conocer por contexto histórico y en entornos muy específicos.

Cada uno de estos métodos tiene sus particularidades, ventajas y desventajas. La clave del éxito radica en saber cuál es el más adecuado para tu situación específica.

1. Copiar una Tabla con `CREATE TABLE AS SELECT (CTAS)`

El comando CREATE TABLE AS SELECT (CTAS) es, sin lugar a dudas, la joya de la corona cuando se trata de copiar una tabla en Oracle de forma rápida y eficiente. Es extremadamente útil para crear una copia de una tabla existente, ya sea solo con su estructura, con todos sus datos, o con un subconjunto de ellos, en una sola operación.

¿Qué Copia CTAS?

Cuando utilizas CTAS, se crea una nueva tabla con la definición de columnas (nombres, tipos de datos, nulabilidad, tamaño) y los datos resultantes de la sentencia SELECT. Sin embargo, es crucial entender que CTAS no copia automáticamente la mayoría de los atributos de almacenamiento y metadatos adicionales de la tabla original, tales como:

Índices (excepto el de la clave primaria si se define en la subconsulta de la creación, lo cual es raro).
Restricciones (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK)
Triggers
Comentarios de tabla y columna
Grantings (permisos)
Atributos de almacenamiento específicos (TABLESPACE, PCTFREE, PCTUSED, etc.) a menos que se especifiquen explícitamente en la cláusula CREATE TABLE.

En esencia, CTAS es excelente para replicar la definición básica de las columnas y el conjunto de datos, pero si necesitas una réplica exacta de la estructura, deberás tomar pasos adicionales.

Sintaxis Básica y Ejemplos

La sintaxis básica es bastante sencilla:

CREATE TABLE nombre_nueva_tabla
AS
SELECT *
FROM nombre_tabla_original
[WHERE condición];

Veamos algunos ejemplos prácticos:

Ejemplo 1: Copiar la Estructura y Todos los Datos

Si quieres una copia completa de la tabla EMPLEADOS con todos sus datos:

CREATE TABLE EMPLEADOS_BACKUP
AS
SELECT *
FROM EMPLEADOS;

Con esta sentencia, EMPLEADOS_BACKUP tendrá todas las columnas de EMPLEADOS y todos los registros presentes en ella al momento de la ejecución. Es una de las maneras más directas y rápidas de crear una réplica de datos para pruebas o un respaldo temporal.

Ejemplo 2: Copiar Solo la Estructura (Sin Datos)

A menudo, solo necesitamos la estructura de la tabla, pero sin sus datos, por ejemplo, para un entorno de desarrollo o para usarla como tabla temporal. Esto se logra añadiendo una condición WHERE que siempre sea falsa:

CREATE TABLE EMPLEADOS_ESTRUCTURA
AS
SELECT *
FROM EMPLEADOS
WHERE 1=0;

La condición WHERE 1=0 garantiza que la consulta SELECT * FROM EMPLEADOS WHERE 1=0 no devolverá ninguna fila, por lo que la nueva tabla EMPLEADOS_ESTRUCTURA se creará con todas las columnas, pero estará vacía. Es un truco muy común y efectivo en el mundo Oracle.

Ejemplo 3: Copiar Datos con Filtrado y Columnas Específicas

Imagina que solo quieres copiar los empleados del departamento 10 y solo necesitas su ID, NOMBRE y SALARIO:

CREATE TABLE EMPLEADOS_DEP10
AS
SELECT ID_EMPLEADO, NOMBRE, SALARIO
FROM EMPLEADOS
WHERE ID_DEPARTAMENTO = 10;

Aquí, no solo filtramos los datos, sino que también seleccionamos explícitamente las columnas que nos interesan, lo que puede ser útil para reducir el tamaño de la tabla copiada o para crear vistas especializadas.

Optimización del Rendimiento con CTAS

Para tablas muy grandes, el rendimiento es crucial. Oracle ofrece algunas opciones para acelerar la operación CTAS:

NOLOGGING: Esta cláusula reduce significativamente el overhead de generar redologs, lo que hace la operación mucho más rápida. Es ideal para copias temporales o de desarrollo donde la recuperabilidad punto-en-el-tiempo no es crítica, o si planeas respaldar la tabla inmediatamente después.

CREATE TABLE EMPLEADOS_BACKUP_RAPIDO NOLOGGING
AS
SELECT *
FROM EMPLEADOS;

¡Atención! Usar NOLOGGING tiene implicaciones. Si ocurre un fallo después de la creación y no tienes un backup de la nueva tabla, podrías perder los datos, ya que no se registrarán todas las operaciones en los archivos de rehacer. Asegúrate de comprender este riesgo o realizar un backup lógico (EXPDP) o un backup de la tablespace de la nueva tabla después de la operación.

PARALLEL: Puedes indicarle a Oracle que use varios procesos paralelos para ejecutar la consulta y la creación de la tabla, lo que puede acelerar drásticamente la operación en sistemas con múltiples CPUs.

CREATE TABLE EMPLEADOS_BACKUP_PARALELO PARALLEL (DEGREE 8)
AS
SELECT *
FROM EMPLEADOS;

Aquí, DEGREE 8 sugiere usar 8 procesos paralelos. El grado de paralelismo óptimo depende de tu hardware y de la carga del sistema.

Es posible combinar ambas opciones para obtener el máximo rendimiento en la creación de tablas grandes:

CREATE TABLE EMPLEADOS_COPIA_OPT NOLOGGING PARALLEL (DEGREE 4)
AS
SELECT /*+ APPEND */ *
FROM EMPLEADOS;

He añadido el hint /*+ APPEND */ en la sentencia SELECT. Aunque no siempre es estrictamente necesario con CTAS (ya que Oracle a menudo lo usa implícitamente), su uso explícito garantiza que Oracle intentará realizar una inserción directa (direct path insert), bypassando el buffer cache y escribiendo directamente en los datafiles, lo cual es muy eficiente para grandes volúmenes de datos.

2. Copiar Datos con `INSERT INTO SELECT`

A diferencia de CTAS, la sentencia INSERT INTO SELECT se utiliza cuando la tabla destino ya existe y lo que deseamos es simplemente añadir datos en ella desde otra tabla. Es decir, no crea la tabla, sino que la popula con información. Este método es ideal para fusionar datos, añadir registros a una tabla de auditoría, o para operaciones ETL (Extract, Transform, Load).

Sintaxis Básica y Ejemplos

La sintaxis es igualmente directa:

INSERT INTO nombre_tabla_destino (columna1, columna2, ...)
SELECT columna1, columna2, ...
FROM nombre_tabla_origen
[WHERE condición];

Es importante que las columnas en la cláusula INSERT INTO y en la cláusula SELECT correspondan en orden y tipo de datos. Si se omiten las columnas en INSERT INTO, Oracle asume que estás insertando valores para todas las columnas de la tabla destino en el orden en que fueron definidas.

Ejemplo 1: Copiar Todos los Datos a una Tabla Existente

Supongamos que ya tienes una tabla EMPLEADOS_ARCHIVADOS con la misma estructura que EMPLEADOS, y quieres mover a ella todos los empleados que ya no están activos:

INSERT INTO EMPLEADOS_ARCHIVADOS
SELECT *
FROM EMPLEADOS
WHERE ESTADO = 'INACTIVO';

Aquí, todos los datos de los empleados ‘INACTIVO’ serán copiados a EMPLEADOS_ARCHIVADOS. Es crucial que la estructura de EMPLEADOS_ARCHIVADOS sea compatible con el SELECT * de EMPLEADOS.

Ejemplo 2: Copiar Datos con Mapeo de Columnas Específicas

A veces, los nombres de las columnas o su orden pueden variar. En estos casos, especificamos las columnas explícitamente:

INSERT INTO REGISTRO_VENTAS (ID_TRANSACCION, FECHA, CANTIDAD, PRECIO_UNITARIO)
SELECT V.ID_PEDIDO, V.FECHA_PEDIDO, V.TOTAL_ARTICULOS, P.PRECIO
FROM PEDIDOS V
JOIN PRODUCTOS P ON V.ID_PRODUCTO = P.ID_PRODUCTO
WHERE V.FECHA_PEDIDO >= SYSDATE - 30; -- Solo pedidos del último mes

Este ejemplo demuestra cómo podemos transformar y seleccionar datos de múltiples tablas origen para insertarlos en una tabla destino con una estructura potencialmente diferente.

Consideraciones y Optimización para `INSERT INTO SELECT`

Cuando trabajamos con grandes volúmenes de datos usando INSERT INTO SELECT, es importante tener en cuenta:

UNIQUE CONSTRAINTS y PRIMARY KEY: Si la tabla destino tiene una clave primaria o una restricción única, asegúrate de que los datos que estás insertando no causen duplicados, de lo contrario, la operación fallará.
Disparadores (TRIGGERS): Los triggers definidos en la tabla destino se ejecutarán por cada fila insertada, lo que puede afectar el rendimiento. Si no son necesarios durante la carga de datos masiva, considera deshabilitarlos temporalmente y volver a habilitarlos después.
Restricciones (CONSTRAINTS): Las restricciones (NOT NULL, CHECK, FOREIGN KEY) se validarán durante la inserción. Si los datos origen no cumplen con estas restricciones, la operación fallará. Para mejorar el rendimiento, a veces se deshabilitan las restricciones de clave externa temporalmente y se revalidan al final, aunque esto requiere un manejo cuidadoso.
APPEND Hint: Al igual que con CTAS, el hint /*+ APPEND */ puede ser muy beneficioso para grandes inserciones, ya que fuerza una inserción directa, omitiendo el buffer cache y escribiendo directamente en los segmentos de datos. Esto reduce la generación de redo y undo, mejorando el rendimiento.

INSERT /*+ APPEND */ INTO EMPLEADOS_ARCHIVADOS
    SELECT *
    FROM EMPLEADOS
    WHERE ESTADO = 'INACTIVO';

Similar al NOLOGGING en CTAS, el uso de APPEND (que implica NOLOGGING en la mayoría de los casos para la inserción en sí) puede afectar la recuperabilidad si el modo de base de datos no es ARCHIVELOG. Es fundamental entender sus implicaciones.

3. Replicar Estructuras Completas con `DBMS_METADATA`

Como mencionamos, CTAS y INSERT INTO SELECT son excelentes para datos y la estructura básica de columnas. Pero, ¿qué pasa si necesitas copiar una tabla en Oracle incluyendo absolutamente todo: índices, restricciones (primarias, únicas, foráneas, de chequeo), triggers, comentarios, privilegios de objeto, atributos de almacenamiento y hasta columnas de identidad? Aquí es donde entra en juego el paquete DBMS_METADATA.

DBMS_METADATA es una utilidad PL/SQL increíblemente poderosa que permite extraer la definición (DDL – Data Definition Language) de cualquier objeto de base de datos en formato de texto. Esto es invaluable para replicar la estructura exacta de una tabla, o de un esquema completo, entre diferentes entornos.

Cómo Funciona `DBMS_METADATA`

El corazón de DBMS_METADATA es la función GET_DDL. Esta función toma el tipo de objeto (por ejemplo, ‘TABLE’, ‘INDEX’, ‘CONSTRAINT’) y el nombre del objeto, opcionalmente el esquema propietario, y devuelve la sentencia DDL correspondiente como un CLOB (Character Large Object).

Pasos para Copiar una Tabla Completa (Estructura y Datos) Usando `DBMS_METADATA`

Extraer la DDL de la Tabla Original:
Primero, obtenemos la sentencia CREATE TABLE de la tabla original. Esto nos dará la estructura de la tabla, incluyendo sus propiedades de almacenamiento.
```
SET LONG 2000000 -- Necesario para mostrar DDLs grandes
SET PAGESIZE 0
SET HEADING OFF
SET FEEDBACK OFF

SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLEADOS', 'HR') FROM DUAL;
```
En este ejemplo, estamos extrayendo la DDL de la tabla EMPLEADOS, propiedad del esquema HR. El resultado será una sentencia CREATE TABLE completa.

A menudo, este resultado se guarda en un archivo SQL o se copia y pega en un editor de texto.

Modificar la DDL (Opcional):

Una vez que tienes la DDL, puedes modificarla. Por ejemplo, cambiar el nombre de la tabla a EMPLEADOS_COPIA, moverla a otro TABLESPACE, o ajustar otros parámetros. Esta flexibilidad es una de las grandes ventajas de DBMS_METADATA.

-- Ejemplo de DDL extraída y modificada
CREATE TABLE "HR"."EMPLEADOS_COPIA"
(    "ID_EMPLEADO" NUMBER(6,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
    "NOMBRE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "APELLIDO" VARCHAR2(25 BYTE) NOT NULL ENABLE,
    "EMAIL" VARCHAR2(25 BYTE) NOT NULL ENABLE,
    "TELEFONO" VARCHAR2(20 BYTE),
    "FECHA_CONTRATACION" DATE NOT NULL ENABLE,
    "ID_TRABAJO" VARCHAR2(10 BYTE) NOT NULL ENABLE,
    "SALARIO" NUMBER(8,2),
    "COMISION_PCT" NUMBER(2,2),
    "ID_GESTOR" NUMBER(6,0),
    "ID_DEPARTAMENTO" NUMBER(4,0),
     CONSTRAINT "EMP_PK" PRIMARY KEY ("ID_EMPLEADO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
) SEGMENT CREATION IMMEDIATE
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

-- DDL para los índices (excepto PK, que ya está con la tabla)
-- SELECT DBMS_METADATA.GET_DDL('INDEX', 'EMP_JOB_IX', 'HR') FROM DUAL; -- Ejemplo de índice
-- SELECT DBMS_METADATA.GET_DDL('INDEX', 'EMP_EMAIL_UK', 'HR') FROM DUAL; -- Ejemplo de índice

-- DDL para las restricciones foráneas (Foreign Keys)
-- SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT', 'EMP_DEPT_FK', 'HR') FROM DUAL; -- Ejemplo de FK
-- SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT', 'EMP_MGR_FK', 'HR') FROM DUAL; -- Ejemplo de FK

-- DDL para triggers
-- SELECT DBMS_METADATA.GET_DDL('TRIGGER', 'AUDIT_EMPLEADOS_TRG', 'HR') FROM DUAL; -- Ejemplo de Trigger

-- DDL para comentarios
-- SELECT DBMS_METADATA.GET_DDL('COMMENT', 'EMPLEADOS', 'HR') FROM DUAL; -- Ejemplo de Comentario

Crear la Nueva Tabla y sus Objetos Relacionados:

Ejecuta las sentencias DDL modificadas en tu base de datos. Es recomendable seguir un orden: primero la tabla, luego índices, luego restricciones (especialmente foráneas después de las tablas referenciadas), y finalmente triggers y otros objetos.

Un truco útil: Para replicar la estructura y los índices, puedes obtener el DDL de la tabla y luego obtener el DDL de los índices y restricciones por separado. O, más fácil, puedes configurar DBMS_METADATA para incluir objetos dependientes.

-- Configurar DBMS_METADATA para incluir objetos dependientes
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.GET_TRANSFORM_HANDLE('DEFAULT'), 'CONSTRAINTS_AS_ALTER', false);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.GET_TRANSFORM_HANDLE('DEFAULT'), 'REF_CONSTRAINTS', true);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.GET_TRANSFORM_HANDLE('DEFAULT'), 'SEGMENT_ATTRIBUTES', true);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.GET_TRANSFORM_HANDLE('DEFAULT'), 'TABLESPACE', true);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.GET_TRANSFORM_HANDLE('DEFAULT'), 'STORAGE', true);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.GET_TRANSFORM_HANDLE('DEFAULT'), 'PRETTY', true);

SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLEADOS', 'HR') FROM DUAL;
-- Este SELECT ahora incluirá CREATE INDEX y ALTER TABLE ADD CONSTRAINT para las restricciones.
-- Recuerda cambiar el nombre de la tabla antes de ejecutar.

Copiar los Datos:

Una vez que la nueva tabla tiene su estructura completa, utiliza INSERT INTO SELECT para copiar los datos de la tabla original a la nueva. Asegúrate de deshabilitar los triggers y las restricciones de clave foránea en la tabla destino si estás copiando grandes volúmenes de datos y la integridad referencial ya está asegurada en el origen, para luego habilitarlas y validarlas.

-- Deshabilitar restricciones y triggers (si aplica) para mejor rendimiento en la carga masiva
ALTER TABLE EMPLEADOS_COPIA DISABLE CONSTRAINT EMP_DEPT_FK;
ALTER TABLE EMPLEADOS_COPIA DISABLE CONSTRAINT EMP_MGR_FK;
-- ALTER TRIGGER AUDIT_EMPLEADOS_TRG DISABLE; -- Descomentar si tienes un trigger

-- Copiar los datos
INSERT /*+ APPEND */ INTO EMPLEADOS_COPIA
SELECT *
FROM EMPLEADOS;

COMMIT;

-- Habilitar restricciones y triggers
ALTER TABLE EMPLEADOS_COPIA ENABLE CONSTRAINT EMP_DEPT_FK;
ALTER TABLE EMPLEADOS_COPIA ENABLE CONSTRAINT EMP_MGR_FK;
-- ALTER TRIGGER AUDIT_EMPLEADOS_TRG ENABLE; -- Descomentar si tienes un trigger

Este método, aunque un poco más manual al combinar la extracción de DDL con la inserción de datos, es el más preciso para replicar una tabla con todas sus características y dependencias.

4. Migración de Tablas Grandes con Data Pump (`EXPDP`/`IMPDP`)

Para operaciones a gran escala, como migrar tablas entre bases de datos, mover esquemas completos, o para respaldos lógicos y recuperaciones, Oracle Data Pump (EXPDP para exportar, IMPDP para importar) es la herramienta de elección. Aunque no es una sentencia SQL directa, es fundamental para copiar una tabla en Oracle con eficiencia y robustez, especialmente cuando se manejan volúmenes considerables de información o cuando se requiere transferir no solo datos sino también todo el entorno de un objeto.

Data Pump opera a nivel de sistema operativo y requiere que tengas permisos adecuados para acceder a directorios de base de datos donde se almacenarán los archivos de exportación (dmp files) y logs.

Ventajas Clave de Data Pump:

Rendimiento Superior: Diseñado para manejar bases de datos muy grandes, utilizando paralelismo y acceso directo a los datos para una velocidad inigualable.
Flexibilidad Extensa: Permite exportar/importar esquemas completos, tablas específicas, tablespaces, o la base de datos entera. Puedes filtrar datos usando la cláusula QUERY.
Preservación de Metadata: Copia automáticamente toda la metadata asociada (índices, restricciones, triggers, grants, comentarios, etc.).
Migración entre Bases de Datos: Ideal para mover datos entre diferentes instancias de Oracle, incluso en plataformas o versiones distintas (dentro de un rango de compatibilidad).
Reasignación de Objetos: Permite renombrar objetos, reasignar a diferentes esquemas o tablespaces durante la importación.

Pasos para Copiar una Tabla Usando Data Pump

Crear un Directorio en la Base de Datos:
Data Pump utiliza objetos de directorio de Oracle para especificar dónde se guardarán los archivos. Necesitas crear uno (si no tienes ya) y otorgar permisos.
```
CREATE DIRECTORY data_pump_dir AS '/u01/app/oracle/datapump';
GRANT READ, WRITE ON DIRECTORY data_pump_dir TO HR; -- O al usuario que ejecutará EXPDP/IMPDP
```
Asegúrate de que la ruta del sistema operativo /u01/app/oracle/datapump exista y que el usuario bajo el cual se ejecuta el proceso de Oracle tenga permisos de lectura/escritura sobre ella.
Exportar la Tabla (EXPDP):
Desde la línea de comandos del sistema operativo (no SQL*Plus o SQL Developer), ejecuta expdp.
```
expdp hr/password@ORCLPDB1 TABLES=HR.EMPLEADOS DIRECTORY=DATA_PUMP_DIR DUMPFILE=empleados.dmp LOGFILE=empleados_exp.log
```
Parámetros clave:
- hr/password@ORCLPDB1: Credenciales del usuario y cadena de conexión a la base de datos origen.
- TABLES=HR.EMPLEADOS: Especifica la tabla o tablas a exportar (formato ESQUEMA.TABLA).
- DIRECTORY=DATA_PUMP_DIR: El objeto de directorio de Oracle.
- DUMPFILE=empleados.dmp: Nombre del archivo donde se guardará la exportación.
- LOGFILE=empleados_exp.log: Archivo de log para registrar el proceso.
- Otros útiles:
  - SCHEMAS=HR: Para exportar un esquema completo.
  - QUERY=HR.EMPLEADOS:"WHERE ID_DEPARTAMENTO = 10": Para exportar solo un subconjunto de datos.
  - EXCLUDE=INDEX: Para excluir índices, si no quieres copiarlos.
  - PARALLEL=4: Para usar 4 procesos paralelos y acelerar.
  - COMPRESSION=ALL: Para comprimir el contenido del dumpfile.
Importar la Tabla (IMPDP):
Para copiar la tabla a otro esquema en la misma base de datos, o a una base de datos diferente, usa impdp.
```
impdp system/password@ORCLPDB1 DIRECTORY=DATA_PUMP_DIR DUMPFILE=empleados.dmp LOGFILE=empleados_imp.log REMAP_SCHEMA=HR:HR_COPIA REMAP_TABLESPACE=USERS:USERS_COPIA TABLE_EXISTS_ACTION=REPLACE
```
Parámetros clave:
- system/password@ORCLPDB1: Credenciales del usuario con permisos de importación (generalmente SYSTEM o SYSDBA).
- DIRECTORY=DATA_PUMP_DIR: El objeto de directorio donde se encuentra el dumpfile.
- DUMPFILE=empleados.dmp: El archivo a importar.
- LOGFILE=empleados_imp.log: Archivo de log para la importación.
- REMAP_SCHEMA=HR:HR_COPIA: Una de las características más potentes. Reasigna los objetos del esquema HR (origen) al esquema HR_COPIA (destino) durante la importación. Si quieres mantener el mismo esquema, omite este parámetro o usa REMAP_SCHEMA=HR:HR.
- REMAP_TABLESPACE=USERS:USERS_COPIA: Para reasignar tablespaces si el destino tiene nombres diferentes o deseas mover los datos.
- TABLE_EXISTS_ACTION=REPLACE: Si la tabla ya existe en el destino, esta opción la reemplazará. Otras opciones son APPEND (añadir datos), SKIP (ignorar), TRUNCATE (truncar y luego añadir).
Data Pump es, sin duda, la opción más robusta y completa para copiar una tabla en Oracle, especialmente en entornos de producción donde la fiabilidad y el rendimiento son críticos.

5. El Comando `COPY` de SQL*Plus (Legado)

El comando COPY de SQL*Plus es una herramienta antigua para copiar datos entre bases de datos o dentro de la misma base de datos. Aunque funcional, ha sido mayormente suplantado por CTAS para copias internas y por Data Pump para transferencias entre bases de datos debido a sus limitaciones en rendimiento, manejo de tipos de datos complejos (como LOBs), y flexibilidad.

Lo menciono aquí por una razón histórica y porque aún podría encontrarse en scripts muy antiguos. Sin embargo, mi recomendación es evitarlo para nuevas implementaciones y preferir las opciones modernas.

Sintaxis Básica (Evitar su Uso Actual)

COPY FROM usuario_origen/password_origen@tns_origen TO usuario_destino/password_destino@tns_destino CREATE nombre_tabla_destino USING SELECT * FROM nombre_tabla_origen;

O, para copiar dentro de la misma base de datos:

COPY FROM usuario/password@tns_alias CREATE nombre_tabla_destino USING SELECT * FROM nombre_tabla_origen;

Limitaciones Notables:

No maneja LOBs eficientemente.
No copia metadata como índices, restricciones, triggers.
Es mucho más lento que CTAS o Data Pump para volúmenes grandes.
Requiere una conexión TNS al destino, incluso si es la misma base de datos.

Consideraciones Avanzadas al Copiar Tablas en Oracle

Copiar una tabla no siempre es tan sencillo como un simple SELECT *. Hay elementos complejos en una base de datos Oracle moderna que requieren una atención especial. Ignorarlos puede llevar a una copia incompleta, con errores, o que no se comporte como se espera.

Columnas LOB (CLOB, BLOB, NCLOB, BFILE)

Las Columnas LOB (Large Objects) almacenan grandes cantidades de datos (texto, imágenes, videos). Su manejo al copiar una tabla es crucial:

CREATE TABLE AS SELECT: CTAS copia los datos LOB por valor. Si la tabla de origen utiliza almacenamiento externo para los LOBs (SecureFiles o BasicFiles), la tabla de destino también los almacenará de forma similar y duplicará el contenido. Esto es generalmente seguro, pero puede consumir mucho espacio.
INSERT INTO SELECT: Similar a CTAS, copia los LOBs por valor.
DBMS_METADATA + INSERT INTO SELECT: DBMS_METADATA extrae la definición de las columnas LOB, incluyendo sus parámetros de almacenamiento (STORAGE_IN_ROW, CHUNK, TABLESPACE para los LOBs, etc.). Luego, INSERT INTO SELECT copiará los datos. Esta combinación asegura que las propiedades de almacenamiento de los LOBs se repliquen correctamente.
Data Pump: Es la opción más robusta para LOBs, ya que maneja su almacenamiento y transferencia de manera optimizada, preservando todas las propiedades.

Columnas de Identidad (Identity Columns – Oracle 12c y posteriores)

Las columnas de identidad son una característica introducida en Oracle 12c que permite generar valores automáticos para una columna (similar a AUTO_INCREMENT en MySQL o IDENTITY en SQL Server). Al copiar una tabla con columnas de identidad, hay una particularidad importante:

CREATE TABLE AS SELECT: CTAS copiará los valores actuales de la columna de identidad como una columna numérica normal. No copiará la propiedad IDENTITY. Esto significa que la nueva tabla no generará automáticamente nuevos valores para esa columna. Si quieres mantener la propiedad IDENTITY, deberás usar DBMS_METADATA para replicar la DDL.
DBMS_METADATA: Este método extraerá la definición exacta de la columna de identidad, incluyendo sus propiedades (GENERATED ALWAYS AS IDENTITY, START WITH, INCREMENT BY, etc.). Luego puedes crear la tabla con esta DDL.
Data Pump: Data Pump es capaz de replicar las columnas de identidad con todas sus propiedades y el estado actual de su secuencia subyacente.

Secuencias (Sequences)

Las secuencias son objetos independientes de la tabla que generan números únicos. Las tablas los usan a menudo para claves primarias. Al copiar una tabla en Oracle:

CTAS y INSERT INTO SELECT: Estos comandos no copian secuencias. Si la tabla original dependía de una secuencia para su clave primaria, deberás crear una nueva secuencia en la tabla destino y ajustar su valor de inicio (START WITH) para evitar colisiones si la tabla ya tiene datos.
DBMS_METADATA: Puedes usar DBMS_METADATA.GET_DDL('SEQUENCE', 'nombre_secuencia', 'esquema') para extraer la definición de la secuencia y crearla por separado. Asegúrate de ajustar el valor START WITH para la nueva secuencia si la tabla de destino ya contiene datos copiados.
Data Pump: Data Pump sí exporta e importa secuencias junto con las tablas que las utilizan. Es la forma más sencilla de replicar secuencias con sus valores correctos.

Índices, Restricciones y Triggers

Ya hemos tocado este punto, pero merece un recordatorio enfático:

CTAS y INSERT INTO SELECT: No copian índices, restricciones (PK, UK, FK, CHECK), ni triggers. Solo la definición de las columnas y los datos.
DBMS_METADATA + INSERT INTO SELECT: Es el método manual más preciso para replicar toda esta metadata. Extrae la DDL para cada tipo de objeto y ejecútalas.
Data Pump: La forma más sencilla y recomendada para copiar tablas con todos sus objetos dependientes, ya que los exporta e importa automáticamente.

Privilegios de Objeto (Grants)

Los permisos GRANT que otorgan acceso a la tabla a otros usuarios o roles no se copian con CTAS ni con INSERT INTO SELECT. Deberás volver a otorgarlos en la tabla copiada. DBMS_METADATA puede extraer la DDL de los GRANTs, y Data Pump los maneja de manera integral.

Mejores Prácticas al Copiar una Tabla en Oracle

Más allá de la sintaxis y las herramientas, adoptar un enfoque estructurado es clave para evitar problemas y garantizar que tu proceso de copia de tablas sea un éxito. Aquí mis recomendaciones basadas en la experiencia:

Define Claramente el Objetivo:
Antes de elegir un método, pregúntate: ¿Por qué necesito copiar esta tabla?
- ¿Es para un respaldo temporal de datos? (CTAS con NOLOGGING)
- ¿Para una tabla de desarrollo o pruebas sin datos? (CTAS WHERE 1=0)
- ¿Necesito replicar la tabla con toda su estructura, índices y restricciones? (DBMS_METADATA + INSERT, o Data Pump)
- ¿Voy a mover la tabla a otra base de datos o esquema? (Data Pump)
- ¿Es una operación única o recurrente? (Automatización)
La respuesta a estas preguntas guiará tu elección de la herramienta y el enfoque.
Comprende el Volumen de Datos:
El tamaño de la tabla es un factor determinante. Para tablas pequeñas (pocos miles de filas), CTAS o INSERT INTO SELECT son perfectamente adecuados. Para tablas medianas a grandes (millones de filas o gigabytes), considera usar NOLOGGING, PARALLEL, el hint APPEND, o directamente Data Pump para un rendimiento óptimo.
Planifica el Manejo de la Metadata:
Si la estructura completa (índices, restricciones, triggers, etc.) es importante, planifica cómo la replicarás. No te fíes solo de CTAS. DBMS_METADATA o Data Pump son tus aliados aquí.
Verifica los Permisos:
Asegúrate de que el usuario que ejecuta la operación tenga los permisos necesarios (CREATE TABLE, SELECT en la tabla origen, INSERT en la tabla destino, permisos de directorio para Data Pump, etc.). Los errores de permisos son una causa común de frustración.
Considera el Impacto en el Rendimiento y el Espacio:
Copiar una tabla grande puede consumir recursos (CPU, I/O) y espacio en disco.
- Ejecuta estas operaciones en periodos de baja carga si es posible.
- Monitorea el espacio disponible en el TABLESPACE destino.
- Si usas NOLOGGING, ten en cuenta que la recuperación a un punto en el tiempo de la nueva tabla podría no ser posible sin un backup posterior.
Prueba Siempre Primero:
Antes de ejecutar una operación de copia en un entorno de producción, pruébala en un entorno de desarrollo o pruebas. Esto te permitirá identificar problemas, validar el resultado y medir el tiempo de ejecución sin riesgo.
Documenta tus Procesos:
Si la operación es compleja o recurrente, documenta los pasos, los comandos utilizados y las consideraciones especiales. Esto es invaluable para futuras referencias y para otros miembros del equipo.

Preguntas Frecuentes al Copiar una Tabla en Oracle (FAQ)

¿Cómo copiar solo la estructura de una tabla en Oracle sin sus datos?

La forma más sencilla y común es utilizando CREATE TABLE AS SELECT con una cláusula WHERE que siempre resulte falsa. Esto crea la tabla con la misma definición de columnas pero sin insertar ninguna fila.

CREATE TABLE NUEVA_TABLA_ESTRUCTURA
AS
SELECT *
FROM TABLA_ORIGEN
WHERE 1=0;

Si además necesitas replicar todas las restricciones (claves primarias, foráneas, únicas), índices y otros atributos de almacenamiento, la aproximación más robusta es usar DBMS_METADATA.GET_DDL('TABLE', 'TABLA_ORIGEN', 'ESQUEMA_ORIGEN'). Extraes el DDL completo, lo ajustas si es necesario (cambiando el nombre de la tabla, por ejemplo), y luego lo ejecutas. Este método te da un control granular sobre la réplica de todos los metadatos.

¿Cómo puedo copiar una tabla con sus índices, restricciones (PK, UK, FK) y triggers?

Como mencionamos, CREATE TABLE AS SELECT no copia esta metadata. Para una réplica completa de la estructura y sus objetos dependientes, tienes dos opciones principales:

La primera es un enfoque de dos pasos usando DBMS_METADATA:

Extrae la DDL de la tabla, de todos sus índices, de sus restricciones (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK) y de sus triggers utilizando DBMS_METADATA.GET_DDL() para cada tipo de objeto.
Modifica las sentencias DDL para que apunten a los nuevos nombres de tabla o esquema si es necesario.
Ejecuta las sentencias DDL en el siguiente orden: primero la tabla, luego los índices, luego las restricciones (primarias y únicas primero, luego las foráneas), y finalmente los triggers.
Una vez creada la estructura, copia los datos usando INSERT INTO SELECT.

La segunda y más automatizada opción, especialmente para tablas grandes o para mover entre bases de datos, es utilizar Oracle Data Pump (EXPDP y IMPDP). Data Pump exporta e importa automáticamente toda la definición de la tabla, incluyendo sus índices, restricciones, triggers, grants y otros objetos dependientes, lo que la convierte en la herramienta predilecta para replicaciones complejas y masivas.

¿Cuál es la forma más rápida de copiar una tabla grande en Oracle?

Para copiar una tabla grande dentro de la misma base de datos, la opción más rápida es CREATE TABLE AS SELECT combinada con las cláusulas NOLOGGING y PARALLEL. El hint /*+ APPEND */ en la subconsulta SELECT también es muy recomendable para forzar una inserción directa (direct path insert), lo que minimiza la generación de redologs y evita el buffer cache, acelerando drásticamente el proceso.

CREATE TABLE NUEVA_TABLA_GRANDE NOLOGGING PARALLEL (DEGREE 8)
AS
SELECT /*+ APPEND */ *
FROM TABLA_ORIGEN;

Para transferencias de tablas grandes entre diferentes bases de datos o servidores, Oracle Data Pump (EXPDP/IMPDP) es la herramienta más eficiente. Permite paralelismo, compresión, y una gestión optimizada de grandes volúmenes de datos y metadatos.

¿Puedo copiar una tabla de un esquema a otro dentro de la misma base de datos?

Sí, absolutamente. Hay varias maneras:

Usando CREATE TABLE AS SELECT: Si el usuario que ejecuta la sentencia tiene permisos para crear una tabla en el esquema destino y SELECT en la tabla origen.

CREATE TABLE ESQUEMA_DESTINO.NUEVA_TABLA
AS
SELECT *
FROM ESQUEMA_ORIGEN.TABLA_ORIGEN;

Usando DBMS_METADATA y INSERT INTO SELECT: Extrae la DDL de la tabla del ESQUEMA_ORIGEN, reemplaza el nombre del esquema en la DDL por ESQUEMA_DESTINO (y el nombre de la tabla si lo deseas), crea la tabla, y luego usa INSERT INTO ESQUEMA_DESTINO.NUEVA_TABLA SELECT * FROM ESQUEMA_ORIGEN.TABLA_ORIGEN;. Este método permite una réplica completa de la estructura.
Usando Data Pump: Esta es la opción más recomendada para una réplica completa de una tabla con todos sus objetos dependientes entre esquemas. Al importar, utilizas el parámetro REMAP_SCHEMA=ESQUEMA_ORIGEN:ESQUEMA_DESTINO.

expdp user_origen/pass@ORCL TABLES=ESQUEMA_ORIGEN.TABLA_ORIGEN DIRECTORY=DATA_PUMP_DIR DUMPFILE=tabla.dmp
impdp system/pass@ORCL DIRECTORY=DATA_PUMP_DIR DUMPFILE=tabla.dmp REMAP_SCHEMA=ESQUEMA_ORIGEN:ESQUEMA_DESTINO LOGFILE=imp_log.log

¿Qué sucede con los LOBs (CLOB, BLOB) al copiar una tabla en Oracle?

Las columnas LOB son un caso especial debido a su tamaño potencial. Tanto CREATE TABLE AS SELECT como INSERT INTO SELECT copiarán los datos LOB por valor, duplicando su contenido en el almacenamiento. Esto es generalmente lo que se espera, pero es importante ser consciente del consumo de espacio adicional.

Si la configuración de almacenamiento de los LOBs (por ejemplo, STORAGE IN ROW, CHUNK SIZE, TABLESPACE específico para LOBs) es crítica y debe replicarse exactamente, la combinación de DBMS_METADATA para la definición DDL y luego INSERT INTO SELECT para los datos es efectiva. DBMS_METADATA extraerá estas propiedades de almacenamiento. Alternativamente, y de forma más completa, Oracle Data Pump maneja de manera óptima la copia de LOBs, preservando todas sus propiedades y el almacenamiento eficiente.

¿Cómo se manejan las secuencias y las columnas de identidad al copiar una tabla?

Las secuencias no son copiadas por CREATE TABLE AS SELECT ni por INSERT INTO SELECT. Son objetos independientes. Si la tabla original utiliza una secuencia, deberás crear una nueva secuencia en la base de datos o esquema destino y configurarla con el valor START WITH apropiado para evitar colisiones con los datos ya copiados. Puedes usar DBMS_METADATA.GET_DDL('SEQUENCE', 'NOMBRE_SECUENCIA', 'ESQUEMA') para obtener su definición.

Las columnas de identidad (introducidas en Oracle 12c) se comportan de manera peculiar con CREATE TABLE AS SELECT: se copiarán solo los valores actuales de la columna, pero la propiedad de que la columna se autogenere (GENERATED ALWAYS AS IDENTITY) no se replicará. La nueva tabla tendrá una columna numérica regular. Para preservar la propiedad de identidad, debes usar DBMS_METADATA para extraer el DDL exacto de la tabla (que incluirá la definición de la columna de identidad) o emplear Oracle Data Pump, que es la herramienta más completa para este tipo de réplicas complejas.

¿Es seguro copiar una tabla mientras está siendo utilizada (concurrencia)?

Copiar una tabla mientras está siendo modificada activamente puede generar una «instantánea» de los datos en un punto específico en el tiempo, pero puede haber implicaciones de consistencia si no se hace correctamente.

Cuando utilizas CREATE TABLE AS SELECT o INSERT INTO SELECT, Oracle aplica su mecanismo de consistencia de lectura. Esto significa que tu consulta SELECT verá los datos tal como estaban al inicio de la sentencia, independientemente de las modificaciones que ocurran después. Sin embargo, si la tabla está bajo una intensa actividad de escritura, la operación de copia podría experimentar bloqueos o un rendimiento reducido debido a la contención de recursos.

Para garantizar una copia totalmente consistente en el tiempo sin afectar a los usuarios activos, una opción es realizar la copia durante un período de baja actividad o utilizar la cláusula AS OF SCN o AS OF TIMESTAMP en la sentencia SELECT (disponible si tienes configurado Oracle Flashback Query). Esto te permite recuperar datos como estaban en un momento o SCN específico en el pasado, evitando cualquier problema de concurrencia con transacciones actuales.

CREATE TABLE EMPLEADOS_COPIA_CONSISTENTE
AS
SELECT *
FROM EMPLEADOS AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '5' MINUTE; -- Datos de hace 5 minutos

Para operaciones con Data Pump, si el origen es una base de datos activa, se recomienda usar el parámetro FLASHBACK_SCN o FLASHBACK_TIME para obtener una exportación consistente a un punto en el tiempo, minimizando el impacto en la base de datos de origen y asegurando la coherencia de los datos exportados.

Conclusión

Como hemos explorado a lo largo de este artículo, copiar una tabla en Oracle es una operación que va mucho más allá de una simple sentencia. Dependiendo de tus requisitos específicos (solo datos, estructura completa, rendimiento, migración entre bases de datos, manejo de LOBs o columnas de identidad), la herramienta y la estrategia adecuada pueden variar significativamente. Desde la eficiencia del CREATE TABLE AS SELECT para copias rápidas de datos y estructura básica, pasando por la granularidad de DBMS_METADATA para replicar cada detalle de la DDL, hasta la potencia inigualable de Data Pump para escenarios de gran envergadura y migración.

Mi recomendación, basada en la experiencia de incontables proyectos, es siempre tomarse un momento para evaluar el objetivo, el volumen de datos y la criticidad de la metadata antes de actuar. No hay una «bala de plata», pero con el conocimiento adecuado de estas herramientas y técnicas, puedes abordar cualquier desafío de replicación de tablas en Oracle con confianza y lograr resultados óptimos y consistentes. La clave está en la comprensión profunda de cada método y sus implicaciones.

Spread the love

Cómo Copiar una Tabla en Oracle: Guía Definitiva para Replicar Datos con Precisión y Eficiencia

Métodos Fundamentales para Copiar una Tabla en Oracle

1. Copiar una Tabla con CREATE TABLE AS SELECT (CTAS)

¿Qué Copia CTAS?

Sintaxis Básica y Ejemplos

Ejemplo 1: Copiar la Estructura y Todos los Datos

Ejemplo 2: Copiar Solo la Estructura (Sin Datos)

Ejemplo 3: Copiar Datos con Filtrado y Columnas Específicas

Optimización del Rendimiento con CTAS

2. Copiar Datos con INSERT INTO SELECT

Sintaxis Básica y Ejemplos

Ejemplo 1: Copiar Todos los Datos a una Tabla Existente

Ejemplo 2: Copiar Datos con Mapeo de Columnas Específicas

Consideraciones y Optimización para INSERT INTO SELECT

3. Replicar Estructuras Completas con DBMS_METADATA

Cómo Funciona DBMS_METADATA

Pasos para Copiar una Tabla Completa (Estructura y Datos) Usando DBMS_METADATA

4. Migración de Tablas Grandes con Data Pump (EXPDP/IMPDP)

Ventajas Clave de Data Pump:

Pasos para Copiar una Tabla Usando Data Pump

5. El Comando COPY de SQL*Plus (Legado)

Sintaxis Básica (Evitar su Uso Actual)

Consideraciones Avanzadas al Copiar Tablas en Oracle

Columnas LOB (CLOB, BLOB, NCLOB, BFILE)

Columnas de Identidad (Identity Columns – Oracle 12c y posteriores)

Secuencias (Sequences)

Índices, Restricciones y Triggers

Privilegios de Objeto (Grants)

Mejores Prácticas al Copiar una Tabla en Oracle

Preguntas Frecuentes al Copiar una Tabla en Oracle (FAQ)

¿Cómo copiar solo la estructura de una tabla en Oracle sin sus datos?

¿Cómo puedo copiar una tabla con sus índices, restricciones (PK, UK, FK) y triggers?

¿Cuál es la forma más rápida de copiar una tabla grande en Oracle?

¿Puedo copiar una tabla de un esquema a otro dentro de la misma base de datos?

¿Qué sucede con los LOBs (CLOB, BLOB) al copiar una tabla en Oracle?

¿Cómo se manejan las secuencias y las columnas de identidad al copiar una tabla?

¿Es seguro copiar una tabla mientras está siendo utilizada (concurrencia)?

Conclusión

1. Copiar una Tabla con `CREATE TABLE AS SELECT (CTAS)`

2. Copiar Datos con `INSERT INTO SELECT`

Consideraciones y Optimización para `INSERT INTO SELECT`

3. Replicar Estructuras Completas con `DBMS_METADATA`

Cómo Funciona `DBMS_METADATA`

Pasos para Copiar una Tabla Completa (Estructura y Datos) Usando `DBMS_METADATA`

4. Migración de Tablas Grandes con Data Pump (`EXPDP`/`IMPDP`)

5. El Comando `COPY` de SQL*Plus (Legado)