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; }