table copy from one database to another 2

[TheDash]

Hello members,
In order to copy data from one table to another via dblink, using insert into select * from syntax, what are the limitations in doing that? Is it a slow approach? Thanks in advance.

 

[SantaMufasa]

Is it a slow approach?

As you can imagine, "slow" is a relative term. Although there may be some faster alternative in terms of "bits per second", when you consider the time savings of ease of use of a db link, then it's probably comparatively fast.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,
You could use the COPY command:

http://www.dba-oracle.com/tips_oracle_copy_examples.htm

as to which is faster, etc..as Santamufasa says ( paraphrased), one test is worth a thousand theories



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[carp]

Or - as we all have probably said at least once - "it depends".

 

[karluk]

The most well-known limitation of the "insert into ... select * from" syntax is that it doesn't work with tables that contain a long or long raw column. You will get an "ORA-00997: illegal use of LONG datatype" error.

 

[SantaMufasa]

...but no one should be using LONG or LONG RAW anymore. They should be using some form of LOB such as CLOB, which does work with "INSERT INTO...SELECT...".

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[karluk]

Advising against the use of long and long raw columns is all very well, but doesn't exactly correspond to the real world. In fact, I note that in one of my 10g databases, there are 223 objects in the system catalog that have these two data types. That includes such well known catalog views as dba_triggers and dba_tab_columns.

In any case, this thread has to do with restrictions on copying data across a db link with "insert into ... select from" syntax. The problem with long and long raw columns is one such restriction. Because of it, it's best to have an alternative approach in mind for when it's needed. Export/import works with longs and, as I recall, Turkbear's suggestion of the SQL*Plus copy command does too.