Is there any specific query or configuration to check Oracle is configured for OLTP or OLAP?I have gone through many sites.But I can find the difference between OLTP and OLAP and not query or configuration to check that difference?

1 Answer 1


OLTP does not require any specific configuration.

Checking the availability of OLAP libraries:

SQL> select * from v$option where parameter = 'OLAP';

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

Checking the availability of OLAP components in the database:

SQL> select comp_name, version, status
     from dba_server_registry where comp_name like '%OLAP%';

COMP_NAME                 VERSION    STATUS
------------------------- ---------- ----------
OLAP Analytic Workspace VALID
OLAP Catalog     VALID

Checking if OLAP is in use (note that, this works with sampled data, the sample interval by default is 1 week, it does not represent the real-time situation):

SQL> select name, detected_usages, currently_used from
     dba_feature_usage_statistics where name like 'OLAP%' order by name;

NAME                           DETECTED_USAGES CURRE
------------------------------ --------------- -----
OLAP - Analytic Workspaces                   0 FALSE
OLAP - Cubes                                 0 FALSE
  • How can we check OLTP is used? Dec 28, 2016 at 10:05
  • The database will run as an OLTP by default but that also depends on how your schema and application is set up to use the database you create. As @Balazs_Papp has said above you will need to ensure that the OLAP options are installed to use specific OLAP features. Dec 28, 2016 at 12:41
  • OLTP is not a "feature" that is "configured" or "used". That's like asking if your pick-up truck is "configured" for carrying dirt or is it "configured" for carrying bags of dog food. "OLTP" simply means "On Line Transaction Processing". It is HOW the database is used, not a particular configuration or component. Many databases are used both as OLTP and as DSS. My pickup truck will haul both bulk dirt AND bags of dog food, and neither has any thing to do with what accessories I may have.
    – EdStevens
    Dec 28, 2016 at 16:11
  • @EdStevens If you think the question is wrong or not asked properly ,You can comment it directly. There is no point in giving the harsh examples. Jan 11, 2017 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.