static function filler_rows($max_rows, $current_row_count, $num_cells, $cell_content = ' ') { if ($current_row_count < $max_rows) { for ($row_num = $current_row_count; $row_num <= $max_rows; $row_num++) { $class_str = u::odd_even_class_str($row_num); u::filler_row($num_cells, $class_str, $cell_content); } } }
# if ($enterprise) { u::a('Monitor SQL Execution', "sql_monitor.php?db={$db}&sql_id={$sql_id}"); } u::a('SQL Plan Management', "sql_plan_management.php?db={$db}&sql_id={$sql_id}"); p(''); # # The sql # if ($rec = $db_obj->single_rec($sql, $binds)) { while (!$rec->SQL_FULLTEXT->eof()) { echo $rec->SQL_FULLTEXT->read(2000); } } p("\n"); $create_outline_link = u::a('Create Outline', "show_page.php?db={$db}&page=run_sql.php&sql_file=create_outline¶m1={$sql_id}¶m2='||child_number||'&dbms_output=1", 1); $create_outline_link = "'" . $create_outline_link . "'"; # # Total stats for this sql # p('Sql details from sga'); if ($enterprise) { $sql_extra = ', sql_plan_baseline, sql_profile'; } else { $sql_extra = ", {$create_outline_link} create_outline, outline_category"; } # # from sqlstat to get IO info # $sql = "\nSELECT \n physical_read_requests\n, physical_read_bytes \n, physical_write_requests\n, physical_write_bytes\n, executions\n, ROUND( physical_read_requests /decode(executions,0,1,executions) ) avg_reads\n, ROUND( physical_read_bytes /decode(executions,0,1,executions) ) avg_read_bytes\n, ROUND( physical_write_requests/decode(executions,0,1,executions) ) avg_writes\n, ROUND( physical_write_bytes /decode(executions,0,1,executions) ) avg_write_bytes\n FROM\n v\$sqlarea\nWHERE sql_id = :sql_id"; $binds = '';
<?php # # $Id: //Infrastructure/GitHub/Database/asbo/web/standby_details.php#1 $ # include 'start.php'; # # Standby status # if ($db_obj->using_dbsync()) { $cur = $db_obj->exec_sql('BEGIN dbamgr.dbsync.process_logs; END;', ''); u::p('Current Details'); $binds = ''; $binds[] = array(":db_str", $db); $sql = file_get_contents("./sql/standby_check.sql"); $cur = $db_obj->exec_sql($sql, $binds); $db_obj->html_results($cur); u::p('Standby Action History'); $sql = file_get_contents("./sql/standby_history.sql"); $cur = $db_obj->exec_sql($sql, $binds); $db_obj->html_results($cur); } else { u::p('No standby for this database'); } include 'end.php';
# 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';
$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'] = ''; }
if ($just_checks !== 1) { include 'start.php'; } # # Check full backup status # $sql = file_get_contents("./sql/backup_check.sql"); $binds = ''; $binds[] = array(":db_str", $db); $cur = $db_obj->exec_sql($sql, $binds); $db_obj->html_results($cur); # # Database space # $sql = file_get_contents("./sql/tablespace_check.sql"); $cur = $db_obj->exec_sql($sql, $binds); $db_obj->html_results($cur); # # Standby status # if ($db_obj->using_dbsync()) { $binds = ''; $binds[] = array(":db_str", $db); $sql = file_get_contents("./sql/standby_check.sql"); $cur = $db_obj->exec_sql($sql, $binds); $db_obj->html_results($cur); } u::flush(); if ($just_checks !== 1) { include 'end.php'; }
$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'; ?>
$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'); }
<?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';
public static function _iconv($str) { if (!u::isUtf8($str)) { $str = utf8_encode($str); } setlocale(LC_CTYPE, 'fr_FR'); return iconv('UTF-8', 'ISO-8859-1//TRANSLIT', $str); }
function html_results($cur, $fmt = 1, $link_col = 0, $link = '', $show_no_rows = 1) { if ($fmt) { u::p('<style>table td {white-space: nowrap;border: 1px solid silver;font-family:Courier New}</style>'); u::p('<table>'); } else { u::p('<pre>'); } $print_header = 1; $rows_fetched = 0; while ($rec = oci_fetch_object($cur)) { $rows_fetched++; if ($fmt) { u::p('<tr>'); } # # Display column headers # if ($print_header) { if ($fmt) { u::p('<tr>'); } foreach ($rec as $key => $val) { if ($fmt) { u::th($key, 'style="background-color:silver;color:white"'); } else { u::p($key); } } if ($fmt) { u::p('</tr>'); } $print_header = 0; } # # Row data # if ($fmt) { u::p('<tr>'); } $col = 0; foreach ($rec as $key => $val) { $col++; if ($col === $link_col) { $val = u::a($val, str_replace('__VAL__', $val, $link), 1); } if ($fmt) { # # Extra formatting? # if ($val == 'CRITICAL') { $extra = 'style="background-color:red;"'; } else { $extra = ''; } u::td($val, $extra); } else { u::p($val); } } if ($fmt) { u::p('</tr>'); } } oci_free_statement($cur); if (!$rows_fetched and $fmt and $show_no_rows) { u::trtd('No Rows Returned', 'style="background-color:red;"'); } if ($fmt) { u::p('</table>'); } else { u::p('</pre>'); } }
} else { $include_it = 0; } } # # Now excludes # $exclude_it = 0; if ($exc_str) { $search_pos = stripos($db, $exc_str); if ($search_pos === 0 or $search_pos > 0) { $exclude_it = 1; } } #u::p( $db . ' Inc : ' . $include_it . ' Exc : ' . $exclude_it . '<br>' ); if ($include_it and !$exclude_it) { echo $break . "DB : {$db} - Connecting to : {$db_detail->conn_str}, user : {$db_detail->user}\n"; u::flush(); $db_obj = new db($db_detail->user, $db_detail->pw, $db_detail->conn_str, 1); $just_checks = 1; if ($db_obj->connection_error) { u::p('<span style="background-color:red;">CRITICAL</span>'); } else { include 'db_checks.php'; } } } if (@$_GET['html']) { u::p('</body>'); u::p('</html>'); }
/** * Used in the UI to get vaidation text for given Form inputs * * @param string $input_key The form input name * @return string The validation text. */ public static function validation($input_key) { if (isset(self::$validation_results[$input_key]) && !empty(self::$validation_results[$input_key])) { echo "<span class=\"error\">" . u::h(self::$validation_results[$input_key], false) . "</span>"; } }
if ($value > 0) { # # Draw the box # $hover_txt = $label_lookup[$item] . ' ' . $value . '%'; $value = $value * $size_factor; if ($use_images) { $image = $bg_image_lookup[$item]; } else { $image = ''; } u::box($value, $color_lookup[$item], $offset, $image, $hover_txt, '', $bar_height); $offset = $offset + $value; } $item++; } # # Did we draw anything? # if ($offset) { # # Total percent label # $total_str = $offset / $size_factor . "%"; u::box($tail_size, '', $offset, '', $total_str, $total_str, $bar_height); } # # End the container div # u::end_tag('div', 1);
<?php # # $Id: //Infrastructure/GitHub/Database/avo/web/sql_plan_management.php#2 $ # # SPM # - T Dale 2011 # include 'start.php'; $sql_id = u::request_val('sql_id'); # # Baseline parameters # $sql = "select * from v\$parameter where name like '%baseline%'"; $cur = $db_obj->exec_sql($sql, ''); $db_obj->html_results($cur); # # Check if this sql is adaptive # $sql = "select sa.is_bind_aware, sa.is_bind_sensitive, sa.sql_plan_baseline baseline from v\$sqlarea sa where sql_id = :sql_id"; $binds = ''; $binds[] = array(":sql_id", $sql_id); $cur = $db_obj->exec_sql($sql, $binds); p('Check v$sqlarea for this sql'); $db_obj->html_results($cur); @($baseline = $db_obj->single_rec($sql, $binds)->BASELINE); # # Check selectivity # $tables = array('v$sql_cs_histogram', 'v$sql_cs_statistics', 'v$sql_cs_selectivity'); foreach ($tables as $table) {
u::tr('<th>sql_id</th><th>% of ' . $sql_mins_hist . ' mins busy</th><th>Execs</th><th>Avg Disk Reads</th><th>Avg Secs</th><th>SQL Text</th>', '', 1); $i = 0; while ($rec = oci_fetch_object($cur)) { # # Row formatting # $class_str = u::odd_even_class_str($i++); u::start_tag('tr', $class_str); $highlight_graph_str = "onmouseover=\"highlight('{$rec->SQL_ID}');\" "; $sql_cell = "<div {$highlight_graph_str}>" . sql_details_link($rec->SQL_ID, $db) . "</div>"; u::td($sql_cell, '', 1); # # Barchart # $values = array($rec->PERCENT_ON_CPU, $rec->PERCENT_USER_IO, $rec->PERCENT_SYSTEM_IO, $rec->PERCENT_BLOCKED, $rec->PERCENT_OTHER); u::start_tag('td', 'style="padding:0px 0px 0px 0px"'); include 'bar_chart.php'; u::end_tag('td'); u::td($rec->EXECS, '', 1); u::td($rec->AVG_DISK_READS, '', 1); u::td($rec->AVG_SECS, '', 1); u::tagged_item('td', $rec->SQL_TEXT, 'style="border-right:0px;font-size:xx-small;font-family:Courier New;"', 1); u::end_tag('tr'); } # # Fill table up if needed # u::filler_rows($table_rows, $i, 6); u::end_tag('table'); u::end_tag('div');
<?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';
xmlhttp.onreadystatechange=function(){ if (xmlhttp.readyState==4 && xmlhttp.status==200){ document.getElementById(div_to_replace).innerHTML=xmlhttp.responseText; } } // Get all the graphs base_url = "<?php echo $sys_load_url; ?> "; xmlhttp.open("GET",base_url + extra_param,true); xmlhttp.send(); } function toggle_button(current_val){ if ( current_val == 'Stop' ){ alter_timers('stop'); document.getElementById("btn").value='Start'; }else{ alter_timers('start'); document.getElementById("btn").value='Stop'; } } </script> </head> <?php u::start_tag('body', 'onload="start_monitoring();"');
function tr($str, $extra = '') { u::tr($str, $extra); }
<?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';
# # 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);
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++; } } } }
# # Start HTML page and menu # # $Id: //Infrastructure/GitHub/Database/avo/web/db_monitor.php#2 $ # # # Start session etc # include 'start.php'; # # Top menu # $db = @$_GET['db']; $form_name = 'db_monitor.php'; u::inc('menu.php'); # # Exit if no db selected, just display the menu # if (!$db) { exit; } # # Divs for displaying monitoring # ?> <div id="graph1" style="position:relative;"></div> <div id="graph3" style="position:relative;left:0;overflow:hidden;"></div> <div id="graph2" style="position:relative;left:0;overflow:hidden;"></div> </body> </html>
<?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';
<?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('<table>'); $print_header = 1; while ($rec = oci_fetch_object($cur)) { # # Display column headers # if ($print_header) { p('<tr>'); foreach ($rec as $key => $val) { td($key); } p('</tr>'); $print_header = 0; } if (@$_GET['all_secs']) { u::fill_in_missing_snaps($last_snap_time, $rec->HOUR_MIN_SEC); $last_snap_time = $rec->HOUR_MIN_SEC; } # # Row data # p('<tr>'); foreach ($rec as $key => $val) { if ($key === 'SQL_ID') { td(sql_details_link($val, $db)); } else { if ($key === 'CURRENT_OBJ#' and $val > 0) { td("<a href=./obj_details.php?db={$db}&obj_id={$val}>{$val}</a>"); } else { if ($key === 'BLOCKING_SESSION' and $val) { td("<a href=./session_drilldown.php?db={$db}&sid={$val}&snaps_table={$snaps_table}>{$val}</a>");
<?php # # $Id: //Infrastructure/GitHub/Database/avo/web/start_html.php#2 $ # # Html start and javascript # # # Main page needs some js etc # $uri = $_SERVER['REQUEST_URI']; if (strpos($uri, 'db_monitor.php') > 0 or strpos($uri, 'system_load.php')) { u::inc('db_monitor_html_style_and_js.php'); } else { echo "<!doctype html>\n<html>\n<head>\n</head>\n<body>\n"; }
<?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';