Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> ** MV questions

** MV questions

From: A Joshi <>
Date: Thu, 25 Oct 2007 06:54:00 -0700 (PDT)
Message-ID: <>


  1. I am gettting error ORA-12014 when creating a MV on local table with complete refresh. Not on MV log create. Sounds strange. Primary key cannot be created on this table. I do not need fast refresh anyway. Why does it need PK for complete refresh. I tried creating a MV log with rowid option on the table and that did not help either. Any workaround.
  2. Is there any issue with creating a MV on a MV. For some reason access is not available to the original table.
  3. For getting sql being executed by a session I generally use v$sqlarea.sql_text by joining it with address. However, I see that sometimes if a session is doing DDL like create I cannot get it. Is there some other way of getting ddl. Especially in case of heavy load and trying to find out what a long running session is doing.
  4. What is the difference between Number and decimal datatype : Manual says : DECIMAL A decimal number with up to 15 significant digits.

A decimal number with up to 38 significant digits. When I specify DECIMAL in the create table and then look at describe table I see it creates it as NUMBER. On 9i. So is it synonymous or is there something DECIMAL can do that NUMBER cannot or vice versa.    

  5) I have schema schemaA which has a table tableA, tableB. Another schema schemaB has a view viewA selecting from these tables. There is user userC who needs to select from view viewA. I gave grant on schemaA tables to schemaB with "grant option". Then select on viewA was granted to userC. Did not work. : error :   ORA-01031: insufficient privileges
  Then after long struggle I gave select any table select any dictionary to userD. It works with just those two privs. Howver if I grant select on viewA to userC it does not work. STrange.    

  Please help on any of the above items. Sorry about too many Q. Thanks for help.    


Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
Received on Thu Oct 25 2007 - 08:54:00 CDT

Original text of this message