Solved

how to load data into a table across different databases

Posted on 2009-04-15
9
678 Views
Last Modified: 2013-12-18
HI,
I have two same structured tables in two different schemas in two different databases in different networks, environments. Say one is table is in testing environment other table is development environment. What is the easiest way to load the changes that made in testing environment table to the development environment table. Any ideas, sample code, resources, links highly appreciated. Thanks in advance
0
Comment
Question by:gudii9
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 24149177
dbms_datapump  is quite easy...

here's a basic framework to move schemas...

for more information check...

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_datpmp.htm#i1007277
declare

   v_handle NUMBER;

BEGIN

   v_handle:=

   DBMS_DATAPUMP.OPEN (operation   => 'IMPORT',

                       job_mode    => 'SCHEMA',

                       remote_link => 'source_db_name'

                               );
 

--  put the schemas in quotes

   DBMS_DATAPUMP.metadata_filter (handle => v_handle,

                                  NAME   => 'SCHEMA_LIST',

                                  VALUE  => '''SCHEMA1'',''SCHEMA2'''

                               );
 

--  options are TRUNCATE, REPLACE, APPEND, and SKIP. 

   DBMS_DATAPUMP.set_parameter (handle => v_handle,

                                NAME   => 'TABLE_EXISTS_ACTION',

                                VALUE  => 'REPLACE'

                               );
 

--  remove this line if you want the data as well

--  don't change the value, remove the call entirely

   DBMS_DATAPUMP.DATA_FILTER(handle  => v_handle,

                             NAME    => 'INCLUDE_ROWS',

                             VALUE   => 0

                               );
 
 

   dbms_datapump.add_file(handle => v_handle,

                          filename => 'you_log_file_name_here.log',

                          directory => 'DATA_PUMP_DIR',

                          filetype=>3); 
 

   DBMS_DATAPUMP.set_parallel(v_handle, 4);
 

   DBMS_DATAPUMP.start_job (v_handle);
 

END;

/

Open in new window

0
 
LVL 16

Assisted Solution

by:Milleniumaire
Milleniumaire earned 125 total points
ID: 24149274
Create a database link on one of the databases and then use this database link to retrieve the data from the other database.

For example, you have database Dev and database Test.  Assuming your data has already been setup on Dev, then on the Test database create a database link:

create public database link devdb
connect to dev_user
identified by "dev_user_password"
using 'XXX';

dev_user is the Oracle account of the owner of the table on your dev database and dev_user_password is the password for this account.  XXX is the entry in your tnsnames.ora file that identifies the Dev database.

The database link can then be used on the Test system in a sql statement to access data on the Dev database as follows:

insert into my_table_on_test
select * from my_table_on_dev@devdb;

This statement assumes that my_table_on_test and my_table_on_dev have exactly the same structure.
0
 
LVL 7

Author Comment

by:gudii9
ID: 24149951
problem is we do not have whole lot of privileges like DBA etc on many databases. Any other quick easy way without hasle of privileges etc. Please advise
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 125 total points
ID: 24150028
Dump to a text file and load using SQL*Loader.

The dump would look something like this:

set lines 20000 pages 0 term off echo off feedback off trimspool on
spool output.txt
select field1 || chr(9) || field2 || chr(9) || ...
from table;
spool off

Then the SQL*Load control file would look something like this (in place of <tab> you need an actual tab character).  This will truncate the existing table and reload it from the text file.

LOAD DATA
INFILE 'output.txt'
TRUNCATE INTO TABLE <table>
FIELDS TERMINATED BY '<tab>'
trailing NULLCOLS
( field1,
  field2,
  .... )
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 24150228
do you have the same user and password on both the target and source?

if so,  you don't need to create a link explicitly.  you can do the inserts as Milleniumaire showed above but where you have the dblink name you instead just use the tnsconnect string.

insert into my_table_on_test
select * from my_table_on_dev@XXX;


note, this ONLY works if the user you are running the insert also exists on the source system with the same password
0
 
LVL 34

Expert Comment

by:johnsone
ID: 24150258
There is also the copy command in SQL*Plus.
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 24155852
You could use export and import.  These operating system commands allow you to export data from a table, schema or database into a file and then import that data into another database.

On your dev system at the os command line (assuming you are using unix) type:

exp

then enter the username password of the user owning the tables.  Follow the prompts to export a single table.  You will then have a file with a .dmp extension.  Copy this binary file to the machine on which the test database resides and at the os prompt type:

imp

again, follow the prompts and the data in the .dmp file will be loaded into the relevant tables.

Be aware that the data will be appended to any other data that already exists in the tables.  Ideally, the schema owners (Oracle accounts) should be the same on dev and test to avoid requiring special privileges.

If you have access to a development tool called Toad it is also possible to create "insert" scripts from table data, which you can then run against another database.  To do this, right click on a table and chose Export Data.
0
 
LVL 7

Author Closing Comment

by:gudii9
ID: 31570500
thank you
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24262613
glad we could help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Cannot open form error 6 63
Oracle 12c patching 1 59
ORA-01008: not all variables bound. 6 37
How to free up undo space? 3 24
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now