Enable and Disable Partitioning after database installation
Updated: Jul 19, 2020
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 .
