6 Replies Latest reply: Dec 18, 2012 4:06 AM by 934008 RSS

    OLAP functions

    934008
      Hi All,

      I have written a query in oracle 10g, my query is having some rank (), OVER (PARTITION BY..) functions to perform some operation.
      Are these OLAP functions?
      I am asking this because we are getting some following message on server logs:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-06512: at "SYS.OLAPIHISTORYRETENTION", line 1
      ORA-06512: at line 15
      ORA-12663: Services required by client not available on the server
      ORA-36961: Oracle OLAP is not available.
      We are having an “Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi” license. (Might be without an OLAP functions.)

      Can someone please suggest me the reason for getting above logs messages?
      Are these because of rank (), OVER (PARTITION BY..) functions?

      If these are an OLAP functions then, will it cause any penalty from Oracle for using these without license?

      Edited by: Sumit Gujar on Dec 3, 2012 12:02 AM
        • 1. Re: OLAP functions
          ranit B
          Hi Sumit,

          Please check this... Database startup error message
          • 2. Re: OLAP functions
            ranit B
            Sumit,

            Did that help or you want some more assistance?
            Please let us know if you face further issues.

            Refer - http://ora-36961.ora-code.com/
            >
            ORA-36961: Oracle OLAP is not available.
            Cause:      The user attempted to utilize functionality found only in Oracle OLAP, but OLAP has not been enabled in the executable.
            Action:      Install the Oracle OLAP option
            >

            This looks quite clear that your Database doesn't have the OLAP functionality installed.
            Could you please check with your Team or DBA regarding this?

            Ranit B.

            Edited by: ranit B on Dec 3, 2012 3:03 PM
            • 3. Re: OLAP functions
              934008
              Thanks for the reply,

              Yes. I confirmed, in our Database OLAP features are not installed.

              But I think
              1) rank () 
              2) over(partition by..)  -  Analytical functions
              comes under OLAP features.

              Then I am really surprised how comes my query is working with above functions without OLAP?

              In the first post I asked some of the questions regarding OLAP features license, could you please tell us about that?
              • 4. Re: OLAP functions
                ranit B
                But I think
                1) rank () 
                2) over(partition by..)  -  Analytical functions
                comes under OLAP features.
                They are called Analytic functions because the result set is used for Analytical study purpose.
                Then I am really surprised how comes my query is working with above functions without OLAP?
                I guess, this doesn't has to do anything with the Database being OLAP or OLTP.
                Please check this... Re: How to Check Database  OLAP or OLTP
                If these are an OLAP functions then, will it cause any penalty from Oracle for using these without license?
                No. It won't cause any penalty.
                AFAIK, there's nothing called OLAP and OLTP functions. Yes, but we do have Analytic functions which helps in easier Data Analysis but is not restricted to only OLAP systems.
                OLAP is just a system where it's more of Data Analysis whereas OLTP is more of Online Transaction of data.

                But at the core, Database is used as Oracle in both.

                Please rectify me if i'm wrong.

                Edited by: ranit B on Dec 3, 2012 3:58 PM
                • 5. Re: OLAP functions
                  934008
                  Thanks a lot for the link, I found it really helpful.

                  Yes, the rank(), over(partition by..) are called Analytic functiion and it doesn't has to do anything with the Database being OLAP or OLTP.

                  The OLAP features are not installed on the database, from the log it looks like someone has been trying to use Oracle OLAP features. How do I rectify these logs?

                  Any steps for finding out why the logs are showing ‘ORA-12663: Services required by client not available on the server’.
                  • 6. Re: OLAP functions
                    934008
                    rank(), over(partition by..) are called Analytic functiion and it doesn't has to do anything with the Database being OLAP or OLTP