Home » RDBMS Server » Server Utilities » Views invalidated on import why?
Views invalidated on import why? [message #196076] Wed, 04 October 2006 01:21 Go to next message
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 #196322 is a reply to message #196076] Thu, 05 October 2006 00:22 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

If you specify IGNORE=y, Import overlooks object creation errors when it attempts to create database objects, and continues without reporting the errors.

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm
Re: Views invalidated on import why? [message #196376 is a reply to message #196322] Thu, 05 October 2006 03:07 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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.
Re: Views invalidated on import why? [message #196415 is a reply to message #196376] Thu, 05 October 2006 04:30 Go to previous message
asherisfine
Messages: 63
Registered: June 2006
Location: India
Member
Thanks for the reply...I have clear understanding now...But what about system privileges are they imported/exported or do we need to to be given seperately as i had to do?
Previous Topic: partitioned counter values
Next Topic: SQL*loader adds .dat to the default filename
Goto Forum:
  


Current Time: Sat Dec 03 05:47:26 CST 2016

Total time taken to generate the page: 0.17080 seconds