Partitioning a Non-Partitioned Oracle system
October 23, 2007
Find out how to bring back manageability and responsiveness using Database Partitioning on your current Non-Partitioned system.
As your Data warehouse and OLTP databases explode in growth from gigabytes to terabytes, beyond any initial design and planning, the responsiveness of the database and the manageability begin to suffer. The roles of Data Architect and DBA are to understand the data and plan for such growth. Yet, even with all the hours spent on initial planning, things change over time. As part of your role, you must manage the complex growth tossed over the cube wall day-to-day and spend countless hours in attempts to raise performance.
If this sounds like you and your current system, then here is the solution for you, something that has been lying around waiting to help you grab a hold of manageability and responsiveness - Database Partitioning. This article will provide you a little help with basic step-by-step instructions and scripting examples, so you can easily make partitioning possible at anytime.
Background / Overview
The purpose of this article will show how you can safely and easily transform non-partitioned tables into partitioned tables. This article shows a general approach to help you get a handle on performance and manageability.
The first step to keep in mind is that all table related objects need to be tracked:
In order to do this, the script below will generate a new DDL script for all table related objects, along with the partitioning syntax and existing data. It will also reproduce all existing related objects to support the new schema.
The transformation process is created in such a way that we are not deleting any of the non-partitioned tables, they will still exist in the database. This allows us to restore non-partitioned tables if any errors are encountered during the transformation procedure. This is done by renaming the non-partitioned table, its index(s) and trigger(s), and all of its user-named constraints.
p_Owner Varchar2, p_Table Varchar2
p_Data_Tblspc Varchar2, p_Index_Tblspc Varchar2
Initialization and setup