Download a db table through UNIX shell script

Hi,

I'm an amateur and need your help in figuring this out. I have been asked to connect to a prod db from non-prod env., and download a table from prod db to non-prod env.

I was able to connect to the prod db and then run a simple query as below.

 @@@@@@@@@@
 X=`$ORACLE_HOME/bin/sqlplus id/pwd@"prod_db"<<eof
set serveroutput on;
set feedback off;
set linesize 1000;
select count(*) from table;
EXIT;
eof`
 echo $X>dbconn.dat
@@@@@@@@@@

and I got the result with count and lot of other text relaed to Oracle product.

Can you please advise as to how would I download an entire table from the prod_db and load into a non_prod db under my schema ? Basically, below are what I need to do.

1. connect to prod_db
2. download/unload a table from prod_db
3. load the same table into another non_prod db under my schema
4. then compare myschema.table with system_schema.table in non_prod db
5. then replace system_schema.table with myschema.table

I will be working on how to figure all of above but since I'm running short on time, would like to get some help

Thanks in advance
Arun

To keep the forums high quality for all users, please take the time to format your posts correctly.

First of all, use Code Tags when you post any code or data samples so others can easily read your code. You can easily do this by highlighting your code and then clicking on the # in the editing menu. (You can also type code tags

```text
 and 
```

by hand.)

Second, avoid adding color or different fonts and font size to your posts. Selective use of color to highlight a single word or phrase can be useful at times, but using color, in general, makes the forums harder to read, especially bright colors like red.

Third, be careful when you cut-and-paste, edit any odd characters and make sure all links are working property.

Thank You.

The UNIX and Linux Forums

My ORACLE has become a bit rusty, but if I remember correctly, you can, from a DB you're connected to, reach out to another DB and select/query/copy data without going via spool/download etc.

Hello,
why don't you use the Oracle SQL-Developer.

1. Connect to prod_db
2. Use export function to export a table, db, sheme ....
3. Connect to dev_bd
4. Import by using the file created in 2.

Table export example as attachment !
(renamed to *.txt for upload)

Regards

Or, how about connecting to the dev server, setting up a database link and issuing:-

CREATE TABLE dev_table AS SELECT * FROM source_table@my_db_link ;

This would drag the table direct between the two, negating the need to export/import.

Robin

This is certainly a valid way of moving data from one database to another. The only downside is that you would
need to manually created indexes, constraints and triggers. Once the table gets created you can also use a merge
command to update the local table from a remote table.

Merge Command

There are cases where people have not wanted to use db links. In that case you would want to use data pump export/import.

11g Datapump

In this case either you have a directory that is visible to both databases and both databases have a database directory
pointing to the same OS directory, or you need to move the file yourself.

I am always curious when someone asks an Oracle question on a website more devoted to OS questions/issues.
While this is a great site there are other Oracle specific forums that are also worth looking at.

gandolf989 - thanks for the insight, I was looking at accomplishing this through a shell script .. that is the reason I posed in this forum. Let me try the options you all suggested. Thank you again.

Your welcome. Many Oracle DBA's spend a significant amount of time doing shell scripting. Hence, there is some overlap between Oracle and Unix/Linux forums.