Oracle OLAP option remove issues(ORA-34350, ORA-20003, ORA-33295)

Hi.

Just quick post for those who are failing to follow one of the next notes to remove Oracle OLAP option from database:

In one of the steps you are required to execute:

@?/olap/admin/catnoaps.sql

which may fail with next error:

ORA-20003: User created AW objects exist, unable to deinstall OLAP

so the good candidate for fixing this error is next MOS document:

ORA-20003 While Manually Removing OLAP Option (Doc ID 431470.1)

which suggest to delete all AWs(Analytic Workspaces) with aw_number>1000;

SELECT owner,aw_name,aw_number FROM dba_aws WHERE aw_number>1000;

execute dbms_aw.execute(‘aw delete sys.<aw_name>’);

then try to execute catnoaps.sql once again, but you may still face ORA-20003 and Oracle suggest:

If there are NO AWs with aw_number>1000, bouncing the database clears this error

so, just restart you instance – simple solution from the Windows World…

You may try to manually remove SYS AWs(aw_number<=1000), but face next error:

execute dbms_aw.execute(‘aw delete sys.EXPRESS’);

So, the only solution is to reboot instance ?

no, actually, you are not required to restart your instance – just kill all sessions mentioned in GV$AW_OLAP and execute catnoaps.sql again.

References:

  • How To Remove The OLAP Option In 10g And 11g (Doc ID 332351.1)
  • How to remove the Oracle OLAP Option from a 12c Database (Doc ID 1940098.1)
  • ORA-33263: Could Not Create Analytic Workspace SYS.EXPRESS (Doc ID 1082283.1)
  • ORA-20003 While Manually Removing OLAP Option (Doc ID 431470.1)

PS:

At some point of time I tried to remove old(not removed by scripts) AW objects according to NOTE:1082283.1 which suggest to execute next failing command, which actually allowed to find solution without bouncing the database:

drop table AW$EXPRESS;

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-33295: (DBERR20) An attached analytic workspace is blocking this command.
ORA-06512: at “SYS.AW_DROP_PROC”, line 73
ORA-06512: at line 2

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s