Monday, January 04, 2010

ORA-01031: insufficient privileges

At my client site, Auto Invoice was slow, so I took trace of “Autoinvoice Import Program”. In trace file I found following sql statement

SELECT l.interface_line_id
FROM ra_interface_lines_gt l
WHERE (((((l.request_id =:b1
AND l.customer_trx_id IS NOT NULL )
AND NVL(l.interface_status,'~')<>'P')
AND l.line_type ='FREIGHT')
AND l.link_to_line_id IS NULL )
AND 1 <
(SELECT COUNT(DISTINCT d.code_combination_id)
FROM ra_interface_lines_gt l2
, ra_interface_distributions d
WHERE (((l.customer_trx_id=l2.customer_trx_id
AND l2.line_type ='FREIGHT')
AND l2.link_to_line_id IS NULL )
AND l2.interface_line_id =d.interface_line_id)

When I do explain plan either in sqldeveloper or TOAD, it would give ORA-01031 error. Explain plan for other queries was working fine. After spending some time, I found that

I do not have insert privilege on RA_INTERFACE_ERRORS and that is why explain plan gives error. If a remove just insert statement then I can get plan for the SELECT statement.

No comments:

Post a Comment