We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

import

rcc50886
rcc50886 asked
on
Medium Priority
248 Views
Last Modified: 2012-12-24
hi slightwv,

i had a situation...where i need to export two tables from XXX schema from source database which is 9i and i need to import into allready existing partitioned table(which has data on it) under YYY schema in 10g target database.

and the target partitioned table has 2 more columns defined additionally.

can iimport data by using ignore=y??

and my main concern is  both schema objects are in different tablespaces.....
i.e  source table was in ZZZ tablespace and target partitioned lies in different  tablespaces ?

can you please provide some guidlies...

i actually went through the oracle doccumentation....but i need to do it on qa environment...

so i need some guidlines from experts.....
Comment
Watch Question

Author

Commented:
i forgot one thing ....i just need to append new data (without truncate the target table)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
>> mrjoltcola
can i use  directly with using dblink??
   INSERT INTO MYUSER.MYTABLE(COL1, COL2) SELECT COL1, COL2 FROM              TEMPUSER.MYTABLE@DBLINK
Top Expert 2009

Commented:
Sure, you can create db links between 9 and 10 databases no problem.

Author

Commented:
but table has 650 rows......( no clob types ...max:  number(100),varchr(100)

so how much time it takes if i go with  insertinto myuser.table (cl1,cl2) select c1,c2 from xxx.table@dblink

if i go with above procedure.....all data will be inserted into partitioned table??

do i need to specify the partition tablespart name???
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
we are adding data(new customers data) to the existing partitioned table....which has more than 2million rows..........

Author

Commented:
>> insertinto myuser.table (cl1,cl2) select c1,c2 from xxx.table@dblink

the above method seems to be clear without any confusion,
but coming to export/import, i am getting lot of confusion  ....
like what happen when tablespaces are different in both source and target?
what happen if table structure is different??

----so i decided to use dblink for now.
i started my carrier as jr.oracle dba just 2mnths back...so i guess long journey ahead.

thanks to everyone for helping
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2009

Commented:
Good point, if you had a data pump file (from expdp) you could probably specify a subset of columns to import with a conversion function. I haven't done it. But I assume with 9i you have a standard old dump file from exp and need to use imp.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
My mistake.  Missed the 9i in the original question.  You are stuck with straight exp.

Author

Commented:
i have sqlnet connection b/w two databases ...so using dblink seems to be much easier without any risks....

Author

Commented:
insert into newtable (newcu1,newcu2) as select (cu1,cu2) from oldtable@dblink

if the column names are different like above is it creates any problem?

i think it will be ok as long as column character is same...correct me if i am wrong?

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
About the 'risks', as long as you are sure you want to load the data, there isn't any risk.

Easiest, probably.

Author

Commented:
thanks once again
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.