Oracle EPM Essbase 11.2.22 Setup Error Solved: ORA-00942 & DBMS_LOB Permission Fix

We recently installed EPM 11.2.22 on-premises with Essbase and encountered some strange issues. When we imported our applications from our older environment, the LCM Import AND the Essbase Web GUI Import operations errored out with “Error: Cannot create olap application. Essbase Error(1042006): Network error []: Failed to connect to [APP_NAME];” .. the application would show in Essbase Web and Shared Services, but had no databases, no objects, and would not start.

Further investigation into the application log (user_projects/domains/essbase_domain/servers/essbase_server1/logs/essbase/essbase/app/*) showed a JAVA error followed by an ORA-00942: table or view does not exist error. During the configuration step for Essbase, a number of logs are dumped to $TEMP (/tmp by default in Linux) in an essbase_config_DATETIME directory. The configuration step didn’t show anything wrong when we ran it, but the logs tell a different tale!

The rcu.log file shows our script ran but had issues with the create_essbase.sql execution. Opening the essbase.log file in the same directory showed four CREATE VIEW statements errored out with ORA-00942: table or view does not exist errors. Each of these had DBMS_LOB references. A search of Oracle’s KBs found “Essbase 21c: Missing Table (ORA-00942) When Creating New Essbase Database For Planning Application (Doc ID 3030878.1)” which described the same problem and suggested the DBA should add permission to DBMS_LOB then re-run the CREATE VIEW statements.

In an Oracle database, DBMS_LOB is by default allowed to be executed by anyone using the “public” privilege. Oracle’s scripts assume the DBAs who created the database instance will not change that default. When the script is denied the ability to run the statements under CREATE VIEW, it should also treat that failure as a stopper or at least flag that as a warning for reporting to the GUI configurator. It does not.

Setting the privilege back to “public” would allow a successful run of the script package from the start. Should this not be allowed by the controlling entity of the DB server, it may be sufficient to retroactively grant that privilege to the ESSBASE schema user that script package creates, then manually run the CREATE VIEW statements that had previously failed. Once that is completed, it should be possible to create Essbase applications successfully in your new environment.

When Essbase errors like ORA-00942 derail your upgrade, it’s easy to feel stuck and lose valuable time. But you don’t have to fight through these challenges alone. At iArch Solutions, we’ve seen these issues before, we know the path forward, and we can guide your team to a stable, secure environment that actually works the way it should. If you’re ready to stop troubleshooting in circles and start moving forward with confidence, let’s talk.