Oracle PatchSet 11.2.0.4 upgrade issues ORA- PLS-

Below is a list of issues (ORA- and PLS- errors) I encountered after upgrading Oracle 11gR2 Database Enterprise Servers to the latest Patchset 11.2.0.4.
The upgrade mostly done:
– from 11.2.0.3.6-8
– to 11.2.0.4.1 (including Patchset Update PSU 1)
– on platforms: HP-UX Itanium, Linux

ORA-01979: missing or invalid password for role

Problem: Roles with passwords are failing after Oracle database Patchset upgrade

Solution: Recreate a role with a new password or without it.
Example:
alter role REPORTING_RW not identified;

ORA-01791: not a SELECTed expression

Problem: SQL having different number of columns in DISTINCT and ORDER BY clauses failing after Oracle database upgrade but it was working on previous release before last Patchset 11.2.0.4 since developers happily used an Oracle Bug that was fixed in 11.2.0.4. So the correct behavior is on 11.2.0.4 and not on older versions.
SQL> select distinct sal, empno from scott.emp order by deptno;
select distinct sal, empno from scott.emp order by deptno
*
ERROR at line 1:
ORA-01791: not a SELECTed expression

Solution: include all ORDER BY columns into Read more »

Patching Oracle with Patch Set best practices

Patch Set is an Oracle major patch bundle that provides bug fixes only. It is a mechanism for delivering fully tested and integrated product fixes that do not include new functionality. All of the fixes in the Patch Set have been tested and are certified to work with each other. Read more »

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email