This code makes the following assumption:
- Partition is by date
- The first partition is the table name postfix with zero (e.g. if table is testList and then the name of the first partition should be testList0).
- Subsequent partition should have the name of the table postfix with _ and the a number (e.g. testList_0, testList_1 ...)
Here is the code:
As in creating partition, I also use an xml instead of the config file. This is to allow me to drop partition from multiple database instance by supporting each database with a separate xml file. Here is a sample xml file:
Imports System.Xml
Imports System.Data.OleDb
' Drop Partition where the partition key is a date field
' Author Strovek
' Date Jul 21, 2005
' Revised Jul 25, 2005
'
' =============================================================================
Public Class DropPartDate
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 = "..\DropPartDate.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 whether the xml configuration is found
Public Function cfgFound() As Boolean
Return vConfigFound
End Function
' 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
' 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, dropCnt, daysAhead As Integer
Dim sql, tabName, tmpStr, partName As String
Dim partRec As clsPart
Dim dbCmd As OleDbCommand = Conn.CreateCommand
Dim dr As OleDbDataReader
Dim partDate, limDate As DateTime
Dim curDate As DateTime = Now
Dim dropPartList As New ArrayList
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)
dropPartList.Clear()
chkKeyType(tabName)
If keyType <> InvalidKey Then
limDate = curDate.AddDays(daysAhead).Date
Trace.WriteLineIf(generalSwitch.TraceVerbose, "Date Limit is " & Format(limDate, "dd-MMM-yyyy HH:mm"))
sql = pfxStr & tabName & "' and partition_name not like '" & tabName & "0' order by " _
& "partseq"
Trace.WriteLineIf(generalSwitch.TraceVerbose, "Get partition list sql is " & vbNewLine & sql)
Try
dbCmd.CommandText = sql
dr = dbCmd.ExecuteReader
If dr.HasRows Then
While dr.Read
tmpStr = dr.Item("high_value")
partDate = getPartDate(tmpStr)
partName = dr.Item("partition_name")
partRec = New clsPart
partRec.Name = partName
partRec.LimDate = partDate
Trace.WriteLineIf(generalSwitch.TraceVerbose, partName & " " & Format(partDate, "dd-MMM-yyyy"))
If DateTime.Compare(partDate, limDate) < 0 Then
dropPartList.Add(partRec)
Trace.WriteLineIf(generalSwitch.TraceVerbose, partRec.Name & " " & Format(partRec.LimDate, "dd-MMM-yyyy") & " should be dropped")
Else
Trace.WriteLineIf(generalSwitch.TraceVerbose, partRec.Name & " " & Format(partRec.LimDate, "dd-MMM-yyyy") & " does not qualify")
End If
End While
End If
Finally
If Not IsNothing(dr) Then
dr.Close()
End If
End Try
' If there are partitions that qualify for dropping
If dropPartList.Count > 0 Then
Console.WriteLine(vbNewLine & "Date Limit is " & Format(limDate, "dd-MMM-yyyy HH:mm"))
For dropCnt = 0 To (dropPartList.Count - 1)
partRec = dropPartList(dropCnt)
Trace.WriteLineIf(generalSwitch.TraceVerbose, "Dropping " & partRec.Name & " with " & Format(partRec.LimDate, "dd-MMM-yyyy"))
sql = "alter table " & tabName & " drop partition " & partRec.Name
dbCmd.CommandText = sql
dbCmd.ExecuteNonQuery()
Trace.WriteLineIf(generalSwitch.TraceVerbose, "Drop partition sql is " & vbNewLine & sql)
Console.WriteLine("Dropped " & partRec.Name & " with " & Format(partRec.LimDate, "dd-MMM-yyyy"))
Next
Else
Console.WriteLine("No partitions qualify")
End If
Else
Console.WriteLine(tabName & " is an invalid table")
End If
Next
dbCmd.Dispose()
End Sub
Public Shared Sub Main(ByVal args() As String)
Dim dPart As New DropPartDate
Dim tmpDate As DateTime = Now()
Dim elapseTime As TimeSpan
Console.WriteLine(vbNewLine & "Create Partition (Date) - Version " & dPart.Ver & vbNewLine & lineStr)
Console.WriteLine(Format(Now(), "dd-MMM-yyyy HH:mm"))
Dim writer As TextWriterTraceListener = New TextWriterTraceListener(System.Console.Out)
System.Diagnostics.Debug.Listeners.Add(writer)
If generalSwitch.TraceVerbose Then
Console.WriteLine("Trace is verbose")
End If
Try
' Checks to see if a parameter is provided
If args.Length > 0 Then
' If parameter is ? then display the help
If args(0) = "?" Then
Console.WriteLine("Usage: " & vbNewLine & "crePartDate <configFileName>")
Exit Sub
Else
dPart.cfgFile = args(0)
End If
End If
dPart.readXML()
If dPart.cfgFound Then
dPart.getTabs()
' List table only if trace is verbose
If generalSwitch.TraceVerbose Then
dPart.listTabs()
End If
dPart.ConnectDB()
dPart.procTab()
End If
Catch ex As Exception
Console.WriteLine(vbNewLine & "Error " & vbNewLine & ex.Message)
Trace.WriteIf(generalSwitch.TraceVerbose, ex.StackTrace)
Finally
dPart.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
Class clsPart
Dim vName As String
Dim vLim As Date
Public Property Name() As String
Get
Return vName
End Get
Set(ByVal Value As String)
vName = Value
End Set
End Property
Public Property LimDate() As Date
Get
Return vLim
End Get
Set(ByVal Value As Date)
vLim = Value
End Set
End Property
End Class
<config>
<connStr Val="Provider=ORAOLEDB.Oracle;PASSWORD=tiger;User ID=scott;Data Source=orcl" />
<tab Name="TESTPART" Period="-200" />
<tab Name="TESTPART2" Period="-300" />
<tab Name="MFGTRANS" Period="-250" />
<tab Name="PARTMASTER" Period="-200" />
</config>
