Reserved terms and keywords are two words in Oracle that have unique meanings within the software.

When coding in Oracle, the error ORA-00905 is likely to occur frequently. Fortunately, it’s much less complicated and time-consuming to fix than any of the other ORA errors you’ll encounter while working with Oracle. The action for this mistake, according to Oracle documentation, is to “correct the syntax.” The solution to ORA-00905 entails determining which keyword is missing and where it should be inserted. The ORA-00905 error code is used to denote a malformed sentence in which the Oracle parser detects a missing keyword.

(Namespaces are another term with a special significance in Oracle, but they have little to do with the topic of ORA-00905.  Reserved terms are those that can’t be redefined and thus can’t be used to describe database objects like columns or tables. Oracle has predefined these terms and they will all have the same meaning. Keywords are words that have a significant significance for Oracle but are not reserved terms and can therefore be redefined. Some keywords, on the other hand, could become reserved keywords in the future, so they should be used with caution when used as variable or function names. A list of keywords can be found here.

The Problem

When a required keyword is missing, the ORA-00905 error occurs. The following will be the error message

ORA-00905: a keyword is missing.

As indicated by the message, the code lacks a keyword where one is required for the question to run successfully

The Solution

Most DBAs, SQL developers, and performance experts are familiar with generating and viewing the execution plan of a SQL statement because it provides them with details on the statement’s performance characteristics. The steps required to execute a SQL statement are shown in detail in an execution plan. These steps are encapsulated in a set of database operators that consume and generate rows. The query optimizer uses a mixture of query transformations and physical optimization techniques to determine the order of the operators and how they are implemented.

The plan is tree-shaped, even though it is commonly displayed in a tabular format. Consider the following query base as an example.

When reading a plan tree, begin at the bottom and work your way up. Begin by looking at the access operators in the preceding example (or the leaves of the tree). In this case, absolute table scans are used to enforce the access operators. The join operator will consume the rows generated by these table scans. The join operator is a hash-join in this case (other alternatives include nested-loop or sort-merge join). Finally, the hash-based group-by operator (sort would be an alternative) consumes the rows generated by the join-operator.

The query optimizer considers several different execution plans when generating an execution plan for a SQL statement. 

You can examine the execution plan of a SQL statement using one of two methods:

1. The EXPLAIN PLAN command shows a SQL statement’s execution plan without actually executing the statement.
2. V$SQL PLAN – A dictionary view that displays the execution plan for a SQL statement in the cursor cache that has been compiled into a cursor.

In some circumstances, the plan displayed by the EXPLAIN PLAN command can vary from the plan displayed by V$SQL PLAN. When the SQL statement includes bind variables, for example, the plan generated by EXPLAIN PLAN ignores the bind variable values, while the plan generated by V$SQL PLAN considers the bind variable values.

The following are the reasons for DBMS XPLAN.DISPLAY:

• Name of the plantable (default: PLAN TABLE)
• Statement id is an identifier for a statement (default NULL)
• (‘TYPICAL’ is the default)
• ORACLE HOME/rdbms/admin/dbmsxpln.sql contains more details.

Formatting

The format argument is highly customizable and allows you to see as little (high-level) or as many (low-level) details as you need/want in the planned output. The high-level options are:

The Fundamentals

The process, options, and the name of the object are all included in the plan (table, index, MV, etc)

It contains the data displayed in BASIC as well as internal optimizer data such as cost, size, cardinality, and so on. This information is shown for each operation in the plan and reflects the optimizer’s estimation of the cost of the operation, the number of rows generated, and so on. It also demonstrates how the procedure tests the predicates. ACCESS and FILTER are the two kinds of predicates. Since they refer to the search columns, the ACCESS predicates for an index are used to fetch the appropriate blocks. After the blocks have been fetched, the FILTER predicates are evaluated.

Section of Notes

In addition to the plan, the kit contains notes in the NOTE section, such as whether dynamic sampling was used during query optimization or whether the query was transformed using star transformation.

If the table SALES, for example, does not have figures, the optimizer will use dynamic sampling and the plan display will show the following (see the s’+note’ information in the query)

Bind Peeking

When generating an execution plan, the query optimizer considers the values of bind variables. It conducts what is known as bind peeking. See the previous posts on bind peeking and its effect on SQL statement plans and results

As previously mentioned, the plan displayed in V$SQL PLAN takes into account the values of bind variables, while the plan displayed in EXPLAIN PLAN does not. The DBMS XPLAN package allows you to see the bind variable values that were used to create a specific cursor/plan. When using the display cursor, simply add ‘+peeked binds’ to the format statement.

Explanation of the EXPLAIN PLAN

The EXPLAIN PLAN statement shows the Oracle optimizer’s choices for Choose, UPDATE, INSERT, and DELETE statements. The execution plan for a statement is the order in which Oracle executes the statement.

• The execution plan’s heart is the row source tree. It displays the following data
• An arrangement of the tables listed in the statement.
• Each table listed in the statement has an access process.
• Join methods for tables that are affected by the statement’s join operations.

• Filtering, sorting, and aggregation is examples of data operations.
• The plantable includes information about the following in addition to the row source tree:
• The cost and cardinality of each operation are examples of optimization.
• Partitioning, such as the number of accessed partitions, is an example of partitioning.
• The distribution method of join inputs, for example, is a good example of parallel execution.
• You may use the EXPLAIN PLAN results to see whether the optimizer chooses a specific execution plan, such as nested loops enter. It also enables you to comprehend optimizer decisions, such as why the optimizer chose a nested loop to join over a hash join, and it allows you to comprehend query results.

Changes in Execution Plans

Execution plans may and do change with the cost-based optimizer as the underlying costs change. The EXPLAIN PLAN performance illustrates how Oracle executes the SQL statement after it has been clarified. Because of variations in the execution environment and describe plan environment, this will vary from the plan for a SQL statement during actual execution.

The following factors will cause execution plans to differ:

• Different Schemas
• Different Costs

Various Schemas

• The implementation and explanation plans are carried out on separate databases.
• The user who describes the argument is not the same as the user who implements it. Different execution plans can result from two users pointing to different objects in the same database.

 

• Between the two operations, there are schema changes (usually index changes).

Various Rates

• If the costs are different, the optimizer may select different execution plans even if the schemas are the same. The following are some of the factors that influence costs
• Volume and statistics of data
• Bind variable types together
• Set global or session-level initialization parameters
• EXPLAIN PLAN is currently in use.
• Using the following to clarify a SQL statement
• EXPLAIN THE Strategy

Consider the following scenario

• EXPLAIN THE Strategy
• FROM staff, Choose the last name;

This clarifies the PLAN TABLE table’s plan. The execution plan can then be chosen from the PLAN TABLE. If you don’t have any other plans in PLAN TABLE or just want to look at the last statement, this is useful.

Conclusion

The error arises in ORACLE and can be solved if you check any of your SQL statements i.e. INSERT, UPDATE, and DELETE. Basically, the error clearly mentions and indicates some malformed statement where ORACLE parser gives some missing keyword issues.

This doesn’t allow query to run and creates issues. Last but not the least, you should check all of the statements you have prepared and also have a look at syntax where you have mentioned everything. You can also check manual for S QL where all the details have been clearly mentioned and addresses along with the issues, which may arise if rules are not followed properly.