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.
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.
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.
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