Oracle PL/SQL and Shell scripting: from one schema to another schema

Question

I want to load data from one table from one schema to another schema on daily basis.

Tables are in different database so to create database link will not be an option due to some security purpose....
About million records will get process....
Databases are on different server , from database "A" I am fetching Employee presence details by combining emp details and emp presence table for period of a month , and loading this data in other table on database "B". Need to run this activity on daily basis.
I need to run a job daily at low peak hours to get complete copy of table into other db ...
will Import/Export or loading data with help of sqlldr?

please let me know the correct way.. Thanks in Advance..

What are my best options?

ntalbs · Accepted Answer · 2012-09-07 00:02:44Z

3

Well, it seems that using database link would best fit for your situation. If you want to read a table from a database, you should have read privilege. Perhaps you can ask the DBA creating an account(user) which only has read privilege for specific table. Then you can use database link connecting with the new user. You can't update or delete data from the table because the user you connecting doesn't have the write privilege. This can solve the security problem.

exp/imp and sqlldr are different tools. They don't work together. You can only import data from an export file. You can't load export file with sqlldr.

answered Sep 7, 2012 at 0:02

ntalbs

29.6k8 gold badges72 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tbone Over a year ago

+1 creating the specific dblink acct and privs is what I've seen done as well. whether to push or pull data across is another question.

Ben Over a year ago

You can also create the DB link on only one of the databases so if one is say internet / client facing it never needs to know that the other exists.

Pablo · Accepted Answer · 2012-09-07 01:18:37Z

If you want to run this periodically, it sounds like you might want to take a look at the Oracle Scheduler

Overview: http://docs.oracle.com/cd/B28359_01/server.111/b28310/schedover001.htm

To export the data and add it into the new database, you might want to use Oracle DataPump, which can do both the export and import for you, securely.

Data Pump Export: http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm

So your bet might be creating a shell script that uses data pump to create an export file from database number 2, and then uses data pump again to import said file into database number 1. Once you have that script, you can schedule it to run during nights or at any time you have low traffic.

Regards

Collectives™ on Stack Overflow

Oracle PL/SQL and Shell scripting: from one schema to another schema

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related