** SQL for DDL question

From: A Joshi <ajoshi977@xxxxxxxxx>
To: "Michael W. Fontana" <m.fontana@xxxxxxxx>, oracle-l@xxxxxxxxxxxxx
Date: Wed, 19 Dec 2007 18:20:05 -0800 (PST)
Yes. MV on MV works. Just wanted to know if any issue or risks with it. 
   
  Another question : 
   
  For getting sql being executed by a session I generally use 
v$sqlarea.sql_text by joining it with v$session. 
However, I see that sometimes if a session is doing DDL like create I cannot 
get the SQL  being executed. Is there some other way of getting ddl sql being 
executed. It is needed specially in case of heavy load and trying to find out 
what a long running session is doing. 
   
  Thanks for help.

"Michael W. Fontana" <m.fontana@xxxxxxxx> wrote:
      I don't think you can create an MV based upon another MV.
   
  Michael Fontana
Sr. Database Administrator
NTT America
P:214.853.7405
M:214.912.3705 
   

    
---------------------------------
  From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of A Joshi
Sent: Thursday, October 25, 2007 8:54 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: ** MV questions 


  
  Hi,
    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. 

NUMBER 
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. 
   
  Thanks
   
   
  __________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
  
****************************************************************  This email 
message is intended for the use of the person to  whom it has been sent,and may 
contain information that is  confidential or legally protected. If you are not 
the intended  recipient or have received this message in error, you are not  
authorized to copy, distribute, or otherwise use this message  or its 
attachments.Please notify the sender immediately by  return e-mail and 
permanently delete this message and any  attachments. NTT America makes no 
warranty that this email is  error or virus free.  Thank you .  
****************************************************************    
  


       
---------------------------------
Looking for last minute shopping deals?  Find them fast with Yahoo! Search.
** SQL for DDL question

Other related posts:
