ORA-00972: identifier is too long

In Oracle 12.1 and below, the maximum length for the name of an object (Table, View, Procedure, Package, Function, Materialized View etc.) is 30 bytes (or 30 characters, if each character is of 1 byte as per character set used). Starting 12.2, name length has been increased to 128 bytes/characters. Likewise, the name of a column of a table or view should also not exceed the allowed length. In the following I am providing a few examples where we will see ORA-00972 error returned in 12.1 database

SQL Plan Management and SQL Baseline

Before you read this article, I would suggest you to read my articles about bind variable peeking and adaptive cursor sharing.

Starting 11g release, adaptive cursor sharing makes optimizer to generate and select multiple execution plans based on the bind variable values passed during the execution. Oracle also introduced SQL Plan Management feature starting 11g whereby the history of plans of an SQL is maintained in SQL Baseline and out of which one or more plans can become “accepted” and can be used for that SQL. This is our choice to make plan(s) “accepted” if think that plan is optimal. We may ignore sub-optimal plans. In this will explain SQL Plan Management using an example.

gc buffer busy acquire and gc buffer busy release

These two wait events are similar to ”buffer busy wait” that you might have observed which is recorded when a session tries to access a data block/buffer which is already being accessed by another session that is connected to the same instance and buffer is also in the current instance’s buffer cache. In RAC environment, if a session tries to read a buffer from a remote instance’s buffer cache but the buffer is already being read by a different session, a “gc buffer busy acquire” wait event is recorded. If a session tries to read a buffer from the buffer cache of local instance, but buffer is already being read into remote instance’s buffer cache from local cache by a session connected to that remote instance, a “gc buffer busy release” wait event is recorded.