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.