Enable and Disable Partitioning after database installation

There can be many features in oracle database which impacts the license costs. So small & medium enterprises disable those features while database installation is done .

So in this blog we will see how to enable those features once database is already installed .

We will take the Partitioning parameter as an example in oracle 12c .

1- Check the database version

SQL> select * from v$version ;

BANNER CON_ID

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

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0

PL/SQL Release 12.2.0.1.0 - Production 0

CORE 12.2.0.1.0 Production 0

TNS for Linux: Version 12.2.0.1.0 - Production 0

NLSRTL Version 12.2.0.1.0 - Production 0

2- Try to create a partition table

SQL> CREATE TABLE parttab

(sl_no NUMBER NOT NULL,

sl_date DATE NOT NULL)

PARTITION BY RANGE (sl_date)

(PARTITION sl_q1 VALUES LESS THAN (TO_DATE('01/04/2019', 'DD/MM/YYYY')) )TABLESPACE users;

CREATE TABLE parttab

*

ERROR at line 1:

ORA-00439: feature not enabled: Partitioning

3- Now check if partitioning is enabled or not

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

PARAMETER VALUE CON_ID

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

Partitioning FALSE 0

As we can see partitioning is not enabled for the database .So we have to enable it .

4-Now shut down all the db running from the same ORACLE_HOME .

5- Go to $ORACLE_HOME/bin directory & run the chopt command to enable a service

[oracle@Node1 bin]$ cd $ORACLE_HOME/bin

[oracle@Node1 bin]$ chopt enable partitioning

Writing to /u01/app/oracle/product/12.2.0.1/db_1/install/enable_partitioning.log...

/usr/bin/make -f /u01/app/oracle/product/12.2.0.1/db_1/rdbms/lib/ins_rdbms.mk part_on ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1

/usr/bin/make -f /u01/app/oracle/product/12.2.0.1/db_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1

6- Now start the database and check if partitioning is enabled or not

SQL> startup

ORACLE instance started.

Total System Global Area 834666496 bytes

Fixed Size 8626192 bytes

Variable Size 587206640 bytes

Database Buffers 230686720 bytes

Redo Buffers 8146944 bytes

Database mounted.

Database opened.

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

PARAMETER VALUE CON_ID

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

Partitioning TRUE 0

7- Now try to create the partition table

SQL> CREATE TABLE parttab

(sl_no NUMBER NOT NULL,

sl_date DATE NOT NULL)

PARTITION BY RANGE (sl_date)

(PARTITION sl_q1 VALUES LESS THAN (TO_DATE('01/04/2019', 'DD/MM/YYYY')) )TABLESPACE users; 2 3 4 5

Table created.

So as you can see the partition feature is enabled and now we can create the partition .

8- To disable any features we can use the same chopt tool

shut down the db

[oracle@Node1 bin]$ chopt disable partitioning

Writing to /u01/app/oracle/product/12.2.0.1/db_1/install/disable_partitioning.log...

/usr/bin/make -f /u01/app/oracle/product/12.2.0.1/db_1/rdbms/lib/ins_rdbms.mk part_off ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1

/usr/bin/make -f /u01/app/oracle/product/12.2.0.1/db_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1

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

PARAMETER VALUE CON_ID

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

Partitioning FALSE 0

Similarly there are other features ,which can be enabled/disabled after the database is already created .