Home » RDBMS Server » Server Utilities » Importing Oracle 10 dump exported via Oracle 9.0 export utility (Oracle 10g)
Importing Oracle 10 dump exported via Oracle 9.0 export utility [message #349159] Fri, 19 September 2008 02:44 Go to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I have a database in Oracle 10g and the client want to import it in Oracle 9i(Version 9.2) database for the schema "wyeth". I exported the Oracle 10g database through the Oracle 9i utility(version9.0) using the below command.
exp userid=wyeth/wyeth@oce.systimembp.com owner=wyeth direct=y constraints=y indexes=y statistics=none file=c:\exp10g.dmp


I tried to test it before sending the export file. So I imported it in Oracle 10g creating a new user "wyethtest". I also created a tablespace named "wyethtest".

I imported using the imp command and gave the required parameters as it asked on the commandline.

It imported all the tables but gave an error/warning about a view... FORCE VIEW....

I did it yesterday so I do not have the exact message with me.
NOw I see that there were two views. But I am able to access only one of them and while I try to access the other view it gives the message :

ORA-04063: view "WYETHTEST.RAW_MATERIAL_LOT_MATCHING_RPT" has errors


I am not sure whether to send it to client.
Kindly advice on this as what might have given this error and how to resolve such a problem.

Regards,
Soni
Re: Importing Oracle 10 dump exported via Oracle 9.0 export utility [message #349171 is a reply to message #349159] Fri, 19 September 2008 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This error has no relation with import itself.
Try to see why this view has errors maybe it points to objects that are in your export file.

Regards
Michel

Re: Importing Oracle 10 dump exported via Oracle 9.0 export utility [message #349176 is a reply to message #349171] Fri, 19 September 2008 03:32 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi Michel,
It seems that the error might be due to a database link involved in this view.

CREATE OR REPLACE VIEW RAW_MATERIAL_LOT_MATCHING_RPT
(WN_JDE_LOT, WN_SUPPLIER, WN_ITEM_NO, NAME, REPORTED_VALUE)
AS 
SELECT
    SAMPLE.WN_JDE_LOT,
    SAMPLE.WN_SUPPLIER,
    SAMPLE.WN_ITEM_NO,
    RESULT.NAME, 
    RESULT.REPORTED_VALUE 
FROM 
    RESULT@DB_LIMS, TEST@DB_LIMS, SAMPLE@DB_LIMS
WHERE
    RESULT.TEST_NUMBER = TEST.TEST_NUMBER
    AND TEST.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER
    AND SAMPLE.STAGE = 'CHEMISTRY' 
    AND SAMPLE.WN_SAMPLE_TYPE  = 'RM' 
    AND SAMPLE.STATUS = 'A' 
    AND SAMPLE.WN_ITEM_NO = 'X0194'
    AND TEST.STATUS = 'A'
        AND RESULT.STATUS = 'A'
    AND RESULT.NAME IN('Avg of Calcium','Avg of Magnesium','Avg of Phosphorus','Avg of Potassium','Avg of Protein','Avg of Sodium','RM Chloride result')
/


Is this may be the reason? Do we need to create database link before importing the dump file?

Doesn't the dblink too gets exported and imported or do we have to do it manually.

Please advice.
Soni
Re: Importing Oracle 10 dump exported via Oracle 9.0 export utility [message #349182 is a reply to message #349176] Fri, 19 September 2008 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Is this may be the reason?

Yes.

Quote:
Do we need to create database link before importing the dump file?

No.
The views are created with the FORCE option but you will get the error in the log.

Quote:
Doesn't the dblink too gets exported and imported or do we have to do it manually.

It depends on your export option and if they public or private db links.

Regards
Michel
Re: Importing Oracle 10 dump exported via Oracle 9.0 export utility [message #349477 is a reply to message #349182] Mon, 22 September 2008 00:48 Go to previous message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I was able to import the 10g schema exporting via 9i export tool into 9i.

Regards,
Soni

[Updated on: Mon, 22 September 2008 07:21]

Report message to a moderator

Previous Topic: IMPORT from file
Next Topic: Export Issue - Takes more than 2 days
Goto Forum:
  


Current Time: Tue Dec 24 10:04:00 CST 2024