$end = date('o_m_d__17', $yest); $top_sql_report_url = "top_sql_report.php?start={$start}&end={$end}&order_by_id="; $values[] = array($top_sql_report_url . '1', 'Top SQL Report'); $values[] = array('db_size.php', 'Database Size'); $values[] = array('standby_details.php', 'Standby Details'); $values[] = array('db_checks_all_dbs.php?html=1', 'Checks all DB'); if (u::request_val('full', 0)) { $values[] = array('undo_monitor.php', 'Undo Monitor'); $values[] = array($top_sql_report_url . '2', 'AWR by disk reads'); $values[] = array($top_sql_report_url . '3', 'AWR by exec elapsed'); $values[] = array('run_sql.php[sql_file----longops]', 'Longops'); $values[] = array('db_sessions.php', 'Sessions'); $base_url = "system_load.php?hide=on&snaps_table={$snaps_table}&db={$db}&sql_mins_hist=2&mins_hist=2&table_styles=1&div_only=1" . "&width={$chart_width}&max_width={$chart_max_width}" . "&graph_icons=0&chart_icons=0&color_scheme=2&cell_style_param=3"; $values[] = array($base_url . "&active_sessions=on", 'Active Sessions'); $values[] = array($base_url . "&top_sql=on", 'Top SQL'); $values[] = array($base_url . "&top_sessions=on", 'Top Sessions'); $values[] = array('rman_progress.php', 'RMAN Progress'); $io_url = "io_monitor.php?db={$db}&wait_secs=10&top_n=10&order_by="; $values[] = array($io_url . "2", 'SQL IO Reads'); $values[] = array($io_url . "4", 'SQL IO Writes'); } u::select_list('page', $values, $db); u::end_tag('form'); u::tag('input', 'type="button" style="position:absolute;left:420;top:2;height:27px" value="Stop" id="btn" onclick="toggle_button(this.value);"'); u::end_tag('div'); # # Need a db to be selected for a page to make any sense # if (u::request_val('page') and !$db) { $_GET['page'] = ''; }
# # Simple run sql # $sql_file = u::request_val('sql_file'); $param1 = u::request_val('param1'); $param2 = u::request_val('param2'); $param3 = u::request_val('param3'); $param4 = u::request_val('param4'); $dbms_output = u::request_val('dbms_output'); p("<title>Run : {$sql_file}</title>"); $sql = file_get_contents("./sql/" . $sql_file . ".sql"); #$sql = str_replace( "'", "\'", $sql ); # # Schema needed? # if ($schema = u::request_val('schema')) { $sql = str_replace('__SCHEMA__', $schema, $sql); } #p($sql); $binds = ''; if ($param1) { $binds[] = array(":param1", $param1); } if ($param2) { $binds[] = array(":param2", $param2); } if ($param3) { $binds[] = array(":param3", $param3); } if ($param4) { $binds[] = array(":param4", $param4);
# include 'start.php'; $db_obj->dbms_output_on(); $sql = file_get_contents("./sql/top_sql_report.sql"); $start = u::request_val('start'); $end = u::request_val('end'); $binds = ''; # # Using our own history tables? # include 'subs_for_history_tables.php'; $binds[] = array(":os_start_snap", '0'); $binds[] = array(":os_end_snap", '0'); #p('---SQL------------------'); #p($sql); #p('---SQL------------------'); p("<title>Quick SQL Report for {$db}</title>"); $binds[] = array(":from_date_str", $start); $binds[] = array(":to_date_str", $end); $binds[] = array(":order_by_id", u::request_val('order_by_id', 1)); $binds[] = array(":db_str", u::request_val('db')); # # These binds should be give as options in the page - Todo # #$binds[] = array( ":ignore_plsql" , 1 ); #$binds[] = array( ":ignore_support_activity", 1 ); #$binds[] = array( ":ignore_reportmgr" , 1 ); #$binds[] = array( ":result_count" , 20 ); $cur = $db_obj->exec_sql($sql, $binds); $db_obj->get_dbms_output(); include 'end.php';
break; case 'SYSTEM_IO': $style_str .= 'lightblue'; break; case 'SESSIONS_IN_LOCK': $style_str .= 'red'; break; } $style_str .= '"'; } return $style_str; } # # Colors # $color_scheme = u::request_val('color_scheme', 2); # # Colors # if ($color_scheme == 1) { $colors = array('blue', 'lightblue', 'green', 'red', 'pink'); } elseif ($color_scheme == 2) { $colors = array('#004fef', '#0095ef', '#13ce00', '#c42c00', '#f670ac'); } # # background color style # function style_color($ind, $colors) { return 'background-color:' . $colors[$ind] . ';'; }
$type = u::request_val('type'); if ($type == 'hist') { u::p('Getting History log'); $sql = "select logfile log from dbamgr.dbsync_standby_hist where hist_id = :hist_id"; $binds = ''; $binds[] = array(":hist_id", u::request_val('hist_id')); } else { u::p('Getting log'); if ($type == 'full') { $col = 'last_full_refresh_log'; } else { $col = 'last_rollforward_log'; } $sql = "select {$col} log from dbamgr.dbsync_standby where standby_id = :standby_id"; $binds = ''; $binds[] = array(":standby_id", u::request_val('standby_id')); } p('<pre>'); # # The log # if ($rec = $db_obj->single_rec($sql, $binds)) { while (!$rec->LOG->eof()) { echo $rec->LOG->read(2000); } } p("\n"); include 'end.php'; ?>
<?php # # $Id$ # # Bar chart using divs and images # # # Do we set the background images? # $use_images = u::request_val('graph_icons', 1); if ($use_images) { $bar_height = 20; } else { $bar_height = 24; } # # Multiply the % by # $size_factor = 3; # # Widths we will draw # $total_bar_width = 0; $graph_total = 0; foreach ($values as $value) { $graph_total = $graph_total + $value; } $total_bar_width = $graph_total * $size_factor; # # Change the factoring?
<?php # # $Id$ # include 'start.php'; $sid = u::request_val('sid'); $serial = u::request_val('serial'); if ($sid and $serial) { $kill_sql = "alter system kill session '{$sid},{$serial}'"; p('SQL : ' . $kill_sql); $cur = $db_obj->exec_sql($kill_sql); p(' DONE!'); } else { p('Pamarms not given'); } include 'end.php';
function cells($sid_str, $db, $current_col, &$current_row, $class, $snaps_table, $sample_time) { # # Anything in the list? # if (strlen($sid_str) > 0) { $sid_list = explode(',', $sid_str); $active_sessions = count($sid_list); $display_cell = 1; # # put each sid in div # foreach ($sid_list as $sid) { # # Session detail drilldown link # $just_sid = current(explode('_', $sid)); $link_str = "onclick=\"window.open('session_drilldown.php?db={$db}&sid={$just_sid}&all_secs=1&snaps_table={$snaps_table}');return false;\""; # # Detail popup # $div_id = "{$sample_time}_{$just_sid}"; $show_hide = "showhide('{$div_id}'"; $show_div_str = "onmouseover=\"{$show_hide},1);\" onmouseout=\"{$show_hide},0);\" "; # # Display? # if ($display_cell) { # # Where does this go? # $bottom = ($current_row - 1) * 20; $left = ($current_col - 1) * 20; $position_str = 'left:' . $left . 'px;bottom:' . $bottom . 'px;'; $id_tail = str_replace(':', '_', $sample_time); $div_format = 'id="graph_' . $sid . '_' . $id_tail . '" style="' . $position_str . '" class="' . $class . '" ' . $show_div_str . ' ' . $link_str; # # too many? # if ($current_row > C_MAX_CELL_HEIGHT and u::request_val('trim', 1)) { # # Last cell for this class # $display_cell = 0; # # Just display the session count # u::tagged_item('div', $active_sessions, $div_format, 1); } else { # # Add cell to page # u::tagged_item('div', '', $div_format, 1); } $current_row++; } } } }
<?php # # $Id: //Infrastructure/GitHub/Database/avo/web/tbs_contents.php#2 $ # include 'start.php'; # # TBS stuff # $tbs = u::request_val('tbs'); $sql1 = "\nselect \n-- '<a href=datafile_contents.php?db={$db}&tbs={$tbs}&file_id='||df.file_id||'>'||df.file_name||'</a>' file_name\n df.file_name file_name\n, file_id,round(bytes/1024/1024/1024) size_gb,autoextensible auto_extend,round(maxbytes/1024/1024/1024) max_size_gb\n, (select round(sum(fs.bytes) / (1024 * 1024)) free_mb from dba_free_space fs where fs.tablespace_name = :tbs and fs.file_id = df.file_id) free_mb\n, '<a href=run_sql.php?db={$db}¶m1=8192&sql_file=resize_datafile¶m2='||df.file_id||'>Resize</a>' resize_datafile_cmd\nfrom\n( \n select tablespace_name, file_name,maxbytes,file_id, bytes, autoextensible from dba_data_files\n union all\n select tablespace_name, file_name,maxbytes,file_id, bytes, autoextensible from dba_temp_files\n) df\nwhere \n tablespace_name=upper(:tbs)\n"; $sql2 = "\nselect \n s.owner || '.' || s.segment_name name\n, s.segment_type\n, round( s.bytes/(1024*1024) ) size_mb\n, '<a href=table_move.php?db={$db}&owner='||s.owner||'&table_name='||nvl( l.table_name, nvl( i.table_name, s.segment_name ) )||'>'||nvl( l.table_name, nvl( i.table_name, s.segment_name ) )||'</a>' move_table_sql\nfrom\n dba_segments s\nleft outer join dba_lobs l on l.owner = s.owner and l.segment_name = s.segment_name\nleft outer join dba_indexes i on i.owner = s.owner and i.index_name = s.segment_name\nwhere \n s.tablespace_name = upper(:tbs)\norder by \n s.bytes desc\n"; #p($sql); if ($tbs) { $binds = ''; $binds[] = array(":tbs", $tbs); $cur = $db_obj->exec_sql($sql1, $binds); $db_obj->html_results($cur); $cur = $db_obj->exec_sql($sql2, $binds); $db_obj->html_results($cur); } include 'end.php';
# # Start of the html page for the main monitor page # # T Dale # # # Detail urls # $graph_extra_param = '&active_sessions=on&mins_hist=2'; $sql_extra_param = '&top_sql=on&mins_hist=2'; $sessions_extra_param = '&top_sessions=on&mins_hist=2'; $graph_icons_param = '&graph_icons=' . u::request_val('graph_icons', '0'); $chart_icons_param = '&chart_icons=' . u::request_val('chart_icons', '0'); $color_scheme_param = '&color_scheme=' . u::request_val('color_scheme', '2'); $cell_style_param = '&cell_style=' . u::request_val('cell_style', '3'); $max_cpu_param = '&display_max_cpu=' . u::request_val('display_max_cpu', 1); $sys_load_url = "system_load.php?&hide=on&snaps_table={$snaps_table}&db={$db}&sql_mins_hist=2&width={$chart_width}&max_width={$chart_max_width}" . $graph_icons_param . $chart_icons_param . $color_scheme_param . $cell_style_param . $max_cpu_param; $graph_url = $sys_load_url . $graph_extra_param; $sql_url = $sys_load_url . $sql_extra_param; $sessions_url = $sys_load_url . $sessions_extra_param; $sys_load_url = $sys_load_url . "&div_only=1"; u::p('<!DOCTYPE html>'); ?> <html> <head> <title>AVO - <?php echo $db; ?> </title> <style> <?php
<?php # # $Id: //Infrastructure/GitHub/Database/avo/web/table_move.php#2 $ # include 'start.php'; # # TBS stuff # $owner = u::request_val('owner'); $table_name = u::request_val('table_name'); $sql = "\nwith move_sql\nas(\nselect 1 build_order, t.table_name segment_name, t.owner, t.table_name, 'alter table \"'||t.owner||'\".\"'||t.table_name||'\" move tablespace tbsdata' cmd from dba_tables t\nunion\nselect 2 build_order, l.segment_name,l.owner, l.table_name,' lob (\"'||l.column_name||'\") store as ( tablespace tbsclob index tbsclob )' cmd from dba_lobs l\nunion\nselect 3 build_order, '' segment_name,:owner, :table_name, ';' cmd from dual\nunion\nselect 4 build_order, i.index_name segment_name, i.owner, i.table_name,'alter index \"'||owner||'\".\"'||i.index_name||'\" rebuild tablespace tbsidx;' cmd from dba_indexes i where index_type != 'LOB'\n)\nselect \n ms.cmd || case when s.bytes is not null then '-- size mb : ' || round( s.bytes/1024/1024 ) end move_sql\nfrom\n move_sql ms \n left outer join dba_segments s on ms.segment_name = s.segment_name and ms.owner = s.owner \nwhere\n ms.owner = :owner and ms.table_name = :table_name\norder by \n ms.build_order\n"; #p($sql); $binds = ''; $binds[] = array(":owner", $owner); $binds[] = array(":table_name", $table_name); $cur = $db_obj->exec_sql($sql, $binds); $db_obj->html_results($cur); include 'end.php';
<?php # # $Id: //Infrastructure/GitHub/Database/avo/web/locks.php#2 $ # # Locks for the session # T Dale # include 'start.php'; # # Locks # $sql = "\nselect \n l.*\n, (select object_name from dba_objects where object_id = lock_id1) object_name \nfrom dba_locks l \nwhere session_id = :sid\n"; $cur = $db_obj->exec_sql($sql, array(array(":sid", u::request_val('sid')))); $db_obj->html_results($cur); include 'end.php';
$sql_mins_hist = u::request_val('sql_mins_hist', 1); $mins_hist = u::request_val('mins_hist', 1); $start = date('o_m_d__07'); $end = date('o_m_d__H'); # # OS load # if (u::request_val('os_stats')) { u::inc('os_stats.php'); } # # Active session details now # if (u::request_val('active_sessions')) { u::inc('active_sessions_graph.php'); } # # Top sql now # if (u::request_val('top_sql')) { u::inc('top_sql.php'); } # # Top sessions # if (u::request_val('top_sessions')) { u::inc('top_sessions.php'); } if (!$div_only) { u::inc('end.php'); }
u::p('<html>'); u::p('<head>'); u::p('</head>'); u::p('<body>'); $break = '<br>'; } else { $break = "\n"; } echo $break . 'Report time : ' . date(DATE_RFC2822) . $break; # # Get all connection details # include 'conf/db_lookup.php'; $db_lookup = simplexml_load_string($db_look_up_xml); $inc_str = u::request_val('inc_str'); $exc_str = u::request_val('exc_str'); foreach ($db_lookup as $key0 => $db_detail) { $db = (string) $db_detail['name']; # # Run Checks? # $include_it = 1; if ($inc_str) { $search_pos = stripos($db, $inc_str); if ($search_pos === 0 or $search_pos > 0) { $include_it = 1; } else { $include_it = 0; } } #
<?php # # $Id$ # include 'start.php'; $binds = array(array(':session_recid', u::request_val('session_recid'))); $cur = $db_obj->exec_sql('select output from v$rman_output where session_recid=:session_recid', $binds); $db_obj->html_results($cur, 0); include 'end.php';
p('<pre>'); if ($rec) { p('<a href="kill_session.php?db=' . $db . '&sid=' . $rec->SID . '&serial=' . $rec->SERIAL . '">Kill session oracle</a>'); p($rec->KILL_OS); } p("<a href=\"sql_monitor.php?db={$db}&sid={$sid}\">Monitor SQL</a> for this session"); # # Locks # $sql = "\nselect\n l.*\n, (select owner || '.' || object_name from dba_objects where object_id = lock_id1) object_name\nfrom dba_locks l\nwhere session_id = :sid\n"; $cur = $db_obj->exec_sql($sql, array(array(":sid", u::request_val('sid')))); $db_obj->html_results($cur); $sql = "select * from v\$session where sid = :sid"; $cur = $db_obj->exec_sql($sql, array(array(":sid", u::request_val('sid')))); $db_obj->html_results($cur); $snaps_table = u::request_val('snaps_table'); $sql = <<<END_SQL select /* ** session drill down ash */ TO_CHAR( sample_time, 'HH24:MI:SS') hour_min_sec , a.* from {$snaps_table} a where a.session_id = :sid and a.sample_time > sysdate - 10/(24*60) order by a.sample_time desc END_SQL; $cur = $db_obj->exec_sql($sql, array(array(':sid', $sid)));
<?php # # $Id: //Infrastructure/GitHub/Database/avo/web/sql_details.php#2 $ # # Information and history on this statment # - T Dale 2010 # include 'start.php'; $sql_id = u::request_val('sql_id'); # # links to previous sql for sessions # good for recursive lob sql # # - need to change to look at v$open_cursor and find the lob object # - this would be better, I think # $enterprise = $db_obj->enterprise_edition(); $sql = "select distinct prev_sql_id prev_sql_id from v\$session where sql_id = :sql_id and prev_sql_id != :sql_id"; $binds = ''; $binds[] = array(":sql_id", $sql_id); $cur = $db_obj->exec_sql($sql, $binds); while ($rec = oci_fetch_object($cur)) { p("Prev sql's : " . sql_details_link($sql_id, $db) . "\n"); } # # Hidden sql # #include 'look_for_hidden_sql.php'; # # Sql text
<?php # # $Id: //Infrastructure/GitHub/Database/avo/web/spm_details.php#2 $ # include 'start.php'; # # SPM Reoprt # $sql = "select * from table( dbms_xplan.display_sql_plan_baseline( sql_handle=>:sql_handle, plan_name=>:plan_name, format=>'TYPICAL' ) )"; $binds = ''; $binds[] = array(":sql_handle", u::request_val('sql_handle')); $binds[] = array(":plan_name", u::request_val('plan_name', '')); $cur = $db_obj->exec_sql($sql, $binds); $db_obj->html_results($cur, 0); include 'end.php';