Tuesday, 19 February 2008

Materialized view - permissions and ora00600 error

Tried to create a materialized view on a schema I did not own, which produced permission errors. Solution turned out to be to grant the owner GLOBAL QUERY REWRITE. The solution was found here http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm#i2162987

Other problem was that even thought a fairly complicated query materialized view created OK, (It consisted of two inline views) when the optimizer tried to use it via 'query rewrite' the query bombed out with ora-00600 [] [] [] etc.

Solution turned out to be to create two separate materialized views.
The optimizer then used the two materialized views with no problem and the query run time went from 45-60 seconds to instant! No rewriting of the query. Result.

Finally, if the mat view is created on a prebuilt table the user creating the view must have 'select ....with grant option' on the 'prebuilt' table.

No comments: