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.  

ORA-15046: ASM file name...is not in single-file creation form

We can use ASM provided command “cp” to copy files from file system to ASM diskgroup and vice versa; and also from one ASM diskgroup to the other. If you receive ORA-15046 while copying a file to an ASM diskgroup from file system, or from another diskgroup, mostly it is because you are providing a file name for the destination file that is not allowed in the ASM. From file name here I mean the file name in the format that is used in ASM i.e. file_name.235.84753829. Same error would also come if an invalid directory location is provided. Following is an example of this error message

DBA_TABLESPACE_USAGE_METRICS and CDB_TABLES_ACES_USAGE_METRICS

DBA_TABLESPACE_USAGE_METRICS and CDB_TABLESPACE_USAGE_METRICS (starting 12c) provide us summary of space usage of each tablespace including TEMP and UNDO tablespaces. Only different between these 2 views is same as between all other CDB* and DBA* views whereby CDB* views contain information from all PDBs that is viewable from root container and have an extra column CON_ID to correspond each row to a particular PDB. Using this view makes it very easy to analyze space usage. In this article I would explain how to get information from these dictionary views to check the utilization of space within the tablespace(s). You may also want to read my articles about Tablespace space usage history and forecast for 12c and above, and for 11g and 10g.

Adaptive Cursor Sharing

This feature was introduced in 11g release 1 to cater a performance issue caused by bind variable peeking. Adaptive cursor sharing means that if an SQL is submitted for execution that has bind variables, and it already has an execution plan in shared pool, still optimizer can generate a new execution plan based on the values passed in the bind variable this time. This means that on every call of an SQL, optimizer may use already available plans in the shared pool, or it may generate a new one that is more efficient based on the bind variable values.

Bind Variable Peeking Prior to 11g

Oracle has a feature called “bind variable peeking” whereby an execution plan for an SQL is generated based on the bind variable values used in the SQL. This technique sometimes created suboptimal execution plan because the plan might be perfect for the SQL containing bind variables based on which this plan was generated, but if same SQL is executed later with different values in bind variables, this current plan may not be optimal for those values, and might cause performance degradation for the SQL. I have discusses a real scenario in this article.

Restricting Access/Connections to the Database

In order to restrict the connections to the database we can add some parameters in the sqlnet.ora file under ORACLE_HOME form where listener is running. This would make sure that sessions to the database are coming only from specific list of hosts, and any session from all other hosts is not allowed to connect to the database. Following are the parameters we can use for such restriction.

Archivelog Deletion Policy for RMAN

RMAN archive deletion policy is used to set when we want archived logs to be deleted when DELETE ARCHIVELOG command is executed from the RMAN prompt, or if we are using Fast Recovery Area for archived logs, and are not deleting archived logs manually and relying on automatic feature of Oracle for deletion of archived logs (archive deletion would happen automatically if space crunch is observed by Oracle under fast recovery area).

ORA-12546: TNS:permission denied

I recently faced this error message while trying to connect with the database (running on my local Windows host) using a TNS service. Although connecting without TNS service was working fine, however, connection was failing with ORA-12546 when I tried to connect using TNS service. Following is what I faced.