How to Copy a Table Between Databases in Oracle

How do I copy a table from one Oracle database to another?

Copying a table between Oracle databases moves data and, optionally, structure from a source DB to a target DB with a database link.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why copy a table between Oracle databases?

Move reference data, migrate modules, or back-fill analytics without full exports. Using a database link (DB link) avoids downtime and large dump files.

What prerequisites must be met?

Create a DB link on the target database pointing to the source.Grant the target user CREATE TABLE, INSERT, and SELECT THROUGH DB LINK privileges.

How do you copy both structure and data in one step?

Use CREATE TABLE AS SELECT (CTAS) with the DB link. This builds the table and loads the rows in a single statement.

Example: duplicate Products table

CREATE TABLE products_copy AS SELECT * FROM products@source_prod;

How do you append data into an existing table?

Issue an INSERT-SELECT referencing the DB link.Ensure column lists match to avoid order-related errors.

Example: append new Orders

INSERT /*+ APPEND */ INTO orders SELECT * FROM orders@source_prod WHERE order_date > SYSDATE - 7;

Can you copy only table structure?

Yes—use a WHERE clause that returns no rows, such as 1=0.This creates the table definition without data.

Example: empty OrderItems template

CREATE TABLE orderitems_tmpl AS SELECT * FROM orderitems@source_prod WHERE 1=0;

Best practices for large tables

Enable parallelism, copy during low traffic, disable triggers and indexes, and commit in batches if using INSERT.

How to keep identity columns and constraints?

CTAS does not copy primary keys, foreign keys, or triggers. Export metadata with DBMS_METADATA and re-apply after load.

How to verify the copy completed?

Compare row counts with COUNT(*).For deeper checksums, use DBMS_CRYPTO.HASH over concatenated columns.

Why How to Copy a Table Between Databases in Oracle is important

How to Copy a Table Between Databases in Oracle Example Usage


-- Copy Customers table structure and data
CREATE TABLE customers_backup AS
SELECT id, name, email, created_at
FROM customers@ecom_prod_link;

-- Append only today’s OrderItems
INSERT INTO orderitems (id, order_id, product_id, quantity)
SELECT id, order_id, product_id, quantity
FROM orderitems@ecom_prod_link
WHERE order_date = TRUNC(SYSDATE);

How to Copy a Table Between Databases in Oracle Syntax


CREATE [TABLE] target_table AS
SELECT column_list
FROM source_table@db_link
[WHERE condition];

-- Append to existing table
INSERT INTO target_table (column_list)
SELECT column_list
FROM source_table@db_link
[WHERE condition];

Parameters:
  target_table          – new or existing table in local DB
  source_table@db_link  – remote table referenced through a DB link
  column_list           – explicit columns for order safety
  WHERE condition       – filter rows (optional)

E-commerce context:
  • Copy full Products table
    CREATE TABLE products_copy AS SELECT * FROM products@src_link;
  • Copy last week's Orders
    INSERT INTO orders SELECT * FROM orders@src_link WHERE order_date >= SYSDATE-7;

Common Mistakes

Using SELECT * without matching column order when the target table already exists. Fix by explicitly listing columns in both SELECT and INSERT.
Assuming constraints and indexes copy automatically with CTAS. They don’t; export metadata with DBMS_METADATA and recreate after data load.