Automated Database Partitioning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Automated Database Partitioning

  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Automated Database Partitioning

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by skiskis
    2. Create partitions for each of the new tablespaces
    I don't even know what this means.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by skiskis
    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

  4. #4
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by skiskis
    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.
    "What is past is PROLOGUE"

  5. #5
    Join Date
    Aug 2006
    Posts
    3
    Quote Originally Posted by dbasan
    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.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by slimdave
    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.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by ixion
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Aug 2006
    Posts
    3
    Quote Originally Posted by slimdave
    ... 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width