Let me share with you a vb.net console program I wrote to automate the creation of partition.
First lets create two tables:
Both tables are partitioned by date but in the first one the date is stored as varchar2 and in the second one it is stored as a date field.
CREATE TABLE TESTPART
(RECID NUMBER,
LOTID VARCHAR2(20),
OLD_LOTID VARCHAR2(20),
ACTIVITY VARCHAR2(40),
TXNTIME CHAR(18),
EXTDATE VARCHAR2(15),
FROMTIME VARCHAR2(18),
TOTIME VARCHAR2(18),
NOTES VARCHAR2(255),
BRIEFDESC VARCHAR2(255),
DETAILDESC VARCHAR2(255)
)
PARTITION BY RANGE (EXTDATE)
(PARTITION TESTPART0 VALUES LESS THAN ('20010101 080001'));
CREATE TABLE TESTPART2
( TIME_DATE DATE,
LOTID VARCHAR2(20),
ACTIVITY VARCHAR2(40),
STEPNAME VARCHAR2(60 BYTE),
REASONCODE VARCHAR2(20 BYTE),
REASONQTY NUMBER,
CATEGORY VARCHAR2(6 BYTE),
BONUSQTY NUMBER
)
PARTITION BY RANGE (TIME_DATE)
(PARTITION FW_WOP_DEF0 VALUES LESS THAN (TO_DATE('20010101', 'YYYYMMDD'));
In order to start the process, we need to know how to get information on the partition. This is available in USER_TAB_PARTITIONS view. Next you need to know how the know how the data is stored. The column High_value, stores high value of the partition key (meaning the max value the partition will store. The problem with this is that the column type is long() and cannot be analyzed directly within PL/SQL which is why I had to use an external program to analyze the value otherwise, I would have created a stored procedure to automate the process.
If the partition key is of date type, then it will be stored as (TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')). For varchar2, it will be stored as an actual value. This will allow us to check the partition key type that we use to perform the logic whether or not to create the partition.
To facilitate the automation, I follow the following convention:
- Have a reference partition which is the table name followed by '0' , so in the example above the table is testpart and the reference partition is testpart0.
- Then when I create the partition, I use a sequential numbering, ie. the partition will be testpart_1, testpart_2 etc.
- This is used so that when I automate the dropping of partition, I will not accidentally drop the reference partition.
- ReadXML will read and load the xml into an XMLDocument and point the xmlnode to the right node (<config>).
- chkKeyType will identify from the high_value whether the type used in the partition key column (varchar2 or Date).
- getDate will get the highest high_value of the partitions that exist for the table.
- procTab will go through the list of table provided in the XML. It will also compare the target date to create the partition and decide whether it is necessary to create the partition. If necessary then it will take the sequence from the last partition increment 1 and then use it as the name to create the new partition.
Finally, here is the code:
Imports System.XmlSince we typically may have multiple database instances or schema, I use an xml instead of the config file so that I can use one xml for each account/database and then have multiple tables in the xml. For example:
Imports System.Data.OleDb
' Create Partition where the partition key is a date field
' Author Strovek
' Date Jul 21, 2005
' Revised
'
' Rewrote the program to recognize the different date partition key.
' date field or string with the format "yyyymmdd hh24miss"
'
' =============================================================================
Public Class CrePartDate
Const DateKey As String = "DateKey"
Const StrKey As String = "StrKey"
Const InvalidKey As String = "InvKey"
Const pfxStr As String = "SELECT PARTITION_NAME, HIGH_VALUE, " _
& "TO_NUMBER(SUBSTR(PARTITION_NAME, LENGTH(TABLE_NAME)+2)) PARTSEQ FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = '"
Const lineStr As String = "================================"
Public Shared generalSwitch As New TraceSwitch("TraceLevelSwitch", "Entire Application")
Dim cfgFile As String = "..\crePartDate.xml"
Dim Conn As New OleDb.OleDbConnection
Dim connStr As String
Dim cfg As New XmlDocument
Dim root As XmlNode
Dim tabCollection As XmlNodeList
Dim keyType As String
Dim vConfigFound As Boolean = False
' Returns the version of this assembly
Public Shared Function Ver() As String
With _
System.Diagnostics.FileVersionInfo.GetVersionInfo(System.Reflection.Assembly.GetExecutingAssembly.Location)
Return .FileMajorPart & "." & .FileMinorPart & "." & .FileBuildPart & "." & .FilePrivatePart
End With
End Function
Public Function cfgFound() As Boolean
Return vConfigFound
End Function
' Property to set the configuration filename
Public Property cfgFName() As String
Get
cfgFName = cfgFile
End Get
Set(ByVal Val As String)
cfgFile = Val
End Set
End Property
' Reads the xml file
Public Sub readXML()
Dim flg As Boolean = False
Dim xNode As XmlNode
cfg.Load(cfgFile)
xNode = cfg.FirstChild
While True
Trace.WriteLineIf(generalSwitch.TraceVerbose, "xnode name is " & xNode.Name & "*")
If xNode.Name <> "config" Then
xNode = xNode.NextSibling
Else
vConfigFound = True
Exit While
End If
If IsNothing(xNode) Then
Exit While
End If
End While
root = xNode
End Sub
' Displays the Connection String from the xml file
Public Sub getConnStr()
Dim xNode As XmlNode
'xNode = cfg.SelectSingleNode("connStr")
xNode = root.SelectSingleNode("connStr")
Trace.WriteLineIf(generalSwitch.TraceVerbose, xNode.Attributes("Val").Value())
End Sub
' Gets the collection of Tables to process
Public Sub getTabs()
tabCollection = root.SelectNodes("tab")
End Sub
' Display the list of tables to be processed
Public Sub listTabs()
Dim cnt As Integer
For cnt = 0 To (tabCollection.Count - 1)
Console.Write(tabCollection(cnt).Attributes("Name").Value & " ")
Console.WriteLine(tabCollection(cnt).Attributes("Period").Value)
Next
End Sub
' Connect to database
Public Sub ConnectDB()
Dim connStr As String = root.SelectSingleNode("connStr").Attributes("Val").Value
Conn.ConnectionString = connStr
Conn.Open()
Trace.WriteLineIf(generalSwitch.TraceVerbose, "Database connected")
End Sub
' Closes the database connection if it is Open
Public Sub closeDB()
If Conn.State = ConnectionState.Open Then
Conn.Close()
Trace.WriteLineIf(generalSwitch.TraceVerbose, "Database connection Closed")
End If
End Sub
' Verify the key type for the table
Sub chkKeyType(ByVal tabName As String)
Dim dbCmd As OleDbCommand = Conn.CreateCommand
Dim dr As OleDbDataReader
Dim sql, tmpStr, tmpStr2 As String
Dim tmpDate As DateTime
keyType = InvalidKey
sql = pfxStr & tabName & "' and partition_name = '" & tabName & "0'"
dbCmd.CommandText = sql
Try
dr = dbCmd.ExecuteReader
If dr.HasRows Then
dr.Read()
tmpStr = dr.Item("high_value")
If Left(tmpStr, 7) = "TO_DATE" Then
keyType = DateKey
Else
Try
tmpStr2 = tmpStr.Substring(1, 8)
tmpDate = Date.ParseExact(tmpStr2, "yyyyMMdd", Nothing)
keyType = StrKey
Catch ex As Exception
keyType = InvalidKey
End Try
End If
End If
Finally
If Not IsNothing(dr) Then
dr.Close()
End If
End Try
dbCmd.Dispose()
End Sub
' Extract the date from the highvalue field and return it as datetime
Function getDate(ByVal highValue As String) As DateTime
Dim tmpStr As String
Dim tmpDate As DateTime
tmpStr = highValue.Substring(highValue.IndexOf("'") + 2, 19)
Trace.WriteLineIf(generalSwitch.TraceVerbose, tmpStr)
tmpDate = DateTime.ParseExact(tmpStr, "yyyy-MM-dd HH:mm:ss", Nothing)
Return tmpDate.Date
End Function
' Decide which method to extract the date portion of the date from the highvalue column
Function getPartDate(ByVal highval As String) As DateTime
Dim tmpDate As DateTime
Dim tmpStr As String
If keyType = DateKey Then
tmpDate = getDate(highval)
Else
tmpStr = highval.Substring(1, 8)
tmpDate = DateTime.ParseExact(tmpStr, "yyyyMMdd", Nothing)
End If
Return tmpDate.Date
End Function
' Routine to process all the tables
Public Sub procTab()
Dim loopCnt, daysAhead, partseq As Integer
Dim tabName, tmpStr, partName, sql As String
Dim dbCmd As OleDbCommand = Conn.CreateCommand
Dim dr As OleDbDataReader
Dim partDate, limDate As DateTime
Dim curDate As DateTime = Now()
Dim crePartFlg As Boolean
For loopCnt = 0 To (tabCollection.Count - 1)
tabName = UCase(tabCollection(loopCnt).Attributes("Name").Value)
daysAhead = tabCollection(loopCnt).Attributes("Period").Value
Console.WriteLine(vbNewLine & "Table " & tabName & " ")
chkKeyType(tabName)
If keyType <> InvalidKey Then
crePartFlg = False
If daysAhead > 0 Then
limDate = curDate.AddDays(daysAhead).Date
sql = pfxStr & tabName & "' and partition_name not like '" & tabName & "0' order by " _
& "to_number(substr(partition_name, length(table_name)+2)) desc"
dbCmd.CommandText = sql
Try
dr = dbCmd.ExecuteReader
If dr.HasRows Then
dr.Read()
tmpStr = dr.Item("high_value")
partDate = getPartDate(tmpStr)
Console.WriteLine("Date limit is " & Format(limDate, "dd-MMM-yyyy HH:mm"))
Console.WriteLine("Part Limit is " & Format(partDate, "dd-MMM-yyyy HH:mm"))
If DateTime.Compare(partDate, limDate) < crepartflg =" True" partseq =" dr.Item(" crepartflg =" True" partseq =" 1" partname =" tabName" keytype =" DateKey" sql = "alter table " sql = "alter table " tmpstr = "Create partition script " commandtext =" sql" datetime =" Now()" textwritertracelistener =" New"> 0 Then
' If parameter is ? then display the help
If args(0) = "?" Then
Console.WriteLine("Usage: " & vbNewLine & "crePartDate")
Exit Sub
Else
cPart.cfgFile = args(0)
End If
End If
cPart.readXML()
If cPart.cfgFound Then
cPart.getTabs()
' List table only if trace is verbose
If generalSwitch.TraceVerbose Then
cPart.listTabs()
End If
cPart.ConnectDB()
cPart.procTab()
End If
Catch ex As Exception
Console.WriteLine(vbNewLine & "Error " & vbNewLine & ex.Message)
Trace.WriteIf(generalSwitch.TraceVerbose, ex.StackTrace)
Finally
cPart.closeDB()
End Try
elapseTime = Now.Subtract(tmpDate)
Console.WriteLine(vbNewLine & "Elapse time " & elapseTime.Seconds & " seconds")
Console.WriteLine("End time " & Format(Now(), "dd-MMM-yyyy HH:mm"))
Console.WriteLine("End Program" & vbNewLine & lineStr)
End Sub
End Class
<?xml version="1.0" encoding="utf-8" ?>The period means how many days ahead I need to create the partition, in the example ahead it is 7 days so if today is May 17, 2008, the program will create a partition with the high_value of May 24, 2008. So to run the program, just execute crePartDate crePartDate.xml. With this you can then use the Windows scheduler to run either daily, weekly etc.
<!--
Document : crePartDate.xml
Created on : Jul 21, 2005
Modified : Jul 25, 2005
Author : Strovek
Description:
Configuration file for crePart Project
-->
<config>
<connStr Val="Provider=ORAOLEDB.Oracle;PASSWORD=tiger;User ID=scott;Data Source=orcl" />
<tab Name="TESTPART" Period="7" />
<tab Name="TESTPART2" Period="7" />
</config>
<!--
Modification History
-->
This concept can be done in any other language like C# and java.
No comments:
Post a Comment