Error: "ORA-01039: Insufficient privileges on underlying objects of the view" (4322617)

Error received when running an Explain Plan on certain queries in Spotlight on Oracle.

"Locations: Top Sessions, Top SQL or Explain Plan Tool.

ORA-01039:Insufficient privileges on underlying objects of the view"

In Spotlight on Oracle, if an ORA-01039 error is generated while Explaining in either Top Sessions, Top SQL or Explain Plan Tool, this can be for a couple of reasons:

a. 'Certain' SYS-owned views that begin with x$, v$, or DBA_% cannot be explained even if the user is logged in as a DBA or SYS account. This is not allowed in Oracle. You can run an Explain Plan on these in SQL*Plus to verify.

b. If the user a non-dba, they may have the system privileges "Select Any Table" and/or "Select_Catalog_Role" granted implicitly through a role or not at all. Spotlight requires these system privileges to be granted explicitly (outside of a role).

c. The user may also have not table privileges on the referencing table or view. This may also apply if their going through a dblink and do not have privileges on the underlying table or view. The user will need Select access on all tables and underlying views.

d. If the sql you are trying to explain is an INSERT, UPDATE or DELETE statement, then the 'Insert any Table' , 'Update any Table', Delete any Table' privilege would need to be granted to the Spotlight user respectively.