Partitioning a Non-Partitioned Oracle system


Alexander Yanushkevich and Bryan Hinds

Find out how to bring back manageability and
responsiveness using Database Partitioning on your current ”Non-Partitioned”

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_’;
    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;
    v_Resulting_Name := p_Possible_Name;
    End If;
    Return v_Resulting_Name;
  • 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);
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.

Latest Articles