Automated Database Partitioning

[skiskis]

Hello,

I have been tasked with coming up with a solution to perform automated partitioning of a very large Oracle 10g database. It was suggested that I make use of an external application or script written in Java or Perl to perform this task. Other suggestions have been to make use of the DBMS_SCHEDULER package or a shell script and crontab.

I'm a relatively junior DBA and I'm not terribly familiar with Perl or Java, so I'd prefer to use a shell script or even PL/SQL and the DBMS_SCHEDULER package if possible/feasible.

Essentially, I am attempting to perform the following task(s):

1. Check to see if any new tablespaces have been created
2. Create partitions for each of the new tablespaces
3. Check to see if additional tablespaces are required and if so, send an email to DBA with a list of tables that will need additional tablespaces

I can provide more details with respect to my planning if required.

Any assistance would be most appreciated!

Thanks in advance,

Scott.

[marist89]

2. Create partitions for each of the new tablespaces

I don't even know what this means.

[davey23uk]

Hello,

3. Check to see if additional tablespaces are required and if so, send an email to DBA with a list of tables that will need additional tablespaces

or that, how do you know more tablespaces are requires? if you have tables they already have tablespaces associated with them.

It's not clear what your goal is

[dbasan]

Hello,

I can provide more details with respect to my planning if required.

Advice to read the manual and know what is possible within the scope of Oracle.

[skiskis]

Advice to read the manual and know what is possible within the scope of Oracle.

I apologize for being vague in my request. After further analysis, I've decided that I would like to code a procedure, etc. in PL/SQL which can be sheduled via a UNIX cron job. With this in mind, I've included a "spec" of what I'd like to accomplish. I'm looking for some insight regarding the automation of table partitioning. Has anyone done this before and if so, am I on the right path? Any examples, advice would be appreciated.

Here's my "spec":

Several large tables need to be partitioned on a daily basis. The partitions are stored in their own tablespaces.

1. Create a table that will contain info about the partitioned tables.

Table Name: PARTITION_STAT
Columns: table_name, tablespace_name, partition_frequency (e.g. daily, weekly, etc), partition_name, max_partition_date

Note: This table will need to be udpated whenever a new table using partitioning is created.

2. Create a table that will contain the rowid of the last tablespace created.

Table Name: TABLESPACE_STAT
Column(s): last_tablespace_rowid

PL/SQL package requirements:

Need to create a procedure that will run automatically. The logic requirements are as follows:

1. Check to see if a new tablespace has been created. This is done by checking TABLESPACE_STAT.last_tablespace_rowid. All rowid's greater than last_tablespace_rowid are new tablespaces. Update the last_tablespace_rowid in the TABLESPACE_STAT table

2. Create partitions for the new tablespaces. For every new tablespace: create new partion and update the PARTITION_STAT table

3. Check to see if new tablespaces are required and send email notification to DBA if tablespaces are needed. This is accomplished by checking every max_partition_date and partition_frequency in PARTITION_STAT table. Send an email to DBA with list of tables that will require new tablespaces.

I realize that this is a very general overview of what I am looking to accomplish. Any assistance would be greatly appreciated.

Thanks and sorry for the long post.

Scott.

[slimdave]

I think you should just be using the dba_tab_partitions and dba_tablespaces views for this. Use a good naming convention for the partitions that lets you relate them to the period of data they contain and the granularity of the partitioning ... eg Y2006M01, Y2006M04D23 etc..

I'd use DBMS_JOB or DBMS_SCHEDULER instead of cron though

[ixion]

I'd use DBMS_JOB or DBMS_SCHEDULER instead of cron though

I second that! Its a bummer when the sysadmin or security people disable cron privileges for your oracle database admin account that runs the partitioning.

[slimdave]

I second that! Its a bummer when the sysadmin or security people disable cron privileges for your oracle database admin account that runs the partitioning.

... and also when you move the database to another server and find that the cron jobs didn't go with it

[skiskis]

... and also when you move the database to another server and find that the cron jobs didn't go with it

Thanks for the information slimdave and ixion. I really appreciate the feedback. I'm working on putting the process together in PL/SQL and will definitely post my code for review/critique.

Thanks again,

Scott