def __init__(self, username, password, ipadress, port, servicename,
dict_config):
self.connection = Oracle_Conn(username, password, ipadress, port,
servicename)
#configuration#
self.dict_config = dict_config
def getawr(ipadress, port, servicename, dbausername, dbapassword, date,
beginhr, endhr, instance_number):
try:
connection = Oracle_Conn(dbausername, dbapassword, ipadress, port,
servicename)
except:
return "Error:数据库无法连接"
else:
pass
#双节点的dbid是一致的
re_dbid = connection.execsql(
"select dbid from v$database") #要么报错str要么就是list
snaptime_begin = str(date) + str(beginhr) #2017032522
snaptime_end = str(date) + str(endhr)
snapid_sql = '''
select distinct snap_id from dba_hist_snapshot s
where to_char(s.end_interval_time,'yyyymmddhh24') in ('{0}','{1}')
order by 1 '''.format(snaptime_begin, snaptime_end)
re_snapid = connection.execsql(snapid_sql)
if isinstance(re_dbid, str) or isinstance(re_snapid, str):
connection.close_commit()
return "Error:获取数据库信息异常"
elif len(
re_snapid
) != 2: #必须出来2个snap_id 如果1个那么后面生产awr会报错 snap_time_begin和snap_time_end不能相等
connection.close_commit()
return "Error:获取SNAPID信息异常"
else:
dbid = re_dbid[0][0]
snapid_begin = re_snapid[0][0]
snapid_end = re_snapid[1][0]
awr_sql = '''
select output from
table(dbms_workload_repository.awr_report_html({0},{1},{2},{3}, 0 ))
'''.format(dbid, instance_number, snapid_begin, snapid_end)
#0 noaddm 8 addm
#print awr_sql
re_awr = connection.execsql(awr_sql)
if isinstance(re_awr, str):
connection.close_commit()
return "Error:获取AWR报告异常:" + re_awr
else:
awrrpt_name = 'awrrpt_'+ servicename + '_' +str(instance_number)+ '_' + \
str(snaptime_begin) + '_' + str(endhr) + '.html'
#print awrrpt_name
try:
makehtml(awrrpt_name, re_awr)
connection.close_commit()
except:
return "Error:AWR报告生成异常"
else:
return "Success:请下载后查阅"
def __init__(self,dbausername,dbapassword,ipadress,port,servicename,directory,\
tables,parallel,content,additional,skema,sysuser,syspassword):
self.connection = Oracle_Conn(dbausername, dbapassword, ipadress, port,
servicename)
self.ipadress = ipadress
self.port = port
self.servicename = servicename
self.directory = directory
self.tables = tables
self.parallel = parallel
self.content = content
self.additional = additional
self.skema = skema
self.sysuser = sysuser
self.syspassword = syspassword
def __init__(self,dbausername,dbapassword,ipadress,port,servicename,directory,skema,sysuser,syspassword,\
table_exists_action,remap_table,expdp_job_name,expdp_skema,tables,\
expdp_tablespace,expdp_dumpfilepath,expdp_parallel):
self.connection = Oracle_Conn(dbausername, dbapassword, ipadress, port,
servicename)
self.ipadress = ipadress
self.port = port
self.servicename = servicename
self.directory = directory
self.skema = skema
self.sysuser = sysuser
self.syspassword = syspassword
self.table_exists_action = table_exists_action
self.remap_table = remap_table
self.expdp_job_name = expdp_job_name
self.expdp_skema = expdp_skema
self.tables = tables
self.expdp_tablespace = expdp_tablespace
self.expdp_dumpfilepath = expdp_dumpfilepath
self.expdp_parallel = expdp_parallel
def planchange(ipadress, port, servicename, dbausername, dbapassword,
parsing_shema_name):
try:
connection = Oracle_Conn(dbausername, dbapassword, ipadress, port,
servicename)
except:
return "Error:数据库无法连接"
else:
pass
tmpsql=planchange_checksql.format(parsing_shema_name,\
planchange_dict['CHANGE_RATE'],\
planchange_dict['EXECUTIONS'],\
planchange_dict['AVG_TIME_MS'])
print tmpsql
re_planchange = connection.execsql(tmpsql) #要么报错str要么就是list
if isinstance(re_planchange, str):
connection.close_commit()
return "Error:获取信息异常"
else:
connection.close_commit()
#print re_planchange
list_planchange = []
for item in re_planchange:
dict_tmp = {
'inst_id': item[0],
'sql_id': item[1],
'sql_text': item[2],
'plan_hash_value': item[3],
'first_load_time': item[4],
'last_active_time': item[5],
'max_last_active_time': item[6],
'executions': item[7],
'avg_time_ms': item[8],
'best_avg_time_ms': item[9],
'change_rate': item[10]
}
list_planchange.append(dict_tmp)
return list_planchange
def getash(ipadress, port, servicename, dbausername, dbapassword, minutebefore,
duration, instance_number):
try:
connection = Oracle_Conn(dbausername, dbapassword, ipadress, port,
servicename)
except:
return "Error:数据库无法连接"
else:
pass
#双节点的dbid是一致的
re_dbid = connection.execsql(
"select dbid from v$database") #要么报错str要么就是list
begintime = "SYSDATE-" + str(minutebefore) + "/1440" #SYSDATE-30/1440
endtime = "SYSDATE-" + str(int(minutebefore) - int(duration)) + "/1440"
if isinstance(re_dbid, str):
connection.close_commit()
return "Error:获取数据库信息异常"
else:
dbid = re_dbid[0][0]
ash_sql = '''
select output from
table(dbms_workload_repository.ash_report_html( {0},{1},{2},{3}))
'''.format(dbid, instance_number, begintime, endtime)
re_ash = connection.execsql(ash_sql)
if isinstance(re_ash, str):
connection.close_commit()
return "Error:获取ASH报告异常:" + re_ash
else:
ashrpt_name = 'ashrpt_'+ servicename + '_' +str(instance_number)+ '_minus' + \
str(minutebefore) + '_duration' + str(duration) + '.html'
try:
makehtml(ashrpt_name, re_ash)
connection.close_commit()
except:
return "Error:ASH报告生成异常"
else:
return "Success:请下载后查阅"
def inefficient(ipadress, port, servicename, dbausername, dbapassword,
parsing_shema_name):
try:
connection = Oracle_Conn(dbausername, dbapassword, ipadress, port,
servicename)
except:
return "Error:数据库无法连接"
else:
pass
tmpsql=inefficient_checksql.format(parsing_shema_name,\
inefficient_dict['EXECUTIONS'],inefficient_dict['EXE_MIN_SINCE'],\
inefficient_dict['AVG_TIME_MS'],inefficient_dict['AVG_GETS_MB'],inefficient_dict['AVG_READS_MB'])
re_inefficient = connection.execsql(tmpsql) #要么报错str要么就是list
#print re_inefficient
if isinstance(re_inefficient, str):
connection.close_commit()
return "Error:获取信息异常"
else:
list_inefficient = []
for item in re_inefficient:
#print type(item[2]) clob
dict_tmp = {
'inst_id': item[0],
'sql_id': item[1],
'sql_text': item[2].read(), #转为str 注意read()在close_commit之前
'plan_hash_value': item[3],
'first_load_time': item[4],
'last_active_time': item[5],
'executions': item[6],
'avg_time_ms': item[7],
'avg_gets_mb': item[8],
'avg_reads_mb': item[9]
}
list_inefficient.append(dict_tmp)
connection.close_commit()
return list_inefficient
def __init__(self, username, password, ipadress, port, servicename):
self.connection = Oracle_Conn(username, password, ipadress, port,
servicename)