CODEWITHSUNDEEP
oracle-databaseplsqloracle11gplsqldeveloper
user975828
user975828

Reputation: 147

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

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

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

What are my best options?

Upvotes: 1

Views: 2330

Answers (2)

Pablo
Pablo

Reputation: 11021

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

Upvotes: 1

ntalbs
ntalbs

Reputation: 29438

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.

Upvotes: 3

Related Questions