I need to understand how partitioning can be applied to tables for an oracle database. Please start a guide with me that hopefully with helps others who would like to learn as well. After the go live , the production database will grow very quickly. Currently a DBA on client side is partitioning the tables on the fly when it is needed and I am not sure what scripts or logic he is using. I will have to take over this responsibility soon.

My Job/Task/Goal: Each important table has specifications for the type of partitioning that will need to be implemented to handle the data. Which I will be applying.

For example using the following parameters to partition a table:

  1. Table: Clogs
  2. Schema: Schema name
  3. Range Partition on ISSUEDATE ( MAJORITY of the table will need range partitionin)
  4. Group partition by daily.
  5. Partition Naming Convention : clogmd_YYYYMMDD

Note: I have no idea where to start. I have a 5 years of IT and Systems Admin experience but I am not an expert but I am not a complete newbie either. I have paid my dues so please take it easy on me.

Some of the code I am working with is.

ALTER TABLE tableowner.tablename drop partition
partitionname_YYYYMMDD (respective partitionname)
ALTER TABLE tableowner.tablename split partition
maxpartitionname_YYYYMMmax at (to_date('YYYY-MM-DD   
00:00:00','SYYYY-MM-DDHH24:MI:SS'))  into (partition NAME_YYYYMMDD   
tablespace name_WXY1, partition NAME_201602MAX tablespace NAME_WXY2;
ALTER TABLE tableowner.tablename add partition NAME_YYYYMMDD
(respective PARTITIONNAME);

Please guide me and let me know of any considerations I should be taking. I greatly appreciate any help.

1 Answer 1


I can give you a few things to watch out for, but I'm not sure what level of detail that you're looking for.

Merging and splitting partitions can be an expensive operation in Oracle, except for a few special cases which are optimized, such as splitting off an empty partition. For example, if you have a single partition of 12 months and you split off a month then Oracle will need to rewrite the entire 12 months worth of data to complete the operation. If you want to split off another month then Oracle will need to rewrite 11 months worth of data to complete the operation, and so on. In Oracle 12c you have the option to split and merge N partitions in a single operation but I do not believe this is available in Oracle 11g. Because of this it can often be faster in 11g to just create a new table with the partitioning structure that you want and to load all data into that new table. Once the operation is done you can drop the old table and rename the new table.

You said that you have specifications for the partitioning. Do those specifications include the tablespaces for the partitions? Are there any existing indexes on the tables to be partitioned? If so, should those be defined as global or local indexes? Do you need to apply any kind of compression to the partitions? Perhaps you are using advanced compression for all compressions or basic compression for old partitions which will no longer have data modified in them. If all of that is documented, great. If not, you might need to do some analysis and make some decisions.

Depending on your requirements you may be able to use interval partitioning. With interval partitioning you can define your starting partitions upfront and also the size of the partitions that you want going forward as new data is loaded into the table. So if you want your table partitioned by a date column into single day partitions you don't need to write code to get a new partition for each day or to script it out. That can be done for you automatically by Oracle.

Here is an example of interval partitioning from one of the links below:

CREATE TABLE interval_sales
   ( prod_id        NUMBER(6)
   , cust_id        NUMBER
   , time_id        DATE
   , channel_id     CHAR(1)
   , promo_id       NUMBER(6)
   , quantity_sold  NUMBER(3)
   , amount_sold    NUMBER(10,2)

In that example query Oracle will automatically create monthly partitions for any data after TO_DATE('1-1-2010', 'DD-MM-YYYY'). Data before TO_DATE('1-1-2010', 'DD-MM-YYYY') will be loaded into one of the non-monthly partitions p0, p1, p2, or p3.

If you want examples of how to apply range partitioning or interval partitioning you could start here:

https://oracle-base.com/articles/11g/partitioning-enhancements-11gr1 https://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#BAJHFFBE https://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#insertedID1

I know that you aren't supposed to just provide links without summarizing information from them, but there's a lot of content in those links and I'm not quite sure what you're looking for.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.