$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';
