Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 23, 2007

Partitioning a Non-Partitioned Oracle system

By DatabaseJournal.com Staff

by Alexander Yanushkevich and Bryan Hinds

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.

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);

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM