CODEWITHSUNDEEP
sqloracle-databasejoin
user3903230
user3903230

Reputation: 1

SQL Error: ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges"

When I run the cross-schema update query I get the error message:

ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges".

Details are as follows:

I am trying to populate table (my_prod_price_tracking) in prod schema from the view (my_vw_product_price_info) which is there in CAT schema.

     my_prod_price_tracking (table)Prod Schema.
     my_vw_product_price_info (view)- CAT schema.

I have given grant select on my_vw_product_price_info to prod. So the inner Select query works fine. But when I run the update script I get the "ORA-01031: insufficient privileges" error.

Please let me know where am I going wrong.

Below is the query:

update ( 
       select a.price_range_low       current_lowest_price,
              a.price_range_low       lowest_price,
              a.last_price_range_low  last_lowest_price,
              a.price_range_low_update_time, 
       b.lowest_price new_lowest_price 
       from my_prod_price_tracking a,
            CATA.my_vw_product_price_info b
       where a.product_id = b.product_id and
             a.price_list_id = b.price_list_id and
             a.price_range_low <> b.lowest_price 
       )up
      set up.lowest_price = up.new_lowest_price,
          up.last_lowest_price = up.current_lowest_price,
          up.lowest_price_update_dt = sysdate

Upvotes: 0

Views: 3839

Answers (1)

Wayne Schroder
Wayne Schroder

Reputation: 1

This may not be your issue, but worth noting: We just spent 48 hours chasing our tails trying to solve this one. One of our modules using NHibernate/Oracle started raising the following exception: ORA-01031: insufficient privileges. Without going into all the gory details, we verified it was random, sporadic, and not a permissions problem. What finally worked was running the following Oracle command: 

alter system flush shared_pool

Upvotes: 0

Related Questions