ORA-00942 error in PL/SQL

Transferred from: Larry. Yue's
Http://miracle.blog.51cto.com/255044/93450 Copyright:
Original Works are allowed to be reproduced. During reprinting, be sure to mark them as hyperlinks.
Original Article Source
, Author information and this statement. Otherwise, legal liability will be held. Http://miracle.blog.51cto.com/255044/93450

For PL/SQL objects such as the process, function, or package of writing database monitoring or writing
Performance view) and data dictionary, the following errors are often encountered:

Error: PL/SQL: ORA-00942:
Table or view does not exist

Check the cause of the error:

Cause:
Table or view entered does not exist, a synonym that is not allowed here
Was used, or a view was referenced where a table is required. Existing
User tables and views can be listed by querying the data dictionary.
Certain privileges may be required to access the table. If
Application returned this message, the table the application tried
Access does not exist in the database, or the application does not have
Access to it.

Action: check each of the following:

The
Spelling of the table or view name.

That a view is not specified
Where a table is required.

That an existing table or view name
Exists.

Contact the database administrator if the table needs
Be created or if user or application privileges are required to access
The table.

Also, if attempting to access a table or view in
Another schema, make certain the correct schema is referenced and that
Access to the object is granted.

However, it is often quite surprising that you can directly query SQL/plus and return results. But why is there an error in the compilation process, function, or package?

Otherwise, it only means that our current user does not have sufficient permissions.
Then, you can directly authorize a user to have the following query permissions:

SQL>
Grant select on dba_tablespaces to XXX;

Grant succeeded.

SQL>
Grant select on V $ session to XXX;
Grant select on V $ session to ASG
*
Error
At Line 1:
ORA-02030: can only select from fixed tables/Views

After performing the preceding operations, you can see that the data dictionary can be directly authorized and used. However, dynamic
Performance view is not available, and an error with the ORA-02030 is reported.

Can only select from fixed
Tables/Views

Cause: an attempt is being made to perform
Operation other than a retrieval from a fixed table/view.

Action:
You may only select rows from fixed tables/views.

As you can see, I have a problem with the authorization of V $ session! Yes, that is to say, V $ is not a view at all (if you say it is not a tables at all, you don't need to read it later.
-:)).
Let's take a look at the instructions given by Oracle:

V $ views

The actual
Dynamic Performance views are identified by the prefixV_$
.
Public synonyms

For these views have the prefixV$
.
Database administrators and other users shoshould access onlyV$
Objects, notV_$
Objects.

Obviously, V $ is a synonym "! Therefore, an authorization error occurs.
So how should we authorize it? Yes, according to the rules given by Oracle, as shown in the figure below:

SQL>
Grant select on V _ $ session to XXX;

Grant succeeded.

We can see that the authorization is successful. In order to compile successfully, we need to make minor changes to the compiled program script. What? That is, change the V $ session to SYS. V _ $ session.
Yes.
Next, let's think about it. If the content we write involves a lot of data dictionary or dynamic
Performance view. Isn't it very troublesome for us to authorize one by one?
We can use Oracle pre-defined roles to complete one-time authorization:

SQL>
Grant select any dictionary to XXX;

Grant succeeded.

In this way, I can directly use data dictionary and dynamic performance
View. However, you must note that such authorization is detrimental to database security.
------------------------

Starting with the ORA-00942, we extend the problem to dynamic performance view and Oracle
Security. Therefore, it is really important to learn more about Oracle and read more official documents.

If there is no technical obstacle, you are willing to drill -:)

This article is from "be the miracle!
Blog, be sure to keep this source http://miracle.blog.51cto.com/255044/93450