How to uninstall/remove invalid OLAP components since OLAP Oracle binary not installed

SQL> select comp_id,comp_name,version from dba_registry where status='INVALID';

COMP_ID         COMP_NAME                 VERSION

--------------- ------------------------- --------------------

APS             OLAP Analytic Workspace   11.2.0.3.0

XOQ             Oracle OLAP API           11.2.0.3.0

AMD             OLAP Catalog              11.2.0.3.0

Short Steps:

oracle@solaris11:~$ sqlplus / as sysdba

----> Remove OLAP Catalog
SQL> @?/olap/admin/catnoamd.sql
----> Remove OLAP API
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoxoq.sql
----> Deinstall APS - OLAP AW component
SQL> @?/olap/admin/catnoaps.sql
SQL> @?/olap/admin/cwm2drop.sql
----> Recompile invalids
SQL> @?/rdbms/admin/utlrp.sql

Detail Steps:

SQL> ----> Remove OLAP Catalog

SQL> @?/olap/admin/catnoamd.sql

oracle@solaris11:~$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed May 29 22:53:27 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> ----> Remove OLAP Catalog

SQL> @?/olap/admin/catnoamd.sql

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Package dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

Synonym dropped.

User dropped.

Role dropped.

SQL> ----> Remove OLAP API

SQL> @?/olap/admin/olapidrp.plb

Package dropped.

Package dropped.

Package dropped.

Package dropped.

Package dropped.

Package dropped.

Package dropped.

Package dropped.

Package dropped.

Package dropped.

Package dropped.

SQL> @?/olap/admin/catnoxoq.sql

Package dropped.

Package dropped.

Type dropped.

Type dropped.

Sequence dropped.

Type dropped.

Synonym dropped.

Procedure dropped.

Synonym dropped.

Function dropped.

Library dropped.

Type dropped.

Type dropped.

Type dropped.

Type dropped.

Package dropped.

Package dropped.

Sequence dropped.

Sequence dropped.

Sequence dropped.

Sequence dropped.

Sequence dropped.

Sequence dropped.

Sequence dropped.

Sequence dropped.

Sequence dropped.

Sequence dropped.

Library dropped.

DROP PUBLIC SYNONYM GV_Olapi_Session_History FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM V_Olapi_Session_History FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM GV_Olapi_Iface_Object_History FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM V_Olapi_Iface_Object_History FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM GV_Olapi_Iface_Op_History FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM V_Olapi_Iface_Op_History FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM GV_Olapi_Memory_Op_History FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM V_Olapi_Memory_Op_History FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM GV_Ksmhp FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM Olapi_History_Seq FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM Olapi_History FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM Olapi_Session_History FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM Olapi_Iface_Object_History FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM Olapi_Iface_Op_History FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM Olapi_Memory_Op_History FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM Olapi_Memory_Heap_History FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM DBMS_XSOQ FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP PUBLIC SYNONYM DBMS_XSOQ_UTIL FORCE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

DROP VIEW GV_Olapi_Session_History

*

ERROR at line 1:

ORA-00942: table or view does not exist

DROP VIEW V_Olapi_Session_History

*

ERROR at line 1:

ORA-00942: table or view does not exist

DROP VIEW GV_Olapi_Iface_Object_History

*

ERROR at line 1:

ORA-00942: table or view does not exist

DROP VIEW V_Olapi_Iface_Object_History

*

ERROR at line 1:

ORA-00942: table or view does not exist

DROP VIEW GV_Olapi_Iface_Op_History

*

ERROR at line 1:

ORA-00942: table or view does not exist

DROP VIEW V_Olapi_Iface_Op_History

*

ERROR at line 1:

ORA-00942: table or view does not exist

DROP VIEW GV_Olapi_Memory_Op_History

*

ERROR at line 1:

ORA-00942: table or view does not exist

DROP VIEW V_Olapi_Memory_Op_History

*

ERROR at line 1:

ORA-00942: table or view does not exist

DROP VIEW GV_Ksmhp

*

ERROR at line 1:

ORA-00942: table or view does not exist

DROP SEQUENCE Olapi_History_Seq

              *

ERROR at line 1:

ORA-02289: sequence does not exist

DROP TABLE Olapi_History CASCADE CONSTRAINTS

           *

ERROR at line 1:

ORA-00942: table or view does not exist

DROP TABLE Olapi_Session_History CASCADE CONSTRAINTS

           *

ERROR at line 1:

ORA-00942: table or view does not exist

DROP TABLE Olapi_Iface_Object_History CASCADE CONSTRAINTS

           *

ERROR at line 1:

ORA-00942: table or view does not exist

DROP TABLE Olapi_Iface_Op_History CASCADE CONSTRAINTS

           *

ERROR at line 1:

ORA-00942: table or view does not exist

DROP TABLE Olapi_Memory_Op_History CASCADE CONSTRAINTS

           *

ERROR at line 1:

ORA-00942: table or view does not exist

DROP TABLE Olapi_Memory_Heap_History CASCADE CONSTRAINTS

           *

ERROR at line 1:

ORA-00942: table or view does not exist

DROP PROCEDURE OlapiHistoryRetention

*

ERROR at line 1:

ORA-04043: object OLAPIHISTORYRETENTION does not exist

DROP TRIGGER OlapiStartupTrigger

*

ERROR at line 1:

ORA-04080: trigger 'OLAPISTARTUPTRIGGER' does not exist

DROP TRIGGER OlapiShutdownTrigger

*

ERROR at line 1:

ORA-04080: trigger 'OLAPISHUTDOWNTRIGGER' does not exist

DROP PACKAGE DBMS_XSOQ

*

ERROR at line 1:

ORA-04043: object DBMS_XSOQ does not exist

DROP PACKAGE DBMS_XSOQ_UTIL

*

ERROR at line 1:

ORA-04043: object DBMS_XSOQ_UTIL does not exist

PL/SQL procedure successfully completed.

Synonym dropped.

Package dropped.

  DROP PACKAGE GenDatabaseInterface

*

ERROR at line 1:

ORA-04043: object GENDATABASEINTERFACE does not exist

  DROP PACKAGE GenConnectionInterface

*

ERROR at line 1:

ORA-04043: object GENCONNECTIONINTERFACE does not exist

  DROP PACKAGE GenServerInterface

*

ERROR at line 1:

ORA-04043: object GENSERVERINTERFACE does not exist

  DROP PACKAGE GenMdmPropertyIdConstants

*

ERROR at line 1:

ORA-04043: object GENMDMPROPERTYIDCONSTANTS does not exist

  DROP PACKAGE GenMdmClassConstants

*

ERROR at line 1:

ORA-04043: object GENMDMCLASSCONSTANTS does not exist

  DROP PACKAGE GenMdmObjectIdConstants

*

ERROR at line 1:

ORA-04043: object GENMDMOBJECTIDCONSTANTS does not exist

  DROP PACKAGE GenMetadataProviderInterface

*

ERROR at line 1:

ORA-04043: object GENMETADATAPROVIDERINTERFACE does not exist

  DROP PACKAGE GenCursorManagerInterface

*

ERROR at line 1:

ORA-04043: object GENCURSORMANAGERINTERFACE does not exist

  DROP PACKAGE GenDataTypeIdConstants

*

ERROR at line 1:

ORA-04043: object GENDATATYPEIDCONSTANTS does not exist

  DROP PACKAGE GenDefinitionManagerInterface

*

ERROR at line 1:

ORA-04043: object GENDEFINITIONMANAGERINTERFACE does not exist

  DROP PACKAGE GenDataProviderInterface

*

ERROR at line 1:

ORA-04043: object GENDATAPROVIDERINTERFACE does not exist

PL/SQL procedure successfully completed.

Procedure dropped.

Function dropped.

Package dropped.

PL/SQL procedure successfully completed.

1 row deleted.

SQL> ----> Deinstall APS - OLAP AW component

SQL> @?/olap/admin/catnoaps.sql

Procedure created.

PL/SQL procedure successfully completed.

Procedure dropped.

1 row deleted.

SQL> @?/olap/admin/cwm2drop.sql

drop public synonym cwm2_olap_exceptions

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_manager

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_utility

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_catalog

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_cube

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_dimension

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_dimension_attribute

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_hierarchy

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_level

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_level_attribute

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_measure

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_validate

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_verify_access

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_export

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_delete

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_table_map

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_pc_transform

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_security

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dbms_aw_utilities

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olapmr_dimensions

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olapmr_dim_attributes

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olapmr_attributes

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olapmr_dim_levels

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym cwm2_olap_classify

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_cubes

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_cube_measures

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_cube_dim_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_cube_meas_dim_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_dimensions

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_dim_hierarchies

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_dim_levels

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_dim_hier_level_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_dim_attributes

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_dim_attr_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_dim_level_attributes

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_cube_measure_maps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_fact_level_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_level_key_col_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_join_key_column_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_dim_level_attr_maps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_catalogs

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_entity_desc_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_catalog_entity_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_fact_table_gid

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_hier_custom_sort

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olapmr_facttblkeymaps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olapmr_facttblfctmaps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_listdims

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_dim_hiers

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_hierdims

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_FactTblKeyMaps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_rufcttblkyMaps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_FactTblFctMaps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_hierdim_keycol_map

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olapmr_dim_levels_keymaps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_dim_levels_keymaps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_mr_measdimview

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap9i1_hier_dimensions

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap9i2_hier_dimensions

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap1_cubes

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_entity_parameters

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym dba_olap2_entity_ext_parms

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_cubes

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_cube_measures

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_cube_dim_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_cube_meas_dim_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_dimensions

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_dim_hierarchies

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_dim_levels

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_dim_hier_level_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_dim_attributes

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_dim_attr_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_dim_level_attributes

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_cube_measure_maps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_fact_level_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_level_key_col_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_join_key_column_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_dim_level_attr_maps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_catalogs

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_entity_desc_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_catalog_entity_uses

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_fact_table_gid

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_hier_custom_sort

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olapmr_facttblkeymaps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olapmr_facttblfctmaps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_listdims

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_dim_hiers

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_hierdims

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_FactTblKeyMaps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_rufcttblkyMaps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_FactTblFctMaps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_hierdim_keycol_map

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olapmr_dim_levels_keymaps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_dim_levels_keymaps

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_mr_measdimview

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap9i1_hier_dimensions

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap9i2_hier_dimensions

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap1_cubes

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_entity_parameters

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop public synonym all_olap2_entity_ext_parms

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

SQL> ----> Recompile invalids

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN  2013-05-29 23:00:29

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC>   objects in the database. Recompilation time is proportional to the

DOC>   number of invalid objects in the database, so this command may take

DOC>   a long time to execute on a database with a large number of invalid

DOC>   objects.

DOC>

DOC>   Use the following queries to track recompilation progress:

DOC>

DOC>   1. Query returning the number of invalid objects remaining. This

DOC>      number should decrease with time.

DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC>   2. Query returning the number of objects compiled so far. This number

DOC>      should increase with time.

DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC>   This script automatically chooses serial or parallel recompilation

DOC>   based on the number of CPUs available (parameter cpu_count) multiplied

DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC>   On RAC, this number is added across all RAC nodes.

DOC>

DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC>   recompilation. Jobs are created without instance affinity so that they

DOC>   can migrate across RAC nodes. Use the following queries to verify

DOC>   whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC>   1. Query showing jobs created by UTL_RECOMP

DOC>         SELECT job_name FROM dba_scheduler_jobs

DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC>   2. Query showing UTL_RECOMP jobs that are running

DOC>         SELECT job_name FROM dba_scheduler_running_jobs

DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_END  2013-05-29 23:00:31

DOC> The following query reports the number of objects that have compiled

DOC> with errors (objects that compile with errors have status set to 3 in

DOC> obj$). If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

OBJECTS WITH ERRORS

-------------------

                  0

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

ERRORS DURING RECOMPILATION

---------------------------

                          0

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

SQL> select comp_id,comp_name,version from dba_registry where status='INVALID';

no rows selected

HTMLDB_SYSTEM Package is Invalid After Removing APEX

Oracle Application Express (formerly HTML DB) - Version: 3.1.0.00.32 to 3.2.1.00.10 - Release: APEX 3.1 to 
Information in this document applies to any platform.

Symptoms

After removing APEX (Application Express) from the database using the apxremov.sql script, the object SYS.HTMLDB_SYSTEM is shown as invalid. 

Changes

Removed APEX from the database using apxremov.sql script. 

Cause

The apexremov.sql script removes APEX, but leaves the HTMLDB_SYSTEM package and synonym. 

Solution

As there is no fix to this issue in terms of a patch, remove the object manually by following the following steps:

oracle@solaris11:/u01/app/oracle/product/11.2.0/dbhome_2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed May 29 22:46:04 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> drop package htmldb_system;

Package dropped.

SQL> drop public synonym htmldb_system;

Synonym dropped.

SQL> exit

How to remove Application Express 3.0 and above

oracle@solaris11:/u01/app/oracle/product/11.2.0/dbhome_2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed May 29 22:29:57 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> show user

USER is "SYS"

SQL> select version,status from dba_registry where comp_id='APEX';

VERSION                        STATUS

------------------------------ -----------

3.2.1.00.12                    VALID

SQL> @?/apex/apxremov.sql

...Removing Application Express

old   1: alter session set current_schema = &APPUN

new   1: alter session set current_schema = APEX_030200

Session altered.

PL/SQL procedure successfully completed.

no rows selected

old   2:     if '&UPGRADE' = '1' then

new   2:     if '1' = '1' then

PL/SQL procedure successfully completed.

Session altered.

PL/SQL procedure successfully completed.

old   1: drop user &APPUN cascade

new   1: drop user APEX_030200 cascade

User dropped.

old   2:     if '&UPGRADE' = '1' then

new   2:     if '1' = '1' then

PL/SQL procedure successfully completed.

old   5:     if '&UPGRADE' = '1' then

new   5:     if '1' = '1' then

PL/SQL procedure successfully completed.

old   2:     if '&UPGRADE' = '1' then

new   2:     if '1' = '1' then

PL/SQL procedure successfully completed.

...Application Express Removed

SQL> select version,status from dba_registry where comp_id='APEX';

no rows selected

Quick way to fix ORA-27102: out of memory on Solaris 10/11 during the installation

When the error encountered, do NOT click “Cancel”, adjust the process level resource limit and click “Retry” will the time.

oracle@solaris11:/u01/stage/database$ ps -ef|grep java

  oracle  1836     1   0 21:08:46 pts/1       2:00 /tmp/OraInstall2013-05-29_09-08-41PM/jdk/jre/bin/amd64/java -Doracle.installer.

  oracle  4035  1800   0 21:44:13 pts/1       0:00 grep java

root@solaris11:~#  prctl -r -n project.max-shm-memory -v 5000000000 -i process 1836

root@solaris11:~# prctl -n project.max-shm-memory  -i process 1836

process: 1836: /tmp/OraInstall2013-05-29_09-08-41PM/jdk/jre/bin/amd64/java -Doracle.i

NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT

project.max-shm-memory

       privileged      4.66GB      -   deny                                 -

        system          16.0EB    max   deny                                 -

The process level resource limit automatically inherited by child process:

To confirm:

root@solaris11:~# ps -ef|grep smon

  oracle  5190     1   0 21:51:35 ?           0:00 ora_smon_orcl2

root@solaris11:~# prctl -n project.max-shm-memory  -i process 5190

process: 5190: ora_smon_orcl2

NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT

project.max-shm-memory

        privileged      4.66GB      -   deny                                 -

        system          16.0EB    max   deny                                 -

T-SQL Quick Reference - SELECT

Topic	Using ESCAPE keyword
Example 1	WHERE Description LIKE '%/%%' ESCAPE '/'
Example 2
Remarks
Topic	Declaring and Assigning Values to Variables
Example 1	Pre-2008 method: DECLARE @AddressLine1 nvarchar(60) SET @AddressLine1 = 'Heiderplatz' SELECT AddressID, AddressLine1 FROM Person.Address WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%'
Example 2	2008 method: DECLARE @AddressLine1 nvarchar(60) = 'Heiderplatz' SELECT AddressID, AddressLine1 FROM Person.Address WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%'
Remarks
Topic	Use TOP Keyword with Ordered Results
Example 1	select top 10 * from  [Person].[Address] order by ModifiedDate; select top 10 percent * from  [Person].[Address] order by ModifiedDate
Example 2	declare @Percentage float = 1 select top (@Percentage) percent * from [Person].[Address] order by ModifiedDate declare @Percentage integer = 1 select top (@Percentage) * from [Person].[Address] order by ModifiedDate
Remarks
Topic	Declaring and Assigning Values to Variables
Example 1	Pre-2008 method: DECLARE @AddressLine1 nvarchar(60) SET @AddressLine1 = 'Heiderplatz' SELECT AddressID, AddressLine1 FROM Person.Address WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%'
Example 2	2008 method: DECLARE @AddressLine1 nvarchar(60) = 'Heiderplatz' SELECT AddressID, AddressLine1 FROM Person.Address WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%'
Remarks
Topic	Use TOP Keyword with Ordered Results
Example 1	select top 10 * from  [Person].[Address] order by ModifiedDate; select top 10 percent * from  [Person].[Address] order by ModifiedDate
Example 2	declare @Percentage float = 1 select top (@Percentage) percent * from [Person].[Address] order by ModifiedDate declare @Percentage integer = 1 select top (@Percentage) * from [Person].[Address] order by ModifiedDate
Remarks
Topic	Using Column Aliases
Example 1	select top 1 AddressID AS "ADDRESS ID", AddressID "Address ID" from [Person].[Address]
Example 2
Remarks
Topic	Performing String Concatenation
Example 1	select convert(varchar(25),AddressID) + ' Is in City ' + City from [Person].[Address]
Example 2	declare @DatabaseName varchar(200) = '' select @DatabaseName = @DatabaseName + d.name + ',' from master.sys.databases d order by d.name select @DatabaseName GO
Remarks
Topic	SELECT .. INTO (CATS equivalent)
Example 1	select * into Person.Address_empty from Person.Address where 1=2
Example 2
Remarks	Caution: Although the structure of the selected columns is reproduced, the constraints, indexes, and other separate objects dependent on the source table are not copied.
Topic	Using Derived Tables
Example 1	SELECT DISTINCT s.PurchaseOrderNumber FROM Sales.SalesOrderHeader s INNER JOIN (SELECT SalesOrderID FROM Sales.SalesOrderDetail WHERE UnitPrice BETWEEN 1000 AND 2000) d ON s.SalesOrderID = d.SalesOrderID
Example 2	SELECT DISTINCT s.PurchaseOrderNumber FROM Sales.SalesOrderHeader s, (SELECT SalesOrderID FROM Sales.SalesOrderDetail WHERE UnitPrice BETWEEN 1000 AND 2000) d where s.SalesOrderID = d.SalesOrderID
Remarks
Topic	Using CROSS APPLY & OUTER APPLY
Example 1	SELECT d.name,v.FileId,v.BytesRead,v.BytesWritten FROM master.sys.databases d cross apply fn_virtualfilestats(DB_ID(d.name),null) v ; GO
Example 2	SELECT d.name,v.FileId,v.BytesRead,v.BytesWritten FROM master.sys.databases d outer apply fn_virtualfilestats(DB_ID(d.name),null) v ; GO
Remarks	The outer apply display non-matching rows in  the base table
Topic	Using TABLESAMPLE
Example 1	select * from person.address tablesample system (1 percent)
Example 2
Remarks	The percentage is the percentage of the table’s data pages. Once the sample pages are selected, all rows for the selected pages are returned.
Topic	Using PIVOT and UNPIVOT
Example 1	select * from (select addressid,city from person.address)  a pivot (count(addressid) for city in ([Spokane],[Columbus])) as b
Example 2	select name, b.proprety_type,b.property_value from (select name,convert(varchar(50),create_date) create_date, convert(varchar(50),collation_name) collation_name  from master.sys.databases) a unpivot (property_value for proprety_type in ([create_date],[collation_name]) ) b
Remarks	The list of pivoted column names cannot already exist in the base table or view query columns being pivoted. For UNPIVOT, all columns have to be of the same data type AND length

Setup SQL Database Mirroring using T-SQL between SQL2005 to SQL2012

	SQL 2005SP4	SQL2012SP1
	USE [master] GO CREATE LOGIN [donghua] WITH PASSWORD=N'p_ssw0rd', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO EXEC master..sp_addsrvrolemember @loginame = N'donghua', @rolename = N'sysadmin' GO	USE [master] GO CREATE LOGIN [donghua] WITH PASSWORD=N'p_ssw0rd', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [donghua] GO
	create database TestDb; go use TestDb; go create table t(c1 varchar(20)); insert into t values(getdate()); go select * from t;
Backup & Restoration
Set Full recovery model	USE master; GO ALTER DATABASE TestDB SET RECOVERY FULL; GO
Backup Database	BACKUP DATABASE TestDb     TO DISK = 'C:\Backup\TestDb_Full.bak'     WITH FORMAT GO	Copy the backup to this Server
Restore Database		RESTORE DATABASE TestDb    FROM DISK='C:\backup\TestDb_Full.bak'    WITH NORECOVERY,       MOVE 'TestDb' TO          'D:\SQL2012\MSSQL11.PROD\MSSQL\DATA\TestDb.mdf',       MOVE 'TestDb_log' TO          'D:\SQL2012\MSSQL11.PROD\MSSQL\DATA\TestDb_log.LDF'; GO
Backup Log	BACKUP LOG TestDb     TO DISK = 'C:\Backup\TestDb_Log.bak'     WITH FORMAT GO	Copy the backup to this Server
		RESTORE LOG TestDb     FROM DISK = 'C:\Backup\TestDb_Log.bak'     WITH NORECOVERY GO
The two server instances run in nontrusted Windows domains, so certificate-based authentication is required
On the master database, create the database master key, if needed.	USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!QAZXSW#$%e'; GO	USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '093j45&*he'; GO
Make a certificate for this server instance.	USE master; GO CREATE CERTIFICATE SQL2005_cert    WITH SUBJECT = 'SQL2005 certificate',    START_DATE = '20121031',    EXPIRY_DATE = '20151031'; GO	USE master; GO CREATE CERTIFICATE SQL2012_cert    WITH SUBJECT = 'SQL2012 certificate',    START_DATE = '20121031',    EXPIRY_DATE = '20151031'; GO
Create a mirroring endpoint for server instance using the certificate.	CREATE ENDPOINT Endpoint_Mirroring    STATE = STARTED    AS TCP (       LISTENER_PORT=7024       , LISTENER_IP = ALL    )    FOR DATABASE_MIRRORING (       AUTHENTICATION = CERTIFICATE SQL2005_cert       , ENCRYPTION = REQUIRED ALGORITHM AES       , ROLE = ALL    ); GO	CREATE ENDPOINT Endpoint_Mirroring    STATE = STARTED    AS TCP (       LISTENER_PORT=7024       , LISTENER_IP = ALL    )    FOR DATABASE_MIRRORING (       AUTHENTICATION = CERTIFICATE SQL2012_cert       , ENCRYPTION = REQUIRED ALGORITHM AES       , ROLE = ALL    ); GO
Back up the HOST_A certificate, and copy it to other system, HOST_B.	BACKUP CERTIFICATE SQL2005_cert TO FILE = 'C:\Backup\SQL2005_cert.cer'; GO	BACKUP CERTIFICATE SQL2012_cert TO FILE = 'C:\Backup\SQL2012_cert.cer'; GO
Setup inbound connection
Create a login on HOST_A for HOST_B.	USE master; CREATE LOGIN sql2012_login WITH PASSWORD = '1Sample_Strong_Password!!#'; GO	USE master; CREATE LOGIN sql2005_login WITH PASSWORD = '2Sample_Strong_Password!!#'; GO
Create a user for that login.	CREATE USER sql2012_user FOR LOGIN sql2012_login; GO	CREATE USER sql2005_user FOR LOGIN sql2005_login; GO
Associate the certificate with the user.	CREATE CERTIFICATE SQL2012_cert    AUTHORIZATION sql2012_user    FROM FILE = 'C:\backup\SQL2012_cert.cer' GO	CREATE CERTIFICATE SQL2012_cert    AUTHORIZATION sql2012_user    FROM FILE = 'C:\backup\SQL2012_cert.cer' GO
Grant CONNECT permission on the login for the remote mirroring endpoint.	GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sql2012_login]; GO	GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sql2012_login]; GO
Setup mirror database
On the mirror server instance on HOST_B, set the server instance on HOST_A as the partner (making it the initial principal server instance).		--At HOST_B, set server instance on HOST_A as partner (principal server): ALTER DATABASE TestDb     SET PARTNER = 'TCP://WIN2003SQL2005:7024'; GO
On the principal server instance on HOST_A, set the server instance on HOST_B as the partner (making it the initial mirror server instance)	--At HOST_A, set server instance on HOST_B as partner (mirror server). ALTER DATABASE TestDb     SET PARTNER = 'TCP://WIN-HN1Q39OK9JF:7024'; GO
This example assumes that the session will be running in high-performance mode. To configure this session for high-performance mode, on the principal server instance (on HOST_A), set transaction safety to OFF.	--Change to high-performance mode by turning off transacton safety.                                                     ALTER DATABASE TestDb     set partner SAFETY off GO
Setup mirror database
Create database snapshot		CREATE DATABASE TestDb_SS ON ( NAME = TestDb, FILENAME = 'D:\SQL2012\MSSQL11.PROD\MSSQL\DATA\TestDb.ss' ) AS SNAPSHOT OF TestDb; GO Msg 946, Level 14, State 1, Line 1 Cannot open database 'TestDb_SS' version 611. Upgrade the database to the latest version. Msg 1823, Level 16, State 7, Line 1 A database snapshot cannot be created because it failed to start.
Perform insert in SQL2005	use testdb go insert into t values(getdate()); go
Manual Failover	USE master go ALTER DATABASE TestDb SET PARTNER FAILOVER go Msg 1477, Level 16, State 1, Line 1 The database mirroring safety level must be FULL to manually failover database "TestDb".  Set safety level to FULL and retry.
Force Service in a Database Mirroring Session		use master; go ALTER DATABASE TestDb SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS go Msg 1455, Level 16, State 106, Line 1 The database mirroring service cannot be forced for database "TestDb" because the database is not in the correct state to become the principal database.
Use the restore command to open the DB		Use master go ALTER DATABASE TestDb SET PARTNER OFF go RESTORE DATABASE TestDb WITH RECOVERY go -- Data is in-sync