| Views invalidated on import why? [message #196076] |
Wed, 04 October 2006 01:21  |
asherisfine
Messages: 63
Registered: June 2006
Location: India
|
Member |
|
|
I took an import(of only table structure ROWS=n) from a user DBO into a user TRIAL.But some of my view in TRIAL were invalid.
I figured out that system privileges assigned to DBO were not there for TRIAL.
Question 1) Doesn't all privilege including system and object privileges get Exported/imported when we export/import?
so now i gave system privileges present in DBO to TRIAl and compiled all views and all got validated!!
now i imported data(with IGNORE=y) into some tables in TRIAL from user DBO(these tables have data in DBO).
Question 2)After import i found that some views have againg got invalid.So that i had to recompile them again, they were compiled with no issues.
But my question is why did those views get invalidated, i had assigned all privileges and they were valid before importing?
question 3) what does IGNORE=y mean.Does it mean supress errors due to existing [B]tables[/B] or does it mean suppress error due to existing objects
|
|
|
|
|
|
| Re: Views invalidated on import why? [message #196376 is a reply to message #196322] |
Thu, 05 October 2006 03:07  |
 |
Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
As you probably have noticed, IMPORT utility creates views using the FORCE keyword. FORCE will create a view regardless of whether underlying tables or views or synonyms (public or private) already exist or not. However, views created in such a way may be INVALID. Usually, it is all about object dependencies.
Objects are imported as they are read from the export file, and it contains objects in the following order:
- type definitions
- table definitions
- table data
- table indexes
- integrity constraints, views, procedures, triggers
- bitmap, function-based and domain indexes
It, unfortunately, doesn't mention synonyms - I have noticed (in my database) that they often tend to invalidate views during import, so I can only *guess* that they are created after views. Can't find a document which will (dis)approve it.
Even though Import creates an invalid view, there's something good about it: you DO NOT HAVE TO manually recompile invalid views - when such, an invalid view, is to be used, Oracle will attempt to recompile the view. If all (previously missing) references are found, the view will become valid. See a nice example in the Managing Object Name Resolution section of this document.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
