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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s