1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
|
Imports System.Data.OleDb
Imports System.IO
Module Module1
Public P_cnOracle As OleDbConnection
Public etat As String = "OK"
Sub Main()
P_cnOracle = New OleDbConnection("Provider=MSDAORA.0; Data Source=****;User Id=***;Password=****;")
P_cnOracle.Open()
Dim tab As String() = RecupererLock()
File.WriteAllLines("lock.conf", tab)
Console.Write(VerifierLock())
End Sub
Function RecupererLock()
Dim LDaOracleListeBDE As OleDbDataAdapter
Dim LDtOracleListeBDE As New DataTable
Dim LDrOracleListeBDE As DataRow
Dim LRequeteSelect As String = ""
Dim MesLocks(18) As String
Dim indice As Integer = 0
LRequeteSelect = "select decode(L.BLOCK, 0, ' ', '>>> X <<<') BLOCK, " & _
"S.SID SID, " & _
"S.SERIAL# SERIAL, " & _
"decode(L.BLOCK, 0, ' ', 'ALTER SYSTEM KILL SESSION '''||S.SID||','||S.SERIAL#||''';') KILL, " & _
"rpad(S.USERNAME, 6) UTILISAT, " & _
"S.PROGRAM PROGRAM, " & _
"S.TERMINAL TERM, " & _
"S.PROCESS PID, " & _
"L.ID1 ID1, " & _
"rpad(O1.OBJECT_NAME, 10) OBJET, " & _
"decode(L.TYPE,'RW','row wait','TM','DML','TX','TRANSAC','UL','USER') TYPE, " & _
"decode(L.LMODE,1,NULL,2,'RS',3,'RX',4,'SH',5,'SRX',6,'X') POSE, " & _
"decode(L.REQUEST,1,NULL,2,'RS',3,'RX',4,'SH',5,'SRX',6,'X') DMDE, " & _
"NVL(SA.SQL_TEXT,'No SQL') SQL " & _
"from V$SESSION S, V$LOCK L, DBA_OBJECTS O1, V$SQLAREA SA " & _
"WHERE(S.USERNAME Is Not NULL) " & _
"AND S.PROCESS is NOT NULL " & _
"AND L.ID1=O1.OBJECT_ID(+) " & _
"AND L.SID=S.SID " & _
"AND S.SQL_ADDRESS = SA.ADDRESS(+) " & _
"order by BLOCK"
LDaOracleListeBDE = New OleDbDataAdapter(LRequeteSelect, P_cnOracle)
LDaOracleListeBDE.Fill(LDtOracleListeBDE)
For Each LDrOracleListeBDE In LDtOracleListeBDE.Rows
MesLocks(indice) = LDrOracleListeBDE("PID").ToString() & LDrOracleListeBDE("ID1").ToString()
indice = indice + 1
Next
Return MesLocks
End Function
Function VerifierLock()
'Verifie si on passe en critique
Using readerlock1 As StreamReader = New StreamReader("lock.conf")
Dim i As Integer = 0
' Read one line from file
Dim line = readerlock1.ReadLine
Using readerchecklock As StreamReader = New StreamReader("warning.conf")
Dim line2 = readerchecklock.ReadLine
If line <> "" And line2 <> "" Then
If line = line2 Then
i = i + 1
etat = "CRITICAL"
End If
End If
End Using
End Using
Dim MesCheckLocks(18) As String
Using readerlock2 As StreamReader = New StreamReader("lock.conf")
Dim i As Integer = 0
' Read one line from file
Dim line = readerlock2.ReadLine
Using readerlock3 As StreamReader = New StreamReader("checkLock.conf")
Dim line2 = readerlock3.ReadLine
If line <> "" And line2 <> "" Then
If line = line2 Then
MesCheckLocks(i) = line
i = i + 1
If etat <> "CRITICAL" Then
etat = "WARNING"
End If
End If
End If
End Using
End Using
File.WriteAllText("checkLock.conf", File.ReadAllText("lock.conf"))
File.WriteAllLines("warning.conf", MesCheckLocks)
Return etat
End Function
End Module |
