Proxy user authentication is a powerful feature in Oracle that lets one database user (proxy user) connect to the database "on behalf of" another user (client user).
Parameters
ORA-01450: Maximum Key Length Exceeded – Possible Causes and Fix
ORA-01450 might crop up when a table index is being created in the database.
ORA-01450: maximum key length (6398) exceeded
Oracle documentation has this to say about the error:
Cause: The combined length of all the columns specified in a CREATE INDEX statement exceeded the maximum index length. The maximum index length varies by operating system.
The total index length is computed as the sum of the width of all indexed columns plus the number of indexed columns.
Date fields have a length of 7, character fields have their defined length, and numeric fields have a length of 22. Numeric length = (precision/2) + 1. If negative, add +1.
Action: Select columns to be indexed so the total index length does not exceed the maximum index length for the operating system.
The action suggests choosing index columns differently so as to remain within the index length limit.
Things are not always so simple though. When faced with this error while installing Oracle’s standard products such as FMW components or OBIEE, it is not in one’s hands to follow this advice and fiddle with the index columns.
How does one fix this error, then? This post suggests possible root causes and solution for ORA-01450 when changing the index itself is not a viable option.
MULTISET Operations: Combining Nested Tables Made Easy
Set operators (UNION, INTERSECT, MINUS) have long been available in basic SQL to process data in tables, but for data in PL/SQL nested tables, we’d earlier have to go through the ritual of traversing through the collections in a loop, doing a row-by-row comparison.
Oracle 10G onwards, MULTISET features have made possible single-step set operations on nested tables.
Here is a demo with scripts for performing MULTISET operations on nested tables of strings.
How to Find Out Your Oracle Database Name
Oracle gives you a number of ways to know the name of the database you are connected to, from inside a SQL*Plus session. Here are three ways to find out your Oracle database name.
Through V$DATABASE
SQL> select name from V$database; NAME --------- XE
The above will work only if your login has access to V$DATABASE. This is generally accessible to DBA logins only. For non-DBA logins, you may need to grant SELECT on V$ views.
In case access to V$DATABASE cannot be granted to you, use one of the two publicly accessible methods below.
How to Set ORACLE_HOME Path
In Oracle installation guides and scripts, you will often come across the term ORACLE_HOME. In fact, we used it on this site a couple of posts back, in the script to install SCOTT schema in Oracle.
This post will cover:
- the meaning and purpose of ORACLE_HOME
- how to check if ORACLE_HOME is set
- how to set ORACLE_HOME
- how to unset, change or delete ORACLE_HOME