Export data from oracle 8i and import into oracle 10g database
Nik
I am looking forward to have help for export and import data from
oracle 8i to oracle 10g database for once in a day. This newly imported
data should append with exisiting data in oracle 10g. In any cause the
previous data will not get delete for replace with newly loaded dmp
file.
Thanks and Regards
Nikhil
fitzj...@cox.net
The exp and imp utilities are not intended for replication or server
synchronisation, so why are you attempting to use them for such a
purpose? And why are you trying to synchronise a 10g database from 8i?
With 9i upwards you have access to DataGuard; with 8i you have
Standby, although I am not certain they will be compatible. Another
option would be data change triggers to populate tables you can use to
update another schema or instance. Data change tables can be exported
and imported into a new instance on a daily basis.
I would seriously reconsider what you're trying to do and how you're
trying to do it, as exp and imp are not the proper tools for the way
you want to use them.
David Fitzjarrell
ExpertDba
Let me give you a more USEFUL answer!
Look at what Tom Kyte has to say to a question which is similar to the
one you have:
=======================================================================
QUESTION:1. when I import the data, Can I append that into the existing
data in the
database?
The answer to 1) is absolutely yes. Here is an example showing that at
work:
ops$tkyte@8i> drop table t;
Table dropped.
ops$tkyte@8i> create table t ( x int );
Table created.
ops$tkyte@8i> insert into t values ( 1 );
1 row created.
ops$tkyte@8i> commit;
Commit complete.
ops$tkyte@8i> host exp userid=/ tables=t
Export: Release 8.1.5.0.0 - Production on Tue Jun 13 07:09:23 2000
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 -
Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T 1 rows
exported
Export terminated successfully without warnings.
ops$tkyte@8i> host imp userid=/ full=y ignore=y
Import: Release 8.1.5.0.0 - Production on Tue Jun 13 07:09:25 2000
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 -
Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
Export file created by EXPORT:V08.01.05 via conventional path
import done in US7ASCII character set and US7ASCII NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing table "T" 1 rows
imported
Import terminated successfully without warnings.
ops$tkyte@8i>
ops$tkyte@8i> select * from t;
X
----------
1
1
So, that shows that the table was appended to.
=======================================================================
GOT any clues===> "ignore=y" option. :+)
For more details look at this link:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:301416506499
regards,
Channesh
fitzj...@cox.net
ExpertDba wrote:
> Hi Nikhil,
> Let me give you a more USEFUL answer!
>
Useful to whom and for what purpose?
> Look at what Tom Kyte has to say to a question which is similar to the
> one you have:
And also notice the data is duplicated, which is not what the OP has
intended. Should proper integrity constraints not be in place the OP
will, without doubt, create duplicated data in these tables as he is
appending to them.
And it shows the data is now duplicated.
> =======================================================================
> GOT any clues===> "ignore=y" option. :+)
>
This has nothing to do with how the data is inserted, it only proceeds
to process the import after the initial 'create table' fails.
> For more details look at this link:
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:301416506499
>
> regards,
> Channesh
Not knowing how the schema is constructed, much less the constraints in
force, offering such 'advice' is foolish, at best, as the OP can create
a data mess with such an import, duplicating data and resulting in a
cleanup effort that may take more time to complete than re-thinking his
strategy and changing directions to one more suited to the intended
result. I have no issue with Tom Kyte nor his answers, however closer
scrutiny needs to be given to such an answer, and the ramifications of
such actions need to be noted. Given blindly, as you have, this
'advice' can do more harm than good.
David FItzjarrell
ExpertDba
You got to READ what the Filer has said more carefully:
QUOTE :"This newly imported data should append with exisiting data in
oracle 10g. In any cause the previous data will not get delete for
replace with newly loaded dmp file. "
Ofcourse the filer knows of the ramifications of what he is doing.
Have you read the words..."In any cause the previous data will not get
delete for replace with newly loaded dmp file".
If some one asks for a question such as "Can a database be shutdown
forcefully without having to wait for users,rollback etc..the answer is
YES....and not about rambling with questions such as "why would you do
that?,who told you to do that?.
I do have a very good idea about what role "constraints" etc play in
this kinda scenario.Else i myself wouldn't have been an Oracle Corp
Employee working on technologies that you wouldn't have even seen in
the market.
Can you see the difference between Tom Kyte and you here.
He too was posd with a similar question as the one posed in this
group.But his response was not like yours.I bet he has more knowledge
of Oracle than you or i do,else he wudn't be one of my VPs here....
I haven't given an advice here.I gave a FACT!
Channesh
fitzj...@cox.net
ExpertDba wrote:
> Hi David,
> You got to READ what the Filer has said more carefully:
>
I have. What leads you to believe I haven't?
> QUOTE :"This newly imported data should append with exisiting data in
> oracle 10g. In any cause the previous data will not get delete for
> replace with newly loaded dmp file. "
>
I understand this.
> Ofcourse the filer knows of the ramifications of what he is doing.
That is not necessarily true.
> Have you read the words..."In any cause the previous data will not get
> delete for replace with newly loaded dmp file".
Several times, now.
>
> If some one asks for a question such as "Can a database be shutdown
> forcefully without having to wait for users,rollback etc..the answer is
> YES....and not about rambling with questions such as "why would you do
> that?,who told you to do that?.
>
> I do have a very good idea about what role "constraints" etc play in
> this kinda scenario.Else i myself wouldn't have been an Oracle Corp
> Employee working on technologies that you wouldn't have even seen in
> the market.
>
> Can you see the difference between Tom Kyte and you here.
> He too was posd with a similar question as the one posed in this
> group.But his response was not like yours.I bet he has more knowledge
> of Oracle than you or i do,else he wudn't be one of my VPs here....
>
Why do you become defensive when unaddressed aspects of a situation are
brought to your attention? The exp/imp scenario you describe *can*
create more problems than it solves in certain cases, and it is
incumbent upon those providing 'answers' to address such issues in
their response. I believe Thomas Kyte would agree.
> I haven't given an advice here.I gave a FACT!
>
You also instructed the original poster to use that fact to 'solve' his
problem; such is considered advice. And, that advice was given absent
the caveats I mentioned.
> Channesh
David Fitzjarrell