Common Questions on the Partitioning Option Installed in the Oracle Database ...

郑全

In this Document

Purpose

Questions and Answers

Which edition of the database do i need in order to have Partitioning?

License for Partitioning

How to check whether partitioning is installed or not?

In the Oracle Home, at file system level:

Within the database

How to check whether partitioning is being used in a database?

What happens with partitioned tables after Partitioning has been uninstalled/disabled?

How to install Partitioning

How to remove Partitioning

What's new in Oracle Database 12c?

References

---

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:   Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

PurposeTo point readers to the relevant information with regard to the installation/enablement/disablement of Partitioning, how these manifests themselves and what are the implications of these in the Oracle Database.

Questions and AnswersWhich edition of the database do i need in order to have Partitioning?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.

Refer to the following for more information.

• Oracle Database Documentation
  

Feature Availability by Edition in the Oracle Database Licensing Information (11.2, 12.1), or to Express Edition Licensing Information (11.2, 12.1).

• MOS Knowledge Articles:
  

Note 1084132.1 Differences Between Enterprise, Standard and Standard One Editions on Oracle 11.2
Note 465460.1 Differences Between Enterprise, Standard and Personal Editions on Oracle 11.1
Note 465465.1 Differences Between Enterprise, Standard and Personal Editions on Oracle 10.2
Note 271886.1 Differences Between Different Editions of Oracle Database 10G Release 1
Note 783290.1 Oracle Database Software Component Guide for 11gR1

License for PartitioningOracle Partitioning is divided into System and User Partitioning. Oracle Partitioning is an Extra cost option for User Partitioning on top of Enterprise Edition license (see Feature Availability by Edition for the different version referenced above).
For licensing in a database that hosts the Management Repository of Enterprise Manager Grid Control (GC)
see Note 456101.1 Grid Control Repository: Everything you Wanted to Know About Partition Maintenance of Metric Data in the 10g/11g Repository.
How to check whether partitioning is installed or not?You can check it in the Oracle Home, at file system level, or inside the database.
In the Oracle Home, at file system level:

• On Unix/Linux refer to the following.
  

Note 948061.1 How to Check and Enable/Disable Oracle Binary Options
Note 1069015.1 How to determine enabled/disabled components in an 11.2 software-only installation

• On Windows
  

If you find <ORACLE_HOME>/bin/oraprtop11.dll.dbl, then Partitioning is disabled.
If you find <ORACLE_HOME>/bin/oraprtop11.dll,  then Partitioning is enabled.

Within the database

• If the following query returns True, then Partitioning is enabled, otherwise it is disabled.
  

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

Partitioning is enabled:

PARAMETER         VALUE-------------------- ----------Partitioning          TRUE

Partitioning is disabled:

PARAMETER         VALUE-------------------- ----------Partitioning          FALSE

• You can also see whether Partitioning is enabled in the SQLPlus Banner when you connect to a particular database:
  

Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsHow to check whether partitioning is being used in a database?Starting from 10g you can query the DBA_FEATURE_USAGE_STATISTICS view to get this information.
select name, version, last_usage_date,currently_used from DBA_FEATURE_USAGE_STATISTICS where upper(name) like '%PARTITION%';

This example output shows the usage of Partitioning in a database created as 10.2.0.1, then subsequently upgraded to 10.2.0.4 and 10.2.0.5.
NAME                           VERSION              LAST_USAGE_DATE CURRENTLY_USED------------------------------ -------------------- --------------- --------------Partitioning (system)          10.2.0.5.0           27-JAN-12       TRUEPartitioning (system)          10.2.0.4.0           02-NOV-10       TRUEPartitioning (system)          10.2.0.1.0           11-MAY-09       TRUEPartitioning (user)            10.2.0.5.0           27-JAN-12       TRUEPartitioning (user)            10.2.0.4.0           02-NOV-10       TRUEPartitioning (user)            10.2.0.1.0                           FALSE
The example output below shows the usage of Partitioning in a database created as 11.2.0.2.
NAME                           VERSION              LAST_USAGE_DATE CURRENTLY_USED------------------------------ -------------------- --------------- ---------------Partitioning (system)          11.2.0.2.0           26-JAN-12       TRUEPartitioning (user)              11.2.0.2.0           26-JAN-12       TRUE Note that DBA_FEATURE_USAGE_STATISTICS shows the usage of System and User Partitioning separately. Partitioning (system) refers to partitioning usage by system objects (e.g. dictionary objects).  To find the non system partitioned objects you can use query from Note 1468698.1.

What happens with partitioned tables after Partitioning has been uninstalled/disabled?Partitioning is divided into System and User Partitioning.
Removing the partitioning option and/or relinking the oracle executable with part_off only affects the User Partitioning, but the functionality as such remains in the Oracle executable.
Tables partitioned before disabling the Partitioning option remain partitioned, and behave the same as before, however any attempt of a user to create a new partitioned table or partitioned index will result in error:
ORA-00439: feature not enabled: Partitioning.Any attempt to create partitioned user object in a Standard Edition database will result in the above ORA-00439 error as well.
To verify whether the oracle executable is linked with partitioning follow instructions in Note  948061.1.
For more information see
Note 434743.1 Can The Partitioning Option Be Deinstalled When System Partitioned Objects Exist
Note 225688.1 How to resolve "ORA-439: Feature not enabled; Partitioning" error
Note 456101.1 Grid Control Repository: Everything you Wanted to Know About Partition Maintenance of Metric Data in the 10g/11g Repository
How to install PartitioningRefer to the note below that fits your environment:

For Unix/Linux:
Note 948061.1 How to Check and Enable/Disable Oracle Binary Options

For Windows 11.2
Note 1188238.1 How to enable Partitioning in Oracle Database Enterprise Edition 11.2 on Windows

Prior 11g refer to Note 373383.1 How to install Partitioning after Patchset Installation
How to remove PartitioningPrior 11.1.0.7 Oracle Universal Installer (OUI) can be used to remove Partitioning on both Unix/Linux and Windows. Alternatively the oracle executable can be re-linked with part_off to disable Partitioning on Linux/Unix. Refer to Note 430239.1 How To De-install Partitioning Option From Enterprise Edition?
For 11.1.0.7 and onwards it is not possible to deinstall/remove Partitioning from already installed Oracle Database Home, however Partitioning can be disabled. For more details refer to Note 888934.1 and Note 1271072.1 Is it possible to deinstall/remove a specific component from already installed Oracle Database Home using OUI?
Starting with Oracle11g Release 2 (11.2), a new "chopt" utility is available on all platforms (including Windows) to disable Partitioning.  Refer to Note 948061.1 How to Check and Enable/Disable Oracle Binary Options.
chopt utility is available in 12c as well to enable or disable a database option.
Known Issue: for 10g Note 343642.1 The Option Partitioning Was Not Selected In The Custom Installation But It Is Still Installed
What's new in Oracle Database 12c?Please go through the note:
Note 1568010.1 12c Partitioning Enhancements
ReferencesNOTE:1188238.1 - How to enable Partitioning in  Oracle Database Enterprise Edition 11.2  on Windows
NOTE:225688.1 - How to resolve "ORA-439: Feature not enabled; Partitioning" error
NOTE:271886.1 - Differences Between Different Editions of Oracle Database 10G Release 1
NOTE:1271072.1 - Is it possible to deinstall/disable a specific licensable option from already installed Oracle Database 11gR2 Home ?
NOTE:456101.1 - Grid Control Repository: Everything you Wanted to Know About Partition Maintenance of Metric Data in the 10g/11g Repository
NOTE:942406.1 - Customize Enterprise Edition Options under 11.2.0.x version
NOTE:465460.1 - Differences Between Enterprise, Standard and Personal Editions on Oracle 11.1
NOTE:1568010.1 - 12c Partitioning Enhancements, New Features
NOTE:783290.1 - Oracle Database Software Component Guide for 11gR1
NOTE:948061.1 - How to Check and Enable/Disable Oracle Binary Options
NOTE:1468698.1 - How To Disable Partitioning And Impact On Licensing
NOTE:343642.1 - The Option Partitioning Was Not Selected In The Custom Installation But It Is Still Installed
NOTE:373383.1 - How to install Partitioning after Patchset Installation
NOTE:1069015.1 - How to determine enabled/disabled components in an 11.2 software-only installation
NOTE:1084132.1 - Differences Between Enterprise, Standard and Standard One Editions on Oracle 11.2
NOTE:434743.1 - Can The Partitioning Option Be Deinstalled When System Partitioned Objects Are Used
NOTE:888934.1 - Is it possible to deinstall/remove a specific component from already installed Oracle Database Home using OUI?
NOTE:465465.1 - Differences Between Enterprise, Standard and Personal Editions on Oracle 10.2