Resolving “ALTER SESSION command already exists” Issue With 9i OLAP Patch 3140662

A recent poster on the OTN OLAP Forum had a problem applying the Oracle OLAP Patch 3140662. This patch is sometimes needed when BI Beans can't connect to the OLAP Catalog, and is applied after the 9.2.0.4.1 patchset required for the Analytic Workspace Manager. The patch consists of a single SQL script, "alterses.sql" that needs to replace an existing script in $ORACLE_HOME/olap/admin, and run as SYS.

When you run the script, however, you get this error:

SQL> @E:\oradb_home\olap\admin\alterses.sql;

call OLAP_API_SESSION_INIT.add_alter_session
('CONNECT', 'SET db_file_multiblock_read_count=2')
*
ERROR at line 1:
ORA-20002: ALTER SESSION command already exists for
CONNECT
ORA-06512: at "SYS.OLAP_API_SESSION_INIT", line 53

call OLAP_API_SESSION_INIT.add_alter_session
('CONNECT', 'SET star_transformation_enabled=true')
*
ERROR at line 1:
ORA-20002: ALTER SESSION command already exists for
CONNECT
ORA-06512: at "SYS.OLAP_API_SESSION_INIT", line 53

I came up with the same error as well, and needed the patch to go on as BI Beans wouldn't connect through to the OLAP Catalog. As a quick way of sorting it out I raised a TAR with Oracle support, and got the following resolution to the problem;

"What you need to do: Before you run the new alterses.sql, perform the following steps:

1. Log into the RDBMS as sys user
2. Enter the following command : delete from sys.OLAP$ALTER_SESSION;

This will clear the table that stores the session information.
Now you can run the alterses.sql script without errors."

This worked fine and the alterses.sql script now runs without any problems.