Enabling and Disabling Database Options

28 CommentsOracle, ,

When you select the “Enterprise Edition” option during Oracle installation, all the components which are licensed under “Enterprise Edition” get installed by default. The idea is to install all these options and then disable those options that were not licensed.

If you need additional functionality such as partitioning some time after the initial installation, you can enable (or disable) the specific component functionality at the binary level:

1. Shutdown all database instance(s)/service(s) running from the Oracle Database Home
2. Run the following relink command to disable the option at the binary level:

Here is the list of database options and switches:

Database Option ON OFF
Data Mining dm_on dm_off
Data Mining Scoring Engine dmse_on dmse_off
Database Vault dv_on dv_off
Label Security lbac_on lbac_off
Partitioning part_on part_off
Real Application Clusters rac_on rac_off
Spatial sdo_on sdo_off
Real Application Testing rat_on rat_off
OLAP olap_on olap_off
Automatic Storage Management asm_on asm_off
Context Management Text ctx_on ctx_off

3. Startup the instance and check if the option is enabled:

Please share
      

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

28 Comments

    • Gokhan Atil

      Hi İlyas,

      If you do not want to relink (as explained in the article), you need to drop Spatial objects/indexes and the Spatial user (MDSYS) to remove Spatial Option. Please check Metalink/MOS ID 179472.1 (and 273573.1)

  2. Michal

    Reply

    Hi Gokhan,
    First of all thanks for sharing your knowledge – excellent job, I really appreciate it!

    Do you know is there also an easy way to disable Active Data Guard option in 11g which comes by default while using OUI ?

  3. Sandy

    Reply

    What would be the steps to be followed for oracle 11g relaease 1 RAC 2 node RAC database to disable OLAP and spatial

  4. LAL PRASAD

    Reply

    How can we enable partitioning option on Oracle 11g express windows 32 version?

    • Gokhan Atil

      Lal,

      XE edition does not support partitioning, so you can not enable it.

  6. rishi

    Reply

    Hello,

    How can u remove the DB objects/schema for these option if installed via OUI/

    Above method with disable the features but what to do with DB objects,please clarify

    Ta

    • Gokhan Atil

      Hi Rishi,

      It depends on which option you disable. You should search My Oracle Support documents to remove related DB objects (if there’s any). For example if you disable OLAP and find invalid OLAP Objects in SYS And OLAPSYS Schemas, you need to follow the instructions in Doc ID 565773.1 to remove the objects.

  7. Ruthira

    Reply

    How can I enable data mining after I installed the Oracle 11g R2 XE?

    • Gokhan Atil

      Hi Ruthira,

      Oracle Data Mining features is not included with Oracle Database XE, so you can’t enable it.

  8. Sean

    Reply

    Hi, Gokhan:

    I am a developer and doing few DBA job in developer database. I am trying to use SOD package. The SOD is on? 

    SELECT * FROM v$option WHERE parameter = ‘Spatial’;

    PARAMETER VALUE
    —————————————————————- ———
    Spatial TRUE

    select comp_id, version, status, comp_name  from dba_registry;

    COMP_ID VERSION STATUS
    —————————— —————————— ——-
    COMP_NAME
    ———————————————————————
    EM 11.2.0.2.0 VALID
    Oracle Enterprise Manager

    CATALOG 11.2.0.2.0 VALID
    Oracle Database Catalog Views

    CATPROC 11.2.0.2.0 VALID
    Oracle Database Packages and Types

    I got error messages when tried to create a geometry table:

    CREATE TABLE cola_markets ( mkt_id NUMBER PRIMARY KEY,
    name VARCHAR2(32),  shape SDO_GEOMETRY);
    shape SDO_GEOMETRY)
    *
    ERROR at line 4:
    ORA-00902: invalid datatype

    What should I do?

    Thanks.

    Sean

  9. Rob Lasonder

    Reply

    How does one turn off Advanced Compression? Many post mention Parameter _oltp_compression. However, that Parameter doesn’t work, see my Little test below:

    SQL> alter system set “_oltp_compression”=false;

    System altered.

    SQL> show parameter oltp

    NAME TYPE VALUE
    ———————————— ——————————— ——————————
    _oltp_compression boolean FALSE

    SQL> create table test (kol1 varchar2(10));

    Table created.

    SQL> alter table test compress for oltp;

    Table altered.

    SQL> select table_name, compress_for from dba_tables where table_name like ‘TEST’;

    TABLE_NAME COMPRESS_FOR
    ———————
    TEST ADVANCED

    • Gokhan Atil

      Hi Rob,

      As I know, it’s not possible to disable or uninstall Advanced Compression. I made a quick search on My Oracle Support and found the following document:

      Can The Advanced Compression Option (ACO) Be Disabled Or Uninstalled? (Doc ID 1459216.1),

      It says there’s no way 🙂

  10. Sreedhar Silveri

    Reply

    Hi Gokhan,

    We have selected Large database option while installing which partitioning the SOAINFRA tablespace however due to some purge issue we want to disable the partitioning for which we would follow you steps given above however what to do with the existing SOAINFRA tablespace partitions.

    We have a activity scheduled to purge the tablespace by dropping the old partition and exp/imp the data to reclaim the space at the OS level, if you can help us on what can be done with the existing partition we can perform those steps in the next outage.

  11. kalyan

    Reply

    Hello Gokhan,

    I just want to disable RAT and data mining in the 10g database,

    Can you help how can I do it

  12. Hari

    Reply

    Hi Gokhan,

    12c dbca in silent mode not excluding unwanted components(example Oracle Spatial,Oracle Text, Oracle Label Security,Oracle Database Vault etc). How we can avoid to get installed during database creation using dbca as we don’t have License for these components.

    I have tried to get it excluded on template which I have created, but still it getting installed.

    Regards,
    Hari

  13. Mustapha

    Reply

    Hello Gokhan,

    I am kinda confused here. the Oracle partition parameter value is ‘TRUE’

    from SELECT * FROM V$OPTION WHERE PARAMETER = ‘Partitioning’;

    Do i still need a license for using it or i can go ahead and start partitioning my pretty large tables.

    • Gokhan Atil

      Yes, you need to buy required licenses to use it. Oracle usually provide their software without any “software protection” or limitation, but it doesn’t mean that it’s a free software 🙂

  14. engine

    Reply

    In v$option, it shows ‘Ture’ for some oracle DB options. How can we disable one optional feature in DB?

  15. Majid

    Reply

    After we disable already enabled partitioning.

    What happens\what is affected?

    • Gokhan Atil

      Partitioned tables remain “partitioned” but you can not create a new partitioned table or index.

  16. Pingback: Removing Options from the Oracle Database kernel in 12c | Upgrade your Database - NOW!

  17. Tukaram

    Reply

    Hi Gokhan,

    WE have single instance 11gr2 databases , we wanted disable or remove RAC (real application cluster) database component , could you please provide me the steps to do same thanks.

  18. VENKATESH RANGARAJAN

    Reply

    I am not able to use some of the oracle functions in oracle 12C database, can you please advise.

Leave Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.