DISABLE PARTITIONING IN ORACLE
By default, partitioning will be enabled in an oracle database. There are two types of partitioning.
- system
- user
System partitions are mandatory for the database to store system and internal tables for dictionary data to enhance performance in purging of system records and optimal sys queries
If customer has purchased license for partitioning, then they are eligible to use them at will. Customers who are not interested to partition their tables can disable them to save or avoid licensing costs.
To disable partition feature in oracle database, chopt utility should be used.
[oracle@xhydra ~]$ chopt -help
usage:
chopt <enable|disable> <option>
options:
oaa = Oracle Advanced Analytics
olap = Oracle OLAP
partitioning = Oracle Partitioning
rat = Oracle Real Application Testing
e.g. chopt enable rat
Check if partition is used in the database for “user”
kIsH@Xhydra<>col NAME for a20
kIsH@Xhydra<>col DETECTED_USAGES for 999999
kIsH@Xhydra<>col CURRENTLY_USED for 999999
kIsH@Xhydra<>col FIRST_USAGE_DATE for 999999
kIsH@Xhydra<>col FEATURE_INFO for a20
kIsH@Xhydra<>select NAME,DETECTED_USAGES,CURRENTLY_USED,FIRST_USAGE_DATE,FEATURE_INFO from dba_feature_usage_statistics where NAME like '%Parti%';
NAME DETECTED_USAGES CURRE FIRST_USA FEATURE_INFO
-------------------- --------------- ----- --------- --------------------
Partitioning (system 6 TRUE 19-MAY-22 1:T:I::1048575:0:1::
) :::10::0:ON-0::N:N::
0::0::::N|1:I:I::104
8575:0:1::L:N:NU:10:
Partitioning (user) 5 TRUE 19-MAY-22 1:T:I::1048575:0:1::
) :::10::0:ON-0::N:N::
0::0::::N|1:I:I::104
8575:0:1::L:N:NU:10:Check the options
kIsH@Xhydra<>col parameter for a20
kIsH@Xhydra<>col value for a20
kIsH@Xhydra<>select parameter,value from v$option where parameter like '%Parti%';
PARAMETER VALUE
-------------------- --------------------
Partitioning TRUE
Shutdown the database before disabling partition to avoid any corruptions due to IO
kIsH@Xhydra<>shu immediate;
Disable partition from OS level
[oracle@xhydra ~]$ chopt disable partitioning = Oracle Partitioning
Writing to /u01/app/oracle/product/19.0.0/dbhome_1/install/disable_partitioning_2022-08-05_22-07-02PM.log...
/usr/bin/make -f /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/ins_rdbms.mk part_off ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
/usr/bin/make -f /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
Validate the status
kIsH@Xhydra<>col value for a20
kIsH@Xhydra<>select parameter,value from v$option where parameter like '%Parti%';
PARAMETER VALUE
-------------------- --------------------
Partitioning FALSE
Check in dba_feature_usage_statistics for usage information
kIsH@Xhydra<>select NAME,DETECTED_USAGES,CURRENTLY_USED,FIRST_USAGE_DATE,FEATURE_INFO from dba_feature_usage_statistics where NAME like '%Parti%';
NAME DETECTED_USAGES CURRE FIRST_USA FEATURE_INFO
-------------------- --------------- ----- --------- --------------------
Partitioning (system 6 TRUE 19-MAY-22 1:T:I::1048575:0:1::
) :::10::0:ON-0::N:N::
0::0::::N|1:I:I::104
8575:0:1::L:N:NU:10:
Partitioning (user) 0 FALSE