Oracle数据库

开发平台：

Perl

oracletool.pl：源码内容

";
print <<"EOF";
<TABLE BORDER =0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Alter rollback(s)"> <B> Shrink to </B>
<INPUT TYPE=TEXT NAME=shrinkto SIZE=10>
<P>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="alter_rollbacks">
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Online</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Shrink</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Offline</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>RBS</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Owner</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Tablespace</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Status</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Bytes</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Optimal</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Initial extent</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Next extent</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Extents</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Max extents</TH>
EOF
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($rbs,$id,$owner,$tsname,$status,$bytes,$init,$next,$extents,$max) = $cursor->fetchrow_array) {
$sql1 = "
SELECT
NVL(TO_CHAR(OPTSIZE,'999,999,999,999'),'Not set')
FROM V$rollstat
WHERE USN = $id
";
$cursor1 = $dbh->prepare($sql1);
$cursor1->execute;
$optimal = $cursor1->fetchrow_array;
$optimal = "Unknown" unless $optimal;
$cursor1->finish;
print <<"EOF";
<TR>
<TD ALIGN=CENTER BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><INPUT TYPE=CHECKBOX NAME=alter~$rbs~online></TD>
<TD ALIGN=CENTER BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><INPUT TYPE=CHECKBOX NAME=alter~$rbs~shrink></TD>
<TD ALIGN=CENTER BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><INPUT TYPE=CHECKBOX NAME=alter~$rbs~offline></TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$rbs</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$owner</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$tsname</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$status</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$bytes</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$optimal</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$init</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$next</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$extents</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$max</TD>
</TR>
EOF
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</FORM>
</TABLE>
EOF
logit("Exit subroutine rbsList");
}
sub sessionList {
logit("Enter subroutine sessionList");
my ($sql,$cursor,$status);
my ($username,$sid,$serial,$osuser,$command,$logon_time,$process,$machine);
refreshButton();
text("Select the sessions you would like to kill.n");
$sql = "$copyright
SELECT
USERNAME,
SID,
SERIAL#,
STATUS,
OSUSER,
DECODE(COMMAND,
'0','No command in progress',
'1','Create table',
'2','Insert',
'3','Select',
'4','Create cluster',
'5','Alter cluster',
'6','Update',
'7','Delete',
'8','Drop cluster',
'9','Create index',
'10','Drop index',
'11','Alter index',
'12','Drop table',
'13','Create sequence',
'14','Alter sequence',
'15','Alter table',
'16','Drop sequence',
'17','Grant',
'18','Revoke',
'19','Create synonym',
'20','Drop synonym',
'21','Create view',
'22','Drop view',
'23','Validate index',
'24','Create procedure',
'25','Alter procedure',
'26','Lock table',
'27','No operation in progress',
'28','Rename',
'29','Comment',
'30','Audit',
'31','Noaudit',
'32','Create database link',
'33','Drop database link',
'34','Create database',
'35','Alter database',
'36','Create rollback segment',
'37','Alter rollback segment',
'38','Drop rollback segment',
'39','Create tablespace',
'40','Alter tablespace',
'41','Drop tablespace',
'42','Alter session',
'43','Alter user',
'44','Commit',
'45','Rollback',
'46','Savepoint',
'47','PL/SQL Execute',
'48','Set transaction',
'49','Alter system switch log',
'50','Explain',
'51','Create user',
'52','Create role',
'53','Drop user',
'54','Drop role',
'55','Set role',
'56','Create schema',
'57','Create control file',
'58','Alter tracing',
'59','Create trigger',
'60','Alter trigger',
'61','Drop trigger',
'62','Analyze table',
'63','Analyze index',
'64','Analyze cluster',
'65','Create profile',
'66','Drop profile',
'67','Alter profile',
'68','Drop procedure',
'69','Drop procedure',
'70','Alter resource cost',
'71','Create snapshot log',
'72','Alter snapshot log',
'73','Drop snapshot log',
'74','Create snapshot',
'75','Alter snapshot',
'76','Drop snapshot',
'79','Alter role',
'85','Truncate table',
'86','Truncate cluster',
'88','Alter view',
'91','Create function',
'92','Alter function',
'93','Drop function',
'94','Create package',
'95','Alter package',
'96','Drop package',
'97','Create package body',
'98','Alter package body',
'99','Drop package body'),
TO_CHAR(LOGON_TIME,'Day MM/DD/YY HH24:MI'),
PROCESS,
MACHINE
FROM V$SESSION
WHERE USERNAME IS NOT NULL
ORDER BY USERNAME, STATUS, SID, SERIAL#
";
# Print the heading
print <<"EOF";
<TABLE BORDER =0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Kill marked sessions"><BR>
<P>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="killsessions">
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Mark</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Ora user</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>SID</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Serial#</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Status</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>OS user</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Command</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Logon time</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Process</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Machine</TH>
EOF
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($username,$sid,$serial,$status,$osuser,$command,$logon_time,$process,$machine) = $cursor->fetchrow_array) {
print <<"EOF";
<TR>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><INPUT TYPE=CHECKBOX NAME=killsession_$sid~$serial></TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$username</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$sid</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$serial</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$status</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$osuser</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$command</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$logon_time</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$process</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$machine</TD>
</TR>
EOF
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</FORM>
</TABLE>
EOF
refreshButton();
logit("Exit subroutine sessionList");
}
sub auditList {
logit("Enter subroutine auditList");
my ($sql,$cursor,$username,$audit_option,$new_audit_option,$count);
my ($object_name,$object_type,$owner);
$sql = "$copyright
SELECT COUNT(*) FROM DBA_STMT_AUDIT_OPTS
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$count = $cursor->fetchrow_array;
$cursor->finish;
if ($count) {
text("SQL statement / System audits");
$sql = "$copyright
SELECT
AUDIT_OPTION "Audit option",
USER_NAME "Username"
FROM DBA_STMT_AUDIT_OPTS
ORDER BY USER_NAME, AUDIT_OPTION
";
print <<"EOF";
<TABLE BORDER =0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Remove marked audits">
<P>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="removestmtaudits">
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Mark</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Audit option</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Username</TH>
EOF
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($audit_option,$username) = $cursor->fetchrow_array) {
$new_audit_option = $audit_option;
$new_audit_option =~ s/ /+/g;
print <<"EOF";
<TR>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><INPUT TYPE=CHECKBOX NAME=removeaudit_$new_audit_option~$username></TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$username</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$audit_option</TD>
</TR>
EOF
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</FORM>
</TABLE>
EOF
} else {
message("There are no SQL statement / System audits enabled.");
}
$sql = "$copyright
SELECT
COUNT(*)
FROM DBA_OBJ_AUDIT_OPTS
WHERE ALT != '-/-'
OR AUD != '-/-'
OR COM != '-/-'
OR DEL != '-/-'
OR GRA != '-/-'
OR IND != '-/-'
OR INS != '-/-'
OR LOC != '-/-'
OR REN != '-/-'
OR SEL != '-/-'
OR UPD != '-/-'
OR REF != '-/-'
OR EXE != '-/-'
";
$sql = "$copyright
SELECT
COUNT(*)
FROM DBA_OBJ_AUDIT_OPTS
WHERE ALT != '-/-'
OR AUD != '-/-'
OR COM != '-/-'
OR DEL != '-/-'
OR GRA != '-/-'
OR IND != '-/-'
OR INS != '-/-'
OR LOC != '-/-'
OR REN != '-/-'
OR SEL != '-/-'
OR UPD != '-/-'
OR EXE != '-/-'
OR CRE != '-/-'
OR REA != '-/-'
OR WRI != '-/-'
" if ($oracle8);
$cursor = $dbh->prepare($sql);
$cursor->execute;
$count = $cursor->fetchrow_array;
$cursor->finish;
if ($count) {
text("Schema object audits");
$sql = "$copyright
SELECT
OBJECT_NAME "Object name",
OBJECT_TYPE "Object type",
OWNER "Owner"
FROM DBA_OBJ_AUDIT_OPTS
WHERE ALT != '-/-'
OR AUD != '-/-'
OR COM != '-/-'
OR DEL != '-/-'
OR GRA != '-/-'
OR IND != '-/-'
OR INS != '-/-'
OR LOC != '-/-'
OR REN != '-/-'
OR SEL != '-/-'
OR UPD != '-/-'
OR REF != '-/-'
OR EXE != '-/-'
ORDER BY OWNER
";
$sql = "$copyright
SELECT
OBJECT_NAME "Object name",
OBJECT_TYPE "Object type",
OWNER "Owner"
FROM DBA_OBJ_AUDIT_OPTS
WHERE ALT != '-/-'
OR AUD != '-/-'
OR COM != '-/-'
OR DEL != '-/-'
OR GRA != '-/-'
OR IND != '-/-'
OR INS != '-/-'
OR LOC != '-/-'
OR REN != '-/-'
OR SEL != '-/-'
OR UPD != '-/-'
OR EXE != '-/-'
OR CRE != '-/-'
OR REA != '-/-'
OR WRI != '-/-'
ORDER BY OWNER
" if ($oracle8);
print <<"EOF";
<TABLE BORDER =0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Remove marked audits">
<P>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="removeobjaudits">
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Mark</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Object name</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Object type</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Owner</TH>
EOF
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($object_name,$object_type,$owner) = $cursor->fetchrow_array) {
print <<"EOF";
<TR>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><INPUT TYPE=CHECKBOX NAME=removeaudit_$owner~$object_name></TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$object_name</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$object_type</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$owner</TD>
</TR>
EOF
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</FORM>
</TABLE>
EOF
} else {
message("There are no individual schema object audits enabled.");
}
logit("Exit subroutine auditList");
}
sub enterDDLtables {
logit("Enter subroutine enterDDLtables");
text("Choose a schema to reverse engineer select tables.");
print <<"EOF";
</CENTER>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="SCHEMATABLEDDL">
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
Choose a schema
<SELECT SIZE=1 NAME=username>
EOF
$sql = "$copyright
SELECT
DISTINCT OWNER
FROM DBA_TABLES
ORDER BY OWNER
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ($username = $cursor->fetchrow) {
print " <OPTION>$usernamen";
}
$cursor->finish;
print <<"EOF";
</SELECT>
<P>
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Display tables">
</FORM>
EOF
print "<P><HR WIDTH=90%><P>n";
text("Enter fully qualified table names separated by commas.<BR>Example: scott.table1, bill.bills_table, prod.bigtable");
print <<"EOF";
<FORM METHOD=POST ACTION="$scriptname">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE=TEXT NAME=arg SIZE=$textarea_w MAXLENGTH=1024>
<P>
<INPUT TYPE=HIDDEN NAME="object_type" VALUE="TABLEDDL">
<INPUT TYPE=HIDDEN NAME="database" VALUE="$database">
<INPUT TYPE=HIDDEN NAME="schema" VALUE="$schema]">
<INPUT TYPE=SUBMIT VALUE="Generate DDL">
<INPUT TYPE="CHECKBOX" NAME="headertype" VALUE="octet"><B>Send DDL to your workstation
</FORM>
EOF
logit("Exit subroutine enterDDLtables");
}
sub showMenu {
logit("Enter subroutine showMenu");
my $schema = shift;
my ($bgline,$key);
$bgline = "<BODY LINK=$linkcolor ALINK=$linkcolor VLINK=$linkcolor BGCOLOR=$bgcolor>n";
if ($menuimage) {
if ((-e "$ENV{'DOCUMENT_ROOT'}/$menuimage") && (-r "$ENV{'DOCUMENT_ROOT'}/$menuimage")) {
logit(" Menu image is $ENV{'DOCUMENT_ROOT'}/$menuimage and is readable");
$bgline = "<BODY LINK=$linkcolor ALINK=$linkcolor VLINK=$linkcolor BACKGROUND=$menuimage>n";
}
}
print <<"EOF";
Content-type: Text/htmlnn
<HTML>
$bgline
<FONT FACE="" SIZE="2" COLOR="$fontcolor">
<CENTER>
<P>
<TABLE "WIDTH=100%">
<TR WIDTH="100%">
<TD VALIGN="TOP">
EOF
Button("$scriptname?database=$database&object_type=LISTUSERS TARGET=body","Schema list","$headingcolor");
Button("$scriptname?database=$database&object_type=SESSIONMENU TARGET=body","Session info","$headingcolor");
Button("$scriptname?database=$database&object_type=TABLESPACES TARGET=body","Tablespaces","$headingcolor");
Button("$scriptname?database=$database&object_type=DATAFILES TARGET=body","Datafiles","$headingcolor");
Button("$scriptname?database=$database&object_type=REDOLOGS TARGET=body","Redo / Archives","$headingcolor");
Button("$scriptname?database=$database&object_type=ROLLBACKMENU TARGET=body","Rollback segs","$headingcolor");
Button("$scriptname?database=$database&object_type=PERFMENU TARGET=body","Perf / memory","$headingcolor");
Button("$scriptname?database=$database&object_type=CONTENTION TARGET=body","Locks / contends","$headingcolor");
Button("$scriptname?database=$database&object_type=EXPLAIN TARGET=body","Explain plan","$headingcolor");
Button("$scriptname?database=$database&object_type=WORKSHEET&schema=$schema TARGET=body","SQL Worksheet","$headingcolor");
Button("$scriptname?database=$database&object_type=SECURITY TARGET=body","Security","$headingcolor");
Button("$scriptname?database=$database&object_type=CONTROLFILES TARGET=body","Controlfiles","$headingcolor");
Button("$scriptname?database=$database&object_type=PARAMETERS TARGET=body","Init parameters","$headingcolor");
if ($oracle8) {
if (repmaster()) {
Button("$scriptname?database=$database&object_type=REPMASTER TARGET=body","Replication (M)","$headingcolor");
}
if (repsnapshot()) {
Button("$scriptname?database=$database&object_type=REFRESHGROUPS TARGET=body","Replication (S)","$headingcolor");
}
if (advrep()) {
Button("$scriptname?database=$database&object_type=ADVREP TARGET=body","Advanced Replication","$headingcolor");
}
}
if (parallel()) {
Button("$scriptname?database=$database&object_type=OPSPAGE TARGET=body","OPS specific","$headingcolor");
}
if ($oracle8) {
if ((backupsFound()) || (rmanCatalogExists())) {
Button("$scriptname?database=$database&object_type=BACKUPMENU TARGET=body","RMAN","$headingcolor");
}
}
if ($oracle8i && statsPackInstalled()) {
Button("$scriptname?database=$database&object_type=STATSPACKMENU TARGET=body","Statspack","$headingcolor");
}
Button("$scriptname?database=$database&object_type=RECENTEVENTS TARGET=body","Recent events","$headingcolor");
Button("$scriptname?database=$database&object_type=PREFMENU TARGET=body","Preferences","$headingcolor");
Button("$scriptname?database=$database&object_type=TASKMENU TARGET=body","DB Admin","$headingcolor");
Button("$scriptname TARGET=_top","Change connection","$headingcolor");
print <<"EOF";
<FORM METHOD=POST ACTION=$scriptname TARGET=body>
<INPUT TYPE="TEXT" NAME="arg" SIZE="10">
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="OBJECTSEARCH">
<INPUT TYPE="SUBMIT" VALUE="Search">
</FORM>
EOF
# Add plugins if found
foreach $key(keys %plugins) {
Button("$plugins{$key} TARGET=body","$key","$headingcolor");
}
Button("","<FONT COLOR=RED>DEBUG ON</FONT>","$headingcolor") if $debug;
Button("","<FONT COLOR=RED>LOGGING</FONT>","$headingcolor") if $logging;
Button("$scriptname?database=About_oracletool TARGET=body","About","$headingcolor");
print <<"EOF";
</TD>
</TR>
</TABLE>
EOF
logit("Exit subroutine showMenu");
exit;
}
sub Footer {
logit("Enter subroutine Footer");
# Usage: Footer();
# Creates a HTML footer that refers back
print <<"EOF";
</BODY>
</HTML>
EOF
logit("Exit subroutine Footer");
}
sub ErrorPage {
logit("Enter subroutine ErrorPage");
# Usage: ErrorPage ($message);
my $message = shift;
my $bgline = "<BODY BGCOLOR=$bgcolor>n";
if ($menuimage) {
if ((-e "$ENV{'DOCUMENT_ROOT'}/$menuimage") && (-r "$ENV{'DOCUMENT_ROOT'}/$menuimage")) {
logit(" Menu image is $ENV{'DOCUMENT_ROOT'}/$menuimage and is readable");
$bgline = "<BODY BACKGROUND=$menuimage>n";
}
}
print <<EOF;
Content-type: Text/htmlnn
<HTML>
<HEAD>
<TITLE>Error!</TITLE>
</HEAD>
$bgline
<FONT FACE="$font" SIZE="$fontsize" COLOR="$fontcolor">
<CENTER>
$message
</CENTER>
</BODY
</HTML>
EOF
logit("Exit subroutine ErrorPage");
exit(1);
}
sub TempPage {
logit("Enter subroutine TempPage");
# Usage: TempPage ($message,$duration,$url);
my $message = shift;
my $duration = shift;
my $url = shift;
my $bgline = "<BODY BGCOLOR=$bgcolor>n";
if ($menuimage) {
if ((-e "$ENV{'DOCUMENT_ROOT'}/$menuimage") && (-r "$ENV{'DOCUMENT_ROOT'}/$menuimage")) {
logit(" Menu image is $ENV{'DOCUMENT_ROOT'}/$menuimage and is readable");
$bgline = "<BODY BACKGROUND=$menuimage>n";
}
}
print <<EOF;
Content-type: Text/htmlnn
<HTML>
<HEAD>
<TITLE>Notice!</TITLE>
<META HTTP-EQUIV="Refresh" Content="$duration;URL=$url">
</HEAD>
$bgline
<FONT FACE="$font" SIZE="$fontsize" COLOR="$fontcolor">
<CENTER>
$message
</CENTER
</BODY
</HTML>
EOF
logit("Exit subroutine TempPage");
}
sub getDBblocksize {
logit("Enter subroutine getDBblocksize");
# Find out the database block size
my $sql = "$copyright
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'db_block_size'
";
my $cursor = $dbh->prepare($sql);
$cursor->execute;
my $db_block_size = $cursor->fetchrow_array;
$cursor->finish;
logit(" DB_BLOCK_SIZE = $db_block_size");
logit("Exit subroutine getDBblocksize");
return ($db_block_size);
}
sub getBanner {
logit("Enter subroutine getBanner");
my ($banner,$port,$foo);
# Get the oracle version info
my $sql = "$copyright
SELECT BANNER
FROM V$VERSION
WHERE BANNER LIKE 'Oracle%'
";
my $cursor = $dbh->prepare($sql);
$cursor->execute;
$banner = $cursor->fetchrow_array;
$cursor->finish;
# Add to the banner PORT information. (OS, hardware)
$sql = "$copyright
BEGIN
:port := SYS.DBMS_UTILITY.PORT_STRING;
END;
";
$cursor = $dbh->prepare($sql);
$cursor->bind_param_inout(":port", $port, 1);
$cursor->execute;
$banner = "$banner ($port)";
$cursor->finish;
$sql = "$copyright
SELECT
TO_CHAR(TO_DATE(D.VALUE,'J'),'Day, Month DD, YYYY')||' - '||
TO_CHAR(TO_DATE(S.VALUE,'sssss'),'HH24:MI:SS')
FROM V$INSTANCE D, V$INSTANCE S
WHERE D.KEY = 'STARTUP TIME - JULIAN'
AND S.key = 'STARTUP TIME - SECONDS'
" if ($oracle7);
$sql = "$copyright
SELECT
TO_CHAR(STARTUP_TIME,'Day, Month DD, YYYY - HH24:MI:SS')
FROM V$INSTANCE
" if ($oracle8);
$cursor = $dbh->prepare($sql);
$cursor->execute;
$foo = $cursor->fetchrow_array;
$cursor->finish;
$banner = "$banner<BR>Instance started on $foo<BR>";
logit("Exit subroutine getBanner");
return($banner);
}
sub showTSgraph {
logit("Enter subroutine showTSgraph");
my ($sql,$text,$link,$cursor,$tablespace,$bytesalloc,$bytesused,$bytesfree,$pctused,$pctfree);
refreshButton();
# Show a graph of tablespace usage based on free space.
# Print the page header
print <<"EOF";
</CENTER>
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0 ALIGN=CENTER>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Tablespace name</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Bytes allocated</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Bytes used</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Bytes free</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>%Used graph</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Percent used</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Percent free</TH>
EOF
$sql = "$copyright
SELECT
DF.TABLESPACE_NAME "Tablespace name",
TO_CHAR(DF.BYTES,'999,999,999,999') "Bytes allocated",
NVL(TO_CHAR(DF.BYTES-SUM(FS.BYTES),'999,999,999,999'),
TO_CHAR(DF.BYTES,'999,999,999,999')) "Bytes used",
NVL(TO_CHAR(SUM(FS.BYTES),'999,999,999,999'),0) "Bytes free",
NVL(ROUND((DF.BYTES-SUM(FS.BYTES))*100/DF.BYTES),100) "Percent used",
NVL(ROUND(SUM(FS.BYTES)*100/DF.BYTES),0) "Percent free"
FROM DBA_FREE_SPACE FS,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY
TABLESPACE_NAME ) DF
WHERE FS.TABLESPACE_NAME (+) = DF.TABLESPACE_NAME
GROUP BY DF.TABLESPACE_NAME, DF.BYTES
ORDER BY "Tablespace name"
";
# Get the space allocation info
$cursor=$dbh->prepare($sql);
$cursor->execute;
while (($tablespace,$bytesalloc,$bytesused,$bytesfree,$pctused,$pctfree) = $cursor->fetchrow_array ) {
$bytesalloc=commify($bytesalloc);
$bytesused=commify($bytesused);
$bytesfree=commify($bytesfree);
print <<"EOF";
<TR>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=TSINFO&arg=$tablespace>$tablespace</A></TD>
<TD ALIGN=RIGHT BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$bytesalloc</TD>
<TD ALIGN=RIGHT BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$bytesused</TD>
<TD ALIGN=RIGHT BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$bytesfree</TD>
<TD BGCOLOR='$cellcolor' WIDTH=100>
<TABLE>
<TR>
<TD WIDTH=$pctused BGCOLOR='$linkcolor'><BR></TD>
</TR>
</TABLE>
</TD>
<TD ALIGN=RIGHT BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$pctused%</TD>
<TD ALIGN=RIGHT BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$pctfree%</TD>
</TR>
EOF
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</TABLE>
EOF
logit("Exit subroutine showTSgraph");
}
sub killSession {
logit("Enter subroutine killSession");
$object_name = "Alter system kill session '$object_name,$schema'";
runSQL();
logit("Exit subroutine killSession");
}
sub showSessionstats {
logit("Enter subroutine showSessionstats");
refreshButton();
my ($sql,$text,$link,$infotext,$count,$cursor,$open_cursors);
$sql = "$copyright
SELECT
SEQ# "Seq#",
EVENT "Event",
SECONDS_IN_WAIT "Seconds waiting"
FROM V$SESSION_WAIT
WHERE SID = $schema
";
$text = "Session wait information.";
$link = "";
$infotext = "No info in V$SESSION_WAIT.";
DisplayTable($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
TO_CHAR(BLOCK_GETS,'999,999,999,999') "Block gets",
TO_CHAR(CONSISTENT_GETS,'999,999,999,999') "Consistent gets",
TO_CHAR(PHYSICAL_READS,'999,999,999,999') "Physical reads",
TO_CHAR(BLOCK_CHANGES,'999,999,999,999') "Block changes",
TO_CHAR(CONSISTENT_CHANGES,'999,999,999,999') "Consistent changes"
FROM V$SESS_IO
WHERE SID = $schema
";
$text = "Session I/O information.";
$link = "";
$infotext = "No info in V$SESS_IO.";
DisplayTable($sql,$text,$link,$infotext);
# Display output from V$SESSION_LONGOPS if Oracle8
if ($oracle8) {
$sql = "$copyright
SELECT
SID "SID",
UPDATE_COUNT "Update count",
COMPNAM "Component",
OBJID "Object ID",
MSG "Message",
STEPSOFAR "Step sofar",
STEPTOTAL "Step total",
SOFAR "Work sofar",
TOTALWORK "Work total",
APPLICATION_DATA_1 "Data 1",
APPLICATION_DATA_2 "Data 2",
APPLICATION_DATA_3 "Data 3",
TO_CHAR(START_TIME,'Month DD, YYYY - HH24:MI') "Start time",
TO_CHAR(CURRENT_TIME,'Month DD, YYYY - HH24:MI') "Current time"
FROM V$SESSION_LONGOPS
WHERE SID = $schema
";
if ($oracle8i) {
$sql = "$copyright
SELECT
SID "SID",
OPNAME "Operation",
TARGET "Object",
TARGET_DESC "Description",
SOFAR "Work sofar",
TOTALWORK "Work total",
UNITS "Units of measure",
TO_CHAR(START_TIME,'Month DD, YYYY - HH24:MI') "Start time",
TO_CHAR(LAST_UPDATE_TIME,'Month DD, YYYY - HH24:MI') "Last update time",
TO_CHAR(ELAPSED_SECONDS,'999,999,999,999') "Elapsed seconds",
MESSAGE "Message"
FROM V$SESSION_LONGOPS
WHERE SID = $schema
";
}
$text = "Long operation information.";
$link = "";
$infotext = "No info in V$SESSION_LONGOPS.";
DisplayTable($sql,$text,$link,$infotext);
}
$sql = "$copyright
SELECT
SQL_TEXT "SQL text"
FROM V$OPEN_CURSOR
WHERE SID = $schema
";
$text = "Open cursors.";
$link = "";
$infotext = "No open cursors for this session.";
$count = DisplayTable($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
VALUE
FROM V$PARAMETER
WHERE NAME = 'open_cursors'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$open_cursors = $cursor->fetchrow_array;
$cursor->finish;
if ($count == $open_cursors) {
message("Warning: This session has opened the maximum number of cursors allowed by a session ($count). You may need to increase the OPEN_CURSORS parameter. This may also be an indication that your application is not closing cursors properly.");
}
$sql = "$copyright
SELECT
OBJECT "Object name",
TYPE "Object type",
OWNER "Owner"
FROM V$ACCESS
WHERE SID= $schema
";
$text = "Objects being accessed by SID $schema.";
$infotext = "This SID is not accessing any objects.";
ObjectTable($sql,$text,$infotext);
# Show session statistics for a particular SID
$sql = "$copyright
SELECT
A.NAME "Parameter name",
TO_CHAR(B.VALUE,'999,999,999,999,999') "Value",
DECODE(A.CLASS,
1,'User',
2,'Redo',
4,'Enqueue',
8,'Cache',
16,'OS',
32,'Parallel server',
64,'SQL',
128,'Debug') "Class"
FROM V$STATNAME A, V$SESSTAT B
WHERE A.STATISTIC# = B.STATISTIC#
AND B.SID = $schema
AND B.VALUE > 0
ORDER BY A.CLASS, A.NAME
";
$text = "Session statistics for SID '$schema'. Only non-zero values displayed.";
$link = "";
$infotext = "No current session statistics for SID $schema.";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showSessionstats");
}
sub showFilegraph {
logit("Enter subroutine showFilegraph");
my ($sql,$text,$link,$cursor,$totalreads);
my ($totalwrites,$file_name,$phyrds,$rdpct,$phywrts,$wrtpct);
refreshButton();
# Show a graph of datafile activity based on physical writes.
$sql = "$copyright
SELECT
SUM(PHYRDS), SUM(PHYWRTS)
FROM V$FILESTAT
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
($totalreads,$totalwrites) = $cursor->fetchrow_array;
$cursor->finish;
$sql = "$copyright
SELECT
NAME "File name",
TO_CHAR(PHYRDS,'999,999,999,999') "Physical reads",
TO_CHAR(PHYRDS * 100 / $totalreads,'999') "Physical reads %",
TO_CHAR(PHYWRTS,'999,999,999,999') "Physical writes",
TO_CHAR(PHYWRTS * 100 / $totalwrites,'999') "Physical writes %"
FROM V$DATAFILE DF, V$FILESTAT FS
WHERE DF.FILE# = FS.FILE#
ORDER BY PHYWRTS DESC
";
print <<"EOF";
Datafiles are ordered by physical writes, descending.<P>
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1 ALIGN=CENTER>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>File name</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Reads</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Percentage</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Writes</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Percentage</TH>
EOF
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ( ($file_name,$phyrds,$rdpct,$phywrts,$wrtpct) = $cursor->fetchrow_array ) {
print <<"EOF";
<TR>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=DATAFILE&arg=$file_name>$file_name</A></TD>
<TD BGCOLOR='$cellcolor'>
<TABLE>
<TR>
<TD WIDTH=$rdpct BGCOLOR='$linkcolor'><BR></TD>
</TR>
</TABLE>
</TD>
<TD ALIGN=RIGHT BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$rdpct%</TD>
<TD BGCOLOR='$cellcolor'>
<TABLE>
<TR>
<TD WIDTH=$wrtpct BGCOLOR='$linkcolor'><BR></TD>
</TR>
</TABLE>
</TD>
<TD ALIGN=RIGHT BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$wrtpct%</TD>
</TR>
EOF
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</TABLE>
EOF
logit("Exit subroutine showFilegraph");
}
sub showTSfilegraph {
logit("Enter subroutine showTSfilegraph");
my ($sql,$text,$link,$cursor,$totalreads);
my ($totalwrites,$file_name,$phyrds,$rdpct,$phywrts,$wrtpct);
my ($file_id,$bytes,$bytesused,$percent);
# Show a graph of datafile activity based on physical writes.
$sql = "$copyright
SELECT
SUM(PHYRDS), SUM(PHYWRTS)
FROM V$FILESTAT
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
($totalreads,$totalwrites) = $cursor->fetchrow_array;
$cursor->finish;
$sql = "$copyright
SELECT
NAME "File name",
TO_CHAR(PHYRDS,'999,999,999,999') "Physical reads",
TO_CHAR(PHYRDS * 100 / $totalreads,'999') "Physical reads %",
TO_CHAR(PHYWRTS,'999,999,999,999') "Physical writes",
TO_CHAR(PHYWRTS * 100 / $totalwrites,'999') "Physical writes %"
FROM V$DATAFILE DF, V$FILESTAT FS
WHERE DF.FILE# = FS.FILE#
AND DF.FILE# IN
(SELECT FILE_ID
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '$schema')
ORDER BY PHYWRTS DESC
";
print <<"EOF";
Datafile I/O statistics.<BR>
Entries are ordered by physical writes, descending.<BR>
Percentage shown is in comparison to all other datafiles in database.<P>
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>File name</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Reads</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Percentage</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Writes</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Percentage</TH>
EOF
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ( ($file_name,$phyrds,$rdpct,$phywrts,$wrtpct) = $cursor->fetchrow_array ) {
print <<"EOF";
<TR>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=DATAFILE&arg=$file_name>$file_name</A></TD>
<TD BGCOLOR='$cellcolor' WIDTH=100>
<TABLE>
<TR>
<TD WIDTH=$rdpct BGCOLOR='$linkcolor'><BR></TD>
</TR>
</TABLE>
</TD>
<TD ALIGN=RIGHT BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$rdpct%</TD>
<TD BGCOLOR='$cellcolor' WIDTH=100>
<TABLE>
<TR>
<TD WIDTH=$wrtpct BGCOLOR='$linkcolor'><BR></TD>
</TR>
</TABLE>
</TD>
<TD BGCOLOR='$cellcolor' ALIGN=RIGHT><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$wrtpct%</TD>
</TR>
EOF
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</TABLE>
EOF
print "<BR>n";
# Free space
$sql = "$copyright
SELECT D.FILE_NAME "Filename",
D.FILE_ID "File ID",
TO_CHAR(D.BYTES,'999,999,999,999') "Bytes",
TO_CHAR(SUM(E.BYTES),'999,999,999,999') "Bytes used",
TO_CHAR(SUM(E.BYTES) / D.BYTES * 100,'999.99') "% used"
FROM SYS.DBA_EXTENTS E,
SYS.DBA_DATA_FILES D
WHERE D.FILE_ID = E.FILE_ID (+)
AND D.TABLESPACE_NAME = '$schema'
GROUP BY D.FILE_NAME, D.FILE_ID, D.BYTES
";
print <<"EOF";
Datafile free space.<P>
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>File name</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>File ID</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Bytes</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Bytes used</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Graph</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Percentage full</TH>
EOF
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ( ($file_name,$file_id,$bytes,$bytesused,$percent) = $cursor->fetchrow_array ) {
print <<"EOF";
<TR>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=DATAFILE&arg=$file_name>$file_name</A></TD>
<TD ALIGN=RIGHT BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$file_id</TD>
<TD ALIGN=RIGHT BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$bytes</TD>
<TD ALIGN=RIGHT BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$bytesused</TD>
<TD BGCOLOR='$cellcolor' WIDTH=100>
<TABLE>
<TR>
<TD WIDTH=$percent BGCOLOR='$linkcolor'><BR></TD>
</TR>
</TABLE>
</TD>
<TD ALIGN=RIGHT BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$percent%</TD>
</TR>
EOF
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</TABLE>
EOF
logit("Exit subroutine showTSfilegraph");
}
sub message {
logit("Enter subroutine message");
# Print a message to the user
my $message = shift;
$message = "<FONT COLOR='$infocolor' SIZE='$fontsize' FACE='$font'>$message</FONT>";
print "<P><B>$message</B></P>n";
logit("Exit subroutine message");
}
sub text {
logit("Enter subroutine text");
# Print a message to the user
my $message = shift;
$message = "<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$message</FONT>";
print "<P><B>$message</B></P>n";
logit("Exit subroutine text");
}
sub commify {
logit("Enter subroutine commify");
# Puts commas in a numeral
my $text = reverse $_[0];
$text =~ s/(ddd)(?=d)(?!d*.)/$1,/g;
return scalar reverse $text;
logit("Exit subroutine commify");
}
sub dbfileBlock {
logit("Enter subroutine dbfileBlock");
my ($sql,$text,,$infotext,$link);
$sql = "$copyright
SELECT
SEGMENT_NAME,
SEGMENT_TYPE,
OWNER
FROM DBA_EXTENTS
WHERE FILE_ID = (
SELECT FILE#
FROM V$DATAFILE
WHERE NAME = '$object_name')
AND $whereclause BETWEEN
BLOCK_ID AND BLOCK_ID + BLOCKS - 1
";
$text = "Object occupying block $whereclause of file $object_name";
$link = "";
$infotext = "No object found";
ObjectTable($sql,$text,$infotext);
logit("Exit subroutine dbfileBlock");
}
sub statsPackAdmin {
logit("Enter subroutine statsPackAdmin");
my ($sql,$command);
$command = $query->param('command');
logit(" Command is $command");
if ($command eq "snapshot") {
# Take a statsPack snapshot.
message("Taking Statspack snapshot...");
$sql = "
BEGIN
STATSPACK.SNAP;
END;
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$cursor->finish;
message("Statspack snapshot complete.");
}
if ($command eq "statsgroups") {
# Show groups of statistics between each database startup / shutdown.
my ($start_snap_id,$end_snap_id,$startup_time,$numsnaps,$start_snap_time,$end_snap_time);
text("Choose one or more groups of statistics to analyze and / or delete.");
$sql = "
SELECT
MIN(SNAP_ID),
MAX(SNAP_ID),
TO_CHAR(STARTUP_TIME,'Mon DD YYYY @ HH24:MI:SS'),
TO_CHAR(COUNT(*),'999,999,999,999'),
TO_CHAR(MIN(SNAP_TIME),'Mon DD YYYY @ HH24:MI:SS'),
TO_CHAR(MAX(SNAP_TIME),'Mon DD YYYY @ HH24:MI:SS')
FROM STATS$SNAPSHOT
WHERE INSTANCE_NUMBER = (
SELECT INSTANCE_NUMBER
FROM V$INSTANCE
)
GROUP BY STARTUP_TIME
ORDER BY STARTUP_TIME
";
print <<"EOF";
<TABLE BORDER =0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Execute">
<P>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="STATSPACKADMIN">
<INPUT TYPE="HIDDEN" NAME="command" VALUE="snapadmin">
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Analyze</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Delete</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Startup time</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'># Snapshots</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>First snap</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Last snap</TH>
EOF
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($start_snap_id,$end_snap_id,$startup_time,$numsnaps,$start_snap_time,$end_snap_time) = $cursor->fetchrow_array) {
print <<"EOF";
<TR>
<TD ALIGN=CENTER BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><INPUT TYPE=CHECKBOX NAME=analyze~$start_snap_id~$end_snap_id></TD>
<TD ALIGN=CENTER BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><INPUT TYPE=CHECKBOX NAME=delete~$start_snap_id~$end_snap_id></TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$startup_time</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$numsnaps</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$start_snap_time</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$end_snap_time</TD>
</TR>
EOF
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</FORM>
</TABLE>
EOF
}
if ($command eq "snapadmin") {
# Analyze and / or delete snapshots.
my (@params,$param,$start_snap_id,$end_snap_id);
@params = $query->param;
foreach $param(@params) {
if ($param =~ /^analyze/) {
$param =~ s/analyze~//;
($start_snap_id,$end_snap_id) = split("~", $param);
logit(" Analyzing all snapshots from $start_snap_id to $end_snap_id");
snapAnalyze($start_snap_id,$end_snap_id);
}
if ($param =~ /^delete/) {
$param =~ s/delete~//;
($start_snap_id,$end_snap_id) = split("~", $param);
logit(" Deleting all snapshots from $start_snap_id to $end_snap_id");
snapDelete($start_snap_id,$end_snap_id);
}
}
}
logit("Exit subroutine statsPackAdmin");
}
sub snapAnalyze {
logit("Enter subroutine snapAnalyze");
my $start_snap_id = shift;
my $end_snap_id = shift;
my ($sql,$cursor,$startup_time,$instance_number,$text,$link,$infotext);
my ($numsnaps,$start_snap_time,$end_snap_time);
# Get the instance number we are attached to. All queries
# should be based on instance number, due to OPS.
$sql = "
SELECT
INSTANCE_NUMBER
FROM V$INSTANCE
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$instance_number = $cursor->fetchrow_array;
$cursor->finish;
# Check to make sure we are not spanning startup / shutdown
# with the range of snap_id's submitted. Blow up if we are.
$sql = "
SELECT COUNT(DISTINCT STARTUP_TIME)
FROM STATS$SNAPSHOT
WHERE INSTANCE_NUMBER = $instance_number
AND SNAP_ID BETWEEN $start_snap_id and $end_snap_id
";
if (recordCount($sql) > 1) {
ErrorPage("You have specified a range that spans database startups. That is invalid.");
}
# Get some values for informational purposes.
$sql = "
SELECT
TO_CHAR(STARTUP_TIME,'Mon DD YYYY @ HH24:MI:SS'),
TO_CHAR(COUNT(*),'999,999,999,999'),
TO_CHAR(MIN(SNAP_TIME),'Mon DD YYYY @ HH24:MI:SS'),
TO_CHAR(MAX(SNAP_TIME),'Mon DD YYYY @ HH24:MI:SS')
FROM STATS$SNAPSHOT
WHERE INSTANCE_NUMBER = $instance_number
AND SNAP_ID BETWEEN $start_snap_id and $end_snap_id
GROUP BY STARTUP_TIME
ORDER BY STARTUP_TIME
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
($startup_time,$numsnaps,$start_snap_time,$end_snap_time) = $cursor->fetchrow_array;
$cursor->finish;
text("Snapshot analyzation for period spanning $start_snap_time to $end_snap_time ($numsnaps snapshots)<BR>Database: $database - Instance#: $instance_number");
# Tablespace I/O information.
$sql = "
SELECT
A.TSNAME "Tablespace name",
TO_CHAR(B.PHYRDS-A.PHYRDS,'999,999,999,999') "Phy. reads",
TO_CHAR(B.PHYWRTS-A.PHYWRTS,'999,999,999,999') "Phy. writes"
FROM
(SELECT
TSNAME,
PHYRDS,
PHYWRTS
FROM STATS$FILESTATXS
WHERE INSTANCE_NUMBER = $instance_number
AND SNAP_ID = $start_snap_id) A,
(SELECT
TSNAME,
PHYRDS,
PHYWRTS
FROM STATS$FILESTATXS
WHERE INSTANCE_NUMBER = $instance_number
AND SNAP_ID = $end_snap_id) B
WHERE A.TSNAME = B.TSNAME
ORDER BY 3 DESC , 2 DESC
";
$text = "Tablespace I/O statistics.";
$link = "$scriptname?database=$database&object_type=TSINFO";
$infotext = "No statistics found.";
DisplayTable($sql,$text,$link,$infotext);
}
sub objectFragMap {
logit("Enter subroutine objectFragMap");
my ($sql1,$sql2,$cursor1,$cursor2,$file_name,$file_id,$bytes,$blocks);
my ($block_id,$block_count,@block_ids,@block_counts,$counter,$extent_count);
my ($collength,$colcounter,$arraycounter,$blockstart,$blockfinish,$i);
my ($extentcolor,$foo,$partitioned,$iot_type);
if ($oracle8) {
$sql1 = "$copyright
SELECT
PARTITIONED,
IOT_TYPE
FROM DBA_TABLES
WHERE OWNER = '$schema'
AND TABLE_NAME = '$object_name'
";
$cursor1 = $dbh->prepare($sql1);
$cursor1->execute;
($partitioned,$iot_type) = $cursor1->fetchrow_array;
$cursor1->finish;
if ($partitioned eq "YES") {
message("Extent mapping for partitioned tables is not yet supported.");
exit;
}
if ($iot_type eq "IOT") {
message("Extent mapping for Index Organized Tables is not yet supported.");
exit;
}
}
if (checkPriv("EXECUTE ANY PROCEDURE") && checkPriv("ANALYZE ANY")) {
logit(" Executing DBMS_SPACE.UNUSED_SPACE");
$sql = "$copyright
BEGIN
SYS.DBMS_SPACE.UNUSED_SPACE(?,?,?,?,?,?,?,?,?,?);
END;
";
my ($total_blocks,$total_bytes,$unused_blocks,$unused_bytes,$last_used_extent_file_id);
my ($last_used_extent_block_id,$last_used_block,$usedpct,$unusedpct,$text,$link,$used_blocks);
$cursor = $dbh->prepare($sql);
$cursor->bind_param(1,"$schema");
$cursor->bind_param(2,"$object_name");
$cursor->bind_param(3,"TABLE");
$cursor->bind_param_inout(4, $total_blocks,80);
$cursor->bind_param_inout(5, $total_bytes,80);
$cursor->bind_param_inout(6, $unused_blocks,80);
$cursor->bind_param_inout(7, $unused_bytes,80);
$cursor->bind_param_inout(8, $last_used_extent_file_id,80);
$cursor->bind_param_inout(9, $last_used_extent_block_id,80);
$cursor->bind_param_inout(10,$last_used_block,80);
$cursor->execute;
$cursor->finish;
logit(" Stored procedure returned :nTotal blocks: $total_blocksnTotal bytes: $total_bytesnUnused blocks: $unused_blocksnUnused bytes: $unused_bytesnLast used extent file id: $last_used_extent_file_idnlast used extent block ID: $last_used_extent_block_id");
$sql = "$copyright
SELECT
TO_CHAR($total_blocks,'999,999,999,999') "Total blocks",
TO_CHAR($total_bytes,'999,999,999,999') "Total bytes",
TO_CHAR($unused_blocks,'999,999,999,999') "Unused blocks",
TO_CHAR($unused_bytes,'999,999,999,999') "Unused bytes",
TO_CHAR($last_used_extent_file_id,'999,999,999,999') "Last used extent file ID",
TO_CHAR($last_used_extent_block_id,'999,999,999,999') "Last used extent block_id"
FROM DUAL
";
$text = "Real-time space usage via DBMS_SPACE.";
$link = "";
DisplayTable($sql,$text,$link);
$used_blocks = $total_blocks-$unused_blocks;
$usedpct = int(($used_blocks/$total_blocks)*100);
$unusedpct = 100-$usedpct;
$unused_blocks = commify($unused_blocks);
$unused_bytes = commify($unused_bytes);
text("$usedpct% of the allocated space for this table is being used. There are $unused_blocks blocks above the highwater mark, totaling $unused_bytes bytes which are allocated, but have never been used since table creation or the last truncate.") unless $DBI::errstr;
}
text("</CENTER>Extent mapping for object $schema.$object_name. This may be a long running query for large objects with many extents, or for very large databases.");
# Get a list of files that this object spans.
$sql1 = "$copyright
SELECT
FILE_NAME,
FILE_ID,
TO_CHAR(BYTES,'999,999,999,999'),
BLOCKS
FROM DBA_DATA_FILES
WHERE FILE_ID IN (
SELECT DISTINCT
FILE_ID
FROM DBA_EXTENTS
WHERE SEGMENT_NAME = '$object_name'
AND OWNER = '$schema'
)
ORDER BY FILE_ID
";
$cursor1=$dbh->prepare($sql1) or ErrorPage("$DBI::errstr");
$cursor1->execute;
text("Extents are shown in alternating white / blue so that they can be distinguished from each other. Blocks shown in green are either free or allocated by other objects.");
# Loop through the datafiles.
while (($file_name,$file_id,$bytes,$blocks) = $cursor1->fetchrow_array) {
logit(" Working on file $file_name for object $schema.$object_name");
undef @block_ids;
undef @block_counts;
$sql2 = "$copyright
SELECT BLOCK_ID, BLOCKS
FROM DBA_EXTENTS
WHERE FILE_ID = '$file_id'
AND SEGMENT_NAME = '$object_name'
AND OWNER = '$schema'
ORDER BY BLOCK_ID
";
$cursor2=$dbh->prepare($sql2);
$cursor2->execute;
while (($block_id, $block_count) = $cursor2->fetchrow_array) {
push @block_ids, $block_id;
push @block_counts, $block_count;
}
$cursor2->finish;
$extent_count = $#block_ids+1;
logit(" Extent count is $extent_count");
# This is for serious debugging only. Major output for objects with
# many extents.
# for ($counter = 0; $counter <= $#block_ids; $counter++) {
# logit(" Segment: $object_name Owner: $schema");
# logit(" Block_id: $block_ids[$counter] Count: $block_counts[$counter]");
# }
# Create the image
$collength = 150;
$colcounter = 0;
$arraycounter = 0;
text("</CENTER>Extent map for $schema.$object_name, datafile $file_name<BR>File is $bytes bytes ($blocks blocks of $db_block_size bytes)<BR>$schema.$object_name has $extent_count extent(s) in this datafile");
print "<FONT SIZE=1><B></CENTER>n";
print "<FONT COLOR=GREEN>";
$blockstart = $block_ids[$arraycounter];
$blockfinish = $blockstart+$block_counts[$arraycounter]-1;
logit(" Block start = $blockstart: Block finish = $blockfinish");
# Go from 0 to the number of blocks in the datafile
for ($i = 1; $i < $blocks; $i++) {
# If $i is equal to the start block_id of the extent, turn the font blue.
if ($i == $blockstart) {
if ($foo) {
$extentcolor = "BLUE";
$foo--;
} else {
$extentcolor = "WHITE";
$foo++
}
print "<FONT COLOR=$extentcolor>";
# logit(" i reached blockstart $blockstart: i = $i");
}
print "I";
if ($i == $blockfinish) {
print "<FONT COLOR=GREEN>";
# logit(" i reached blockfinish $blockfinish: i = $i");
$arraycounter++;
if ($arraycounter > $#block_ids) {
logit(" End of array reached at element $arraycounter.");
$blockstart = $blocks+1;
$blockfinish = $blockstart+$block_counts[$arraycounter];
} else {
$blockstart = $block_ids[$arraycounter];
$blockfinish = $blockstart+$block_counts[$arraycounter]-1;
}
}
$colcounter++;
if ($colcounter == $collength) {
print "<BR>n";
$colcounter = 0;
}
}
}
print "</FONT><CENTER></B>";
print "<FONT FACE="$font" SIZE="$fontsize" COLOR="$fontcolor">n";
$cursor1->finish;
logit("Exit subroutine objectFragMap");
}
sub fragMap {
logit("Enter subroutine fragMap");
# Creates a datafile fragmentation map,
# showing used and unused blocks.
my ($sql,$sql1,$cursor,$cursor1,$file_id,$blocks);
my ($collength,$width,$height,$hstart,$vstart);
my ($blockused,$blockfree,$x,$y,$numblocks);
my ($colcounter,$image,$id,$i,$pointer,$length);
my ($block_id,$counter,$file_name,$bytes);
my (@datafiles);
if ($whereclause eq "datafile") {
push @datafiles, $object_name;
}
if ($whereclause eq "tablespace") {
$sql = "$copyright
SELECT
FILE_NAME
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '$object_name'
ORDER BY FILE_NAME
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
while ($file_name = $cursor->fetchrow_array) {
push @datafiles, $file_name;
}
$cursor->finish;
}
# Loop through the datafiles.
foreach $file_name(@datafiles) {
$sql = "$copyright
SELECT
FILE_ID,
TO_CHAR(BYTES,'999,999,999,999'),
BLOCKS
FROM DBA_DATA_FILES
WHERE FILE_NAME = '$file_name'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
while (($file_id,$bytes,$blocks) = $cursor->fetchrow_array) {
$sql1 = "$copyright
SELECT BLOCK_ID, BLOCKS
FROM DBA_EXTENTS
WHERE FILE_ID = '$file_id'
ORDER BY BLOCK_ID
";
$cursor1=$dbh->prepare($sql1);
$cursor1->execute;
# Create the image
$collength = 150;
$colcounter = 1;
$counter = 1;
$pointer = 1;
$hstart = 0;
$x = 0;
text("</CENTER>Fragmentation map for datafile $file_name<BR>File is $bytes bytes ($blocks blocks of $db_block_size bytes)");
print "<FONT SIZE=1><B></CENTER>n";
while (($block_id,$numblocks) = $cursor1->fetchrow) {
print "<FONT COLOR=GREEN>";
for ($i = $pointer; $i < $block_id; $i++) {
print "I";
$x = $x+1;
if ($x == $collength) {
print "<BR>n";
$x = $hstart;
$colcounter=0;
}
$pointer++;
}
print "<FONT COLOR=RED>";
for ($i = 1; $i <= $numblocks; $i++ ) {
print "I";
$x = $x+1;
if ($x == $collength+$hstart) {
print "<BR>n";
$x = $hstart;
$colcounter=0;
}
$counter = $block_id + $numblocks + 1;
$pointer++;
}
}
$cursor1->finish;
print "<FONT COLOR=GREEN>";
for ($i = $pointer; $i <= $blocks; $i++) {
print "I";
$x = $x+1;
if ($x == $collength+$hstart) {
print "<BR>n";
$x = $hstart;
$colcounter=0;
}
$pointer++;
}
}
$cursor->finish;
print "</FONT><CENTER></B>";
print "<FONT FACE="$font" SIZE="$fontsize" COLOR="$fontcolor">n";
}
logit("Exit subroutine fragMap");
}
sub about {
logit("Enter subroutine about");
# Give me a pat on the back. :)
my ($title,$heading,$fontsize,$encstring);
$title = "Oracletool v$VERSION";
$heading = "</CENTER><B>Thanks for using Oracletool!</B><BR>";
if ($encryption_enabled) {
$encstring = "Cookie encryption is enabled, level $encryption_enabled of 2.";
} else {
$encstring = "Cookie encryption is not enabled.";
}
Header($title,$heading,$font,$fontsize,$fontcolor,$bgcolor);
print <<"EOF";
<BR>
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TR>
<TD BGCOLOR='$cellcolor'>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
oracletool.pl version $VERSION<BR>
<HR ALIGN=LEFT WIDTH=75% NOSHADE SIZE=1>
$encstring<BR>
Your theme is set to $theme.
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
<BR>
Written and maintained by Adam vonNieda in Kansas, USA.<P>
You may distribute under the terms of either the GNU General Public<BR>
License or the Artistic License, as specified in the Perl README file,<BR>
with the exception that it cannot be placed on a CD-ROM or similar media<BR>
for commercial distribution without the prior approval of the author.<P>
Home site: <A HREF="http://www.oracletool.com">http://www.oracletool.com</A><BR>
Join the announcement <A HREF="http://www.oracletool.com/mailinglist.html">announcement list</A>
 (one, maybe two emails a month)..<BR><BR>
Questions, comments, bug reports, and suggestions are encouraged!<BR>
Tell me what to do to make it better!<BR>
Drop me a note at <A HREF="mailto:adam@oracletool.com">adam@oracletool.com</A>.<BR><BR>
I'd like to thank everyone (too many to name) who has contributed to this<BR>
project be it through suggestions, criticism, or code contributions.<BR>
Oracletool is a useful product because of you!
EOF
logit("Exit subroutine about");
exit;
}
sub advrep {
logit("Enter subroutine advrep");
# Find out if this server is an advanced replication
# master server.
my ($sql,$cursor,$count);
$count = 0;
$sql = "$copyright
SELECT
COUNT(*)
FROM DBA_REPGROUP
";
$cursor=$dbh->prepare($sql);
if ($cursor) {
$cursor->execute;
$count=$cursor->fetchrow;
$cursor->finish;
return($count);
} else {
return(0);
}
logit("Exit subroutine advrep");
}
sub repmaster {
logit("Enter subroutine repmaster");
# Find out if this server is the master for
# replication.
my ($sql,$cursor,$count);
$sql = "$copyright
SELECT
COUNT(*)
FROM DBA_REGISTERED_SNAPSHOTS
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$count=$cursor->fetchrow;
$cursor->finish;
return $count;
logit("Exit subroutine repmaster");
}
sub repsnapshot {
logit("Enter subroutine repsnapshot");
# Find out if this instance has snapshots
# replicated from a master.
my ($sql,$cursor,$count);
$sql = "$copyright
SELECT
COUNT(*)
FROM DBA_SNAPSHOTS
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$count=$cursor->fetchrow;
$cursor->finish;
return $count;
logit("Exit subroutine repsnapshot");
}
sub rmanCatalogExists {
logit("Enter subroutine rmanCatalogExists");
# Find out if there is one or more Recovery Manager
# catalogs in this database. This pertains to
# Oracle8 and above only.
my ($sql,$cursor,$count);
$sql = "$copyright
SELECT
COUNT(*)
FROM DBA_TABLES
WHERE TABLE_NAME = 'RCVER'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$count = $cursor->fetchrow_array;
$cursor->finish;
return($count);
logit("Exit subroutine rmanCatalogExists");
}
sub backupsFound {
logit("Enter subroutine backupsFound");
my ($sql,$cursor,$count);
$sql = "$copyright
SELECT
COUNT(*)
FROM V$BACKUP_DATAFILE
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$count = $cursor->fetchrow_array;
$cursor->finish;
return($count);
logit("Exit subroutine backupsFound");
}
sub parallel {
logit("Enter subroutine parallel");
# Find out if this is a parallel database.
# Oracle8 only. I'm not going to cover Oracle7
# parallel databases at this time.
if ($oracle8) {
my $sql = "$copyright
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'parallel_server'
";
my $cursor = $dbh->prepare($sql);
$cursor->execute;
my $foo = $cursor->fetchrow_array;
$cursor->finish;
if ( $foo eq "TRUE" ) {
return 1;
} else {
return 0;
}
} else {
return 0;
}
logit("Exit subroutine parallel");
}
sub DisplayGraph {
logit("Enter subroutine DisplayGraph");
my $graphtype = shift;
my $object_name = shift;
my $text = shift;
my ($file,$tablespace_name,$rgif,$vgif,$sql,$cursor);
if ($graphtype eq "dbfile") {
$sql = "$copyright
SELECT TABLESPACE_NAME
FROM DBA_DATA_FILES
WHERE FILE_NAME = '$object_name'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$tablespace_name = $cursor->fetchrow_array;
$cursor->finish;
$file = basename($object_name);
$rgif = "$ENV{DOCUMENT_ROOT}/$repository/$database/$tablespace_name/$file.gif";
$vgif = "$repository/$database/$tablespace_name/$file.gif";
}
if ($graphtype eq "sessions") {
$rgif = "$ENV{DOCUMENT_ROOT}/$repository/$database/sessions.gif";
$vgif = "$repository/$database/sessions.gif";
}
# If the image file does not exist, return.
if (! -e $rgif) {
return (1);
}
print <<"EOF" if defined ($text);
<P><B>$text</B>
EOF
print "<IMG SRC=$vgif>n";
logit("Exit subroutine DisplayGraph");
}
sub showGrantButton {
logit("Enter subroutine showGrantButton");
my ($sql,$cursor,$count);
$sql = "$copyright
SELECT COUNT(*)
FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$count = $cursor->fetchrow_array;
$cursor->finish;
if ($count > 0 ) {
print <<"EOF";
<BR>
<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0>
<TR>
<TD ALIGN=CENTER>
<FORM METHOD="GET" ACTION="$scriptname">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="OBJECTGRANTS">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="$object_name">
<INPUT TYPE="SUBMIT" NAME="tablerows" VALUE="Display ($count) grants">
</FORM>
</TD>
</TR>
</TABLE>
EOF
}
logit("Exit subroutine showGrantButton");
return($count);
}
sub showObjectGrants {
logit("Enter subroutine showObjectGrants");
my ($sql,$cursor,$text,$link);
$sql = "$copyright
SELECT
GRANTOR "Grantor",
GRANTEE "Grantee",
PRIVILEGE "Privilege",
GRANTABLE "Grantable"
FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
ORDER BY GRANTEE, PRIVILEGE
";
$text = "Grants for object $schema.$object_name";
$link = "";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showObjectGrants");
}
sub Button {
logit("Enter subroutine Button");
my $href = shift;
my $text = shift;
my $bgcolor = shift;
my $align = shift;
my $pixels = shift;
$align = "CENTER" unless $align;
$pixels = 100 unless $pixels;
print <<"EOF";
</FONT>
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0 ALIGN=$align WIDTH=$pixels>
<TR>
<TD VALIGN="TOP" WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1 WIDTH=100%>
<TR ALIGN="CENTER">
<TD BGCOLOR='$cellcolor'><B><FONT SIZE="$menufontsize">
EOF
if ($href) {
print "<A HREF=$href>$text</A>";
} else {
print "<FONT COLOR=$bordercolor>$text</FONT>";
}
print <<"EOF";
</B></TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
<TABLE WIDTH="100" CELLPADDING="1" CELLSPACING="0" BORDER="0">
<TD></TD>
</TABLE>
EOF
logit("Exit subroutine Button");
}
sub showIndextype {
logit("Enter subroutine showIndextype");
my ($sql,$text,$link);
# General indextype info
$sql = "$copyright
SELECT
INDEXTYPE_NAME "Indextype name",
IMPLEMENTATION_SCHEMA "Implementation schema",
IMPLEMENTATION_NAME "Implementation name",
IMPLEMENTATION_VERSION "Implementation version",
TO_CHAR(NUMBER_OF_OPERATORS,'999,999,999,999') "# operators"
FROM DBA_INDEXTYPES
WHERE INDEXTYPE_NAME = '$object_name'
AND OWNER = '$schema'
";
$text = "";
$link = "";
DisplayTable($sql,$text,$link);
$sql = "$copyright
SELECT
INDEXTYPE_NAME "Indextype name",
BINDING# "Binding#",
OPERATOR_NAME "Operator name"
FROM DBA_INDEXTYPE_OPERATORS
WHERE INDEXTYPE_NAME = '$object_name'
AND OWNER = '$schema'
ORDER BY BINDING#
";
$text = "Indextype operators";
$link = "";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showIndextype");
}
sub showLibrary {
logit("Enter subroutine showLibrary");
my ($sql,$text,$link);
# General library info
$sql = "$copyright
SELECT
LIBRARY_NAME "Library name",
FILE_SPEC "Filename",
DECODE(DYNAMIC,'N','No','Y','Yes') "Dynamic?",
STATUS "Status"
FROM DBA_LIBRARIES
WHERE LIBRARY_NAME = '$object_name'
AND OWNER = '$schema'
";
$text = "";
$link = "";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showLibrary");
}
sub showOperator {
logit("Enter subroutine showOperator");
my ($sql,$text,$link);
# General operator info
$sql = "$copyright
SELECT
OPERATOR_NAME "Operator name",
TO_CHAR(NUMBER_OF_BINDS,'999,999,999,999') "# Binds"
FROM DBA_OPERATORS
WHERE OPERATOR_NAME = '$object_name'
AND OWNER = '$schema'
";
$text = "";
$link = "";
DisplayTable($sql,$text,$link);
$sql = "$copyright
SELECT
BINDING# "Binding#",
FUNCTION_NAME "Function name",
RETURN_SCHEMA "Return schema",
RETURN_TYPE "Return type",
IMPLEMENTATION_TYPE_SCHEMA "Imp type schema",
IMPLEMENTATION_TYPE "Imp type"
FROM DBA_OPBINDINGS
WHERE OPERATOR_NAME = '$object_name'
AND OWNER = '$schema'
ORDER BY BINDING#
";
$text = "Bindings";
$link = "";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showOperator");
}
sub showQueue {
logit("Enter subroutine showQueue");
my ($sql,$text,$link);
# General queue info
$sql = "$copyright
SELECT
NAME "Name",
QUEUE_TABLE "Queue table",
QID "ID",
QUEUE_TYPE "Type",
TO_CHAR(MAX_RETRIES,'999,999,999,999') "Max retries",
TO_CHAR(RETRY_DELAY,'999,999,999,999') "Retry delay",
ENQUEUE_ENABLED "Enqueue enabled?",
DEQUEUE_ENABLED "Dequeue enabled?",
RETENTION "Retention",
USER_COMMENT "User comment"
FROM DBA_QUEUES
WHERE NAME = '$object_name'
AND OWNER = '$schema'
";
$text = "";
$link = "";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showQueue");
}
sub recordCount {
logit("Enter subroutine recordCount");
my ($sql,$cursor,$record,$count);
$sql = shift;
$cursor = $dbh->prepare($sql) or logit(" $DBI::errstr");
$cursor->execute;
while ($record = $cursor->fetchrow_array) {
$count++;
}
$cursor->finish;
logit(" Count = $count");
logit(" Enter subroutine recordCount");
logit("Exit subroutine recordCount");
return($count);
}
sub rmanBackups {
logit("Enter subroutine rmanBackups");
my ($sql,$cursor,$text,$infotext,$link,$command,$count);
my ($recid,$stamp,$set_stamp,$filenum,$name,$inc_level);
my ($ckpt_chng,$ckpt_time,$mar_cor,$med_cor,$log_cor);
my ($df_blocks,$blocks,$comp_time,$rowcount,$backupfile_record);
my ($set_count,@backupfile_array);
$command = $query->param('command') || shift;
# Commands:
# menu: Show menu
# datafiles_added: Show datafiles added since last backup
# last_backup: Show most recent information about backups of existing datafiles
# unrecoverable_datafiles: Show datafiles which have had UNRECOVERABLE operations performed
# on them since their last backup.
if ($command eq "menu") {
text("All backup information in this section is taken from the controlfiles.");
Button("$scriptname?database=$database&object_type=RMANBACKUPS&command=last_backup TARGET=body","Current datafile backups","$headingcolor","CENTER","200");
# Check for datafiles added since last backup..
$sql = "$copyright
SELECT
COUNT(*)
FROM V$DATAFILE
WHERE CREATION_CHANGE# NOT IN (
SELECT DISTINCT CREATION_CHANGE#
FROM V$BACKUP_DATAFILE
)
";
$count = recordCount($sql);
if ($count) {
Button("$scriptname?database=$database&object_type=RMANBACKUPS&command=datafiles_added TARGET=body","Datafiles never backed up","$headingcolor","CENTER","200");
}
# Check for datafiles which are backed up but have been dropped.
# No good, can't get filename.
# $sql = "$copyright
#SELECT
# COUNT(*)
#FROM V$BACKUP_DATAFILE
# WHERE CREATION_CHANGE#
#NOT IN (SELECT CREATION_CHANGE#
#FROM V$DATAFILE)
#";
# $count = recordCount($sql);
# if ($count) {
# Button("$scriptname?database=$database&object_type=RMANBACKUPS&command=datafiles_dropped TARGET=body","Datafiles backed up but dropped","$headingcolor","CENTER","200");
# }
# Check for files which have had unrecoverable operations run against them.
# This does not mean you can't recover the file, it means that you cant
# recover the transactions if you lose the datafile.
$sql = "$copyright
SELECT COUNT(*)
FROM
V$DATAFILE VD,
(
SELECT BD.CREATION_CHANGE#, MAX(BD.COMPLETION_TIME) COMPLETION_TIME
FROM
V$BACKUP_DATAFILE BD
GROUP BY BD.CREATION_CHANGE#
) VBD
WHERE VBD.CREATION_CHANGE# = VD.CREATION_CHANGE#
AND VD.UNRECOVERABLE_TIME > VBD.COMPLETION_TIME
";
$count = recordCount($sql);
if ($count) {
Button("$scriptname?database=$database&object_type=RMANBACKUPS&command=unrecoverable_datafiles TARGET=body","Unrecoverable datafiles","$headingcolor","CENTER","200");
}
}
if ($command eq "datafiles_added") {
$sql = "
SELECT
NAME "Filename",
TO_CHAR(CREATION_TIME,'Mon DD YYYY @ HH24:MI:SS') "Date created"
FROM V$DATAFILE
WHERE CREATION_CHANGE# NOT IN (
SELECT DISTINCT CREATION_CHANGE# FROM V$BACKUP_DATAFILE
)
";
$text = "Datafile(s) which have been added but not backed up.";
$link = "$scriptname?database=$database&object_type=DATAFILE";
$infotext = "No datafiles have been added since the last backup.";
DisplayTable($sql,$text,$link,$infotext);
}
# if ($command eq "datafiles_dropped") {
# $sql = "$copyright
#SELECT
# NAME
#FROM V$DATAFILE
# WHERE CREATION_CHANGE# IN (
#SELECT CREATION_CHANGE#
#FROM V$BACKUP_DATAFILE
# WHERE CREATION_CHANGE#
#NOT IN (SELECT CREATION_CHANGE#
#FROM V$DATAFILE))
#";
# $text = "Datafiles which are backed up but have been dropped.";
# $link = "$scriptname?database=$database&object_type=DATAFILE";
# $infotext = "";
# DisplayTable($sql,$text,$link,$infotext);
# }
if ($command eq "last_backup") {
$sql = "
SELECT
VDF.NAME "Filename",
TO_CHAR(VBD.COMPLETION_TIME,'Mon DD YYYY @ HH24:MI:SS') "Completion time",
TO_CHAR(VBD.DATAFILE_BLOCKS*VBD.BLOCK_SIZE,'999,999,999,999') "File size",
TO_CHAR(VBD.BLOCKS*VBD.BLOCK_SIZE,'999,999,999,999') "Bytes written",
VBD.INCREMENTAL_LEVEL "Level",
VBD.CHECKPOINT_CHANGE# "Ckpt change#",
TO_CHAR(VBD.CHECKPOINT_TIME,'Mon DD YYYY @ HH24:MI:SS') "Checkpoint time",
VBD.MARKED_CORRUPT "Mrkd crpt",
VBD.MEDIA_CORRUPT "Media crpt",
VBD.LOGICALLY_CORRUPT "Lgcl crpt"
FROM V$BACKUP_DATAFILE VBD, V$DATAFILE VDF,
(SELECT
CREATION_CHANGE#,
MAX(COMPLETION_TIME) COMPLETION_TIME
FROM V$BACKUP_DATAFILE
WHERE CREATION_CHANGE# IN (
SELECT CREATION_CHANGE# FROM V$DATAFILE)
GROUP BY CREATION_CHANGE#
) QUERY1
WHERE VBD.CREATION_CHANGE# = VDF.CREATION_CHANGE#
AND VBD.CREATION_CHANGE# = QUERY1.CREATION_CHANGE#
AND VBD.COMPLETION_TIME = QUERY1.COMPLETION_TIME
ORDER BY 2 DESC, 5 DESC, 6 DESC
";
$text = "Most recent backup information for existing datafiles.";
$link = "$scriptname?database=$database&object_type=DATAFILE";
$infotext = "No backups found.";
$count = DisplayTable($sql,$text,$link,$infotext);
}
if ($command eq "unrecoverable_datafiles") {
$sql = "$copyright
SELECT VD.NAME
FROM
V$DATAFILE VD,
(
SELECT BD.CREATION_CHANGE#, MAX(BD.COMPLETION_TIME) COMPLETION_TIME
FROM
V$BACKUP_DATAFILE BD
GROUP BY BD.CREATION_CHANGE#
) VBD
WHERE VBD.CREATION_CHANGE# = VD.CREATION_CHANGE#
AND VD.UNRECOVERABLE_TIME > VBD.COMPLETION_TIME
";
$text = "Datafiles which have had UNRECOVERABLE operations performed on them since their last backup.";
$link = "$scriptname?database=$database&object_type=RMANBACKUPS&command=datafile";
$infotext = "";
$count = DisplayTable($sql,$text,$link,$infotext);
}
logit("Exit subroutine rmanBackups");
}
sub rmanCatalogQuery {
logit("Enter subroutine rmanCatalogQuery");
my ($sql1,$cursor1,$sql2,$cursor2,$owner,$count,$version,$command);
my ($text,$infotext,$link);
$command = $query->param('command') || "";
logit(" Command is $command");
if ($command eq "listdbs") {
# List the databases contained in the catalog
$sql = "$copyright
SELECT
NAME "DB name",
TO_CHAR(RESETLOGS_TIME,'Month DD, YYYY - HH24:MI') "Last resetlogs"
FROM $schema.RC_DATABASE
";
$text = "Database(s) registered in this catalog.";
$infotext = "RMAN catalog owned by $schema contains no databases.";
$link = "$scriptname?database=$database&schema=$schema&object_type=RMANCATALOGQUERY&command=listbackups";
DisplayTable($sql,$text,$link,$infotext);
}
if ($command eq "listbackups") {
# List information about the database chosen.
$sql = "$copyright
SELECT
RESYNC_TYPE "Resync type",
TO_CHAR(RESYNC_TIME,'Month DD, YYYY - HH24:MI') "Resync time"
FROM $schema.RC_RESYNC
WHERE DB_NAME = '$object_name'
ORDER BY 2
";
$text = "Resyncs for database $object_name.";
$infotext = "No resyncs for database $object_name on record.";
$link = "";
DisplayTable($sql,$text,$link,$infotext);
}
unless ($command) {
# No command is passed, so show the catalog(s)
text("The following RMAN catalog(s) exist.");
print << "EOF";
<TABLE BORDER =0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TH BGCOLOR='$headingcolor' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Catalog owner</A></TH>
<TH BGCOLOR='$headingcolor' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Catalog version</TH>
EOF
$sql1 = "$copyright
SELECT
OWNER
FROM DBA_TABLES
WHERE TABLE_NAME = 'RCVER'
";
$cursor1 = $dbh->prepare($sql1);
$cursor1->execute;
while ($owner = $cursor1->fetchrow_array) {
$sql2 = "$copyright
SELECT
VERSION
FROM $owner.RCVER
";
$cursor2 = $dbh->prepare($sql2);
next unless ($cursor2);
$cursor2->execute;
$version = $cursor2->fetchrow_array;
$cursor2->finish;
logit(" Seems to be a RMAN catalog, version $version owned by $owner");
print "<TR><TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=RMANCATALOGQUERY&schema=$owner&command=listdbs>$owner</A></TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$version</TD></TR>n";
}
$cursor1->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</TABLE>
EOF
}
logit("Exit subroutine rmanCatalogQuery");
}
sub dbAdmin {
logit("Enter subroutine dbAdmin");
my ($sql,$cursor,$username,$password,$deftablespace,$temptablespace,$profile);
my ($cascade,$copyuser,$bytes,$tablespace_name,$privilege,$granted_role);
my ($admin_option,$default_role,@params,$param,$sid,$serial,$obj_name);
my ($owner,$object_type,$object_id,$foo,@audits,$audit,$audits,$by,$whenever);
my ($whenevercount,@objects,@privileges,@users,$moresql,$users,$wheneversql);
my ($status,$text,@default_roles,$roles);
$username = $query->param('username') || "";
$password = $query->param('password') || "";
$deftablespace = $query->param('deftablespace') || "";
$temptablespace = $query->param('temptablespace') || "";
$profile = $query->param('profile') || "";
$cascade = $query->param('cascade') || "";
$copyuser = $query->param('copyuser') || "";
logit(" Command is $object_name");
if ($object_name eq "killsessions") {
@params = $query->param;
foreach $param(@params) {
if ($param =~ /^killsession/) {
$param =~ s/killsession_//;
($sid,$serial) = split("~", $param);
runSQL("ALTER SYSTEM KILL SESSION '$sid,$serial'");
}
}
}
if ($object_name eq "alter_rollbacks") {
my $shrinkto = $query->param('shrinkto');
my ($rbs,$command);
logit(" Shrinkto value is set to: $shrinkto");
@params = $query->param;
foreach $param(@params) {
logit(" Param = $param");
if ($param =~ /^alter/) {
($foo,$rbs,$command) = split("~", $param);
logit(" RBS: $rbs, Command: $command");
if ($command eq "online") {
runSQL("ALTER ROLLBACK SEGMENT $rbs ONLINE");
}
if ($command eq "offline") {
runSQL("ALTER ROLLBACK SEGMENT $rbs OFFLINE");
}
if ($command eq "shrink") {
if ($shrinkto) {
runSQL("ALTER ROLLBACK SEGMENT $rbs SHRINK TO $shrinkto");
} else {
runSQL("ALTER ROLLBACK SEGMENT $rbs SHRINK");
}
}
}
}
}
if ($object_name eq "dependencies") {
my ($link,$infotext);
@params = $query->param;
foreach $param(@params) {
logit(" Param = $param");
if ($param =~ /^dependency/) {
($foo,$object_id) = split("~", $param);
$sql = "
SELECT
OBJECT_NAME,
OBJECT_TYPE,
OWNER
FROM DBA_OBJECTS
WHERE OBJECT_ID = $object_id
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
($object_name,$object_type,$owner) = $cursor->fetchrow_array;
$cursor->finish;
$sql = "
SELECT
OBJECT_NAME "Object name",
OBJECT_TYPE "Object type",
OWNER "Owner"
FROM DBA_OBJECTS
WHERE OBJECT_ID IN (
SELECT
P_OBJ#
FROM SYS.DEPENDENCY$
WHERE D_OBJ# = $object_id
)
ORDER BY 3,2,1
";
$text = "$object_type $owner.$object_name depends on the following objects.";
$link = "";
$infotext = "$object_type $owner.$object_name has no dependencies on other objects.";
ObjectTable($sql,$text,$infotext);
$sql = "
SELECT
OBJECT_NAME "Object name",
OBJECT_TYPE "Object type",
OWNER "Owner"
FROM DBA_OBJECTS
WHERE OBJECT_ID IN (
SELECT
D_OBJ#
FROM SYS.DEPENDENCY$
WHERE P_OBJ# = $object_id
)
ORDER BY 3,2,1
";
$text = "The following objects depend on $object_type $owner.$object_name.";
$link = "";
$infotext = "No other objects have dependencies on $object_type $owner.$object_name.";
ObjectTable($sql,$text,$infotext);
print "<HR WIDTH=75%>";
}
}
}
if ($object_name eq "compile") {
@params = $query->param;
logit(" Params: @params");
foreach $param(@params) {
if ($param =~ /^compile_/) {
logit(" Param: $param");
($foo,$object_id) = split("_", $param);
logit(" Object_id = $object_id");
$sql = "$copyright
SELECT
OBJECT_TYPE,
OWNER,
OBJECT_NAME
FROM DBA_OBJECTS
WHERE OBJECT_ID = $object_id
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
($object_type,$owner,$obj_name) = $cursor->fetchrow_array;
$cursor->finish;
if ($object_type eq "PACKAGE BODY") {
runSQL("ALTER PACKAGE $owner.$obj_name COMPILE BODY");
} else {
runSQL("ALTER $object_type $owner.$obj_name COMPILE");
}
logit(" Object $object_type $owner.$obj_name COMPILE");
$sql = "$copyright
SELECT
STATUS
FROM DBA_OBJECTS
WHERE OBJECT_NAME = '$obj_name'
AND OBJECT_TYPE = '$object_type'
AND OWNER = '$owner'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$status = $cursor->fetchrow_array;
$cursor->finish;
if ($status eq "INVALID") {
$sql = "$copyright
SELECT
LINE "Line",
POSITION "Position",
TEXT "Text"
FROM DBA_ERRORS
WHERE NAME = '$obj_name'
AND TYPE = '$object_type'
AND OWNER = '$owner'
ORDER BY SEQUENCE
";
$text = "Object $owner.$obj_name still has errors..";
DisplayTable($sql,$text);
print "<P>n";
}
}
}
}
if ($object_name eq "changepassword") {
$sql = "
ALTER USER $username IDENTIFIED BY $password
";
runSQL("$sql");
}
if ($object_name eq "createuser") {
$sql = "
CREATE USER $username
IDENTIFIED BY $password
DEFAULT TABLESPACE $deftablespace
TEMPORARY TABLESPACE $temptablespace
PROFILE $profile
";
runSQL("$sql");
}
if ($object_name eq "dropuser") {
if ($cascade) {
$sql = "
DROP USER $username CASCADE
";
} else {
$sql = "
DROP USER $username
";
}
runSQL("$sql");
}
if ($object_name eq "removestmtaudits") {
@params = $query->param;
foreach $param(@params) {
if ($param =~ /^removeaudit/) {
$param =~ s/removeaudit_//;
($privilege,$username) = split("~", $param);
$privilege =~ s/+/ /g;
if ($username) {
$sql = "
NOAUDIT $privilege BY $username
";
} else {
$sql = "
NOAUDIT $privilege
";
}
logit(" Remove audits: $sql");
runSQL($sql);
}
}
}
if ($object_name eq "removeobjaudits") {
@params = $query->param;
foreach $param(@params) {
if ($param =~ /^removeaudit/) {
$param =~ s/removeaudit_//;
($owner,$obj_name) = split("~", $param);
$sql = "
NOAUDIT ALL ON $owner.$obj_name
";
logit(" Remove audits: $sql");
runSQL($sql);
}
}
}
if ($object_name eq "dostatementaudits") {
@params = $query->param;
logit(" Params: @params");
@users = $query->param('users');
$users = join(",", @users);
logit(" Users: $users");
@privileges = $query->param('privilege');
logit(" Privileges: @privileges");
unless (@privileges) {
message("You must select at least one privilege..");
Footer();
}
$by = $query->param('by');
if ($users) {
$moresql = " BY $users";
}
if ($by) {
$moresql .= " BY $by";
}
foreach $param(@params) {
if ($param =~ /whenever~/) {
$whenevercount++;
($foo,$whenever) = split("~", $param);
if ($whenever eq "NOTSUCCESSFUL") {
$wheneversql = " WHENEVER NOT SUCCESSFUL";
}
if ($whenever eq "SUCCESSFUL") {
$wheneversql = " WHENEVER SUCCESSFUL";
}
}
}
if ($whenevercount == 1) {
$moresql .= $wheneversql;
}
foreach $privilege (@privileges) {
$sql = "
AUDIT $privilege$moresql
";
logit(" $sql");
runSQL("$sql");
}
}
if ($object_name eq "doschemaaudits") {
@params = $query->param;
logit(" Params: @params");
@objects = $query->param('object');
logit(" Objects: @objects");
$by = $query->param('by');
foreach $param(@params) {
if ($param =~ /audit~/) {
($foo,$audit) = split("~", $param);
push @audits, $audit;
}
if ($param =~ /whenever~/) {
$whenevercount++;
($foo,$whenever) = split("~", $param);
if ($whenever eq "NOTSUCCESSFUL") {
$whenever = "NOT SUCCESSFUL";
}
}
}
logit(" Audits = @audits");
$audits = join(",", @audits);
foreach $obj_name(@objects) {
if ($whenevercount != 1) {
$sql = "
AUDIT $audits ON $obj_name BY $by
";
} else {
$sql = "
AUDIT $audits ON $obj_name BY $by WHENEVER $whenever
";
}
logit(" $sql");
runSQL("$sql");
}
}
if ($object_name eq "copyuser") {
$sql = "
SELECT
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE,
PROFILE
FROM DBA_USERS
WHERE USERNAME = '$copyuser'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
($deftablespace,$temptablespace,$profile) = $cursor->fetchrow;
$cursor->finish;
$sql = "
CREATE USER $username
IDENTIFIED BY $password
DEFAULT TABLESPACE $deftablespace
TEMPORARY TABLESPACE $temptablespace
PROFILE $profile
";
runSQL("$sql");
$sql = "
SELECT
MAX_BYTES,
TABLESPACE_NAME
FROM DBA_TS_QUOTAS
WHERE USERNAME = '$copyuser'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
while (($bytes,$tablespace_name) = $cursor->fetchrow) {
$bytes = "UNLIMITED" if ($bytes eq "-1");
$sql = "
ALTER USER $username QUOTA $bytes on $tablespace_name
";
runSQL("$sql");
}
$sql = "$copyright
SELECT
GRANTED_ROLE,
ADMIN_OPTION,
DEFAULT_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = '$copyuser'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($granted_role,$admin_option,$default_role) = $cursor->fetchrow) {
$sql = "
GRANT $granted_role TO $username
";
$sql .= " WITH ADMIN OPTION" if ($admin_option eq "YES");
runSQL("$sql");
if ($default_role eq "YES") {
push @default_roles, $granted_role;
}
}
$cursor->finish;
if (@default_roles) {
$roles = join(",",@default_roles);
runSQL("ALTER USER $username DEFAULT ROLE $roles");
}
$sql = "$copyright
SELECT
PRIVILEGE,
ADMIN_OPTION
FROM DBA_SYS_PRIVS
WHERE GRANTEE = '$copyuser'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($privilege,$admin_option) = $cursor->fetchrow) {
$sql = "
GRANT $privilege TO $username
";
$sql .= " WITH ADMIN OPTION" if ($admin_option eq "YES");
runSQL("$sql");
}
}
logit("Exit subroutine dbAdmin");
}
sub enterCreateUser {
logit("Enter subroutine enterCreateUser");
my ($sql,$cursor,$tablespace_name,$username,$profile);
text("</CENTER>Reset a user password.");
print <<"EOF";
<B>
</CENTER>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="changepassword">
Alter user
<SELECT SIZE=1 NAME=username>
EOF
$sql = "$copyright
SELECT
USERNAME
FROM DBA_USERS
ORDER BY USERNAME
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ($username = $cursor->fetchrow) {
print " <OPTION>$usernamen";
}
$cursor->finish;
print <<"EOF";
</SELECT>
  identified by
<INPUT TYPE=PASSWORD MAXLENGTH=30 SIZE=10 NAME=password>
<P>
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Reset password">
</FORM>
<P><HR WIDTH=90%><P>
<CENTER>
EOF
text("</CENTER>Create a user.");
print <<"EOF";
</CENTER>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="createuser">
Create user
<INPUT TYPE=TEXT MAXLENGTH=30 SIZE=10 NAME=username>
  identified by
<INPUT TYPE=PASSWORD MAXLENGTH=30 SIZE=10 NAME=password>
  default tablespace
EOF
print <<"EOF";
<SELECT SIZE=1 NAME=deftablespace>
EOF
$sql = "$copyright
SELECT
TABLESPACE_NAME
FROM DBA_TABLESPACES
ORDER BY TABLESPACE_NAME
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ($tablespace_name = $cursor->fetchrow) {
print " <OPTION>$tablespace_namen";
}
$cursor->finish;
print <<"EOF";
</SELECT>
<BR>temporary tablespace
<SELECT SIZE=1 NAME=temptablespace>
EOF
$sql = "$copyright
SELECT
TABLESPACE_NAME
FROM DBA_TABLESPACES
ORDER BY TABLESPACE_NAME
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ($tablespace_name = $cursor->fetchrow) {
print " <OPTION>$tablespace_namen";
}
$cursor->finish;
print <<"EOF";
</SELECT>
  profile
<SELECT SIZE=1 NAME=profile>
EOF
$sql = "$copyright
SELECT
DISTINCT PROFILE
FROM DBA_PROFILES
ORDER BY PROFILE
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ($profile = $cursor->fetchrow) {
print " <OPTION>$profilen";
}
$cursor->finish;
print <<"EOF";
</SELECT>
<P>
<INPUT TYPE="SUBMIT" NAME="tablerows" VALUE="Create user">
</FORM>
<P><HR WIDTH=90%><P>
<CENTER>
EOF
text("</CENTER>Create a user "like" another user.<BR>This will give the new user the same quotas, profile, default and temporary tablespace, roles, and system privileges as the selected user.");
print <<"EOF";
</CENTER>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="copyuser">
Create user
<INPUT TYPE=TEXT MAXLENGTH=30 SIZE=10 NAME=username>
  identified by
<INPUT TYPE=PASSWORD MAXLENGTH=30 SIZE=10 NAME=password>
  same as
<SELECT SIZE=1 NAME=copyuser>
EOF
$sql = "$copyright
SELECT
USERNAME
FROM DBA_USERS
ORDER BY USERNAME
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ($username = $cursor->fetchrow) {
print " <OPTION>$usernamen";
}
$cursor->finish;
print <<"EOF";
</SELECT>
<P>
<INPUT TYPE="SUBMIT" NAME="tablerows" VALUE="Create user">
</FORM>
<P><HR WIDTH=90%><P>
<CENTER>
EOF
text("</CENTER>Drop a user");
print <<"EOF";
</CENTER>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="dropuser">
Drop user
<SELECT SIZE=1 NAME=username>
EOF
$sql = "$copyright
SELECT
USERNAME
FROM DBA_USERS
ORDER BY USERNAME
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ($username = $cursor->fetchrow) {
print " <OPTION>$usernamen";
}
$cursor->finish;
print <<"EOF";
</SELECT>
  cascade
<INPUT TYPE=CHECKBOX NAME=cascade VALUE=cascade>
<P>
<INPUT TYPE="SUBMIT" NAME="dropuser" VALUE="Drop user">
</FORM>
EOF
logit("Exit subroutine enterCreateUser");
}
sub noInfo {
logit("Enter subroutine showInfo");
message("There is no additional information to display");
logit("Exit subroutine showInfo");
}
sub Director {
logit("Enter subroutine Director");
# Put the if's here for reporting on the different types of objects.
# Show audit trail records for deletion.
if ($object_type eq "VALIDATEINDEX") {validateIndex();}
# Show audit trail records for deletion.
if ($object_type eq "AUDITLIST") {auditList();}
# Show rollbacks for administration.
if ($object_type eq "RBSLIST") {rbsList();}
# Show Recovery Manager controlfile information.
if ($object_type eq "RMANBACKUPS") {rmanBackups();}
# Show Recovery Manager catalog information.
if ($object_type eq "RMANCATALOGQUERY") {rmanCatalogQuery();}
# Show audit trail records.
if ($object_type eq "SHOWAUDITTRAIL") {showAuditTrail();}
# Choose auditing options.
if ($object_type eq "ENTERAUDITS") {enterAudits();}
# Show a list of tables for a certain schema for reverse engineering.
if ($object_type eq "SCHEMATABLEDDL") {schemaTableDDL();}
# Show a schemas invalid objects
if ($object_type eq "SHOWINVALIDOBJECTS") {showInvalidObjects();}
# Show SQL in V$SQL for a user.
if ($object_type eq "SQLAREALISTBYUSER") {sqlAreaListByUser();}
# Show list of users with SQL in V$SQL.
if ($object_type eq "SQLAREALIST") {sqlAreaList();}
# Object administration.
if ($object_type eq "OBJECTADMIN") {objectAdmin();}
# Rollback / transaction information
if ($object_type eq "ROLLBACKMENU") {rollbackMenu();}
# StatsPack information menu.
if ($object_type eq "STATSPACKMENU") {statsPackMenu();}
# StatsPack snapshot.
if ($object_type eq "STATSPACKADMIN") {statsPackAdmin();}
# Backup (RMAN) information menu.
if ($object_type eq "BACKUPMENU") {backupMenu();}
# Auditing administration menu.
if ($object_type eq "PERFMENU") {perfMenu();}
# Auditing administration menu.
if ($object_type eq "AUDITMENU") {auditMenu();}
# Session administration menu.
if ($object_type eq "SESSIONMENU") {sessionMenu();}
# Preferences menu.
if ($object_type eq "PREFMENU") {prefMenu();}
# Auditing administration.
if ($object_type eq "AUDITADMIN") {auditAdmin();}
# Kill multiple sessions. Die die die!!! :)
if ($object_type eq "SESSIONLIST") {sessionList();}
# Execute user administration commands
if ($object_type eq "DBADMIN") {dbAdmin();}
# Create a user
if ($object_type eq "ENTERCREATEUSER") {enterCreateUser();}
# Enter names of tables to reverse engineer
if ($object_type eq "ENTERDDLTABLES") {enterDDLtables();}
# Report for a specific something
if ($object_type eq "JAVA CLASS" || $object_type eq "JAVA RESOURCE") {noInfo();}
# Display all themes for choosing.
if ($object_type eq "SHOWTHEMES") {showThemes();}
# Display menu for choosing typical DBA type tasks.
if ($object_type eq "TASKMENU") {taskMenu();}
# Display tool properties.
if ($object_type eq "SHOWPROPS") {showProps();}
# Set a default theme that the user has chosen. Store in a cookie.
# if ($object_type eq "SETTHEME") {setTheme();}
# Report for a specific queue
if ($object_type eq "QUEUE") {showQueue();}
# Report for a specific operator
if ($object_type eq "OPERATOR") {showOperator();}
# Report for a specific library
if ($object_type eq "LIBRARY") {showLibrary();}
# Report for a specific cluster
if ($object_type eq "CLUSTER") {showCluster();}
# Report for a specific indextype
if ($object_type eq "INDEXTYPE") {showIndextype();}
# Report for a specific table.
if ($object_type eq "TABLE") {showTable();}
# Report for a specific table partition
if ($object_type eq "TABLE PARTITION") {showTablePart();}
# Report for a specific view.
if ($object_type eq "VIEW") {showView();}
# Report for a specific trigger.
if ($object_type eq "TRIGGER") {showTrigger();}
# Report for a specific database link.
if ($object_type eq "DATABASE LINK") {showDBlink();}
# Report for a specific function.
if ($object_type eq "FUNCTION") {showSource();}
# Report for a specific package body.
if ($object_type eq "PACKAGE BODY") {showSource();}
# Report for a specific package.
if ($object_type eq "PACKAGE") {showSource();}
# Report for a specific procedure.
if ($object_type eq "PROCEDURE") {showSource();}
# Report for a specific sequence.
if ($object_type eq "SEQUENCE") {showSequence();}
# Report for a specific index.
if ($object_type eq "INDEX") {showIndex();}
# Report for a specific index partition.
if ($object_type eq "INDEX PARTITION") {showIndexPart();}
# Report for a specific synonym.
if ($object_type eq "SYNONYM") {showSynonym();}
# Report on all privileges granted to a specific user.
if ($object_type eq "GRANTSTO") {showGrantsto();}
# Report on all privileges granted to a specific role.
if ($object_type eq "ROLES") {showRoles();}
# Report on all privileges granted from a specific user.
if ($object_type eq "GRANTSFROM") {showGrantsfrom();}
# This is what I call the "toplevel" page. Lists all users in database.
if ($object_type eq "LISTUSERS") {showUsers();}
# This is where you go after selecting a user. Lists general info,
# buttons for grants, and a list of object types owned by the user.
if ($object_type eq "USERINFO") {userInfo();}
# This will bring back a table of objects that are of the type that
# the user clicked on.
if ($object_type eq "LISTOBJECTS") {showObjects();}
# List all of the tablespaces in the database.
if ($object_type eq "TABLESPACES") {showTablespaces();}
# Report on information about a specific tablespace.
if ($object_type eq "TSINFO") {showTSinfo();}
# Show a clickable list of all datafiles in the database
if ($object_type eq "DATAFILES") {showDBfiles();}
# Show information abaout a particular datafile
if ($object_type eq "DATAFILE") {showFile();}
# Show all instance parameters from V$PARAMETER in a table
if ($object_type eq "PARAMETERS") {showParameters();}
# Show information about redologs, including a graph
if ($object_type eq "REDOLOGS") {showRedo();}
# Show information about all sessions in the instance, or
# for a particular user, if $user is set
if ($object_type eq "SESSIONS") {showSessions($user);}
# Show instance session summary with refresh, sortable.
if ($object_type eq "TOPSESSIONS") {topSessions();}
# Display "n" rows
if ($object_type eq "TABLEROWS") {showRows("$rowdisplay");}
# Display information about a constraint
if ($object_type eq "CONSTRAINT") {showConstraint();}
# Show a clickable list of all rollback segments in the database
if ($object_type eq "SHOWROLLBACKS") {showRollbacks();}
# Show a list of all active / rolling back transactions in the database
if ($object_type eq "SHOWTRANSACTIONS") {showTransactions();}
# Show information about a particular rollback segment
if ($object_type eq "ROLLBACK") {showRollback();}
# Check the instance for contending and non-contending locks
if ($object_type eq "CONTENTION") {showContention();}
# Show a table graph of datafiles with allocation
if ($object_type eq "FILEGRAPH") {showFilegraph();}
# Show a table graph of tablespaces with allocation
if ($object_type eq "TSGRAPH") {showTSgraph();}
# Show statistics for a particular file
if ($object_type eq "TSFILEGRAPH") {showTSfilegraph();}
# Show statistics for a selected session
if ($object_type eq "SESSIONSTATS") {showSessionstats();}
# Break down a user's object usage by object type and tablespace
if ($object_type eq "OBJECTREPORT") {objectReport();}
# Report of space usage by user
if ($object_type eq "USERSPACEREPORT") {userSpaceReport();}
# Report of space usage by tablespace / user
if ($object_type eq "TSSPACEREPORT") {tsSpaceReport();}
# Report of datafile fragmentation
if ($object_type eq "FILEFRAGREPORT") {fileFragReport();}
# Enter number of extents for extent report
if ($object_type eq "ENTEREXTENTREPORT") {enterExtentReport();}
# Run an extent report
if ($object_type eq "EXTENTREPORT") {extentReport();}
# Show OPS related information
if ($object_type eq "OPSPAGE") {OPSpage();}
# Search for objects by object name or object ID
if ($object_type eq "OBJECTSEARCH") {objectSearch();}
# Show the privileges granted to users for a particular object
if ($object_type eq "OBJECTGRANTS") {showObjectGrants();}
# Show memory and performance related data
if ($object_type eq "PERFORMANCE") {showPerformance();}
# Bring up a screen for the user to enter a new username and
# password in order to run an explain plan as that user.
if ($object_type eq "EXPLAIN") {enterUserExplainPlan();}
# Bring up the explain plan screen
if ($object_type eq "EXPLAINSCREEN") {explainScreen();}
# Bring up a box for a user to run SQL in an explain plan as a
# user other than the user logged in to Oracletool. This is
# executed from the session information screens.
if ($object_type eq "EXPLAINPLAN") {enterExplainPlan();}
# Run the explain plan
if ($object_type eq "RUNEXPLAINPLAN") {runExplainPlan();}
# SQL-Worksheet
if ($object_type eq "WORKSHEET") {enterWorksheet();}
# Run the SQL entered on the worksheet
if ($object_type eq "RUNSQL") {runSQL();}
# Show settings for a profile
if ($object_type eq "PROFILE") {showProfile();}
# Show roles, profiles, and users with the DBA role
if ($object_type eq "SECURITY") {showSecurity();}
# Show auditing information
if ($object_type eq "AUDITING") {showAllAuditing();}
# Show archiving information
if ($object_type eq "ARCHIVING") {showArchiving();}
# Show controlfile information
if ($object_type eq "CONTROLFILES") {showControlfiles();}
# Show Replication information (Master)
if ($object_type eq "REPMASTER") {showRepmaster();}
# Show advanced replication groups
if ($object_type eq "ADVREP") {showAdvRepGroups();}
# Show group information, advanced replication
if ($object_type eq "ADVREPGROUP") {showAdvRepGroup();}
# Show Refresh groups
if ($object_type eq "REFRESHGROUPS") {showRefreshgroups();}
# Show Refresh group children.
if ($object_type eq "REFRESHINFO") {showRefreshinfo();}
# Show info for a particular snapshot
if ($object_type eq "SNAPINFO") {showSnapinfo();}
# Generate all DDL to recreate a tablespace
if ($object_type eq "TSDDL") {tsDDL();}
# Generate all DDL to recreate a rollback segment
if ($object_type eq "RBSDDL") {rbsDDL();}
# Generate all DDL to recreate a table and its dependencies
if ($object_type eq "TABLEDDL") {tableDDL();}
# Generate all DDL to recreate a user and its dependencies
if ($object_type eq "USERDDL") {userDDL();}
# Show a fragmentation map of a datafile or tablespace
if ($object_type eq "FRAGMAP") {fragMap();}
# Show a fragmentation map of an object
if ($object_type eq "OBJECTFRAGMAP") {objectFragMap();}
# Kill a session
if ($object_type eq "KILLSESSION") {killSession();}
# Show a fragmentation map of a datafile
if ($object_type eq "DBFILE_BLOCK") {dbfileBlock();}
# Show some info about things that have recently changed in the database.
if ($object_type eq "RECENTEVENTS") {recentEvents();}
logit("Exit subroutine Director");
}