def wait_for_execution(t): """Note that a query started manually from IDE will stay "executing" until you fetch all it's rows.""" \ """ In such case "Wait for session" can be helpful.""" if not t.data: r = execute(t.target, "select min(sql_exec_start) from v$sql_monitor" " where sql_id = :sql_id and status = 'EXECUTING'" , t.parameters , 'one' , False) if not r[0]: return t.abort(f"SQL {t.parameters['sql_id']} Not found") else: t.data = r[0] return r = execute(t.target , "select nvl(sum(case when status = 'EXECUTING' then 1 else 0 end), 0) e" ", nvl(sum(case when status like 'DONE%' then 1 else 0 end), 0) d" ", max(status) s" " from v$sql_monitor where sql_id = :sql_id and sql_exec_start >= :start_time" , {'sql_id': t.parameters['sql_id'], 'start_time': t.data} , 'one' , False) if r[0] + r[1] == 0: return t.abort(f"SQL {t.parameters['sql_id']} Not found") if r[0] > 0: return if t.reply_to_message_id: return t.finish(r[2].lower()) return t.finish('{} on {} is {}.'.format(t_link(f"{t.target}/Q/{t.parameters['sql_id']}", t.parameters['sql_id']) , t.target , r[2].lower()))
def wait_for_sql_error(t): """This task shows sql errors, stored in sql_monitor cache. Errors, displaced from the cache, will be lost.""" \ """ A good approach is creating a trigger "after servererror".""" if not t.data: t.data = {'start_date': t.create_date} end_date = datetime.now() r = execute( t.target, "select username, sql_id, sid, error_message" " from v$sql_monitor" " where status = 'DONE (ERROR)'" " and error_number not in (1013, 28, 604, 24381)" # cancel, kill, recursive, DML array " and last_refresh_time between :start_date and :end_date" " and (username not like :user_name or username is null)", { 'start_date': t.data['start_date'], 'end_date': end_date, 'user_name': t.optional.get('ignore_user', '---') }, 'many', False) t.data['start_date'] = end_date return t.get_message( r, lambda o, i: '{} ({}, {}) on {} is failed ({}).'.format( t_link(f'{o.target}/Q/{i[1]}', i[1]), i[2], i[0], o.target, i[3]. replace('\n', ' ')))
def wait_for_heavy(t): r = execute( t.target, "select username, sql_id, exec_time_min, temp_usage_gb, exec_id, sid from" " (select s.username, m.sql_id, to_char(round(elapsed_time / 60000000)) exec_time_min, s.sid," " m.sql_id || to_char(m.sql_exec_id) || to_char(m.sql_exec_start, 'yyyymmddhh24miss') exec_id," " rtrim(to_char(((nvl(sum(u.blocks), 0) * min(p.value)) / 1024 / 1024 / 1024), 'fm999990d99')" ", to_char(0,'fmd')) temp_usage_gb" " from v$session s" " left join v$sort_usage u on s.saddr = u.session_addr" " join v$parameter p on p.name = 'db_block_size'" " join v$sql_monitor m on m.sid = s.sid and m.session_serial# = s.serial#" " where m.status = 'EXECUTING'{}{}" " group by s.username, m.sql_id, round(elapsed_time / 60000000), s.sid," " m.sql_id || to_char(m.sql_exec_id) || to_char(m.sql_exec_start, 'yyyymmddhh24miss'))" " where exec_time_min >= :exec_time_min or temp_usage_gb >= :temp_usage_gb" .format( ' and s.username like :user_name' if t.optional.get( 'user_name', None) else '', ' and s.username not like :ignore_user' if t.optional.get( 'ignore_user', None) else ''), { **t.parameters, **t.optional }, 'many', False) return t.get_message( r, lambda o, i: '{} ({}, {}) on {} is executing {} minutes and consumes {} Gb of temp space.' .format(t_link(f'{o.target}/Q/{i[1]}', i[1]), i[5], i[0], o.target, i[ 2], i[3]), None, 4)
def check_session_stats(t): """Please see "Activity -> Session monitor -> Session -> Session stats" to find all available statistic names.""" r = execute( t.target, "select s.sid, n.name, s.value from v$sesstat s join v$statname n on s.statistic# = n.statistic#" " where n.name = :statistic_name and s.value >= :value order by s.value desc", {**t.parameters}, 'many', False) return t.get_message( r, lambda o, i: 'Session {} on {} has {} = {}.'.format( t_link(f'{o.target}/S/{str(i[0])}', str(i[0])), o.target, i[1], get_num_str(i[2])), None, 0)
def check_frequent_sql(t): """This task based on v$sqlarea which accumulate statistics since SQL statement had been cached.""" r = execute( t.target, "select sql_id, parsing_schema_name, executions," " to_char(to_date(first_load_time, 'yyyy-mm-dd/hh24:mi:ss'), 'dd.mm hh24:mi')" " from v$sqlarea where parsing_schema_name not like '%SYS%'" " and executions > :executions order by executions desc", {**t.parameters}, 'many', False) return t.get_message( r, lambda o, i: '{} ({}) executed {} times since {}.'.format( t_link(f'{o.target}/Q/{i[0]}', i[0]), i[1], get_num_str(i[2]), i[3] ), lambda o: f'Frequent executions on {o.target}', 0)
def wait_for_zombie(t): """User sessions could stay active and being waiting for an event that never comes.""" \ """ They must be killed to free locked resources.""" r = execute(t.target , "select sid, username from v$session where type = 'USER' and (" "(sysdate - last_call_et / 86400 < sysdate - :last_call_minutes * 1 / 24 / 60 and status = 'ACTIVE')" " or (event = 'SQL*Net break/reset to client' and status = 'INACTIVE'))" , {**t.parameters} , 'many' , False) return t.get_message(r , lambda o, i: 'Session {} ({}) on {} seems to be a zombie.' .format(t_link(f'{o.target}/S/{str(i[0])}', str(i[0])), i[1], t.target), None, 0)
def wait_for_queued(t): pt = t.period[-1:] pv = t.period[:-1] t.parameters['start_date'] = datetime.now() - get_offset(pv, pt) r = execute(t.target , "select nvl(sql_id, 'Unknown sql') || ' ' || event || ' ' || to_char(session_id), " " nvl(sql_id, 'Unknown sql'), event, session_id, machine, count(1) waits" " from v$active_session_history" " where event like 'enq:%' and sample_time > :start_date" " and event not like :ignore_event" " group by nvl(sql_id, 'Unknown sql') || ' ' || event || ' ' || to_char(session_id)," " sql_id, event, session_id, machine" " having count(1) > :queued_time_sec" , {'start_date': t.parameters['start_date'] , 'queued_time_sec': t.parameters['queued_time_sec'] , 'ignore_event': t.optional.get('ignore_event', '---')} , 'many' , False) return t.get_message(r, lambda o, i: '{} ({}, {}) on {} has been queued for {} seconds ({}).' .format(t_link(f'{t.target}/Q/{i[1]}', i[1]), i[4], i[3], t.target, i[5], i[2]), None, 0)
def run(self): while self.active: try: updates = loads( get_updates(self.offset).read().decode('utf-8')) if not updates.get('ok', False): app.logger.error( updates.get('description', 'Unknown bot error (getUpdates)')) break for update in updates['result']: self.offset = update['update_id'] + 1 if not update.get('message'): continue if not update['message'].get('text'): continue if update['message']['text'] == '/id': send_message({ 'chat_id': update['message']['chat']['id'], 'text': f'id = {update["message"]["chat"]["id"]}.' }) continue user_list = tuple( k for k, v in app.config['USERS'].items() if v[1] and v[1] == update['message']['from']['id']) user_name = None if len(user_list) == 1: user_name = user_list[0] if not user_name: if update['message']['chat']['id'] in app.config[ 'BOT_CHAT_LIST'].keys(): send_message({ 'chat_id': update['message']['chat']['id'], 'text': f'I don\'t know who you are.' f' Please open @{app.config["BOT_NAME"]} and send me /id command.' f' Then forward my reply to system administrator.' }) continue if update['message']['text'] == '/help': send_message({ 'chat_id': update['message']['chat']['id'], 'text': 'God help you.' }) continue rr, endpoint, target, parameters = parse_command( update['message']['text']) if rr: send_message({ 'chat_id': update['message']['chat']['id'], 'text': f'Incorrect value: {rr}.' }) continue if target and target not in app.config['USERS'][user_name][ 2]: send_message({ 'chat_id': update['message']['chat']['id'], 'text': f'Target "{target}" does not exist or not allowed.' }) continue task = Task(endpoint=endpoint, user_name=user_name, target=target, parameters=parameters, chat_id=str(update['message']['chat']['id']), reply_to_message_id=str( update['message']['message_id'])) task_pool[task.uuid] = task send_message({ 'chat_id': update['message']['chat']['id'], 'text': '{} added.'.format( t_link(f'adm?task_id={task.uuid}', 'Task')), 'parse_mode': 'HTML' }) except Exception as e: app.logger.error(e, exc_info=True) break