Partitioning a Non-Partitioned Oracle system

by

Alexander Yanushkevich and Bryan Hinds

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.

Preliminary Setup

The first step to keep in mind is
that all table related objects need to be tracked:

• Primary keys
• Unique keys
• Foreign keys
• Check constraint
• Triggers
• Indexes
• Synonyms
• Comments
• Grants to users
• Grants to roles
• Referenced objects

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.

Input parameters

• First you must define an owner for the new partitioned table and
  define the name of the table under transformation:

	p_Owner           Varchar2,

	p_Table           Varchar2

• Next, you must define where data and indexes are to be stored:

	

	p_Data_Tblspc     Varchar2,

	p_Index_Tblspc    Varchar2

• In addition, you can pass a partitioned clause to make the
  transformation procedure unified. This allows for the partitioning transformation
  to work with any table:

	

	p_Partitioning_Claus Varchar2

Initialization and setup

• If any database object is generated by a DBA or Architect, the
  length of the object name must be reduced to your length standards. The
  following procedure checks the length of names and either allows it or
  generates a new unique object name:

  	
  
  	Function Get_Name(p_Possible_Name Varchar2) Return Varchar2 Is
  
  	       v_Resulting_Name Varchar2(30);
  
  	       v_Prefix         Varchar2(4) Default ‘GEN_’;
  
  	Begin
  
  	       If Length(p_Possible_Name) > 30 Then
  
  	              v_Resulting_Name := Substr(To_Char(Rawtohex(Sys_Guid())), 1, 26);
  
  	              v_Resulting_Name := v_Prefix || v_Resulting_Name;
  
  	       Else
  
  	              v_Resulting_Name := p_Possible_Name;
  
  	       End If;
  
  	       Return v_Resulting_Name;
  
  	End;
  
  

• Incoming parameters can be passed in upper or lower case, but
  will be transformed to upper case by default:

  	
  
  	v_Table     := Upper(p_Table);
  
  	v_Owner     := Upper(p_Owner);
  
  

• All tables under this transformation are renamed to free up a
  name for the new partitioned table, which will be duplicated to a
  non-partitioned table:

  	
  
  	v_Table_Old := Get_Name(v_Table || ‘_OLD’);
  
  

• The package dbms_metadata is used during the transformation
  procedure. Normally dbms_metadata.get_ddl function will return a DDL script
  that defines the storage clause and segment attributes (physical attributes,
  storage attributes, tablespace, login). If dbms_metadata.get_ddl is used to get
  table DDL statements, then it also returns all constraints definitions. To
  suppress storage clause, segment attributes and constraints, execute the
  following procedures:

  	
  
  	— 0. setup transformation to reduce appearance of storage,constraints
  
  	in ddl 
  
  	—
  
  	coming out from get_ddl
  
  	Dbms_Metadata.Set_Transform_Param(Dbms_Metadata.Session_Transform, ‘STORAGE’, False);
  
  	Dbms_Metadata.Set_Transform_Param(Dbms_Metadata.Session_Transform, ‘CONSTRAINTS’, False);
  
  	Dbms_Metadata.Set_Transform_Param(Dbms_Metadata.Session_Transform, ‘SEGMENT_ATTRIBUTES’, False);