{"id":3126,"date":"2025-04-26T08:22:01","date_gmt":"2025-04-26T08:22:01","guid":{"rendered":"https:\/\/w3buddy.com\/?p=3126"},"modified":"2026-01-15T12:44:25","modified_gmt":"2026-01-15T07:14:25","slug":"how-to-copy-data-from-prod-to-test-via-db-link","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/how-to-copy-data-from-prod-to-test-via-db-link\/","title":{"rendered":"How to Copy Data from Prod to Test via DB Link"},"content":{"rendered":"\n

This guide helps you copy data from a table owned by another user in the production database<\/strong> into your own schema in the test database<\/strong> using a database link<\/strong>.<\/p>\n\n\n\n

Step 1: Create a Table in Your Schema in Production DB<\/strong><\/h3>\n\n\n\n

Copy the data from the other user\u2019s table into a table under your own schema.<\/p>\n\n\n\n

-- Login to production DB as your user\nSQL>conn your_user\/your_password;\nSQL>show user;\n\nCREATE TABLE my_table AS\nSELECT * FROM source_user.source_table;\n\nselect count(*) from your_user.my_table;<\/code><\/pre>\n\n\n\n
\n
\ud83d\udcdd Note<\/strong>: You need SELECT <\/strong>privilege on source_user.source_table<\/strong>. If not, ask your DBA to grant it:<\/p>\n<\/blockquote>\n\n\n\n
GRANT SELECT ON source_user.source_table TO your_user;<\/code><\/pre>\n\n\n\n
Step 2: Grant Access to Your Table in Prod (if needed)<\/strong><\/h3>\n\n\n\n
This step allows your test DB to access your table via DB link.<\/p>\n\n\n\n
-- Optional: Grant select to PUBLIC or a specific user if needed\nGRANT SELECT ON my_table TO PUBLIC;<\/code><\/pre>\n\n\n\n
Step 3: Create a DB Link in the Test Database<\/strong><\/h3>\n\n\n\n
Create a database link from test DB<\/strong> to your production DB user<\/strong>.<\/p>\n\n\n\n
-- Login to test DB\nCREATE DATABASE LINK prod_user_link\nCONNECT TO your_user IDENTIFIED BY your_password\nUSING 'PROD_TNS_ALIAS';<\/code><\/pre>\n\n\n\n
\n
\ud83d\udd01 Replace:<\/p>\n\n\n\n
    \n
  • your_user<\/strong> \u2192 your production DB username<\/li>\n\n\n\n
  • your_password<\/strong> \u2192 your production DB password<\/li>\n\n\n\n
  • ‘PROD_TNS_ALIAS’<\/strong> \u2192 TNS entry for production DB in tnsnames.ora<\/strong><\/li>\n<\/ul>\n<\/blockquote>\n\n\n\n
    Step 4: Create the Table in Test DB (Only If It Doesn\u2019t Exist)<\/strong><\/h3>\n\n\n\n
    This creates the table in your test DB only if it\u2019s not already there.<\/p>\n\n\n\n
    -- On test DB\nCREATE TABLE my_table AS\nSELECT * FROM your_user.my_table@prod_user_link WHERE 1=0;<\/code><\/pre>\n\n\n\n
    \n
    WHERE 1=0<\/strong> ensures the structure is copied without copying data.<\/p>\n<\/blockquote>\n\n\n\n
    Step 5: Insert Data from Production to Test<\/strong><\/h3>\n\n\n\n
    Copy the actual data over the DB link.<\/p>\n\n\n\n
    -- On test DB\nINSERT INTO my_table\nSELECT * FROM your_user.my_table@prod_user_link;<\/code><\/pre>\n\n\n\n
    \u2705 Done!<\/h3>\n\n\n\n
    You have now:<\/p>\n\n\n\n
      \n
    • Created a local copy of another user’s table in production<\/strong><\/li>\n\n\n\n
    • Created a DB link<\/strong> from test<\/strong> to prod<\/strong><\/li>\n\n\n\n
    • Inserted the data into your test DB<\/strong><\/li>\n<\/ul>\n\n\n\n
      Let me know if you want this as a ready-to-run SQL script or with dynamic table names.<\/p>\n","protected":false},"excerpt":{"rendered":"
      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\u2019s table into a table under your own schema. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-3126","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3126","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=3126"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3126\/revisions"}],"predecessor-version":[{"id":3127,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3126\/revisions\/3127"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=3126"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3126"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=3126"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}