CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
Abdurrahim ESKİN
Abdurrahim ESKİN

Reputation: 15

How To Copy Tables And Their Specific Columns To Another Database in Oracle

I have huge sized Oracle Db, and for reporting, queries are working too slow because of the network traffic. The data warehouse tools are expensive so that I have decided to create a new database and feed it from the main database in every quarter. I was fresh in the company and I am not working as a DBA, so I have worked on 48 different SQL queries to detect which Db tables and columns are using for reporting and finished. Now I know which Db tables and columns are using. At that stage, I wanted to ask the best methods to create a new database with the same tables and columns names with the data of the main database?

Upvotes: 1

Views: 206

Answers (1)

Adnan Bhatti
Adnan Bhatti

Reputation: 3480

  1. On Target (where tables need to be created) login as DBA user and grant create database link privilege to user who will own these tables. Something like, grant create database link to adnan;

  2. Login as that user now which will own the table and then create a private database link

CREATE DATABASE LINK dblink CONNECT TO [username at sourcedb] IDENTIFIED BY [sourcedb] user password> USING '[remote_database_name_in_tnsnames.ora file or actual connect identifier]';

using connection string directly instead of tnsnames.ora- CREATE DATABASE LINK dblink CONNECT TO remote_user IDENTIFIED BY password USING '(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=oracledb.example.com)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=service_name)) )';

  1. create table <yourtablenmae> as select * from [table_name_at_sourcedb]@dblink where rownum<1; --- This will create empty table if you want with data remove where condition.

Disclaimer: Haven't ran this so there might be some mistakes.

Upvotes: 1

Related Questions