def oracle_grant(request): messageinfo_list = models_frame.TabAlarmInfo.objects.all() tags = request.GET.get('tags') print tags username = request.GET.get('username') sql = "select host,port,service_name,user,password,user_os,password_os from tab_oracle_servers where tags= '%s' " %tags oracle = tools.mysql_query(sql) host = oracle[0][0] port = oracle[0][1] service_name = oracle[0][2] user = oracle[0][3] password = oracle[0][4] password = base64.decodestring(password) url = host + ':' + port + '/' + service_name # 角色权限 sql = """ select grantee, granted_role, admin_option, default_role, to_char(sysdate, 'yyyy-mm-dd') get_date from dba_role_privs where grantee = '%s' """ %username user_roles = tools.oracle_django_query(user,password,url,sql) # 系统权限 sql = """ select grantee,privilege,admin_option,to_char(sysdate,'yyyy-mm-dd') get_date from dba_sys_privs where grantee = '%s' """ %username sys_privs = tools.oracle_django_query(user,password,url,sql) # 对象权限 sql = """ select owner,grantee,grantor,table_name,privilege ,grantable,hierarchy,to_char(sysdate,'yyyy-mm-dd') get_date from dba_tab_privs where grantee <> 'PUBLIC' and privilege <> 'EXECUTE' and grantee = '%s' """ %username tab_privs = tools.oracle_django_query(user,password,url,sql) now = tools.now() if request.method == 'POST': logout(request) return HttpResponseRedirect('/login/') if messageinfo_list: msg_num = len(messageinfo_list) msg_last = models_frame.TabAlarmInfo.objects.latest('id') msg_last_content = msg_last.alarm_content tim_last = (datetime.datetime.now() - msg_last.alarm_time).seconds / 60 else: msg_num = 0 msg_last_content = '' tim_last = '' return render_to_response('oracle_grant.html', {'messageinfo_list': messageinfo_list, 'msg_num':msg_num,'user_roles': user_roles,'sys_privs':sys_privs, 'tab_privs':tab_privs,'username':username,'tags': tags,'now': now, 'msg_last_content': msg_last_content, 'tim_last': tim_last})
def oracle_profile(request): messageinfo_list = models_frame.TabAlarmInfo.objects.all() tags = request.GET.get('tags') profile_name = request.GET.get('profile_name') sql = "select host,port,service_name,user,password,user_os,password_os from tab_oracle_servers where tags= '%s' " % tags oracle = tools.mysql_query(sql) host = oracle[0][0] port = oracle[0][1] service_name = oracle[0][2] user = oracle[0][3] password = oracle[0][4] password = base64.decodestring(password) url = host + ':' + port + '/' + service_name sql = """ select profile,resource_name,resource_type,limit,to_char(sysdate,'yyyy-mm-dd') get_date from dba_profiles where profile = '%s' """ % profile_name oracle_profiles = tools.oracle_django_query(user, password, url, sql) now = tools.now() if request.method == 'POST': logout(request) return HttpResponseRedirect('/login/') if messageinfo_list: msg_num = len(messageinfo_list) msg_last = models_frame.TabAlarmInfo.objects.latest('id') msg_last_content = msg_last.alarm_content tim_last = (datetime.datetime.now() - msg_last.alarm_time).seconds / 60 else: msg_num = 0 msg_last_content = '' tim_last = '' return render_to_response( 'oracle_profile.html', { 'messageinfo_list': messageinfo_list, 'msg_num': msg_num, 'oracle_profiles': oracle_profiles, 'profile_name': profile_name, 'tags': tags, 'now': now, 'msg_last_content': msg_last_content, 'tim_last': tim_last })
def show_oracle_resource(request): messageinfo_list = models_frame.TabAlarmInfo.objects.all() tagsinfo = models_oracle.OracleDb.objects.filter(mon_status='connected') tagsdefault = request.GET.get('tagsdefault') if not tagsdefault: tagsdefault = models_oracle.OracleDb.objects.filter( mon_status='connected').order_by('tags')[0].tags typedefault = request.GET.get('typedefault') redo_range_default = request.GET.get('redo_range_default') if not redo_range_default: redo_range_default = 7 tbsinfo_list = models_oracle.OracleTbs.objects.filter( tags=tagsdefault).order_by('-pct_used') # 分页 paginator_tbs = Paginator(tbsinfo_list, 5) undotbsinfo_list = models_oracle.OracleUndoTbs.objects.filter( tags=tagsdefault).order_by('-pct_used') paginator_undo = Paginator(undotbsinfo_list, 5) tmptbsinfo_list = models_oracle.OracleTmpTbs.objects.filter( tags=tagsdefault).order_by('-pct_used') paginator_tmp = Paginator(tmptbsinfo_list, 5) page_tbs = request.GET.get('page_tbs') try: tbsinfos = paginator_tbs.page(page_tbs) except PageNotAnInteger: # If page is not an integer, deliver first page. tbsinfos = paginator_tbs.page(1) except EmptyPage: # If page is out of range (e.g. 9999), deliver last page of results. tbsinfos = paginator_tbs.page(paginator_tbs.num_pages) page_undo = request.GET.get('page_undo') try: undotbsinfos = paginator_undo.page(page_undo) except PageNotAnInteger: # If page is not an integer, deliver first page. undotbsinfos = paginator_undo.page(1) except EmptyPage: # If page is out of range (e.g. 9999), deliver last page of results. undotbsinfos = paginator_undo.page(paginator_undo.num_pages) page_tmp = request.GET.get('page_tmp') try: tmptbsinfos = paginator_undo.page(page_tmp) except PageNotAnInteger: # If page is not an integer, deliver first page. tmptbsinfos = paginator_tmp.page(1) except EmptyPage: # If page is out of range (e.g. 9999), deliver last page of results. tmptbsinfos = paginator_tmp.page(paginator_tmp.num_pages) # 获取控制文件信息 sql = "select host,port,service_name,user,password,user_os,password_os from tab_oracle_servers where tags= '%s' " % tagsdefault oracle = tools.mysql_query(sql) host = oracle[0][0] port = oracle[0][1] service_name = oracle[0][2] user = oracle[0][3] password = oracle[0][4] password = base64.decodestring(password) url = host + ':' + port + '/' + service_name sql = """ select name,round(block_size*file_size_blks/1024/1024,2) size_M,'controlfile' tpye from v$controlfile """ oracle_controlfiles = tools.oracle_django_query(user, password, url, sql) # 获取在线日志 sql = """ select a.GROUP# group_no,b.THREAD# thread_no,a.TYPE,b.SEQUENCE# sequence_no,b.BYTES/1024/1024 SIZE_M,b.ARCHIVED,b.STATUS,a.MEMBER from v$logfile a,v$log b where a.GROUP#=b.GROUP#(+) """ oracle_redo_files = tools.oracle_django_query(user, password, url, sql) # 在线日志统计 if redo_range_default == '1': sql = """ select 'hh'||to_char(first_time, 'hh24') stat_date, count(1) log_count, (select bytes / 1024 / 1024 sizem from v$log where rownum < 2) log_size from v$log_history where to_char(first_time, 'yyyymmdd') < to_char(sysdate, 'yyyymmdd') and to_char(first_time, 'yyyymmdd') >= to_char(sysdate - 1, 'yyyymmdd') group by to_char(first_time, 'hh24'),to_char(first_time, 'dy') order by to_char(first_time, 'hh24') """ oracle_redo_cnts = tools.oracle_django_query(user, password, url, sql) else: sql = """ select to_char(first_time, 'yyyy-mm-dd') stat_date, count(1) log_count, (select bytes / 1024 / 1024 sizem from v$log where rownum < 2) log_size from v$log_history where to_char(first_time, 'yyyymmdd') < to_char(sysdate, 'yyyymmdd') and to_char(first_time, 'yyyymmdd') >= to_char(sysdate-%s, 'yyyymmdd') group by to_char(first_time, 'yyyy-mm-dd'), to_char(first_time, 'dy') order by to_char(first_time, 'yyyy-mm-dd')""" % redo_range_default oracle_redo_cnts = tools.oracle_django_query(user, password, url, sql) # 表变化记录 sql = """ select table_owner,table_name,inss,upds,dels, to_char(inss + upds + dels) dmls,to_char(sysdate , 'yyyy-mm-dd') get_date,truncated,num_rows, to_char(last_analyzed ,'yyyy-mm-dd hh24:mi:ss') last_analyzed from (select m.table_owner, m.table_name, inserts as inss, updates as upds, deletes as dels, truncated, t.num_rows,t.last_analyzed from sys.dba_tab_modifications m, dba_tables t where m.table_name = t.table_name and t.owner not in ('SYS','SYSTEM','OUTLN','DIP','ORACLE_OCM','DBSNMP','APPQOSSYS','WMSYS','EXFSYS', 'CTXSYS','ANONYMOUS','XDB','XS$NULL','ORDDATA','SI_INFORMTN_SCHEMA','ORDPLUGINS','ORDSYS','MDSYS','OLAPSYS', 'MDDATA','SPATIAL_WFS_ADMIN_USR','SPATIAL_CSW_ADMIN_USR','SYSMAN','MGMT_VIEW','APEX_030200','FLOWS_FILES', 'APEX_PUBLIC_USER','OWBSYS','OWBSYS_AUDIT','SCOTT') and m.table_owner = t.owner and m.partition_name is null) """ oracle_table_changes = tools.oracle_django_query(user, password, url, sql) # 序列 sql = """ select sequence_owner,sequence_name,min_value,max_value,increment_by,cycle_flag,order_flag, cache_size,last_number, round((max_value - last_number) / (max_value - min_value), 2) * 100 pct_used, (case when (round((max_value - last_number) / (max_value - min_value), 2) * 100) > 30 then 'green' when (round((max_value - last_number) / (max_value - min_value), 2) * 100) <= 30 and (round((max_value - last_number) / (max_value - min_value), 2) * 100) > 10 then 'yellow' when (round((max_value - last_number) / (max_value - min_value), 2) * 100) <= 10 then 'red' else '' end) seq_color, to_char(sysdate, 'yyyy-mm-dd') last_analyzed from dba_sequences s where s.sequence_owner not in ('SYS','SYSTEM','OUTLN','DIP','ORACLE_OCM','DBSNMP','APPQOSSYS','WMSYS','EXFSYS', 'CTXSYS','ANONYMOUS','XDB','XS$NULL','ORDDATA','SI_INFORMTN_SCHEMA','ORDPLUGINS','ORDSYS','MDSYS','OLAPSYS', 'MDDATA','SPATIAL_WFS_ADMIN_USR','SPATIAL_CSW_ADMIN_USR','SYSMAN','MGMT_VIEW','APEX_030200','FLOWS_FILES', 'APEX_PUBLIC_USER','OWBSYS','OWBSYS_AUDIT','SCOTT') """ oracle_sequences = tools.oracle_django_query(user, password, url, sql) # 账号 sql = """ select username,profile,to_char(created,'yyyy-mm-dd hh24:mi:ss') created, account_status, (case when account_status <> 'OPEN' then 'red' else 'green'end ) account_color, to_char(lock_date,'yyyy-mm-dd hh24:mi:ss') lock_date, to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss') expiry_date, (case when expiry_date - sysdate > 30 then 'green' when expiry_date - sysdate <= 30 and expiry_date - sysdate > 7 then 'yellow' when expiry_date - sysdate <= 7 then 'red' else '' end) expiry_color,default_tablespace,temporary_tablespace from dba_users """ oracle_users = tools.oracle_django_query(user, password, url, sql) # alert日志 oracle_alert_logs = models_oracle.OracleAlertLog.objects.all().order_by( '-log_time') if request.method == 'POST': if request.POST.has_key('select_tags'): tagsdefault = request.POST.get('select_tags', None).encode("utf-8") return HttpResponseRedirect( '/show_oracle_resource?tagsdefault=%s&redo_range_default=%s' % (tagsdefault, redo_range_default)) else: logout(request) return HttpResponseRedirect('/login/') if messageinfo_list: msg_num = len(messageinfo_list) msg_last = models_frame.TabAlarmInfo.objects.latest('id') msg_last_content = msg_last.alarm_content tim_last = (datetime.datetime.now() - msg_last.alarm_time).seconds / 60 else: msg_num = 0 msg_last_content = '' tim_last = '' return render_to_response( 'show_oracle_res.html', { 'tagsdefault': tagsdefault, 'typedefault': typedefault, 'tagsinfo': tagsinfo, 'msg_num': msg_num, 'msg_last_content': msg_last_content, 'tim_last': tim_last, 'tbsinfos': tbsinfos, 'undotbsinfos': undotbsinfos, 'tmptbsinfos': tmptbsinfos, 'oracle_controlfiles': oracle_controlfiles, 'oracle_redo_files': oracle_redo_files, 'oracle_redo_cnts': oracle_redo_cnts, 'oracle_table_changes': oracle_table_changes, 'oracle_sequences': oracle_sequences, 'oracle_users': oracle_users, 'oracle_alert_logs': oracle_alert_logs })