Archive for March, 2009

Oracle: Convert a partitioned table into an unpartitioned one

License fees can bee massive. So for some machines it’s just useless to buy Oracle Enterprise Edition, sometimes Standard Edition is simply enough. Migration to SE for a schema with partitioned tables leaves you one option: CTAS all partitioned tables and export them, reimport them on your new box and modify all DBMS_METATDATA-generated DDLs so that all constraints and indexes still fit.

For this case or for other cases it might be useful to simply “remove partitioning” from a table, preserving all dependent objects either valid (most) or running with a quick recompile (PL/SQL). The attached SQL script shows how to unpartition all tables of a given user, using DBMS_METADTA. You may want to change or remove the big loop wrapping the whole DBMS_REDEFINITION stuff to make some finer selection.

The unpartitioning works with a dummy table. It’s created “as select *” within the loop, used by the package, and dropped in the end of the iteration. The package duplicates all constraints, indexes and other metadata to the interim table’s values, and renames all that in the end. Since you created the table in the beginning “as select”, all data is already there. Finally, you have the option to synchronize original and dummy, so nothing will be lost in the end.


The script is rather simple, licensed under GPLv2, and considered as a starting point for future working with DBMS_REDEFINITION. This package is a very useful tool, once you discovered it’s possibilities.

If you find bugs, misses or have additions to make, feel free to contact me.


By Martin Klier in Oracle  .::. Read Comments (3)

Talk: IT Performance

On Thuesday, March 24th, 2009 at 9 a.m. I will speak at Berufsschule Wiesau about general matters of IT performance.

Papers and the presentation are available online now:

Handout “IT-performance”


Martin Klier

EDIT: That’s a nice article about the event on the school’s webpage. Thanks!

By Martin Klier in Lectures  .::. (Add your comment)

Oracle: Specify the number of occurrences before metric alerting starts

It’s not possible to specify a minimum of consecutive occurrences before alerting starts in in Enterprise Manager, it will always scream out at first time touching the line. But you can do so in command line. Just execute DBMS_SERVER_ALERT.SET_THRESHOLD with parameter consecutive_occurrences set to a value >1. It’s a very nice way to stop annoying one-time-alerts!

Just another hint, how I found out: Sometimes you want to display the package header informations, mostly for packages shipped by Oracle. :) One example is the above-mentioned DBMS_SERVER_ALERT, that specifies metric values. It’s not easy to find out which meatric means what or generally what (undocumented?) feature a package provides, but

select text from dba_source
and type='PACKAGE'
order by line;

may help a bit.

Take care

By Martin Klier in Oracle  .::. (Add your comment)

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.