How to Copy Data from Prod to Test via DB Link

This guide helps you copy data from a table owned by another user in the production database into your own schema in the test database using a database link.

Step 1: Create a Table in Your Schema in Production DB

Copy the data from the other user’s table into a table under your own schema.

-- Login to production DB as your user
SQL>conn your_user/your_password;
SQL>show user;

CREATE TABLE my_table AS
SELECT * FROM source_user.source_table;

select count(*) from your_user.my_table;

📝 Note: You need SELECT privilege on source_user.source_table. If not, ask your DBA to grant it:

GRANT SELECT ON source_user.source_table TO your_user;

Step 2: Grant Access to Your Table in Prod (if needed)

This step allows your test DB to access your table via DB link.

-- Optional: Grant select to PUBLIC or a specific user if needed
GRANT SELECT ON my_table TO PUBLIC;

Step 3: Create a DB Link in the Test Database

Create a database link from test DB to your production DB user.

-- Login to test DB
CREATE DATABASE LINK prod_user_link
CONNECT TO your_user IDENTIFIED BY your_password
USING 'PROD_TNS_ALIAS';

🔁 Replace:
your_user → your production DB username
your_password → your production DB password
‘PROD_TNS_ALIAS’ → TNS entry for production DB in tnsnames.ora

Step 4: Create the Table in Test DB (Only If It Doesn’t Exist)

This creates the table in your test DB only if it’s not already there.

-- On test DB
CREATE TABLE my_table AS
SELECT * FROM your_user.my_table@prod_user_link WHERE 1=0;

WHERE 1=0 ensures the structure is copied without copying data.

Step 5: Insert Data from Production to Test

Copy the actual data over the DB link.

-- On test DB
INSERT INTO my_table
SELECT * FROM your_user.my_table@prod_user_link;

✅ Done!

You have now:

Created a local copy of another user’s table in production
Created a DB link from test to prod
Inserted the data into your test DB

Let me know if you want this as a ready-to-run SQL script or with dynamic table names.