• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

import

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.....
0
rcc50886
Asked:
rcc50886
  • 8
  • 6
  • 6
8 Solutions
 
rcc50886Author Commented:
i forgot one thing ....i just need to append new data (without truncate the target table)
0
 
slightwv (䄆 Netminder) Commented:
On mobile right now so cannot confirm but I believe the columns ned to be exact.  The new datapump version might have different mapping options.  Not that familiar with them.

Can you maybe import into a temp table then migrate the data over?

Also, you really shouldn't ask question to specific Experts.  There are a lot of qualified Experts that can answer them.
0
 
mrjoltcolaCommented:
No, ignore=y won't help you since the table structure changed (different columns). You can't import directly to it for that reason.

You can create a new user, import the table into that schema user, so you get the table into the database...

import using fromuser/touser syntax

  imp system/admin file=expdat.dmp fromuser=MYUSER touser=TEMPUSER


Then write a SQL query to copy over the records into the partitioned table.

   INSERT INTO MYUSER.MYTABLE(COL1, COL2) SELECT COL1, COL2 FROM TEMPUSER.MYTABLE

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
rcc50886Author Commented:
>> mrjoltcola
can i use  directly with using dblink??
   INSERT INTO MYUSER.MYTABLE(COL1, COL2) SELECT COL1, COL2 FROM              TEMPUSER.MYTABLE@DBLINK
0
 
mrjoltcolaCommented:
Sure, you can create db links between 9 and 10 databases no problem.
0
 
rcc50886Author 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???
0
 
slightwv (䄆 Netminder) Commented:
How long it will take is impossible to answer.  To many variables.  I can say in my system, it wouldn't even notice 650 rows but I'm running on a fast network with fast servers/disks.

The new rows will abide by the partitions of the new table.  They sort of have to be definition of partitioning.
0
 
mrjoltcolaCommented:
Not sure why you'd partition a table with 650 rows anyway. Perhaps you left off some zeros? :)

But no, you don't need to specify partitions, that is how partitioned tables work, Oracle knows where to put the data. If you had a huge amount of data, then you can specify a partition to help Oracle, but 650 rows isn't worth the trouble.
0
 
rcc50886Author Commented:
we are adding data(new customers data) to the existing partitioned table....which has more than 2million rows..........
0
 
rcc50886Author 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
0
 
mrjoltcolaCommented:
In that case, if you have a remote database link between the systems, a simple cross-db INSERT...SELECT will work.
If the data is in a dump file, you should be safe and:

1) Import the customer data into its own schema
2) Check the data, analyze it
3) Copy it over with an INSERT ... SELECT FROM ... as mentioned above.
0
 
mrjoltcolaCommented:
When you import a schema level dump file, Oracle will choose the default tablespace of the current user if it cannot find the original. The table will create succesfully.

Note that the fromuser/touser options are needed, though, unless you have the exact username on the destination.
0
 
slightwv (䄆 Netminder) Commented:
Just to add:
Insert into select across a link works when the databases are connected.

When they aren't, exp/imp might be the only option (or a csv/delimited file using sql loader).

Then,you will likely need to use the option mrjoltcola said, import into a staging area.

That is if the datapump version will not let you import specfic columns.

Yes, a long journey ahead but hopefully a very enjoyable one!  I've enjoyed mine as a DBA!
0
 
mrjoltcolaCommented:
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.
0
 
slightwv (䄆 Netminder) Commented:
My mistake.  Missed the 9i in the original question.  You are stuck with straight exp.
0
 
rcc50886Author Commented:
i have sqlnet connection b/w two databases ...so using dblink seems to be much easier without any risks....
0
 
rcc50886Author 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?

0
 
slightwv (䄆 Netminder) Commented:
Column name differences not a problem.

Data types aren't a problem if they are the same or Oracle can figure out the conversion for you.

When in doubt, you should do conversions.  It eliminates confusion.

For example Oracle is good about inserting '1' into a number column.  String to number is simple.  Dates and other data types should be done manually.  Given the above: to_number('1')
0
 
slightwv (䄆 Netminder) Commented:
About the 'risks', as long as you are sure you want to load the data, there isn't any risk.

Easiest, probably.
0
 
rcc50886Author Commented:
thanks once again
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 8
  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now