Forum OpenACS Q&A: oracle exp and imp question

Posted by Tilmann Singer on 03/10/03 03:08 PM

I am trying to import the oracle dump file of a oacs/dotlrn installation. It gets imported mostly fine, and most parts of the installation even work, but there are some messages like these in the import log:

IMP-00017: following statement failed with ORACLE error 1730:
 "CREATE FORCE VIEW "MIG_TARGET"."SURVEY_RESPONSES_LATEST"                   "
 "   ("RESPONSE_ID","INITIAL_RESPONSE_ID","SURVEY_ID","TITLE","NOTIFY_ON_COMM"
 "ENT_P","CREATION_DATE","CREATION_USER","INITIAL_USER_ID") AS "
 "select sr.*, o.creation_date,"
 "       o.creation_user,"
 "       survey_response.initial_user_id(sr.response_id) as initial_user_id"
 "  from survey_responses sr,"
 "  acs_objects o,"
 "  (select max(response_id) as response_id"
 "          from survey_responses"
 "         group by survey_response.initial_response_id(response_id)) latest"
 "  where sr.response_id = o.object_id"
 "  and sr.response_id= latest.response_id"
IMP-00003: ORACLE error 1730 encountered
ORA-01730: invalid number of column names specified
ORA-00904: invalid column name
IMP-00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "MIG_TARGET"."NEWS_ITEM_FULL_ACTIVE"                     "
 " ("ITEM_ID","PACKAGE_ID","REVISION_ID","PUBLISH_TITLE","PUBLISH_BODY","HTML"
...

This happens with some views. I can recreate them later manually without a problem, but why can't they be imported? The export is from oracle 8.1.7.0.0 on linux and the import happens on the same version although it is another machine.

I need to use fromuser and touser like that:

imp mig_target/**** file=aiesec_4_dev_fresh_install.dmp fromuser=aiesec_4_dev touser=mig_target

Does that ring a bell for somebody?

2: Re: oracle exp and imp question (response to 1)

Posted by Dirk Gomez on 03/11/03 03:49 PM

Yeah it does. Sounds like a case of RTFM:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/views.htm#377

"If there are no syntax errors in a CREATE VIEW statement, Oracle can create the view even if the defining query of the view cannot be executed; the view is considered "created with errors." For example, when a view is created that refers to a nonexistent table or an invalid column of an existing table, or when the view owner does not have the required privileges, the view can be created anyway and entered into the data dictionary. However, the view is not yet usable.

By default, views with errors are not created as VALID. When you try to create such a view, Oracle returns a message indicating the view was created with errors. The status of a view created with errors is INVALID. If conditions later change so that the query of an invalid view can be executed, the view can be recompiled and be made valid (usable). For information changing conditions and their impact on views"

And then:

"Manually Recompiling Views
To recompile a view manually, you must have the ALTER ANY TABLE system privilege or the view must be contained in your schema. Use the ALTER VIEW statement with the COMPILE clause to recompile a view. The following statement recompiles the view EMP_DEPT contained in your schema:

ALTER VIEW emp_dept COMPILE;"