private async Task<int?> GetSyntaxErrorIndex(string sqlText, CancellationToken cancellationToken)
{
using (var connection = new OracleConnection(_databaseModel.BackgroundConnectionString))
{
using (var command = connection.CreateCommand())
{
command.CommandText = OracleDatabaseCommands.GetSyntaxErrorIndex;
command.AddSimpleParameter("SQL_TEXT", sqlText);
var errorPositionParameter = command.AddSimpleParameter("ERROR_POSITION", null, TerminalValues.Number);
await connection.OpenAsynchronous(cancellationToken);
await command.ExecuteNonQueryAsynchronous(cancellationToken);
await connection.CloseAsynchronous(cancellationToken);
var result = (OracleDecimal)errorPositionParameter.Value;
return result.IsNull ? null : (int?)result.Value;
}
}
}
private async Task SetSessionTracing(OracleSessionValues sessionData, string commandText, CancellationToken cancellationToken)
{
using (var connection = new OracleConnection(BackgroundConnectionString))
{
using (var command = connection.CreateCommand())
{
command.BindByName = true;
command.CommandText = commandText;
command.AddSimpleParameter("SID", sessionData.Id);
command.AddSimpleParameter("SERIAL", sessionData.Serial);
await connection.OpenAsynchronous(cancellationToken);
await command.ExecuteNonQueryAsynchronous(cancellationToken);
await connection.CloseAsynchronous(cancellationToken);
}
}
}
private async Task<Exception> ResolveExecutionPlanIdentifiersAndTransactionStatus(CancellationToken cancellationToken)
{
using (var connection = new OracleConnection(_databaseModel.BackgroundConnectionString))
{
using (var command = connection.CreateCommand())
{
command.BindByName = true;
command.CommandText = OracleDatabaseCommands.SelectExecutionPlanIdentifiersCommandText;
command.AddSimpleParameter("SID", _userSessionIdentifier.Value.SessionId);
try
{
await connection.OpenAsynchronous(cancellationToken);
connection.ModuleName = $"{_moduleName}/BackgroundConnection".EnsureMaximumLength(64);
connection.ActionName = "Fetch execution info";
_userCommandSqlId = null;
using (var reader = await command.ExecuteReaderAsynchronous(CommandBehavior.Default, cancellationToken))
{
if (!await reader.ReadAsynchronous(cancellationToken))
{
return null;
}
_userCommandSqlId = OracleReaderValueConvert.ToString(reader["SQL_ID"]);
_userCommandChildNumber = OracleReaderValueConvert.ToInt32(reader["SQL_CHILD_NUMBER"]) ?? 0;
var undoSegmentNumber = OracleReaderValueConvert.ToInt32(reader["XIDUSN"]);
if (undoSegmentNumber.HasValue)
{
_userTransactionInfo =
new TransactionInfo
{
UndoSegmentNumber = undoSegmentNumber.Value,
SlotNumber = Convert.ToInt32(reader["XIDSLOT"]),
SequnceNumber = Convert.ToInt32(reader["XIDSQN"]),
IsolationLevel = (IsolationLevel)Convert.ToInt32(reader["TRANSACTION_ISOLATION_LEVEL"])
};
}
var currentSchema = Convert.ToString(reader["SCHEMANAME"]);
_databaseModel.SetCurrentSchema(currentSchema);
}
return null;
}
catch (OracleException e)
{
Trace.WriteLine($"Execution plan identifers and transaction status could not been fetched: {e}");
return e;
}
finally
{
await connection.CloseAsynchronous(cancellationToken);
}
}
}
}
public async Task<DatabaseSessions> GetAllSessionDataAsync(CancellationToken cancellationToken)
{
var databaseSessions = new DatabaseSessions();
using (var connection = new OracleConnection(BackgroundConnectionString))
{
using (var command = connection.CreateCommand())
{
command.CommandText = OracleDatabaseCommands.SelectBasicSessionInformationCommandText;
await connection.OpenAsynchronous(cancellationToken);
connection.ModuleName = "Database monitor";
using (var reader = await command.ExecuteReaderAsynchronous(CommandBehavior.Default, cancellationToken))
{
databaseSessions.ColumnHeaders = DatabaseSessionColumnHeaders;
var sessions = new Dictionary<int, DatabaseSession>();
while (await reader.ReadAsynchronous(cancellationToken))
{
var oracleSession =
new OracleSessionValues
{
Process = OracleReaderValueConvert.ToString(reader["PROCESS"]),
ExecutionId = OracleReaderValueConvert.ToInt32(reader["SQL_EXEC_ID"]),
Type = (string)reader["TYPE"],
Id = Convert.ToInt32(reader["SID"]),
ExecutionStart = OracleReaderValueConvert.ToDateTime(reader["SQL_EXEC_START"]),
Action = OracleReaderValueConvert.ToString(reader["ACTION"]),
State = (string)reader["STATE"],
Status = (string)reader["STATUS"],
AuditingSessionId = Convert.ToInt64(reader["AUDSID"]),
ChildNumber = OracleReaderValueConvert.ToInt32(reader["SQL_CHILD_NUMBER"]),
ClientInfo = OracleReaderValueConvert.ToString(reader["CLIENT_INFO"]),
CurrentCommandText = OracleReaderValueConvert.ToString(await reader.GetValueAsynchronous(reader.GetOrdinal("CURRENT_COMMAND_TEXT"), cancellationToken)),
Event = (string)reader["EVENT"],
FailedOver = (string)reader["FAILED_OVER"],
FailoverMethod = (string)reader["FAILOVER_METHOD"],
FailoverType = (string)reader["FAILOVER_TYPE"],
LockWait = OracleReaderValueConvert.ToString(reader["LOCKWAIT"]),
LogonTime = (DateTime)reader["LOGON_TIME"],
Machine = OracleReaderValueConvert.ToString(reader["MACHINE"]),
Module = OracleReaderValueConvert.ToString(reader["MODULE"]),
OperatingSystemUser = OracleReaderValueConvert.ToString(reader["OSUSER"]),
ParallelDdlStatus = (string)reader["PDDL_STATUS"],
ParallelDmlEnabled = (string)reader["PDML_ENABLED"],
ParallelDmlStatus = (string)reader["PDML_STATUS"],
ParallelQueryStatus = (string)reader["PQ_STATUS"],
Parameter1 = Convert.ToDecimal(reader["P1"]),
Parameter1Text = OracleReaderValueConvert.ToString(reader["P1TEXT"]),
Parameter2 = Convert.ToDecimal(reader["P2"]),
Parameter2Text = OracleReaderValueConvert.ToString(reader["P2TEXT"]),
Parameter3 = Convert.ToDecimal(reader["P3"]),
Parameter3Text = OracleReaderValueConvert.ToString(reader["P3TEXT"]),
OwnerSessionId = OracleReaderValueConvert.ToInt32(reader["OWNER_SID"]),
Port = OracleReaderValueConvert.ToInt32(reader["PORT"]),
PrecedingChildNumber = OracleReaderValueConvert.ToInt32(reader["PREV_CHILD_NUMBER"]),
PrecedingCommandText = OracleReaderValueConvert.ToString(await reader.GetValueAsynchronous(reader.GetOrdinal("PRECEDING_COMMAND_TEXT"), cancellationToken)),
PrecedingExecutionId = OracleReaderValueConvert.ToInt32(reader["PREV_EXEC_ID"]),
PrecedingExecutionStart = OracleReaderValueConvert.ToDateTime(reader["PREV_EXEC_START"]),
PrecedingSqlId = OracleReaderValueConvert.ToString(reader["PREV_SQL_ID"]),
ProcessAddress = (string)reader["PADDR"],
Program = OracleReaderValueConvert.ToString(reader["PROGRAM"]),
RemainingTimeMicroseconds = OracleReaderValueConvert.ToInt64(reader["TIME_REMAINING_MICRO"]),
ResourceConsumeGroup = OracleReaderValueConvert.ToString(reader["RESOURCE_CONSUMER_GROUP"]),
SchemaName = OracleReaderValueConvert.ToString(reader["SCHEMANAME"]),
Serial = Convert.ToInt32(reader["SERIAL#"]),
Server = (string)reader["SERVER"],
ServiceName = OracleReaderValueConvert.ToString(reader["SERVICE_NAME"]),
Instance = Convert.ToInt32(reader["INSTANCE_ID"]),
SessionAddress = (string)reader["SADDR"],
SqlId = OracleReaderValueConvert.ToString(reader["SQL_ID"]),
SqlTrace = (string)reader["SQL_TRACE"],
TimeSinceLastWaitMicroseconds = OracleReaderValueConvert.ToInt64(reader["TIME_SINCE_LAST_WAIT_MICRO"]),
TransactionAddress = OracleReaderValueConvert.ToString(reader["TADDR"]),
UserName = OracleReaderValueConvert.ToString(reader["USERNAME"]),
WaitClass = (string)reader["WAIT_CLASS"],
WaitTime = OracleReaderValueConvert.ToInt64(reader["WAIT_TIME"]),
WaitTimeMicroseconds = OracleReaderValueConvert.ToInt64(reader["WAIT_TIME_MICRO"]),
ProcessIdentifier = OracleReaderValueConvert.ToInt32(reader["PID"]),
OperatingSystemIdentifier = OracleReaderValueConvert.ToInt32(reader["SOSID"]),
OperatingSystemProcessIdentifier = OracleReaderValueConvert.ToInt32(reader["SPID"]),
TraceId = OracleReaderValueConvert.ToString(reader["TRACEID"]),
TraceFile = OracleReaderValueConvert.ToString(reader["TRACEFILE"]),
ProgramGlobalAreaUsedMemoryBytes = OracleReaderValueConvert.ToInt64(reader["PGA_USED_MEM"]),
ProgramGlobalAreaAllocatedMemoryBytes = OracleReaderValueConvert.ToInt64(reader["PGA_ALLOC_MEM"]),
ProgramGlobalAreaFreeableMemoryBytes = OracleReaderValueConvert.ToInt64(reader["PGA_FREEABLE_MEM"]),
ProgramGlobalAreaMaximumMemoryBytes = OracleReaderValueConvert.ToInt64(reader["PGA_MAX_MEM"])
};
var databaseSession =
new DatabaseSession
{
Id = oracleSession.Id,
ProviderValues = oracleSession,
Type = String.Equals(oracleSession.Type, "User") ? SessionType.User : SessionType.System,
IsActive = Convert.ToString(oracleSession.Status) == "Active"
};
sessions.Add(databaseSession.Id, databaseSession);
}
foreach (var session in sessions.Values)
{
DatabaseSession ownerSession;
var ownerSid = ((OracleSessionValues)session.ProviderValues).OwnerSessionId;
if (ownerSid.HasValue && sessions.TryGetValue(ownerSid.Value, out ownerSession))
{
session.Owner = ownerSession;
ownerSession.ChildSessions.Add(session);
}
}
databaseSessions.Rows = sessions.Values.ToArray();
}
}
await connection.CloseAsynchronous(cancellationToken);
}
return databaseSessions;
}
