how to use exp/imp only for specific columns?

[alederer]

hallo!

i have for example the following situation:

a table on site a tab1(a_key integer,
a_val1 varchar(500),
a_val2 varchar(1000))

and a table on site b tab2((b_key integer,
b_val2 varchar(1000),
b_val3 integer)

is it possible to use the export/import utility or the sql loader to
transfer only the data of the columns a_key and a_val2 from tab1 to
tab2(into b_key, b_val2)?

in the documentation of export, i have only found the QUERY parameter,
but it can only limit the rows not the columns which should be exported.

if i have a direct database connection i can use
"insert into tab2(b_key,b_val2) (select a_key, a_val2 from tab1)"

but how can a make this with export/import utility or sql loader?

what i need is a way to export only specific columns of a table and to
import these export into tables with different column names and order.

where can i find the missing information?

thanks
andreas

[FredBear]

"alederer" <alederer@gmx.at> a écrit dans le message de news:40616570$1@e-post.inode.at... hallo! i have for example the following situation: a table on site a tab1(a_key integer, a_val1 varchar(500), a_val2 varchar(1000)) and a table on site b tab2((b_key integer, b_val2 varchar(1000), b_val3 integer) is it possible to use the export/import utility or the sql loader to transfer only the data of the columns a_key and a_val2 from tab1 to tab2(into b_key, b_val2)? in the documentation of export, i have only found the QUERY parameter, but it can only limit the rows not the columns which should be exported. if i have a direct database connection i can use "insert into tab2(b_key,b_val2) (select a_key, a_val2 from tab1)" but how can a make this with export/import utility or sql loader? what i need is a way to export only specific columns of a table and to import these export into tables with different column names and order. where can i find the missing information? thanks andreas

You can execute your first query with SQL*Plus and spool the result in a file
which can be loaded with SQL*Loader.

Regards
Michel Cadot

[alederer]

Michel Cadot schrieb: "alederer" <alederer@gmx.at> a écrit dans le message de news:40616570$1@e-post.inode.at... hallo!i have for example the following situation:a table on site a tab1(a_key integer, a_val1 varchar(500), a_val2 varchar(1000))and a table on site b tab2((b_key integer, b_val2 varchar(1000), b_val3 integer)is it possible to use the export/import utility or the sql loader totransfer only the data of the columns a_key and a_val2 from tab1 totab2(into b_key, b_val2)?in the documentation of export, i have only found the QUERY parameter,but it can only limit the rows not the columns which should be exported.if i have a direct database connection i can use"insert into tab2(b_key,b_val2) (select a_key, a_val2 from tab1)"but how can a make this with export/import utility or sql loader?what i need is a way to export only specific columns of a table and toimport these export into tables with different column names and order.where can i find the missing information?thanksandreas You can execute your first query with SQL*Plus and spool the result in a file which can be loaded with SQL*Loader.

I see the following problems:
* I need to make the export/spool within a c++ application, ok i can
call a external process which make the work.
* because the application is generic, i don't know what data is in the
table columns, so what termination character should i use for the
sqlldr control section

i have downloaded the sqlldr_exp skripts from
http://asktom.oracle.com/~tkyte/flat/index.html
but these have some limits.
* what about the limit, that one row/line is limited to 4000 characters,
how can i do larger exports/spools?
* what about newline characters in the column data?

Why is it so complicated in oracle to transfer data via files from one
database to another, between different database versions ?
Is there no way like in db2 where the export recognizes a select
statement, the data is dumped to ixf file format which can be used on
all plattforms and on the load site, you can specify a different order
for the columns to load from the ixf file. I do not need to worry about
a termination character.

thanks
andreas

[Kevin A Lewis]

Have you considered setting up a Database Link - to enable a single piece of
SQL to know about two Oracle Databases (even two different version : albeit
with limits)

You can then update (INSERT, UPDATE or DELETE) based on data read in the
source database.

Does this help! Not sure why you needed the file based approach.

Regards Kevin
"alederer" <alederer@gmx.at> wrote in message
news:406194de$1@e-post.inode.at...
Michel Cadot schrieb: "alederer" <alederer@gmx.at> a écrit dans le message de
news:40616570$1@e-post.inode.at...hallo!i have for example the following situation:a table on site a tab1(a_key integer, a_val1 varchar(500), a_val2 varchar(1000))and a table on site b tab2((b_key integer, b_val2 varchar(1000), b_val3 integer)is it possible to use the export/import utility or the sql loader totransfer only the data of the columns a_key and a_val2 from tab1 totab2(into b_key, b_val2)?in the documentation of export, i have only found the QUERY parameter,but it can only limit the rows not the columns which should be exported.if i have a direct database connection i can use"insert into tab2(b_key,b_val2) (select a_key, a_val2 from tab1)"but how can a make this with export/import utility or sql loader?what i need is a way to export only specific columns of a table and toimport these export into tables with different column names and order.where can i find the missing information?thanksandreas You can execute your first query with SQL*Plus and spool the result in a
file which can be loaded with SQL*Loader.

I see the following problems:
* I need to make the export/spool within a c++ application, ok i can
call a external process which make the work.
* because the application is generic, i don't know what data is in the
table columns, so what termination character should i use for the
sqlldr control section

i have downloaded the sqlldr_exp skripts from
http://asktom.oracle.com/~tkyte/flat/index.html
but these have some limits.
* what about the limit, that one row/line is limited to 4000 characters,
how can i do larger exports/spools?
* what about newline characters in the column data?

Why is it so complicated in oracle to transfer data via files from one
database to another, between different database versions ?
Is there no way like in db2 where the export recognizes a select
statement, the data is dumped to ixf file format which can be used on
all plattforms and on the load site, you can specify a different order
for the columns to load from the ixf file. I do not need to worry about
a termination character.

thanks
andreas

[alederer]

The problem is, that the two databases are on different sites which are
connected via a small bandwith line.

so i don't want to move large amount of data (e.g. 17 GB) between these
databases.

is it possible to make a database link which compresses the data?

thanks
andreas

Kevin A Lewis schrieb:
Have you considered setting up a Database Link - to enable a single piece of SQL to know about two Oracle Databases (even two different version : albeit with limits) You can then update (INSERT, UPDATE or DELETE) based on data read in the source database. Does this help! Not sure why you needed the file based approach. Regards Kevin "alederer" <alederer@gmx.at> wrote in message news:406194de$1@e-post.inode.at... Michel Cadot schrieb: "alederer" <alederer@gmx.at> a écrit dans le message de news:40616570$1@e-post.inode.at... hallo!i have for example the following situation:a table on site a tab1(a_key integer, a_val1 varchar(500), a_val2 varchar(1000))and a table on site b tab2((b_key integer, b_val2 varchar(1000), b_val3 integer)is it possible to use the export/import utility or the sql loader totransfer only the data of the columns a_key and a_val2 from tab1 totab2(into b_key, b_val2)?in the documentation of export, i have only found the QUERY parameter,but it can only limit the rows not the columns which should be exported.if i have a direct database connection i can use"insert into tab2(b_key,b_val2) (select a_key, a_val2 from tab1)"but how can a make this with export/import utility or sql loader?what i need is a way to export only specific columns of a table and toimport these export into tables with different column names and order.where can i find the missing information?thanksandreasYou can execute your first query with SQL*Plus and spool the result in a file which can be loaded with SQL*Loader. I see the following problems: * I need to make the export/spool within a c++ application, ok i can call a external process which make the work. * because the application is generic, i don't know what data is in the table columns, so what termination character should i use for the sqlldr control section i have downloaded the sqlldr_exp skripts from http://asktom.oracle.com/~tkyte/flat/index.html but these have some limits. * what about the limit, that one row/line is limited to 4000 characters, how can i do larger exports/spools? * what about newline characters in the column data? Why is it so complicated in oracle to transfer data via files from one database to another, between different database versions ? Is there no way like in db2 where the export recognizes a select statement, the data is dumped to ixf file format which can be used on all plattforms and on the load site, you can specify a different order for the columns to load from the ixf file. I do not need to worry about a termination character. thanks andreas

[Kevin A Lewis]

What Oracle DB versions are each of these databases (source and target)

Regards Kevin
"alederer" <alederer@gmx.at> wrote in message
news:40619df6$1@e-post.inode.at...
The problem is, that the two databases are on different sites which are
connected via a small bandwith line.

so i don't want to move large amount of data (e.g. 17 GB) between these
databases.

is it possible to make a database link which compresses the data?

thanks
andreas

Kevin A Lewis schrieb:
Have you considered setting up a Database Link - to enable a single piece
of SQL to know about two Oracle Databases (even two different version :
albeit with limits) You can then update (INSERT, UPDATE or DELETE) based on data read in the source database. Does this help! Not sure why you needed the file based approach. Regards Kevin "alederer" <alederer@gmx.at> wrote in message news:406194de$1@e-post.inode.at... Michel Cadot schrieb:"alederer" <alederer@gmx.at> a écrit dans le message de news:40616570$1@e-post.inode.at...hallo!i have for example the following situation:a table on site a tab1(a_key integer, a_val1 varchar(500), a_val2 varchar(1000))and a table on site b tab2((b_key integer, b_val2 varchar(1000), b_val3 integer)is it possible to use the export/import utility or the sql loader totransfer only the data of the columns a_key and a_val2 from tab1 totab2(into b_key, b_val2)?in the documentation of export, i have only found the QUERY parameter,but it can only limit the rows not the columns which should be exported.if i have a direct database connection i can use"insert into tab2(b_key,b_val2) (select a_key, a_val2 from tab1)"but how can a make this with export/import utility or sql loader?what i need is a way to export only specific columns of a table and toimport these export into tables with different column names and order.where can i find the missing information?thanksandreasYou can execute your first query with SQL*Plus and spool the result in a filewhich can be loaded with SQL*Loader. I see the following problems: * I need to make the export/spool within a c++ application, ok i can call a external process which make the work. * because the application is generic, i don't know what data is in the table columns, so what termination character should i use for the sqlldr control section i have downloaded the sqlldr_exp skripts from http://asktom.oracle.com/~tkyte/flat/index.html but these have some limits. * what about the limit, that one row/line is limited to 4000 characters, how can i do larger exports/spools? * what about newline characters in the column data? Why is it so complicated in oracle to transfer data via files from one database to another, between different database versions ? Is there no way like in db2 where the export recognizes a select statement, the data is dumped to ixf file format which can be used on all plattforms and on the load site, you can specify a different order for the columns to load from the ixf file. I do not need to worry about a termination character. thanks andreas

[Andreas Lederer]

That differs, e.g. 8.1.7 and 9.2

andreas

Kevin A Lewis schrieb:
What Oracle DB versions are each of these databases (source and target) Regards Kevin "alederer" <alederer@gmx.at> wrote in message news:40619df6$1@e-post.inode.at... The problem is, that the two databases are on different sites which are connected via a small bandwith line. so i don't want to move large amount of data (e.g. 17 GB) between these databases. is it possible to make a database link which compresses the data? thanks andreas Kevin A Lewis schrieb: Have you considered setting up a Database Link - to enable a single piece of SQL to know about two Oracle Databases (even two different version : albeit with limits)You can then update (INSERT, UPDATE or DELETE) based on data read in thesource database.Does this help! Not sure why you needed the file based approach.Regards Kevin"alederer" <alederer@gmx.at> wrote in messagenews:406194de$1@e-post.inode.at...Michel Cadot schrieb:"alederer" <alederer@gmx.at> a écrit dans le message denews:40616570$1@e-post.inode.at...>hallo!>>i have for example the following situation:>>a table on site a tab1(a_key integer,> a_val1 varchar(500),> a_val2 varchar(1000))>>and a table on site b tab2((b_key integer,> b_val2 varchar(1000),> b_val3 integer)>>is it possible to use the export/import utility or the sql loader to>transfer only the data of the columns a_key and a_val2 from tab1 to>tab2(into b_key, b_val2)?>>in the documentation of export, i have only found the QUERY parameter,>but it can only limit the rows not the columns which should be exported.>>if i have a direct database connection i can use>"insert into tab2(b_key,b_val2) (select a_key, a_val2 from tab1)">>but how can a make this with export/import utility or sql loader?>>what i need is a way to export only specific columns of a table and to>import these export into tables with different column names and order.>>where can i find the missing information?>>thanks>andreasYou can execute your first query with SQL*Plus and spool the result inafilewhich can be loaded with SQL*Loader.I see the following problems:* I need to make the export/spool within a c++ application, ok i can call a external process which make the work.* because the application is generic, i don't know what data is in the table columns, so what termination character should i use for the sqlldr control sectioni have downloaded the sqlldr_exp skripts fromhttp://asktom.oracle.com/~tkyte/flat/index.htmlbut these have some limits.* what about the limit, that one row/line is limited to 4000 characters,how can i do larger exports/spools?* what about newline characters in the column data?Why is it so complicated in oracle to transfer data via files from onedatabase to another, between different database versions ?Is there no way like in db2 where the export recognizes a selectstatement, the data is dumped to ixf file format which can be used onall plattforms and on the load site, you can specify a different orderfor the columns to load from the ixf file. I do not need to worry abouta termination character.thanksandreas

[Kevin A Lewis]

Ok good - that helps

You could produce a simple flat file from the 8.1.7 source database and
declare that file once on the target system as an External Table. This
defines the structure of the table in the dictionary but allows the data to
be stored in a flat file on the OS.

The only thing I am not sure of is the performance. By the way the External
table will by definition be read only. Check out to SQL Reference Manual
for 9i

Does that help.

Regards Kevin
"Andreas Lederer" <Andreas.Lederer@tecco.at> wrote in message
news:4061a39e$1@e-post.inode.at...
That differs, e.g. 8.1.7 and 9.2

andreas

Kevin A Lewis schrieb:
What Oracle DB versions are each of these databases (source and target) Regards Kevin "alederer" <alederer@gmx.at> wrote in message news:40619df6$1@e-post.inode.at... The problem is, that the two databases are on different sites which are connected via a small bandwith line. so i don't want to move large amount of data (e.g. 17 GB) between these databases. is it possible to make a database link which compresses the data? thanks andreas Kevin A Lewis schrieb:Have you considered setting up a Database Link - to enable a single piece ofSQL to know about two Oracle Databases (even two different version : albeitwith limits)You can then update (INSERT, UPDATE or DELETE) based on data read in thesource database.Does this help! Not sure why you needed the file based approach.Regards Kevin"alederer" <alederer@gmx.at> wrote in messagenews:406194de$1@e-post.inode.at...Michel Cadot schrieb:"alederer" <alederer@gmx.at> a écrit dans le message denews:40616570$1@e-post.inode.at...>hallo!>>i have for example the following situation:>>a table on site a tab1(a_key integer,> a_val1 varchar(500),> a_val2 varchar(1000))>>and a table on site b tab2((b_key integer,> b_val2 varchar(1000),> b_val3 integer)>>is it possible to use the export/import utility or the sql loader to>transfer only the data of the columns a_key and a_val2 from tab1 to>tab2(into b_key, b_val2)?>>in the documentation of export, i have only found the QUERY parameter,>but it can only limit the rows not the columns which should be exported.>>if i have a direct database connection i can use>"insert into tab2(b_key,b_val2) (select a_key, a_val2 from tab1)">>but how can a make this with export/import utility or sql loader?>>what i need is a way to export only specific columns of a table and to>import these export into tables with different column names and order.>>where can i find the missing information?>>thanks>andreasYou can execute your first query with SQL*Plus and spool the result in afilewhich can be loaded with SQL*Loader.I see the following problems:* I need to make the export/spool within a c++ application, ok i can call a external process which make the work.* because the application is generic, i don't know what data is in the table columns, so what termination character should i use for the sqlldr control sectioni have downloaded the sqlldr_exp skripts fromhttp://asktom.oracle.com/~tkyte/flat/index.htmlbut these have some limits.* what about the limit, that one row/line is limited to 4000 characters,how can i do larger exports/spools?* what about newline characters in the column data?Why is it so complicated in oracle to transfer data via files from onedatabase to another, between different database versions ?Is there no way like in db2 where the export recognizes a selectstatement, the data is dumped to ixf file format which can be used onall plattforms and on the load site, you can specify a different orderfor the columns to load from the ixf file. I do not need to worry abouta termination character.thanksandreas

[alederer]

Kevin A Lewis schrieb:
Ok good - that helps You could produce a simple flat file from the 8.1.7 source database and declare that file once on the target system as an External Table. This defines the structure of the table in the dictionary but allows the data to be stored in a flat file on the OS. The only thing I am not sure of is the performance. By the way the External table will by definition be read only. Check out to SQL Reference Manual for 9i Does that help.

as i wrote (see below), i have no information about the data which is
stored inside the tables. e.g. it is possible that there are line breaks
or other special charaters inside of column data. or they contains
unicode...

so, for a flat file, i need to specify a field termination charater, but
which one should i use?

thanks
andreas
Regards Kevin "Andreas Lederer" <Andreas.Lederer@tecco.at> wrote in message news:4061a39e$1@e-post.inode.at... That differs, e.g. 8.1.7 and 9.2 andreas Kevin A Lewis schrieb: What Oracle DB versions are each of these databases (source and target)Regards Kevin"alederer" <alederer@gmx.at> wrote in messagenews:40619df6$1@e-post.inode.at...The problem is, that the two databases are on different sites which areconnected via a small bandwith line.so i don't want to move large amount of data (e.g. 17 GB) between thesedatabases.is it possible to make a database link which compresses the data?thanksandreasKevin A Lewis schrieb:Have you considered setting up a Database Link - to enable a single pieceofSQL to know about two Oracle Databases (even two different version :albeitwith limits)You can then update (INSERT, UPDATE or DELETE) based on data read in thesource database.Does this help! Not sure why you needed the file based approach.Regards Kevin"alederer" <alederer@gmx.at> wrote in messagenews:406194de$1@e-post.inode.at...Michel Cadot schrieb:>"alederer" <alederer@gmx.at> a écrit dans le message denews:40616570$1@e-post.inode.at...>>hallo!>>>>i have for example the following situation:>>>>a table on site a tab1(a_key integer,>> a_val1 varchar(500),>> a_val2 varchar(1000))>>>>and a table on site b tab2((b_key integer,>> b_val2 varchar(1000),>> b_val3 integer)>>>>is it possible to use the export/import utility or the sql loader to>>transfer only the data of the columns a_key and a_val2 from tab1 to>>tab2(into b_key, b_val2)?>>>>in the documentation of export, i have only found the QUERY parameter,>>but it can only limit the rows not the columns which should be exported.>>>>if i have a direct database connection i can use>>"insert into tab2(b_key,b_val2) (select a_key, a_val2 from tab1)">>>>but how can a make this with export/import utility or sql loader?>>>>what i need is a way to export only specific columns of a table and to>>import these export into tables with different column names and order.>>>>where can i find the missing information?>>>>thanks>>andreas>>>You can execute your first query with SQL*Plus and spool the result in afile>which can be loaded with SQL*Loader.>I see the following problems:* I need to make the export/spool within a c++ application, ok i can call a external process which make the work.* because the application is generic, i don't know what data is in the table columns, so what termination character should i use for the sqlldr control sectioni have downloaded the sqlldr_exp skripts fromhttp://asktom.oracle.com/~tkyte/flat/index.htmlbut these have some limits.* what about the limit, that one row/line is limited to 4000 characters,how can i do larger exports/spools?* what about newline characters in the column data?Why is it so complicated in oracle to transfer data via files from onedatabase to another, between different database versions ?Is there no way like in db2 where the export recognizes a selectstatement, the data is dumped to ixf file format which can be used onall plattforms and on the load site, you can specify a different orderfor the columns to load from the ixf file. I do not need to worry abouta termination character.thanksandreas

[Kevin A Lewis]

Ok

How about if you use the form in this example from the manual. The Key bit
being
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

Surely the chances of the data containing the combination "," other than
between field, columns would be very low.

Regards Kevin




CREATE TABLE emp_external (
employee_id NUMBER(6),

last_name VARCHAR2(20),

email VARCHAR2(25),

hire_date DATE,

job_id VARCHAR2(10),

salary NUMBER(8,2)

)

ORGANIZATION EXTERNAL

(TYPE oracle_loader

DEFAULT DIRECTORY admin

ACCESS PARAMETERS

(

RECORDS DELIMITED BY newline

BADFILE 'ulcase1.bad'

DISCARDFILE 'ulcase1.dis'

LOGFILE 'ulcase1.log'

SKIP 20

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

(

deptno INTEGER EXTERNAL,

dname CHAR,

loc CHAR

)

)

LOCATION ('ulcase1.dat')

)

REJECT LIMIT UNLIMITED;

"alederer" <alederer@gmx.at> wrote in message
news:40616570$1@e-post.inode.at... hallo! i have for example the following situation: a table on site a tab1(a_key integer, a_val1 varchar(500), a_val2 varchar(1000)) and a table on site b tab2((b_key integer, b_val2 varchar(1000), b_val3 integer) is it possible to use the export/import utility or the sql loader to transfer only the data of the columns a_key and a_val2 from tab1 to tab2(into b_key, b_val2)? in the documentation of export, i have only found the QUERY parameter, but it can only limit the rows not the columns which should be exported. if i have a direct database connection i can use "insert into tab2(b_key,b_val2) (select a_key, a_val2 from tab1)" but how can a make this with export/import utility or sql loader? what i need is a way to export only specific columns of a table and to import these export into tables with different column names and order. where can i find the missing information? thanks andreas

[Mark]

Andreas,

I think I read that the "COPY" command in sqlplus was going away in
10g. That's too bad. It was great for little one-off jobs like this.

If you have 9i or below you can still schedule it this way:

/* I believe remotedb must be in your tnsnames.ora file. */
sqlplus user/pass@localdb <<EOF 1> logfile 2>&1
sql> set copycommit 100;
sql> set long 1000000; -- only necessary if you are copying a long
value
sql> copy from remotedb append tab2(b_key, b_val2) using select a_key,
a_val2;
EOF

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX


alederer <alederer@gmx.at> wrote in message news:<40616570$1@e-post.inode.at>... hallo! i have for example the following situation: a table on site a tab1(a_key integer, a_val1 varchar(500), a_val2 varchar(1000)) and a table on site b tab2((b_key integer, b_val2 varchar(1000), b_val3 integer) is it possible to use the export/import utility or the sql loader to transfer only the data of the columns a_key and a_val2 from tab1 to tab2(into b_key, b_val2)? in the documentation of export, i have only found the QUERY parameter, but it can only limit the rows not the columns which should be exported. if i have a direct database connection i can use "insert into tab2(b_key,b_val2) (select a_key, a_val2 from tab1)" but how can a make this with export/import utility or sql loader? what i need is a way to export only specific columns of a table and to import these export into tables with different column names and order. where can i find the missing information? thanks andreas

[Turkbear]

The OP indicated 2 problems with this approach, a slow connection between sites and 17Gb of data...


simmons_mark@yahoo.com (Mark) wrote:
Andreas,I think I read that the "COPY" command in sqlplus was going away in10g. That's too bad. It was great for little one-off jobs like this.If you have 9i or below you can still schedule it this way:/* I believe remotedb must be in your tnsnames.ora file. */sqlplus user/pass@localdb <<EOF 1> logfile 2>&1sql> set copycommit 100;sql> set long 1000000; -- only necessary if you are copying a longvaluesql> copy from remotedb append tab2(b_key, b_val2) using select a_key,a_val2;EOFMark SimmonsSr. Oracle DBASabre-Holdings, Southlake, TXalederer <alederer@gmx.at> wrote in message news:<40616570$1@e-post.inode.at>... hallo! i have for example the following situation: a table on site a tab1(a_key integer, a_val1 varchar(500), a_val2 varchar(1000)) and a table on site b tab2((b_key integer, b_val2 varchar(1000), b_val3 integer) is it possible to use the export/import utility or the sql loader to transfer only the data of the columns a_key and a_val2 from tab1 to tab2(into b_key, b_val2)? in the documentation of export, i have only found the QUERY parameter, but it can only limit the rows not the columns which should be exported. if i have a direct database connection i can use "insert into tab2(b_key,b_val2) (select a_key, a_val2 from tab1)" but how can a make this with export/import utility or sql loader? what i need is a way to export only specific columns of a table and to import these export into tables with different column names and order. where can i find the missing information? thanks andreas

[Mark]

Sorry. I didn't catch that part of the posting. Please ignore my post.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

simmons_mark@yahoo.com (Mark) wrote in message news:<5366fb41.0403240855.402c6025@posting.google.com>... Andreas, I think I read that the "COPY" command in sqlplus was going away in 10g. That's too bad. It was great for little one-off jobs like this. If you have 9i or below you can still schedule it this way: /* I believe remotedb must be in your tnsnames.ora file. */ sqlplus user/pass@localdb <<EOF 1> logfile 2>&1 sql> set copycommit 100; sql> set long 1000000; -- only necessary if you are copying a long value sql> copy from remotedb append tab2(b_key, b_val2) using select a_key, a_val2; EOF Mark Simmons Sr. Oracle DBA Sabre-Holdings, Southlake, TX alederer <alederer@gmx.at> wrote in message news:<40616570$1@e-post.inode.at>... hallo! i have for example the following situation: a table on site a tab1(a_key integer, a_val1 varchar(500), a_val2 varchar(1000)) and a table on site b tab2((b_key integer, b_val2 varchar(1000), b_val3 integer) is it possible to use the export/import utility or the sql loader to transfer only the data of the columns a_key and a_val2 from tab1 to tab2(into b_key, b_val2)? in the documentation of export, i have only found the QUERY parameter, but it can only limit the rows not the columns which should be exported. if i have a direct database connection i can use "insert into tab2(b_key,b_val2) (select a_key, a_val2 from tab1)" but how can a make this with export/import utility or sql loader? what i need is a way to export only specific columns of a table and to import these export into tables with different column names and order. where can i find the missing information? thanks andreas