How to enable and disable Oracle  partition in oracle 9i to 11gr2

Partitioning is only available in Enterprise Edition of the Oracle Database. It is not available in Standard Edition or in Standard Edition One or in Express Edition.

Oracle Partitioning is divided into System and User Partitioning. Oracle Partitioning is an Extra cost option for User Partitioning.

Check partition is enabled or not :

select * from v$option where parameter=’Partitioning’;

sqlplus connection

select name, version, last_usage_date,currently_used from DBA_FEATURE_USAGE_STATISTICS where upper(name) like ‘%PARTITION%’;

To find all non-system partitioned tables, use
SELECT OWNER, TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE,
FROM DBA_PART_TABLES
WHERE OWNER <> ‘SYSTEM’ AND OWNER <> ‘SYS’;

– DBA_PART_INDEXES contains information on INDEX objects that are partitioned and / or subpartitioned:

To find all non-system partitioned indexes, use:
SELECT OWNER, INDEX_NAME, TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE
FROM DBA_PART_INDEXES
WHERE OWNER <> ‘SYSTEM’ AND OWNER <> ‘SYS’;

Confirmation of partition installation by below method.

1)

$ cd <location of OUI>

$ ./runInstaller

click on “Installed Products”
select the ORACLE_HOME
click on “Expand All”
Review the list or products installed

2)

Use the opatch utility:
$ export PATH=$ORACLE_HOME/OPatch:$PATH

$ opatch lsinventory -detail

All processes running from the ORACLE_HOME including but not limited to databases, listeners, intelligent agents, etc. must be shutdown/stopped before issuing these commands

Disable partition between 9.2.0.x through 11.1.0.6.x :
For example to enable/disable Oracle Partitioning:
Enable
——
$ cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk part_on ioracle

Disable
——-
$ cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk part_off ioracle

Product/Component
Automated Storage Management     asm_on             asm_off
Oracle Data Mining         dm_on             dm_off
Database Vault             dv_on             dv_off
Oracle OLAP             olap_on         olap_off
Oracle Label Security         lbac_on         lbac_off
Oracle Partitioning         part_on         part_off
Real Application Cluster     rac_on             rac_off
Real Application Testing     rat_on             rat_off

Starting with 11.2

Enable
——–
$ chopt enable partitioning

Disable
———
$ chopt disable partitioning

Product/Component         Option
Oracle Data Mining           dm
Database Vault                   dv
Oracle OLAP                      olap
Oracle Label Security         lbac
Oracle Partitioning          partitioning
Real Application Testing     rat

Document : 1468698.1  , 948061.1