Re: how to use exp/imp only for specific columns?

From: alederer <alederer_at_gmx.at>
Date: Wed, 24 Mar 2004 15:40:53 +0100
Message-ID: <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_at_gmx.at> wrote in message
> news:406194de$1_at_e-post.inode.at...
> Michel Cadot schrieb:
>

>>"alederer" <alederer_at_gmx.at> a écrit dans le message de

>
> news:40616570$1_at_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.
>>

>
>
> 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
>
>
Received on Wed Mar 24 2004 - 08:40:53 CST