"Fossies" - the Fresh Open Source Software Archive  

Source code changes of the file "oracletool.pl" between
oracletool-3.0.1.tgz and oracletool-3.0.2.tgz

About: Oracletool is an interactive, web based tool for Oracle DBA (Perl, CGI).

oracletool.pl  (oracletool-3.0.1.tgz):oracletool.pl  (oracletool-3.0.2.tgz)
#!/usr/bin/perl #!/apps/perl/bin/perl
# Copyright (c) 1998 - 2014 Adam vonNieda - Kansas USA # Copyright (c) 1998 - 2020 Adam vonNieda - Kansas USA
# #
# You may distribute under the terms of either the GNU General Public # You may distribute under the terms of either the GNU General Public
# License or the Artistic License, as specified in the Perl README file, # License or the Artistic License, as specified in the Perl README file,
# with the exception that it cannot be placed on a CD-ROM or similar media # with the exception that it cannot be placed on a CD-ROM or similar media
# for commercial distribution without the prior approval of the author. # for commercial distribution without the prior approval of the author.
# This software is provided without warranty of any kind. If your server # This software is provided without warranty of any kind. If your server
# melts as a result of using this script, that's a bummer. But it won't. # melts as a result of using this script, that's a bummer. But it won't.
require 5.003; require 5.003;
skipping to change at line 38 skipping to change at line 38
use vars qw($expire $username $password $dbh $sql $majversion $minversion $rowdi splay $banner); use vars qw($expire $username $password $dbh $sql $majversion $minversion $rowdi splay $banner);
use vars qw($db_block_size $title $heading $cursor $upload_limit $nls_date_forma t $stylecss); use vars qw($db_block_size $title $heading $cursor $upload_limit $nls_date_forma t $stylecss);
use vars qw($logging $explainschema $bgcolor $headingcolor $fontcolor $infocolor $font $fontsize); use vars qw($logging $explainschema $bgcolor $headingcolor $fontcolor $infocolor $font $fontsize);
use vars qw($linkcolor $cellcolor $bordercolor $description %themes $schema_cols $menufontsize); use vars qw($linkcolor $cellcolor $bordercolor $description %themes $schema_cols $menufontsize);
use vars qw($expiration $oraclenames $theme $repository $logfile %plugins $confi g_file); use vars qw($expiration $oraclenames $theme $repository $logfile %plugins $confi g_file);
use vars qw($encryption_string $bgimage $menuimage $encryption_enabled $copyrigh t $headingfont); use vars qw($encryption_string $bgimage $menuimage $encryption_enabled $copyrigh t $headingfont);
use vars qw($headingfontcolor $encryption_method $dbstatus $myoracletool $mydbh $alertlogrows); use vars qw($headingfontcolor $encryption_method $dbstatus $myoracletool $mydbh $alertlogrows);
use vars qw($explainpassword $myoracletoolexpire $norefreshbutton $hostname $sta tspack_schema); use vars qw($explainpassword $myoracletoolexpire $norefreshbutton $hostname $sta tspack_schema);
use vars qw($dataguard); use vars qw($dataguard);
$VERSION = "3.0.1"; $VERSION = "3.0.2";
# Edit the following if you want to use a config file not named "oracletool.ini" . # Edit the following if you want to use a config file not named "oracletool.ini" .
# The following assumes that the file is in the same directory as oracletool.pl. # The following assumes that the file is in the same directory as oracletool.pl.
$config_file = "oracletool.ini"; $config_file = "oracletool.ini";
$nls_date_format = "Mon DD YYYY @ HH24:MI:SS"; $nls_date_format = "Mon DD YYYY @ HH24:MI:SS";
Main(); Main();
skipping to change at line 94 skipping to change at line 94
$theme = cookie("OracletoolTheme"); $theme = cookie("OracletoolTheme");
$theme = "Default" unless ($theme); $theme = "Default" unless ($theme);
$myoracletool = cookie("MyOracletool"); $myoracletool = cookie("MyOracletool");
# Get the settings from the config file. # Get the settings from the config file.
parseConfig(); parseConfig();
# Decide whether to display copyright in all SQL statements. # Decide whether to display copyright in all SQL statements.
if ($ENV{'DISPLAY_COPYRIGHT'}) { if ($ENV{'DISPLAY_COPYRIGHT'}) {
$copyright = "/* Oracletool v$VERSION is copyright 1998 - 2012 Adam vonNie da, Kansas USA */ "; $copyright = "/* Oracletool v$VERSION is copyright 1998 - 2020 Adam vonNie da, Kansas USA */ ";
} else { } else {
$copyright = ""; $copyright = "";
} }
logit("Enter subroutine Main"); logit("Enter subroutine Main");
logit("Database = $database Object type = $object_type ARG = $object_name"); logit("Database = $database Object type = $object_type ARG = $object_name");
# Check for cookie encryption functionality. # Check for cookie encryption functionality.
skipping to change at line 273 skipping to change at line 273
my ($value); my ($value);
# Find out if we are dealing with Oracle7 or Oracle8 # Find out if we are dealing with Oracle7 or Oracle8
logit(" Getting Oracle version"); logit(" Getting Oracle version");
# $sql = "$copyright # $sql = "$copyright
#SELECT MAX(SUBSTR(RELEASE,1,1)), #SELECT MAX(SUBSTR(RELEASE,1,1)),
# MAX(SUBSTR(RELEASE,3,1)) # MAX(SUBSTR(RELEASE,3,1))
# FROM V\$COMPATIBILITY # FROM V\$COMPATIBILITY
#"; #";
$sql = "$copyright #
SELECT # Changed this 04/2015
VALUE #
FROM V\$PARAMETER # $sql = "$copyright
WHERE NAME = 'compatible' #SELECT
"; # VALUE
#FROM V\$PARAMETER
# WHERE NAME = 'compatible'
#";
$sql = "Select version from v\$instance";
$cursor = $dbh->prepare($sql); $cursor = $dbh->prepare($sql);
if (defined $cursor) { if (defined $cursor) {
$cursor->execute; $cursor->execute;
$value = $cursor->fetchrow_array; $value = $cursor->fetchrow_array;
logit(" Version is $value"); logit(" Version is $value");
($majversion,$minversion) = split(/\./,$value); ($majversion,$minversion) = split(/\./,$value);
$cursor->finish; $cursor->finish;
logit(" Major version = $majversion, Minor = $minversion"); logit(" Major version = $majversion, Minor = $minversion");
if ( $majversion eq "7" ) { if ( $majversion eq "7" ) {
skipping to change at line 1048 skipping to change at line 1053
WHERE A.SEGMENT_NAME = '$object_name' WHERE A.SEGMENT_NAME = '$object_name'
AND A.SEGMENT_TYPE = 'INDEX' AND A.SEGMENT_TYPE = 'INDEX'
AND A.OWNER = '$schema' AND A.OWNER = '$schema'
AND B.OBJECT_NAME = '$object_name' AND B.OBJECT_NAME = '$object_name'
AND B.OBJECT_TYPE = 'INDEX' AND B.OBJECT_TYPE = 'INDEX'
AND B.OWNER = '$schema' AND B.OWNER = '$schema'
AND B.OWNER = C.OWNER AND B.OWNER = C.OWNER
AND B.OBJECT_NAME = C.INDEX_NAME AND B.OBJECT_NAME = C.INDEX_NAME
"; ";
$object_type = lc $object_type; $object_type = lc $object_type;
$text = "General info: $object_type $object_name"; $text = "General info: $object_type $schema.$object_name";
$link = ""; $link = "";
DisplayTable($sql,$text,$link); DisplayTable($sql,$text,$link);
# Check to see if index is partitioned # Check to see if index is partitioned
$sql = "$copyright $sql = "$copyright
SELECT SELECT
PARTITIONED PARTITIONED
FROM DBA_INDEXES FROM DBA_INDEXES
WHERE INDEX_NAME = '$object_name' WHERE INDEX_NAME = '$object_name'
skipping to change at line 1142 skipping to change at line 1147
HIGH_VALUE \"High value\", HIGH_VALUE \"High value\",
HIGH_VALUE_LENGTH \"High value length\", HIGH_VALUE_LENGTH \"High value length\",
LOGGING \"Logging\" LOGGING \"Logging\"
FROM DBA_IND_PARTITIONS FROM DBA_IND_PARTITIONS
WHERE PARTITION_NAME = '$object_name' WHERE PARTITION_NAME = '$object_name'
AND INDEX_NAME = '$index_name' AND INDEX_NAME = '$index_name'
AND INDEX_OWNER = '$schema' AND INDEX_OWNER = '$schema'
"; ";
$object_type = lc $object_type; $object_type = lc $object_type;
$text = "General info: $object_type $object_name"; $text = "General info: $object_type $schema.$object_name";
$link = ""; $link = "";
DisplayTable($sql,$text,$link); DisplayTable($sql,$text,$link);
logit("Exit subroutine showIndexPart"); logit("Exit subroutine showIndexPart");
} }
sub showTablePart { sub showTablePart {
logit("Enter subroutine showTablePart"); logit("Enter subroutine showTablePart");
skipping to change at line 1181 skipping to change at line 1186
HIGH_VALUE \"High value\", HIGH_VALUE \"High value\",
HIGH_VALUE_LENGTH \"High value length\", HIGH_VALUE_LENGTH \"High value length\",
LOGGING \"Logging\" LOGGING \"Logging\"
FROM DBA_TAB_PARTITIONS FROM DBA_TAB_PARTITIONS
WHERE PARTITION_NAME = '$object_name' WHERE PARTITION_NAME = '$object_name'
AND TABLE_NAME = '$table_name' AND TABLE_NAME = '$table_name'
AND TABLE_OWNER = '$schema' AND TABLE_OWNER = '$schema'
"; ";
$object_type = lc $object_type; $object_type = lc $object_type;
$text = "General info: $object_type $object_name"; $text = "General info: $object_type $schema.$object_name";
$link = ""; $link = "";
DisplayTable($sql,$text,$link); DisplayTable($sql,$text,$link);
# Check to see if partition has been analyzed. # Check to see if partition has been analyzed.
$sql = "$copyright $sql = "$copyright
SELECT SELECT
DISTINCT LAST_ANALYZED DISTINCT LAST_ANALYZED
FROM DBA_TAB_PARTITIONS FROM DBA_TAB_PARTITIONS
WHERE PARTITION_NAME = '$object_name' WHERE PARTITION_NAME = '$object_name'
skipping to change at line 1253 skipping to change at line 1258
FUNCTION \"Function\", FUNCTION \"Function\",
INSTANCES \"Instances\", INSTANCES \"Instances\",
SINGLE_TABLE \"Single table\" SINGLE_TABLE \"Single table\"
FROM DBA_CLUSTERS FROM DBA_CLUSTERS
WHERE CLUSTER_NAME = '$object_name' WHERE CLUSTER_NAME = '$object_name'
AND OWNER = '$schema' AND OWNER = '$schema'
"; ";
logit (" $sql"); logit (" $sql");
$text = "General info: Cluster $object_name"; $text = "General info: Cluster $schema.$object_name";
$link = ""; $link = "";
DisplayTable($sql,$text,$link); DisplayTable($sql,$text,$link);
$sql = "$copyright $sql = "$copyright
SELECT SELECT
TABLE_NAME \"Table_name\", TABLE_NAME \"Table_name\",
TO_CHAR(INITIAL_EXTENT,'999,999,999,999') \"Initial extent\", TO_CHAR(INITIAL_EXTENT,'999,999,999,999') \"Initial extent\",
TO_CHAR(NEXT_EXTENT,'999,999,999,999') \"Next extent\", TO_CHAR(NEXT_EXTENT,'999,999,999,999') \"Next extent\",
TO_CHAR(MAX_EXTENTS,'999,999,999,999') \"Max extents\" TO_CHAR(MAX_EXTENTS,'999,999,999,999') \"Max extents\"
FROM DBA_TABLES FROM DBA_TABLES
skipping to change at line 1510 skipping to change at line 1515
AND A.SEGMENT_TYPE = 'TABLE' AND A.SEGMENT_TYPE = 'TABLE'
AND A.OWNER = '$schema' AND A.OWNER = '$schema'
AND B.OBJECT_NAME = '$object_name' AND B.OBJECT_NAME = '$object_name'
AND B.OBJECT_TYPE = 'TABLE' AND B.OBJECT_TYPE = 'TABLE'
AND B.OWNER = '$schema' AND B.OWNER = '$schema'
AND C.TABLE_NAME = '$object_name' AND C.TABLE_NAME = '$object_name'
AND C.OWNER = '$schema' AND C.OWNER = '$schema'
"; ";
$object_type = lc $object_type; $object_type = lc $object_type;
$text = "General info: $object_type $object_name"; $text = "General info: $object_type $schema.$object_name";
$link = "$scriptname?database=$database&object_type=TSINFO"; $link = "$scriptname?database=$database&object_type=TSINFO";
DisplayTable($sql,$text,$link); DisplayTable($sql,$text,$link);
} }
# Check to see if table is partitioned # Check to see if table is partitioned
if ($partitioned) { if ($partitioned) {
$sql = "$copyright $sql = "$copyright
skipping to change at line 1662 skipping to change at line 1667
TABLESPACE_NAME \"Tablespace name\", TABLESPACE_NAME \"Tablespace name\",
DECODE(UNIQUENESS, DECODE(UNIQUENESS,
'UNIQUE','Yes', 'UNIQUE','Yes',
'NONUNIQUE','No') \"Unique?\", 'NONUNIQUE','No') \"Unique?\",
TO_CHAR(INITIAL_EXTENT,'999,999,999,999') \"Initial extent\", TO_CHAR(INITIAL_EXTENT,'999,999,999,999') \"Initial extent\",
TO_CHAR(NEXT_EXTENT,'999,999,999,999') \"Next extent\", TO_CHAR(NEXT_EXTENT,'999,999,999,999') \"Next extent\",
TO_CHAR(MAX_EXTENTS,'999,999,999,999') \"Max extents\" TO_CHAR(MAX_EXTENTS,'999,999,999,999') \"Max extents\"
FROM DBA_INDEXES FROM DBA_INDEXES
WHERE TABLE_NAME = '$object_name' WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema' AND OWNER = '$schema'
ORDER BY 1
"; ";
$cursor = $dbh->prepare($sql); $cursor = $dbh->prepare($sql);
$cursor->execute; $cursor->execute;
while (($index_name,$index_type,$status,$tablespace_name,$uniqueness,$init ial_extent,$next_extent,$max_extents) = $cursor->fetchrow) { while (($index_name,$index_type,$status,$tablespace_name,$uniqueness,$init ial_extent,$next_extent,$max_extents) = $cursor->fetchrow) {
undef @columns; undef @columns;
$indexes++; $indexes++;
$sql1 = "$copyright $sql1 = "$copyright
SELECT COLUMN_NAME SELECT COLUMN_NAME
FROM DBA_IND_COLUMNS FROM DBA_IND_COLUMNS
WHERE INDEX_NAME = '$index_name' WHERE INDEX_NAME = '$index_name'
skipping to change at line 2158 skipping to change at line 2164
} }
sub objectSearch { sub objectSearch {
logit("Enter subroutine objectSearch"); logit("Enter subroutine objectSearch");
my ($sql,$text,$link,$infotext,$moresql,$count); my ($sql,$text,$link,$infotext,$moresql,$count);
my ($obj_name,$object_type,$owner,$object_id); my ($obj_name,$object_type,$owner,$object_id);
my ($object_found,$filenum,$block_id,$obj_name_url); my ($object_found,$filenum,$block_id,$obj_name_url);
my ($object_type_url);
# Search for an object in the entire database # Search for an object in the entire database
# Check for a null value # Check for a null value
if ($object_name eq "") { if ($object_name eq "") {
message("You must enter an object name!\n"); message("You must enter an object name!\n");
Footer(); Footer();
exit; exit;
} else { } else {
skipping to change at line 2278 skipping to change at line 2285
$moresql = "AND OWNER = UPPER('$schema')"; $moresql = "AND OWNER = UPPER('$schema')";
} }
$sql = "$copyright $sql = "$copyright
SELECT SELECT
OBJECT_NAME \"Object name\", OBJECT_NAME \"Object name\",
OBJECT_TYPE \"Object type\", OBJECT_TYPE \"Object type\",
OWNER \"Owner\", OWNER \"Owner\",
OBJECT_ID \"Object ID\" OBJECT_ID \"Object ID\"
FROM DBA_OBJECTS FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE UPPER('\%$object_name\%') WHERE UPPER(OBJECT_NAME) LIKE UPPER('\%$object_name\%')
AND OBJECT_TYPE NOT LIKE '%PARTITION' AND OBJECT_TYPE NOT LIKE '%PARTITION'
$moresql $moresql
ORDER BY 1,2,3 ORDER BY 1,2,3
"; ";
$cursor = $dbh->prepare($sql); $cursor = $dbh->prepare($sql);
$cursor->execute; $cursor->execute;
$object_found = $cursor->fetchrow_array; $object_found = $cursor->fetchrow_array;
$cursor->finish; $cursor->finish;
skipping to change at line 2314 skipping to change at line 2321
<th class="center">Mark</th> <th class="center">Mark</th>
<th class="center">Object name</th> <th class="center">Object name</th>
<th class="center">Object type</th> <th class="center">Object type</th>
<th class="center">Owner</th> <th class="center">Owner</th>
EOF EOF
$cursor = $dbh->prepare($sql); $cursor = $dbh->prepare($sql);
$cursor->execute; $cursor->execute;
while (($obj_name,$object_type,$owner,$object_id) = $cursor->fetchrow) { while (($obj_name,$object_type,$owner,$object_id) = $cursor->fetchrow) {
$_ = $object_type; $object_type_url = $object_type;
$object_type_url =~ s/ /+/g;
$obj_name_url = $obj_name; $obj_name_url = $obj_name;
$obj_name_url =~ s/ /+/g; $obj_name_url =~ s/ /+/g;
$obj_name_url =~ s/#/\%23/g; $obj_name_url =~ s/#/\%23/g;
$obj_name_url =~ s/</\%3C/g; $obj_name_url =~ s/</\%3C/g;
$obj_name_url =~ s/>/\%3E/g; $obj_name_url =~ s/>/\%3E/g;
$obj_name_url =~ s/{/\%7B/g; $obj_name_url =~ s/{/\%7B/g;
$obj_name_url =~ s/\|/\%7C/g; $obj_name_url =~ s/\|/\%7C/g;
$obj_name_url =~ s/}/\%7D/g; $obj_name_url =~ s/}/\%7D/g;
$obj_name_url =~ s/\\/\%5C/g; $obj_name_url =~ s/\\/\%5C/g;
$obj_name_url =~ s/\^/\%5E/g; $obj_name_url =~ s/\^/\%5E/g;
$obj_name_url =~ s/#/\%23/g; $obj_name_url =~ s/#/\%23/g;
# Object ID's are sometimes not returned because of database link naming convent ions... # Object ID's are sometimes not returned because of database link naming convent ions...
if ($object_id) { if ($object_id) {
logit("Object name: $obj_name Type: $object_type ID: $object_id");
print <<"EOF"; print <<"EOF";
<tr> <tr>
<td class="center"><input type=CHECKBOX NAME=dependency~$object_id></td> <td class="center"><input type=CHECKBOX NAME=dependency~$object_id></td>
<td class="left"><A href=$scriptname?database=$database&arg=$obj_name_url&ob ject_type=$_&schema=$owner>$obj_name</A></td> <td class="left"><A href=$scriptname?database=$database&arg=$obj_name_url&ob ject_type=$object_type_url&schema=$owner>$obj_name</A></td>
<td class="center">$object_type</td> <td class="center">$object_type</td>
<td class="center">$owner</td> <td class="center">$owner</td>
</tr> </tr>
EOF EOF
} }
} }
$cursor->finish; $cursor->finish;
print <<"EOF"; print <<"EOF";
</form> </form>
</table> </table>
skipping to change at line 2425 skipping to change at line 2434
CONSTRAINT_NAME \"Parent constraint\", CONSTRAINT_NAME \"Parent constraint\",
OWNER \"Parent owner\" OWNER \"Parent owner\"
FROM DBA_CONSTRAINTS FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_NAME = WHERE CONSTRAINT_NAME =
(SELECT R_CONSTRAINT_NAME (SELECT R_CONSTRAINT_NAME
FROM DBA_CONSTRAINTS FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_NAME = '$object_name' WHERE CONSTRAINT_NAME = '$object_name'
AND OWNER = '$schema')) AND OWNER = '$schema'))
"; ";
$text = "General info: Constraint $object_name"; $text = "General info: Constraint $schema.$object_name";
$link = ""; $link = "";
DisplayTable($sql,$text,$link); DisplayTable($sql,$text,$link);
logit("Exit subroutine showConstraint"); logit("Exit subroutine showConstraint");
} }
sub showView() { sub showView() {
logit("Enter subroutine showView"); logit("Enter subroutine showView");
skipping to change at line 2491 skipping to change at line 2500
SELECT SELECT
TO_CHAR(CREATED,'Month DD, YYYY - HH24:MI') \"Date created\", TO_CHAR(CREATED,'Month DD, YYYY - HH24:MI') \"Date created\",
TO_CHAR(LAST_DDL_TIME,'Month DD, YYYY - HH24:MI') \"Last compiled\", TO_CHAR(LAST_DDL_TIME,'Month DD, YYYY - HH24:MI') \"Last compiled\",
STATUS \"Status\" STATUS \"Status\"
FROM DBA_OBJECTS FROM DBA_OBJECTS
WHERE OBJECT_NAME = '$object_name' WHERE OBJECT_NAME = '$object_name'
AND OBJECT_TYPE = '$object_type' AND OBJECT_TYPE = '$object_type'
AND OWNER = '$schema' AND OWNER = '$schema'
"; ";
$text = "General info: $object_type $object_name"; $text = "General info: $object_type $schema.$object_name";
DisplayTable($sql,$text); DisplayTable($sql,$text);
checkValidity(); checkValidity();
$object_type = lc $object_type; $object_type = lc $object_type;
# View structure # View structure
$sql = "$copyright $sql = "$copyright
SELECT SELECT
skipping to change at line 2713 skipping to change at line 2722
SELECT SELECT
TO_CHAR(CREATED,'Month DD, YYYY - HH24:MI') \"Date created\", TO_CHAR(CREATED,'Month DD, YYYY - HH24:MI') \"Date created\",
TO_CHAR(LAST_DDL_TIME,'Month DD, YYYY - HH24:MI') \"Last compiled\", TO_CHAR(LAST_DDL_TIME,'Month DD, YYYY - HH24:MI') \"Last compiled\",
STATUS \"Status\" STATUS \"Status\"
FROM DBA_OBJECTS FROM DBA_OBJECTS
WHERE OBJECT_NAME = '$object_name' WHERE OBJECT_NAME = '$object_name'
AND OBJECT_TYPE = '$object_type' AND OBJECT_TYPE = '$object_type'
AND OWNER = '$schema' AND OWNER = '$schema'
"; ";
$text = "General info: $object_type $object_name"; $text = "General info: $object_type $schema.$object_name";
DisplayTable($sql,$text); DisplayTable($sql,$text);
checkValidity(); checkValidity();
$sql = "$copyright $sql = "$copyright
SELECT SELECT
SYNONYM_NAME \"Synonym name\", SYNONYM_NAME \"Synonym name\",
OWNER \"Owner\", OWNER \"Owner\",
DB_LINK \"DB link\" DB_LINK \"DB link\"
FROM DBA_SYNONYMS FROM DBA_SYNONYMS
skipping to change at line 2831 skipping to change at line 2840
ADMIN_OPTION \"Admin option\", ADMIN_OPTION \"Admin option\",
DEFAULT_ROLE \"Default role\" DEFAULT_ROLE \"Default role\"
FROM DBA_ROLE_PRIVS FROM DBA_ROLE_PRIVS
WHERE GRANTEE = '$schema' WHERE GRANTEE = '$schema'
"; ";
$text = "Roles granted to $schema"; $text = "Roles granted to $schema";
$link = "$scriptname?database=$database&schema=$schema&object_type=ROLES"; $link = "$scriptname?database=$database&schema=$schema&object_type=ROLES";
$infotext = "There are no roles granted to $schema."; $infotext = "There are no roles granted to $schema.";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
# Directory privileges
$sql = "
Select
table_name \"Directory name\",
privilege \"Privilege\"
from dba_tab_privs
where grantee = '$schema'
and table_name in
(Select directory_name from dba_directories)
order by 1";
$text = "Directory privileges granted to $schema";
$link = "";
$infotext = "There are no directory privileges granted to $schema.";
DisplayTable($sql,$text,$link,$infotext);
# Granted java privileges (explicit) # Granted java privileges (explicit)
$sql = " $sql = "
Select Select
kind \"Kind\", kind \"Kind\",
type_name \"Type name\", type_name \"Type name\",
name \"Name\", name \"Name\",
action \"Action\" action \"Action\",
enabled \"Enabled?\",
seq \"Sequence\"
from dba_java_policy from dba_java_policy
where grantee = '$schema' where grantee = '$schema'
"; ";
$text = "Java privileges granted to $schema"; $text = "Java privileges granted to $schema<br>Use the sequence to drop permi ssions<br>Example:<br>dbms_java.disable_permission(sequence);<br>dbms_java.delet e_permission(sequence);";
$link = ""; $link = "";
$infotext = "There are no java privileges granted to $schema."; $infotext = "There are no java privileges granted to $schema.";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
# Granted object privileges (explicit) # Granted object privileges (explicit)
$sql = "$copyright $sql = "$copyright
SELECT SELECT
PRIVILEGE \"Privilege\", PRIVILEGE \"Privilege\",
TABLE_NAME \"Table name\", TABLE_NAME \"Table name\",
GRANTOR \"Grantor\", GRANTOR \"Grantor\",
GRANTABLE \"Grantable\" GRANTABLE \"Grantable\"
FROM DBA_TAB_PRIVS FROM DBA_TAB_PRIVS
WHERE GRANTEE = '$schema' WHERE GRANTEE = '$schema'
ORDER BY GRANTOR, TABLE_NAME ORDER BY 2, 1, 3
"; ";
$text = "Explicit grants to $schema"; $text = "Explicit grants to $schema";
$link = ""; $link = "";
$infotext = "There are no explicit grants to $schema."; $infotext = "There are no explicit grants to $schema.";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showGrantsto"); logit("Exit subroutine showGrantsto");
} }
skipping to change at line 3250 skipping to change at line 3278
logit("Exit subroutine opsInfo"); logit("Exit subroutine opsInfo");
} }
sub showUsers { sub showUsers {
logit("Enter subroutine showUsers"); logit("Enter subroutine showUsers");
my ($sql,$user,$dbid,@dbausers,@connectedusers,$dba,$connected,$text,$cursor) ; my ($sql,$user,$dbid,@dbausers,@connectedusers,$dba,$connected,$text,$cursor) ;
my (@lockedusers,$locked,$skip,$counter,$row,$usercount,$i,$moretext); my (@lockedusers,$locked,$skip,$counter,$row,$usercount,$i,$moretext,$dbname) ;
my $highlight = "#FFFFC6"; my $highlight = "#FFFFC6";
my $redlight = "#DEBDDE"; my $redlight = "#DEBDDE";
# Show database connection info # Show database connection info
logit(" Showing connection information"); logit(" Showing connection information");
$sql = " $sql = "
SELECT SELECT
DBID DBID, NAME
FROM V\$DATABASE FROM V\$DATABASE
"; ";
$cursor = $dbh->prepare($sql); $cursor = $dbh->prepare($sql);
$cursor->execute; $cursor->execute;
$dbid = $cursor->fetchrow_array; ($dbid,$dbname) = $cursor->fetchrow_array;
$cursor->finish; $cursor->finish;
if ($hostname) { if ($hostname) {
$moretext = "Hostname : $hostname"; $moretext = "Hostname : $hostname";
} else { } else {
$moretext = ""; $moretext = "";
} }
if ($dbid) { if ($dbid) {
$moretext = "$moretext (DBID : $dbid)"; $moretext = "$moretext (DBID : $dbid)";
} }
print <<"EOF"; print <<"EOF";
Connected to database : $database $moretext<BR> Connected to database : $dbname $moretext<BR>
$banner $banner
<P> <P>
EOF EOF
# If database is not open, show a message explaining this. # If database is not open, show a message explaining this.
if ($dbstatus ne "OPEN") { if ($dbstatus ne "OPEN") {
logit("Database is not open, displaying warning message."); logit("Database is not open, displaying warning message.");
message("Warning: this database is in $dbstatus mode. Operations will be l imited."); message("Warning: this database is in $dbstatus mode. Operations will be l imited.");
return; return;
skipping to change at line 3342 skipping to change at line 3370
logit(" Done getting users with non-open accounts"); logit(" Done getting users with non-open accounts");
# Get all users that are currently connected. # Get all users that are currently connected.
logit(" Getting list of currently connected users"); logit(" Getting list of currently connected users");
$sql = "$copyright $sql = "$copyright
SELECT DISTINCT USERNAME SELECT DISTINCT USERNAME
FROM V\$SESSION FROM V\$SESSION
WHERE USERNAME IS NOT NULL WHERE USERNAME IS NOT NULL
AND STATUS in ('ACTIVE','INACTIVE')
"; ";
$cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr"); $cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr");
$cursor->execute or ErrorPage ("$DBI::errstr"); $cursor->execute or ErrorPage ("$DBI::errstr");
while ($user = $cursor->fetchrow_array) { while ($user = $cursor->fetchrow_array) {
push (@connectedusers,$user); push (@connectedusers,$user);
} }
$cursor->finish; $cursor->finish;
skipping to change at line 3473 skipping to change at line 3502
USERNAME \"Username\", USERNAME \"Username\",
USERHOST \"Host\", USERHOST \"Host\",
TERMINAL \"Terminal\", TERMINAL \"Terminal\",
TO_CHAR(TIMESTAMP,'Day, Month DD YYYY - HH24:MI:SS') \"Timestamp\", TO_CHAR(TIMESTAMP,'Day, Month DD YYYY - HH24:MI:SS') \"Timestamp\",
OWNER \"Owner\", OWNER \"Owner\",
OBJ_NAME \"Object name\", OBJ_NAME \"Object name\",
ACTION_NAME \"Action\", ACTION_NAME \"Action\",
PRIV_USED \"Priv used\" PRIV_USED \"Priv used\"
FROM DBA_AUDIT_TRAIL FROM DBA_AUDIT_TRAIL
WHERE PRIV_USED = '$object_name' WHERE PRIV_USED = '$object_name'
ORDER BY TIMESTAMP DESC, OWNER ORDER BY TIMESTAMP DESC, USERNAME
"; ";
$text = "Audit records: $object_name."; $text = "Audit records: $object_name.";
$link = ""; $link = "";
$infotext = "No audit records for $object_name."; $infotext = "No audit records for $object_name.";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
} }
if ($command eq "object") { if ($command eq "object") {
skipping to change at line 3644 skipping to change at line 3673
<form method="GET" ACTION="$scriptname"> <form method="GET" ACTION="$scriptname">
<input type="HIDDEN" NAME="database" VALUE="$database"> <input type="HIDDEN" NAME="database" VALUE="$database">
<input type="HIDDEN" NAME="object_type" VALUE="AUDITING"> <input type="HIDDEN" NAME="object_type" VALUE="AUDITING">
<input type="SUBMIT" NAME="auditing" VALUE="Auditing information"> <input type="SUBMIT" NAME="auditing" VALUE="Auditing information">
</form> </form>
EOF EOF
} else { } else {
message("Database auditing is not enabled."); message("Database auditing is not enabled.");
} }
$sql = " if ( $majversion eq "11" ) {
Select $sql = "
version \"DB version\", Select
to_char(action_time,'MM/DD/YYYY HH24:MI') \"Date applied\", version \"DB version\",
comments \"Patch\" to_char(action_time,'MM/DD/YYYY HH24:MI') \"Date applied\",
comments \"Patch\"
from sys.registry\$history from sys.registry\$history
where action_time = where action_time =
(Select max(action_time) (Select max(action_time)
from sys.registry\$history from sys.registry\$history
where comments like 'PSU%' or comments like 'CPU%' or comments like 'SPU% where comments like 'PSU%'
')"; or comments like 'CPU%'
or comments like 'SPU%'
or comments like 'APPLIED jvmpsu.sql')
";
}
if ( $majversion eq "12" ) {
if ( $minversion eq "1" ) {
#
# This format, putting both patches in the same column
# Started with the Jan 2017 PSU
#
$sql = "
Select
version \"DB version\",
to_char(action_time,'MM/DD/YYYY HH24:MI') \"Date applied\",
description \"Patch\"
from dba_registry_sqlpatch
where description like '%Database PSU%'
and action_time =
(Select max(action_time)
from dba_registry_sqlpatch
where description like '%Database PSU%')
";
}
if ( $minversion eq "2" ) {
$sql = "
Select
version \"DB version\",
to_char(action_time,'MM/DD/YYYY HH24:MI') \"Date applied\",
description \"Patch\"
from dba_registry_sqlpatch
where description like 'DATABASE%RELEASE UPDATE%'
and action_time =
(Select max(action_time)
from dba_registry_sqlpatch
where description like 'DATABASE%RELEASE UPDATE%')
";
}
}
if ( $majversion eq "19" ) {
$sql = "
Select
target_version \"DB version\",
to_char(action_time,'MM/DD/YYYY HH24:MI') \"Date applied\",
description \"Patch\"
from dba_registry_sqlpatch
where description like '%Database Release Update%'
and action_time =
(Select max(action_time)
from dba_registry_sqlpatch
where description like '%Database Release Update%')
";
}
$text = "Most recent Critical Patch / Patch Set found"; $text = "Most recent Critical Patch / Patch Set found";
$infotext = "No Critical Patch Update info found"; $infotext = "No Critical Patch Update info found";
$link = ""; $link = "";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
# #
# Need to come back to this. Public db links dependency listing is acting funky # Need to come back to this. Public db links dependency listing is acting funky
# #
$count = recordCount($dbh,"Select db_link from dba_db_links where owner='PUBL IC'"); $count = recordCount($dbh,"Select db_link from dba_db_links where owner='PUBL IC'");
skipping to change at line 3702 skipping to change at line 3786
$text = "Access Control Lists"; $text = "Access Control Lists";
$link = "$scriptname?database=$database&object_type=ACL"; $link = "$scriptname?database=$database&object_type=ACL";
$infotext = "There are no Access Control Lists defined"; $infotext = "There are no Access Control Lists defined";
DisplayColTable($sql,$text,$link,$infotext,$schema_cols); DisplayColTable($sql,$text,$link,$infotext,$schema_cols);
} }
$sql = " $sql = "
Select Select
owner \"Owner\",
directory_name \"Name\", directory_name \"Name\",
owner \"Owner\",
directory_path \"Path\" directory_path \"Path\"
from dba_directories order by 1,2"; from dba_directories order by 1,2";
$text = "Directories"; $text = "Directories";
$link = ""; $link = "$scriptname?database=$database&object_type=DIRECTORY";
$infotext = "No directories defined."; $infotext = "No directories defined.";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
$sql = "$copyright $sql = "$copyright
SELECT SELECT
ROLE ROLE
FROM DBA_ROLES FROM DBA_ROLES
ORDER BY ROLE ORDER BY ROLE
"; ";
skipping to change at line 3769 skipping to change at line 3853
(SELECT ROLE (SELECT ROLE
FROM DBA_ROLES) FROM DBA_ROLES)
"; ";
$text = "Roles with the \"DBA\" role granted to them"; $text = "Roles with the \"DBA\" role granted to them";
$link = "$scriptname?database=$database&object_type=ROLES"; $link = "$scriptname?database=$database&object_type=ROLES";
$infotext = "There are no roles with the \"DBA\" role in this database"; $infotext = "There are no roles with the \"DBA\" role in this database";
DisplayColTable($sql,$text,$link,$infotext,$schema_cols); DisplayColTable($sql,$text,$link,$infotext,$schema_cols);
$count = recordCount("Select count(*) from dba_audit_session"); $count = recordCount($dbh,"Select count(*) from dba_audit_session");
logit(" There are $count records in the dba_audit_session table"); logit(" There are $count records in the dba_audit_session table");
logit("Exit subroutine showSecurity"); logit("Exit subroutine showSecurity");
} }
sub showProfile { sub showProfile {
logit("Enter subroutine showProfile"); logit("Enter subroutine showProfile");
skipping to change at line 3830 skipping to change at line 3914
# User info # User info
# Get the data from the database # Get the data from the database
my ($sql,$cursor,$count,$text,$link,$infotext,$cols); my ($sql,$cursor,$count,$text,$link,$infotext,$cols);
my ($uname,$defts,$tmpts,$created,$profile,$objcount); my ($uname,$defts,$tmpts,$created,$profile,$objcount);
my ($status,$ldate,$edate); my ($status,$ldate,$edate);
$schema = $object_name unless $schema; $schema = $object_name unless $schema;
refreshButton();
# General user info # General user info
$sql = "$copyright $sql = "$copyright
Select Select
username, username,
default_tablespace, default_tablespace,
temporary_tablespace, temporary_tablespace,
to_char(created,'MM/DD/YYYY - HH24:MI'), to_char(created,'MM/DD/YYYY - HH24:MI'),
to_char(lock_date,'MM/DD/YYYY - HH24:MI'), to_char(lock_date,'MM/DD/YYYY - HH24:MI'),
to_char(expiry_date,'MM/DD/YYYY - HH24:MI'), to_char(expiry_date,'MM/DD/YYYY - HH24:MI'),
skipping to change at line 3906 skipping to change at line 3992
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
# Buttons for displaying grants / session info # Buttons for displaying grants / session info
print <<"EOF"; print <<"EOF";
<P> <P>
<table class="noborder"> <table class="noborder">
<tr> <tr>
<td class="noborder"> <td class="noborder">
EOF EOF
Button("$scriptname?database=$database&schema=$schema&object_type=GRANTSTO TARGET=body","Display grants <i>to</i> $schema","$headingcolor","CENTER",200); Button("$scriptname?database=$database&schema=$schema&object_type=GRANTSTO TARGET=body","Display grants <i>to</i> $schema","$headingcolor","CENTER",250);
print <<"EOF"; print <<"EOF";
</td> </td>
<td class="noborder"> <td class="noborder">
EOF EOF
Button("$scriptname?database=$database&schema=$schema&object_type=GRANTSFR OM TARGET=body","Display grants <i>from</i> $schema","$headingcolor","CENTER",20 0); Button("$scriptname?database=$database&schema=$schema&object_type=GRANTSFR OM TARGET=body","Display grants <i>from</i> $schema","$headingcolor","CENTER",25 0);
print <<"EOF"; print <<"EOF";
</td> </td>
</tr> </tr>
</table> </table>
EOF EOF
# Display a button if the user currently has sessions in this instance. # Display a button if the user currently has sessions in this instance.
$sql = "$copyright $sql = "$copyright
SELECT COUNT(*) SELECT COUNT(*)
FROM V\$SESSION FROM V\$SESSION
WHERE USERNAME = '$schema' WHERE USERNAME = '$schema'
"; ";
$cursor=$dbh->prepare($sql); $cursor=$dbh->prepare($sql);
$cursor->execute; $cursor->execute;
$count = $cursor->fetchrow_array; $count = $cursor->fetchrow_array;
$cursor->finish; $cursor->finish;
if ($count > 0) { if ($count > 0) {
Button("$scriptname?database=$database&schema=$schema&object_type=TOPSESSI ONS TARGET=body","Display $schema session info","$headingcolor","CENTER",200); Button("$scriptname?database=$database&schemaname=$schema&object_type=TOPS ESSIONS TARGET=body","Display $schema session info","$headingcolor","CENTER",200 );
} else { } else {
message("$schema has no sessions in this instance."); message("$schema has no sessions in this instance.");
} }
Button("$scriptname?database=$database&schemaname=$schema&object_type=LOGINAU
DITRECORDS TARGET=body","Display $schema connection history","$headingcolor","CE
NTER",200);
# Check to see if there are any public synonyms pointing # Check to see if there are any public synonyms pointing
# to objects owned by the schema selected. # to objects owned by the schema selected.
$sql = "$copyright $sql = "$copyright
SELECT SELECT
COUNT(*) COUNT(*)
FROM DBA_SYNONYMS FROM DBA_SYNONYMS
WHERE TABLE_OWNER = '$schema' WHERE TABLE_OWNER = '$schema'
AND OWNER = 'PUBLIC' AND OWNER = 'PUBLIC'
"; ";
skipping to change at line 3986 skipping to change at line 4074
"; ";
$text = "Object types owned by $schema.<BR>Click an object type for a list ."; $text = "Object types owned by $schema.<BR>Click an object type for a list .";
} }
$link = "$scriptname?database=$database&schema=$schema&object_type=LISTOBJECT S"; $link = "$scriptname?database=$database&schema=$schema&object_type=LISTOBJECT S";
$infotext = "There are no objects owned by $schema in this database."; $infotext = "There are no objects owned by $schema in this database.";
DisplayColTable($sql,$text,$link,$infotext,$schema_cols); DisplayColTable($sql,$text,$link,$infotext,$schema_cols);
$sql = "$copyright $sql = "$copyright
Select segment_type \"Object type\",
to_char(sum(bytes)/1048576,'999,999,999,999,999,999') \"Space used in M
B\"
from dba_segments where owner='$schema' group by segment_type
";
$link = "";
$text = "Segment space usage (MB)";
$infotext = "";
DisplayTable($sql,$text,$link,$infotext,$schema_cols);
$sql = "$copyright
SELECT COUNT(*) FROM DBA_OBJECTS WHERE OWNER = '$schema' SELECT COUNT(*) FROM DBA_OBJECTS WHERE OWNER = '$schema'
"; ";
$cursor = $dbh->prepare($sql); $cursor = $dbh->prepare($sql);
$cursor->execute; $cursor->execute;
$objcount = $cursor->fetchrow_array; $objcount = $cursor->fetchrow_array;
$cursor->finish; $cursor->finish;
if ($objcount) { if ($objcount) {
$sql = "$copyright $sql = "$copyright
skipping to change at line 4532 skipping to change at line 4631
logit(" Object is a $object_name"); logit(" Object is a $object_name");
# Object types with spaces are not +'d at this point. # Object types with spaces are not +'d at this point.
$object_type = $object_name; $object_type = $object_name;
if ($object_type eq "DIRECTORY") { if ($object_type eq "DIRECTORY") {
$sql = " $sql = "
Select Select
directory_name \"Name\", directory_name \"Name\",
owner \"Owner\",
directory_path \"Path\" directory_path \"Path\"
from dba_directories from dba_directories
where owner = '$schema' where owner = '$schema'
"; ";
$infotext = "No directories."; $infotext = "No directories.";
$text = "Directories"; $text = "Directories";
$link = "$scriptname?database=$database&schema=$schema&object_type=$object _type"; $link = "$scriptname?database=$database&schema=$schema&object_type=$object _type";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
Footer(); Footer();
} }
if ($object_type eq "SYNONYM") { if ($object_type eq "SYNONYM") {
$sql = "$copyright $sql = "$copyright
SELECT SELECT
SYNONYM_NAME \"Synonym name\", SYNONYM_NAME \"Synonym name\",
TABLE_OWNER \"Object owner\", TABLE_OWNER \"Object owner\",
TABLE_NAME \"Object name\" TABLE_NAME \"Object name\",
DB_LINK \"DB Link\"
FROM DBA_SYNONYMS FROM DBA_SYNONYMS
WHERE OWNER = '$schema' WHERE OWNER = '$schema'
"; ";
$infotext = "No synonyms."; $infotext = "No synonyms.";
$text = "Synonyms"; $text = "Synonyms";
$link = "$scriptname?database=$database&schema=$schema&object_type=$object _type"; $link = "$scriptname?database=$database&schema=$schema&object_type=$object _type";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
Footer(); Footer();
skipping to change at line 4620 skipping to change at line 4721
$infotext = "No global temporary tables in this schema."; $infotext = "No global temporary tables in this schema.";
$text = "Global temporary tables."; $text = "Global temporary tables.";
$link = "$scriptname?database=$database&schema=$schema&object_type=$object _name"; $link = "$scriptname?database=$database&schema=$schema&object_type=$object _name";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
# Now, show the normal tables. # Now, show the normal tables.
logit(" Start standard tables"); logit(" Start standard tables");
$sql = "$copyright $sql = "$copyright
SELECT Select
SEGMENT_NAME \"Object name\", dt.table_name \"Table n
TABLESPACE_NAME \"Tablespace name\", ame\",
TO_CHAR(BYTES,'999,999,999,999') \"Bytes\" dt.tablespace_name \"Tablesp
FROM DBA_SEGMENTS ace name\",
WHERE OWNER = '$schema' nvl(to_char(ds.bytes,'999,999,999,999'),'No space allocated') \"Bytes\"
AND SEGMENT_TYPE = '$object_type' from dba_tables dt, dba_segments ds
ORDER BY SEGMENT_NAME where dt.owner = '$schema'
and dt.owner = ds.owner (+)
and dt.table_name = ds.segment_name (+)
order by segment_name
"; ";
#
# This did not return tables when there werre no extents allocated
#
# $sql = "$copyright
#SELECT
# SEGMENT_NAME \"Object name\",
# TABLESPACE_NAME \"Tablespace name\",
# TO_CHAR(BYTES,'999,999,999,999') \"Bytes\"
#FROM DBA_SEGMENTS
# WHERE OWNER = '$schema'
# AND SEGMENT_TYPE = '$object_type'
#ORDER BY SEGMENT_NAME
#";
$text = "Standard tables."; $text = "Standard tables.";
$infotext = "No standard tables in this schema."; $infotext = "No standard tables in this schema.";
$link = "$scriptname?database=$database&schema=$schema&object_type=$object _name"; $link = "$scriptname?database=$database&schema=$schema&object_type=$object _name";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
Footer(); Footer();
} }
# If object is an index, show the space used # If object is an index, show the space used
skipping to change at line 5421 skipping to change at line 5537
logit("Exit subroutine sessionWait"); logit("Exit subroutine sessionWait");
} }
sub showDirectory { sub showDirectory {
logit("Enter subroutine showDirectory"); logit("Enter subroutine showDirectory");
my ($sql,$sql1,$cursor,$cursor1,$directory,$grantee,$text,$link,$infotext); my ($sql,$sql1,$cursor,$cursor1,$directory,$grantee,$text,$link,$infotext);
my ($priv,$privs,$count); my ($priv,$privs,$count);
my $owner = $query->param('arg2') || "";
$sql = " $sql = "
Select Select
directory_name \"Name\", directory_name \"Name\",
owner \"Owner\",
directory_path \"Path\" directory_path \"Path\"
from dba_directories from dba_directories
where directory_name = '$object_name' where directory_name = '$object_name'
"; ";
$text = "Directory details"; $text = "Directory details";
$link = ""; $link = "";
$infotext = ""; $infotext = "";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
$sql = " $sql = "
Select Select
distinct grantee distinct grantee
from dba_tab_privs from dba_tab_privs
where table_name = '$object_name' where table_name = '$object_name'
and owner = '$schema' and owner = '$owner'
"; ";
$count = recordCount($dbh,$sql); $count = recordCount($dbh,$sql);
if ($count) { if ($count) {
message("Privileges granted"); message("Privileges granted");
$cursor = $dbh->prepare($sql); $cursor = $dbh->prepare($sql);
$cursor->execute; $cursor->execute;
print "<table class=\"ot\"><th class=\"center\">Username</th><th class=\"c enter\">Privileges</th>"; print "<table class=\"ot\"><th class=\"center\">Username</th><th class=\"c enter\">Privileges</th>";
while ($grantee = $cursor->fetchrow_array) { while ($grantee = $cursor->fetchrow_array) {
print "<tr><td class=\"left\">$grantee</td>"; print "<tr><td class=\"left\">$grantee</td>";
$sql1 = "Select privilege from dba_tab_privs where table_name = '$objec t_name' and owner = '$schema' and grantee = '$grantee' order by privilege"; $sql1 = "Select privilege from dba_tab_privs where table_name = '$objec t_name' and owner = '$owner' and grantee = '$grantee' order by privilege";
$cursor1 = $dbh->prepare($sql1); $cursor1 = $dbh->prepare($sql1);
$cursor1->execute; $cursor1->execute;
$privs = ""; $privs = "";
while ($priv = $cursor1->fetchrow_array) { while ($priv = $cursor1->fetchrow_array) {
$privs = $privs . ",$priv"; $privs = $privs . ",$priv";
} }
$cursor1->finish; $cursor1->finish;
$privs =~ s/^,//; $privs =~ s/^,//;
logit(" Privs for $grantee: $privs"); logit(" Privs for $grantee: $privs");
print "<td class=\"left\">$privs</td></tr>"; print "<td class=\"left\">$privs</td></tr>";
skipping to change at line 6108 skipping to change at line 6227
'91','Create function', '91','Create function',
'92','Alter function', '92','Alter function',
'93','Drop function', '93','Drop function',
'94','Create package', '94','Create package',
'95','Alter package', '95','Alter package',
'96','Drop package', '96','Drop package',
'97','Create package body', '97','Create package body',
'98','Alter package body', '98','Alter package body',
'99','Drop package body') \"Command type\", '99','Drop package body') \"Command type\",
TO_CHAR(EXECUTIONS,'999,999,999,999') \"Executions\", TO_CHAR(EXECUTIONS,'999,999,999,999') \"Executions\",
to_char(last_load_time,'MM/DD/YYYY HH24:MI:SS') \"Last load time\",
to_char(last_active_time,'MM/DD/YYYY HH24:MI:SS') \"Last active time\",
TO_CHAR(ROWS_PROCESSED,'999,999,999,999') \"Rows processed\", TO_CHAR(ROWS_PROCESSED,'999,999,999,999') \"Rows processed\",
TO_CHAR(DISK_READS,'999,999,999,999') \"Disk reads\", TO_CHAR(DISK_READS,'999,999,999,999') \"Disk reads\",
TO_CHAR(SORTS,'999,999,999,999') \"Sorts\", TO_CHAR(SORTS,'999,999,999,999') \"Sorts\",
OPTIMIZER_MODE \"Optimizer mode\", OPTIMIZER_MODE \"Optimizer mode\",
SQL_TEXT \"SQL text\" SQL_FULLTEXT \"SQL text\"
FROM V\$SQLAREA FROM V\$SQLAREA
WHERE PARSING_SCHEMA_ID = WHERE PARSING_SCHEMA_ID =
(SELECT USER_ID FROM DBA_USERS (SELECT USER_ID FROM DBA_USERS
WHERE USERNAME = '$object_name') WHERE USERNAME = '$object_name')
ORDER BY 3 DESC ORDER BY 5 DESC
"; ";
logit($sql); logit($sql);
$text = " SQL for user $object_name in the shared SQL area."; $text = " SQL for user $object_name in the shared SQL area.";
$link = "$scriptname?database=$database&object_type=SQLINFO"; $link = "$scriptname?database=$database&object_type=SQLINFO";
$infotext = "There are no entries in the shared SQL area for user $object_nam e"; $infotext = "There are no entries in the shared SQL area for user $object_nam e";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine sqlAreaListByUser"); logit("Exit subroutine sqlAreaListByUser");
skipping to change at line 6183 skipping to change at line 6304
<input type=PASSWORD NAME=explainpassword SIZE=20> <input type=PASSWORD NAME=explainpassword SIZE=20>
</td> </td>
</tr> </tr>
</table> </table>
<BR> <BR>
<input type="SUBMIT" NAME="foobar" VALUE="Run explain plan"> <input type="SUBMIT" NAME="foobar" VALUE="Run explain plan">
</FORM> </FORM>
EOF EOF
} }
Footer();
logit("Exit subroutine enterExplainPlan"); logit("Exit subroutine enterExplainPlan");
exit; exit;
} }
sub ASMinfo { sub ASMinfo {
logit("Enter subroutine ASMinfo"); logit("Enter subroutine ASMinfo");
my ($sql,$text,$link,$infotext); my ($sql,$text,$link,$infotext);
skipping to change at line 6691 skipping to change at line 6814
$cursor->finish; $cursor->finish;
print "</table>\n"; print "</table>\n";
} }
sub showDBfiles { sub showDBfiles {
logit("Enter subroutine showDBfiles"); logit("Enter subroutine showDBfiles");
my ($sql,$cursor,$text,$link,$infotext); my ($sql,$cursor,$text,$link,$infotext);
my ($dfspace,$tfspace,$totalspace); my ($dfspace,$tfspace,$totalspace,$fra_space);
my ($redo_log_space,$standby_log_space,$controlfile_space);
my ($dfspace_gb,$tfspace_gb,$totalspace_gb,$fra_space_gb,$redo_log_space_gb,$
standby_log_space_gb,$controlfile_space_gb);
print " <table class=\"noborder\">\n"; print " <table class=\"noborder\">\n";
print " <tr>\n"; print " <tr>\n";
print " <td>\n"; print " <td>\n";
Button("$scriptname?database=$database&object_type=FILEGRAPH TARGET=body","Fi le I/O graph","$headingcolor"); Button("$scriptname?database=$database&object_type=FILEGRAPH TARGET=body","Fi le I/O graph","$headingcolor");
print " </td>\n"; print " </td>\n";
print " <td style=\"width:10px;\"></td>\n"; print " <td style=\"width:10px;\"></td>\n";
print " <td>\n"; print " <td>\n";
Button("$scriptname?database=$database&object_type=CONTROLFILES TARGET=body", "Controlfile info","$headingcolor"); Button("$scriptname?database=$database&object_type=CONTROLFILES TARGET=body", "Controlfile info","$headingcolor");
print " </td>\n"; print " </td>\n";
skipping to change at line 6727 skipping to change at line 6852
#FROM DBA_DATA_FILES A, #FROM DBA_DATA_FILES A,
# V\$RECOVER_FILE B # V\$RECOVER_FILE B
#WHERE A.FILE_ID = B.FILE# #WHERE A.FILE_ID = B.FILE#
#"; #";
# $text = "Datafiles need recovery!!"; # $text = "Datafiles need recovery!!";
# $link = "$scriptname?database=$database&object_type=DATAFILE"; # $link = "$scriptname?database=$database&object_type=DATAFILE";
# $infotext = "No datafiles are needing media recovery :-)"; # $infotext = "No datafiles are needing media recovery :-)";
# DisplayTable($sql,$text,$link,$infotext); # DisplayTable($sql,$text,$link,$infotext);
# #
$sql = "
Select
sum(bytes)
from v\$log
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$redo_log_space = $cursor->fetchrow_array();
$cursor->finish;
$sql = "
Select
nvl(sum(bytes),0)
from v\$standby_log
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$standby_log_space = $cursor->fetchrow_array();
$cursor->finish;
$sql = "
Select
block_size*file_size_blks
from v\$controlfile
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$controlfile_space = $cursor->fetchrow_array();
$cursor->finish;
$sql = "$copyright $sql = "$copyright
Select Select
sum(bytes) sum(bytes)
from dba_data_files from dba_data_files
"; ";
$cursor = $dbh->prepare($sql); $cursor = $dbh->prepare($sql);
$cursor->execute; $cursor->execute;
$dfspace = $cursor->fetchrow_array(); $dfspace = $cursor->fetchrow_array();
$cursor->finish; $cursor->finish;
$sql = "$copyright $sql = "$copyright
Select Select
sum(bytes) nvl(sum(bytes),0)
from dba_temp_files from dba_temp_files
"; ";
$cursor = $dbh->prepare($sql); $cursor = $dbh->prepare($sql);
$cursor->execute; $cursor->execute;
$tfspace = $cursor->fetchrow_array(); $tfspace = $cursor->fetchrow_array();
$cursor->finish; $cursor->finish;
$totalspace = commify($dfspace+$tfspace); $sql = "
Select
nvl(space_used,0)
from v\$recovery_file_dest
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$fra_space = $cursor->fetchrow_array();
$cursor->finish;
$totalspace_gb = int(($dfspace+$tfspace+$redo_log_space+$standby_log_space+$c
ontrolfile_space+$fra_space)/1000000000);
$totalspace = commify($dfspace+$tfspace+$redo_log_space+$standby_log_space+$c
ontrolfile_space+$fra_space);
$dfspace_gb = int($dfspace/1000000000);
$dfspace = commify($dfspace); $dfspace = commify($dfspace);
$tfspace_gb = int($tfspace/1000000000);
$tfspace = commify($tfspace); $tfspace = commify($tfspace);
$redo_log_space_gb = int($redo_log_space/1000000000);
$redo_log_space = commify($redo_log_space);
$standby_log_space_gb = int($standby_log_space/1000000000);
$standby_log_space = commify($standby_log_space);
$controlfile_space_gb = int($controlfile_space/1000000000);
$controlfile_space = commify($controlfile_space);
$fra_space_gb = int($fra_space/1000000000);
$fra_space = commify($fra_space);
$sql = " $sql = "
Select Select
'$totalspace' \"Total space allocation\", '$totalspace ($totalspace_gb Gb)' \"Total space allocation\
'$dfspace' \"Datafile allocation\", ",
'$tfspace' \"Tempfile allocation\" '$dfspace ($dfspace_gb Gb)' \"Datafiles\",
'$tfspace ($tfspace_gb Gb)' \"Tempfiles\",
'$redo_log_space ($redo_log_space_gb Gb)' \"Redo logs\",
'$standby_log_space ($standby_log_space_gb Gb)' \"Standby logs\",
'$controlfile_space ($controlfile_space_gb GB)' \"Control files\",
'$fra_space ($fra_space_gb Gb)' \"Recovery Area\"
from dual from dual
"; ";
$text = "Space allocation summary"; $text = "Space allocation summary";
$link = ""; $link = "";
DisplayTable($sql,$text,$link); DisplayTable($sql,$text,$link);
dbFileList(); dbFileList();
logit("Exit subroutine showDBfiles"); logit("Exit subroutine showDBfiles");
skipping to change at line 6781 skipping to change at line 6972
sub dbFileList { sub dbFileList {
# General datafile information, all datafiles # General datafile information, all datafiles
my ($sql,$text,$link,$infotext); my ($sql,$text,$link,$infotext);
$dbh->do("Alter session set optimizer_mode=RULE"); $dbh->do("Alter session set optimizer_mode=RULE");
$sql = "$copyright $sql = "$copyright
SELECT SELECT
NAME \"Name\",
DECODE (STATUS,'','OK') \"Status\"
FROM V\$CONTROLFILE
";
$text = "Controlfile information";
$link = "";
$infotext = "There is something terribly wrong...";
DisplayTable($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
A.FILE_NAME \"File name\", A.FILE_NAME \"File name\",
TO_CHAR(A.BYTES,'999,999,999,999,999') \"Bytes\", TO_CHAR(A.BYTES,'999,999,999,999,999') \"Bytes\",
A.TABLESPACE_NAME \"Tablespace name\", A.TABLESPACE_NAME \"Tablespace name\",
TO_CHAR(B.PHYBLKRD,'999,999,999,999,999') \"Physical block reads\", TO_CHAR(B.PHYBLKRD,'999,999,999,999,999') \"Physical block reads\",
TO_CHAR(B.PHYBLKWRT,'999,999,999,999,999') \"Physical block writes\", TO_CHAR(B.PHYBLKWRT,'999,999,999,999,999') \"Physical block writes\",
A.STATUS \"Status\", A.STATUS \"Status\",
DECODE(A.AUTOEXTENSIBLE, DECODE(A.AUTOEXTENSIBLE,
'YES','Yes', 'YES','Yes',
'NO','No') \"Xtend?\", 'NO','No') \"Xtend?\",
TO_CHAR(A.MAXBYTES,'999,999,999,999,999') \"Max bytes\", TO_CHAR(A.MAXBYTES,'999,999,999,999,999') \"Max bytes\",
skipping to change at line 6816 skipping to change at line 7019
TO_CHAR(A.INCREMENT_BY*$db_block_size,'999,999,999,999,999') \"Increme nt\" TO_CHAR(A.INCREMENT_BY*$db_block_size,'999,999,999,999,999') \"Increme nt\"
FROM DBA_TEMP_FILES A, V\$TEMPSTAT B FROM DBA_TEMP_FILES A, V\$TEMPSTAT B
WHERE A.FILE_ID = B.FILE# WHERE A.FILE_ID = B.FILE#
ORDER BY \"File name\", \"Tablespace name\" ORDER BY \"File name\", \"Tablespace name\"
"; ";
$text = "Datafile information" unless $norefreshbutton; $text = "Datafile information" unless $norefreshbutton;
$link = "$scriptname?database=$database&object_type=DATAFILE"; $link = "$scriptname?database=$database&object_type=DATAFILE";
DisplayTable($sql,$text,$link); DisplayTable($sql,$text,$link);
# Online redo log information
$sql = "$copyright
SELECT
A.MEMBER \"Member\",
A.TYPE \"Type\",
B.GROUP# \"Group#\",
B.THREAD# \"Thread#\",
B.SEQUENCE# \"Sequence#\",
TO_CHAR(B.BYTES,'999,999,999,999') \"Bytes\",
B.MEMBERS \"Members\",
B.ARCHIVED \"Archived\",
B.STATUS \"Status\"
FROM V\$LOGFILE A, V\$LOG B
WHERE A.GROUP# = B.GROUP#
AND A.TYPE='ONLINE'
ORDER BY 5 DESC
";
$text = "Online redo log info";
$link = "";
DisplayTable($sql,$text,$link);
# Standby redo log information
$sql = "$copyright
SELECT
A.MEMBER \"Member\",
A.TYPE \"Type\",
B.GROUP# \"Group#\",
B.THREAD# \"Thread#\",
B.SEQUENCE# \"Sequence#\",
TO_CHAR(B.BYTES,'999,999,999,999') \"Bytes\",
B.ARCHIVED \"Archived\",
B.STATUS \"Status\"
FROM V\$LOGFILE A, V\$STANDBY_LOG B
WHERE A.GROUP# = B.GROUP#
AND A.TYPE='STANDBY'
ORDER BY 5 DESC
";
$text = "Standby redo log info";
$link = "";
DisplayTable($sql,$text,$link);
} }
sub EnterPasswd { sub EnterPasswd {
logit("Enter subroutine EnterPasswd"); logit("Enter subroutine EnterPasswd");
# Usage: EnterPasswd($database); # Usage: EnterPasswd($database);
logit("Object type: $object_type");
if ($object_type ne "MENU") { if ($object_type ne "MENU") {
my $database = shift; my $database = shift;
my ($title,$heading); my ($title,$heading);
$title = "Add database $database"; $title = "Add database $database";
$heading = "Please enter a username and password for database $database.<B R>This user needs to have CREATE SESSION and either SELECT ANY TABLE privileges, or privileges to see all of the neccessary data dictionary tables."; $heading = "Please enter a username and password for database $database.<B R>This user needs to have CREATE SESSION and either SELECT ANY TABLE privileges, or privileges to see all of the neccessary data dictionary tables.";
Header($title,$heading,$font,$fontsize,$fontcolor,$bgcolor); Header($title,$heading,$font,$fontsize,$fontcolor,$bgcolor);
skipping to change at line 6865 skipping to change at line 7115
<input type="HIDDEN" NAME="database" VALUE="$database"> <input type="HIDDEN" NAME="database" VALUE="$database">
&nbsp;&nbsp;&nbsp;<input type="SUBMIT" VALUE="Submit"> &nbsp;&nbsp;&nbsp;<input type="SUBMIT" VALUE="Submit">
</FORM> </FORM>
</td> </td>
</TR> </TR>
</TABLE> </TABLE>
</td> </td>
</TR> </TR>
</TABLE> </TABLE>
EOF EOF
logit("Exit subroutine EnterPasswd"); logit("Exit subroutine EnterPasswd");
exit; Footer();
} else { } else {
Header($title,$heading,$font,$fontsize,$fontcolor,$bgcolor); Header($title,$heading,$font,$fontsize,$fontcolor,$bgcolor);
} }
logit("Exit subroutine EnterPasswd"); logit("Exit subroutine EnterPasswd");
} }
sub GetPasswd { sub GetPasswd {
logit("Enter subroutine GetPasswd"); logit("Enter subroutine GetPasswd");
skipping to change at line 7925 skipping to change at line 8175
"; ";
$text = "Registry history"; $text = "Registry history";
$link = ""; $link = "";
DisplayTable($sql,$text,$link); DisplayTable($sql,$text,$link);
# Did we start with a pfile or spfile? # Did we start with a pfile or spfile?
$sql = " $sql = "
Select Select
decode(value, NULL, 'PFILE', 'SPFILE') \"Instance started with\" value \"Parameter file\"
from v\$parameter WHERE name = 'spfile' from v\$parameter WHERE name = 'spfile'
"; ";
$text = ""; $text = "";
$link = ""; $link = "";
DisplayTable($sql,$text); DisplayTable($sql,$text);
# Non-default instance parameters # Non-default instance parameters
$sql = "$copyright $sql = "$copyright
skipping to change at line 8406 skipping to change at line 8656
DisplayTable($sql,$text,$link); DisplayTable($sql,$text,$link);
logit("Exit subroutine showRollbacks"); logit("Exit subroutine showRollbacks");
} }
sub showContention { sub showContention {
logit("Enter subroutine showContention"); logit("Enter subroutine showContention");
my ($sql,$text,$link,$infotext,$count); my ($sql,$text,$link,$infotext,$count,$holding_serial,$waiting_serial,$sql2,$ cursor2);
my ($holding_username,$object_id,$object_name,$holding_sid,$waiting_sid,$lock _type,$mode_held,$mode_requested); my ($holding_username,$object_id,$object_name,$holding_sid,$waiting_sid,$lock _type,$mode_held,$mode_requested);
refreshButton(); refreshButton();
# Locking contention information # Locking contention information
$sql = "$copyright $sql = "$copyright
Select Select
vlo.oracle_username, vlo.oracle_username,
vlo.object_id, vlo.object_id,
skipping to change at line 8458 skipping to change at line 8708
<th class="center">Lock Type</th> <th class="center">Lock Type</th>
<th class="center">Mode Held</th> <th class="center">Mode Held</th>
<th class="center">Mode Requested</th> <th class="center">Mode Requested</th>
EOF EOF
$cursor = $dbh->prepare($sql); $cursor = $dbh->prepare($sql);
$cursor->execute; $cursor->execute;
while (($holding_username,$object_id,$object_name,$holding_sid,$waiting_sid,$ lock_type,$mode_held,$mode_requested) = $cursor->fetchrow_array) { while (($holding_username,$object_id,$object_name,$holding_sid,$waiting_sid,$ lock_type,$mode_held,$mode_requested) = $cursor->fetchrow_array) {
$count++; $count++;
$sql2 = "Select serial# from v\$session where sid=$holding_sid";
$cursor2 = $dbh->prepare($sql2);
$cursor2->execute;
$holding_serial = $cursor2->fetchrow_array;
$cursor2->finish;
$sql2 = "Select serial# from v\$session where sid=$waiting_sid";
$cursor2 = $dbh->prepare($sql2);
$cursor2->execute;
$waiting_serial = $cursor2->fetchrow_array;
$cursor2->finish;
#$object_name = "$owner.$object_name"; #$object_name = "$owner.$object_name";
print <<"EOF"; print <<"EOF";
<tr> <tr>
<td class="center"><a href=$scriptname?database=$database&object_type= SESSIONINFO&sid=$holding_sid&page=general>$holding_sid</a></td> <td class="center"><a href=$scriptname?database=$database&object_type= SESSIONINFO&sid=$holding_sid&serial=$holding_serial&page=general>$holding_sid</a ></td>
<td class="center">$holding_username</td> <td class="center">$holding_username</td>
<td class="center"><a href=$scriptname?database=$database&object_type= SESSIONINFO&sid=$waiting_sid&page=general>$waiting_sid</a></td> <td class="center"><a href=$scriptname?database=$database&object_type= SESSIONINFO&sid=$waiting_sid&serial=$waiting_serial&page=general>$waiting_sid</a ></td>
<td class="left">$object_id</td> <td class="left">$object_id</td>
<td class="left">$object_name</td> <td class="left">$object_name</td>
<td class="center">$lock_type</td> <td class="center">$lock_type</td>
<td class="center">$mode_held</td> <td class="center">$mode_held</td>
<td class="center">$mode_requested</td> <td class="center">$mode_requested</td>
</tr> </tr>
EOF EOF
} }
$cursor->finish; $cursor->finish;
print <<"EOF"; print <<"EOF";
skipping to change at line 8996 skipping to change at line 9259
$cursor->execute; $cursor->execute;
$value = $cursor->fetchrow_array; $value = $cursor->fetchrow_array;
$cursor->finish; $cursor->finish;
logit(" Value returned for FLASHBACK_ON: $value"); logit(" Value returned for FLASHBACK_ON: $value");
if ( $value eq "YES" ) { if ( $value eq "YES" ) {
message("Flashback database is enabled for this database"); message("Flashback database is enabled for this database");
} else { } else {
message("Flashback database is NOT enabled for this database"); message("Flashback database is NOT enabled for this database");
} }
Button("$scriptname?object_type=FLASHBACKINFO&database=$database","FRA / Flas hback information","$headingcolor","CENTER","200"); Button("$scriptname?object_type=FLASHBACKINFO&database=$database","Recovery a rea / Flashback info","$headingcolor","CENTER","200");
$sql = "$copyright $sql = "$copyright
Select Select
log_mode log_mode
from v\$database from v\$database
"; ";
$cursor=$dbh->prepare($sql); $cursor=$dbh->prepare($sql);
$cursor->execute; $cursor->execute;
$value = $cursor->fetchrow_array; $value = $cursor->fetchrow_array;
$cursor->finish; $cursor->finish;
if ( $value eq "ARCHIVELOG" || $value eq "STARTED" ) { if ( $value eq "ARCHIVELOG" || $value eq "STARTED" ) {
Button("$scriptname?object_type=ARCHIVING&database=$database","Archived lo g history","$headingcolor","CENTER","200"); Button("$scriptname?object_type=ARCHIVING&database=$database","Archived lo g history","$headingcolor","CENTER","200");
Button("$scriptname?object_type=ARCHIVELOGSPACE&database=$database","Archi ved log space report","$headingcolor","CENTER","200"); Button("$scriptname?object_type=ARCHIVELOGSPACE&database=$database","Archi ved log space report","$headingcolor","CENTER","200");
} else { } else {
message("Database archiving is not enabled."); message("Database archiving is not enabled.");
} }
$sql = "$copyright
SELECT
TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') \"Date\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') \"00\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') \"01\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') \"02\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') \"03\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') \"04\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') \"05\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') \"06\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') \"07\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') \"08\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') \"09\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') \"10\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') \"11\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') \"12\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') \"13\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') \"14\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') \"15\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') \"16\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') \"17\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') \"18\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') \"19\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') \"20\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') \"21\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') \"22\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') \"23\"
FROM V\$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
";
$text = "Graph of log switch history by day and hour";
$link = "";
DisplayTable($sql,$text,$link);
# Online redo log information # Online redo log information
$sql = "$copyright $sql = "$copyright
SELECT SELECT
A.MEMBER \"Member\", A.MEMBER \"Member\",
A.TYPE \"Type\", A.TYPE \"Type\",
B.GROUP# \"Group#\", B.GROUP# \"Group#\",
B.THREAD# \"Thread#\", B.THREAD# \"Thread#\",
B.SEQUENCE# \"Sequence#\", B.SEQUENCE# \"Sequence#\",
TO_CHAR(B.BYTES,'999,999,999,999') \"Bytes\", TO_CHAR(B.BYTES,'999,999,999,999') \"Bytes\",
skipping to change at line 9061 skipping to change at line 9360
FROM V\$LOGFILE A, V\$STANDBY_LOG B FROM V\$LOGFILE A, V\$STANDBY_LOG B
WHERE A.GROUP# = B.GROUP# WHERE A.GROUP# = B.GROUP#
AND A.TYPE='STANDBY' AND A.TYPE='STANDBY'
ORDER BY 5 DESC ORDER BY 5 DESC
"; ";
$text = "Standby redo log info"; $text = "Standby redo log info";
$link = ""; $link = "";
DisplayTable($sql,$text,$link); DisplayTable($sql,$text,$link);
$sql = "$copyright logit("Exit subroutine showRedo");
SELECT
TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') \"Date\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') \"00\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') \"01\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') \"02\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') \"03\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') \"04\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') \"05\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') \"06\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') \"07\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') \"08\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') \"09\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') \"10\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') \"11\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') \"12\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') \"13\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') \"14\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') \"15\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') \"16\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') \"17\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') \"18\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') \"19\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') \"20\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') \"21\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') \"22\",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') \"23\"
FROM V\$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
";
$text = "Graph of log switch history by day and hour"; }
$link = "";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showRedo"); sub featureUsageReport {
logit("Enter subroutine featureUsageReport");
my ($sql,$text,$link,$infotext);
$sql = "
Select
name \"Name\",
to_char(detected_usages,'999,999,999,999,999') \"Detected#\",
currently_used \"Currently used?\",
first_usage_date \"First use\",
last_usage_date \"Last use\"
from dba_feature_usage_statistics
where version = (select max(version) from dba_feature_usage_statistics)
and last_usage_date is not null
order by last_usage_date desc";
$text = "Database feature usage for the current database version";
$link = "";
$infotext = "";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine featureUsageReport");
} }
sub tempSegmentReport { sub tempSegmentReport {
logit("Enter subroutine tempSegmentReport"); logit("Enter subroutine tempSegmentReport");
my ($sql,$text,$link,$infotext); my ($sql,$text,$link,$infotext);
refreshButton(); refreshButton();
skipping to change at line 9316 skipping to change at line 9605
print "<input type=HIDDEN NAME=user VALUE=$username>\n" if $username; print "<input type=HIDDEN NAME=user VALUE=$username>\n" if $username;
print "<input type=HIDDEN NAME=listing VALUE=$listing>\n" if $listing; print "<input type=HIDDEN NAME=listing VALUE=$listing>\n" if $listing;
print "<input type=HIDDEN NAME=schemaname VALUE=$schemaname>\n" if $schemana me; print "<input type=HIDDEN NAME=schemaname VALUE=$schemaname>\n" if $schemana me;
print "<input type=SUBMIT NAME=sessions VALUE=Refresh>\n"; print "<input type=SUBMIT NAME=sessions VALUE=Refresh>\n";
print "</FORM>\n"; print "</FORM>\n";
logit("Exit subroutine refreshButton"); logit("Exit subroutine refreshButton");
} }
sub developerSessions {
#
# This is to display sessions pertaining to client tools such as TOAD and SQL
-Developer
#
logit("Enter subroutine developerSessions");
my ($sql,$cursor,$sid,$serial,$username,$cpu,$command,$osuser,$status,$sproce
ss,$cprocess,$machine,$program);
my ($sortfield,$refreshrate,$highlight,$color,$blockchanges,$sqltext,$last_ca
ll_et,$logontime,$foo);
my ($minutes,$seconds,$paddr,$sql1,$cursor1,$seconds_in_wait,$newsqltext,$sql
_id,$sql_child_number);
my ($altprocess,$moresql,$redlight,$lockwait,$orig_cellcolor,$schemaname);
my ($text,$link,$infotext);
refreshButton();
$sql = "$copyright
Select
vs.sid \"SID\",
vs.serial# \"Serial#\",
vs.username \"Ora user\",
vs.osuser \"OS user\",
vs.process \"Client process\",
vp.spid \"Server process\",
vs.status \"Status\",
to_char(vs.logon_time,'Day MM/DD/YY HH24:MI:SS') \"Logon time\",
nvl(vs.machine,'Unknown') \"Machine\",
vs.program \"Program\",
to_char(trunc(sysdate) + NUMTODSINTERVAL (vs.last_call_et, 'second'), 'hh24:m
i:ss') \"Time since last call\",
decode(vs.command,
'0','None',
'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') \"Command\"
FROM V\$SESSION VS,
V\$SESS_IO VSI,
V\$SESSTAT VSS,
V\$SESSION_WAIT VSW,
V\$PROCESS VP
WHERE VS.SID = VSI.SID
AND VS.SID = VSW.SID
AND VS.SID = VSS.SID
AND VSS.STATISTIC# = 12
AND VS.PADDR = VP.ADDR
AND VS.USERNAME IS NOT NULL
and vs.program = 'SQL Developer'";
#$scriptname?database=$database&object_type=SESSIONINFO&user=$username&sid=$sid&
serial=$serial>$sid</A></td>\n";
$text = "Developer tool sessions";
$link = "";
$infotext = "No developer tool sessions found";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine developerSessions");
}
sub loginAuditRecords {
logit("Enter subroutine loginAuditRecords");
my ($sql,$text,$link,$infotext);
my $schemaname = $query->param('schemaname');
$sql = "$copyright
Select
timestamp,
comment_text,
os_user,
db_user,
userhost,
terminal
from (Select
to_char(extended_timestamp,'MM/DD/YYYY HH24:MI:SS') timestamp,
comment_text ,
os_user,
db_user,
userhost,
terminal
from dba_common_audit_trail
where db_user='$schemaname'
and comment_text is not null
order by 1 desc) where rownum < 26
";
$link = "";
$text = "Most recent logins (from audit trail)";
$infotext = "No login information found in the audit log";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine loginAuditRecords");
}
sub topSessions { sub topSessions {
logit("Enter subroutine topSessions"); logit("Enter subroutine topSessions");
my ($sql,$cursor,$sid,$serial,$username,$cpu,$command,$osuser,$status,$sproce ss,$cprocess,$machine,$program); my ($sql,$cursor,$sid,$serial,$username,$cpu,$command,$osuser,$status,$sproce ss,$cprocess,$machine,$program);
my ($sortfield,$refreshrate,$highlight,$color,$blockchanges,$sqltext,$last_ca ll_et,$logontime,$foo); my ($sortfield,$refreshrate,$highlight,$color,$blockchanges,$sqltext,$last_ca ll_et,$logontime,$foo);
my ($minutes,$seconds,$paddr,$sql1,$cursor1,$seconds_in_wait,$newsqltext,$sql _id,$sql_child_number); my ($minutes,$seconds,$paddr,$sql1,$cursor1,$seconds_in_wait,$newsqltext,$sql _id,$sql_child_number);
my ($altprocess,$moresql,$redlight,$lockwait,$orig_cellcolor,$schemaname); my ($altprocess,$moresql,$redlight,$lockwait,$orig_cellcolor,$schemaname);
$sortfield = $query->param('sortfield') || "status"; $sortfield = $query->param('sortfield') || "status";
skipping to change at line 9708 skipping to change at line 10179
sub sqlInfo { sub sqlInfo {
# Explain plan / statistics etc. # Explain plan / statistics etc.
logit("Enter subroutine sqlInfo"); logit("Enter subroutine sqlInfo");
my $sql_id = $query->param('arg'); my $sql_id = $query->param('arg');
my ($sql,$text,$link,$infotext); my ($sql,$text,$link,$infotext);
my ($cursor,$line,$explain); my ($cursor,$line,$explain,$sql_text);
logit(" Collecting info for SQL ID $sql_id"); logit(" Collecting info for SQL ID $sql_id");
$sql = "Alter session set statistics_level = 'ALL'"; $sql = "Alter session set statistics_level = 'ALL'";
$dbh->do($sql); $dbh->do($sql);
$dbh->{LongReadLen} = 10485760;
$dbh->{LongTruncOk} = 0;
my $size = $fontsize + 1; my $size = $fontsize + 1;
$sql = " $sql = "
Select Select
to_char(executions,'999,999,999,999,999') \"Executions\" to_char(executions,'999,999,999,999,999') \"Executions\",
to_char(last_load_time,'MM/DD/YYYY HH24:MI:SS') \
"Last load time\",
to_char(last_active_time,'MM/DD/YYYY HH24:MI:SS') \
"Last active time\"
from v\$sqlarea where sql_id = '$sql_id'"; from v\$sqlarea where sql_id = '$sql_id'";
$text = "Number of executions"; $text = "Number of executions";
$infotext = ""; $infotext = "";
$link = ""; $link = "";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
$sql = " $sql = "
Select name \"Name\", Select name \"Name\",
value_string \"Value\" value_string \"Value\"
skipping to change at line 9760 skipping to change at line 10236
} }
$cursor->finish; $cursor->finish;
print <<"EOF"; print <<"EOF";
</pre> </pre>
</td> </td>
</tr> </tr>
</table> </table>
EOF EOF
#$sql = "
#Select
# sql_fulltext
#from v\$sqlarea
# where sql_id = '$object_name'
#";
#
# $text = "Full SQL text";
# $infotext = "";
# $link = "";
#
# DisplayTable($sql,$text,$link,$infotext);
#
# $sql = "
#Select
# sql_fulltext
#from v\$sqlarea
# where sql_id = '$object_name'
#";
#
# $cursor = $dbh->prepare($sql);
# $cursor->execute;
# $sql_text = $cursor->fetchrow_array;
# $cursor->finish;
# print $sql_text;
logit("Exit subroutine sqlInfo"); logit("Exit subroutine sqlInfo");
} }
sub sessionInfo { sub sessionInfo {
logit("Enter subroutine sessionInfo"); logit("Enter subroutine sessionInfo");
logit(" Username connected to database is $username"); logit(" Username connected to database is $username");
my $sid = $query->param('sid') || ""; my $sid = $query->param('sid') || "";
skipping to change at line 9782 skipping to change at line 10284
my $click = $query->param('click') || ""; my $click = $query->param('click') || "";
my $page = $query->param('page') || "general" ; my $page = $query->param('page') || "general" ;
my $refreshrate = 5; my $refreshrate = 5;
logit("SID : $sid"); logit("SID : $sid");
logit("Serial : $serial"); logit("Serial : $serial");
logit("Script : $scriptname"); logit("Script : $scriptname");
my ($sql,$cursor,$hours,$minutes,$seconds,$text,$link,$infotext,$sqltext,$pie ce,$count,$filename,$object_name,$owner,$object_string); my ($sql,$cursor,$hours,$minutes,$seconds,$text,$link,$infotext,$sqltext,$pie ce,$count,$filename,$object_name,$owner,$object_string);
my ($blocking_session_string,$current_sql_string,$prev_sql_string,$sql_trace_ string,$killstring,$width,$temp_seg_string,$tablespace); my ($blocking_session_string,$current_sql_string,$prev_sql_string,$sql_trace_ string,$killstring,$width,$temp_seg_string,$tablespace);
my ($blocks); my ($blocks,$blocking_serial,$io_blocks);
my ($saddr, $paddr, $user, $command, $ownerid, $taddr, $lockwait, $status, $s erver, $schema, $schemaname, $osuser, $cprocess, $sprocess, $machine, $terminal, $program, $type, $sql_address, $sql_hash_value, $sql_id, $sql_child_number, $pr ev_sql_addr, $prev_hash_value, $prev_sql_id, $prev_child_number, $module, $actio n, $client_info, $row_wait_obj, $row_wait_file, $row_wait_block, $row_wait_row, $logon_time, $last_call_et, $resource_consumer_group, $client_identifier, $block ing_session_status, $blocking_instance, $blocking_session, $event, $p1text, $p1, $p1raw, $p2text, $p2, $p2raw, $p3text, $p3, $p3raw, $wait_class_id, $wait_class , $wait_time, $seconds_in_wait, $state, $service_name, $sql_trace, $sql_trace_wa its, $sql_trace_binds); my ($saddr, $paddr, $user, $command, $ownerid, $taddr, $lockwait, $status, $s erver, $schema, $schemaname, $osuser, $cprocess, $sprocess, $machine, $terminal, $program, $type, $sql_address, $sql_hash_value, $sql_id, $sql_child_number, $pr ev_sql_addr, $prev_hash_value, $prev_sql_id, $prev_child_number, $module, $actio n, $client_info, $row_wait_obj, $row_wait_file, $row_wait_block, $row_wait_row, $logon_time, $last_call_et, $resource_consumer_group, $client_identifier, $block ing_session_status, $blocking_instance, $blocking_session, $event, $p1text, $p1, $p1raw, $p2text, $p2, $p2raw, $p3text, $p3, $p3raw, $wait_class_id, $wait_class , $wait_time, $seconds_in_wait, $state, $service_name, $sql_trace, $sql_trace_wa its, $sql_trace_binds);
# print <<"EOF"; # print <<"EOF";
# <FORM METHOD="POST" ACTION="$scriptname"> # <FORM METHOD="POST" ACTION="$scriptname">
# #
# <input type=HIDDEN NAME=database VALUE=$database> # <input type=HIDDEN NAME=database VALUE=$database>
# <input type=HIDDEN NAME=object_type VALUE=$object_type> # <input type=HIDDEN NAME=object_type VALUE=$object_type>
# <input type=HIDDEN NAME=arg VALUE=$object_name> # <input type=HIDDEN NAME=arg VALUE=$object_name>
# <input type=HIDDEN NAME=sid VALUE=$sid> # <input type=HIDDEN NAME=sid VALUE=$sid>
skipping to change at line 9894 skipping to change at line 10396
$cursor->execute; $cursor->execute;
($saddr, $serial, $paddr, $user, $username, $command, $ownerid, $taddr, $l ockwait, $status, $server, $schema, $schemaname, $osuser, $cprocess, $machine, $ terminal, $program, $type, $sql_address, $sql_hash_value, $sql_id, $sql_child_nu mber, $prev_sql_addr, $prev_hash_value, $prev_sql_id, $prev_child_number, $modul e, $action, $client_info, $row_wait_obj, $row_wait_file, $row_wait_block, $row_w ait_row, $logon_time, $last_call_et, $resource_consumer_group, $client_identifie r, $blocking_session_status, $blocking_instance, $blocking_session, $event, $p1t ext, $p1, $p1raw, $p2text, $p2, $p2raw, $p3text, $p3, $p3raw, $wait_class_id, $w ait_class, $wait_time, $seconds_in_wait, $state, $service_name, $sql_trace, $sql _trace_waits, $sql_trace_binds) = $cursor->fetchrow_array; ($saddr, $serial, $paddr, $user, $username, $command, $ownerid, $taddr, $l ockwait, $status, $server, $schema, $schemaname, $osuser, $cprocess, $machine, $ terminal, $program, $type, $sql_address, $sql_hash_value, $sql_id, $sql_child_nu mber, $prev_sql_addr, $prev_hash_value, $prev_sql_id, $prev_child_number, $modul e, $action, $client_info, $row_wait_obj, $row_wait_file, $row_wait_block, $row_w ait_row, $logon_time, $last_call_et, $resource_consumer_group, $client_identifie r, $blocking_session_status, $blocking_instance, $blocking_session, $event, $p1t ext, $p1, $p1raw, $p2text, $p2, $p2raw, $p3text, $p3, $p3raw, $wait_class_id, $w ait_class, $wait_time, $seconds_in_wait, $state, $service_name, $sql_trace, $sql _trace_waits, $sql_trace_binds) = $cursor->fetchrow_array;
$cursor->finish; $cursor->finish;
$sql = "Select spid from v\$process where addr = '$paddr'"; $sql = "Select spid from v\$process where addr = '$paddr'";
$cursor = $dbh->prepare($sql) || print "$DBI::errstr"; $cursor = $dbh->prepare($sql) || print "$DBI::errstr";
$cursor->execute; $cursor->execute;
$sprocess = $cursor->fetchrow_array; $sprocess = $cursor->fetchrow_array;
$cursor->finish; $cursor->finish;
# I/O
$sql = "Select block_changes from v\$sess_io where sid = '$sid'";
$cursor = $dbh->prepare($sql) || print "$DBI::errstr";
$cursor->execute;
$io_blocks = $cursor->fetchrow_array;
$cursor->finish;
my $io_mb = commify(int(($io_blocks*$db_block_size)/1048576));
$io_blocks = commify($io_blocks);
# Set up the string for SQL_ID # Set up the string for SQL_ID
if ($sql_id) { if ($sql_id) {
$current_sql_string = "<a href=$scriptname?database=$database&object_ty pe=SQLINFO&arg=$sql_id><b>$sql_id</b></a>"; $current_sql_string = "<a href=$scriptname?database=$database&object_ty pe=SQLINFO&arg=$sql_id><b>$sql_id</b></a>";
} else { } else {
$current_sql_string = "<b>n/a</b>"; $current_sql_string = "<b>n/a</b>";
} }
# If the current SQL ID and the previous sql ID are the same, # If the current SQL ID and the previous sql ID are the same,
# let's not repeat ourselves. That's if prev_sql_id is even set. # let's not repeat ourselves. That's if prev_sql_id is even set.
if (($prev_sql_id) && ($prev_sql_id ne $sql_id)) { if (($prev_sql_id) && ($prev_sql_id ne $sql_id)) {
skipping to change at line 9957 skipping to change at line 10468
# ($object_name,$object_type,$owner) = $cursor->fetchrow_array; # ($object_name,$object_type,$owner) = $cursor->fetchrow_array;
# $cursor->finish; # $cursor->finish;
# $object_string = "($object_type) $owner.$object_name"; # $object_string = "($object_type) $owner.$object_name";
# } else { # } else {
# $blocking_session_string = "<b>None</b>"; # $blocking_session_string = "<b>None</b>";
# $filename = "<b>n/a</b>"; # $filename = "<b>n/a</b>";
# $object_string = "<b>n/a</b>"; # $object_string = "<b>n/a</b>";
# } # }
if ($blocking_session) { if ($blocking_session) {
$blocking_session_string = "<a href=$scriptname?database=$database&obje $sql = "Select serial# from v\$session where sid=$blocking_session";
ct_type=SESSIONINFO&sid=$blocking_session><b>$blocking_session</b></a>"; $cursor = $dbh->prepare($sql) || print "$DBI::errstr";
$cursor->execute;
$blocking_serial = $cursor->fetchrow_array;
$cursor->finish;
$blocking_session_string = "<a href=$scriptname?database=$database&obje
ct_type=SESSIONINFO&sid=$blocking_session&serial=$blocking_serial><b>$blocking_s
ession</b></a>";
} else { } else {
$blocking_session_string = "<b>None</b>"; $blocking_session_string = "<b>None</b>";
} }
# Get some info about what object we're waiting for (if we're waiting) # Get some info about what object we're waiting for (if we're waiting)
$sql = "Select name from v\$datafile where file# = $row_wait_file"; $sql = "Select name from v\$datafile where file# = $row_wait_file";
$cursor = $dbh->prepare($sql); $cursor = $dbh->prepare($sql);
$cursor->execute; $cursor->execute;
$filename = $cursor->fetchrow_array; $filename = $cursor->fetchrow_array;
$cursor->finish; $cursor->finish;
$sql = "Select object_name, object_type, owner from dba_objects where obje ct_id = $row_wait_obj"; $sql = "Select object_name, object_type, owner from dba_objects where obje ct_id = $row_wait_obj";
skipping to change at line 10208 skipping to change at line 10724
</tr> </tr>
<!-- Spacer --> <!-- Spacer -->
<tr><td colspan=3 style="height:15px;"></tr> <tr><td colspan=3 style="height:15px;"></tr>
<tr> <tr>
<!-- Column 1 - Contention info --> <!-- Column 1 - Contention info -->
<td valign=top> <td valign=top>
<b><em>Contention / Object info</em></b> <b><em>Contention and I/O</em></b>
<table class="noborder"> <table class="noborder">
<tr> <tr>
<td align=right> <td align=right>
Blocking session ID<br> Blocking session ID<br>
</td> </td>
<td style="width:${width}px;">&nbsp;</td> <td style="width:${width}px;">&nbsp;</td>
<td align=left> <td align=left>
$blocking_session_string $blocking_session_string
</td> </td>
</tr> </tr>
skipping to change at line 10239 skipping to change at line 10755
<td align=right> <td align=right>
Waiting on<br> Waiting on<br>
</td> </td>
<td style="width:${width}px;">&nbsp;</td> <td style="width:${width}px;">&nbsp;</td>
<td align=left> <td align=left>
<b>$object_string</b> <b>$object_string</b>
</td> </td>
</tr> </tr>
<tr> <tr>
<td align=right> <td align=right>
Block changes<br>
</td>
<td style="width:${width}px;">&nbsp;</td>
<td align=left>
<b>$io_blocks (${io_mb} Mb)</b>
</td>
</tr>
<tr>
<td align=right>
Temp segment(s)<br> Temp segment(s)<br>
</td> </td>
<td style="width:${width}px;">&nbsp;</td> <td style="width:${width}px;">&nbsp;</td>
<td align=left> <td align=left>
<b>$temp_seg_string</b> <b>$temp_seg_string</b>
</td> </td>
</tr> </tr>
</table> </table>
</td> </td>
skipping to change at line 11093 skipping to change at line 11618
} else { } else {
logit(" This database is not backed up by RMAN, nor does it have any RMA N catalogs."); logit(" This database is not backed up by RMAN, nor does it have any RMA N catalogs.");
message("This database is not backed up using RMAN."); message("This database is not backed up using RMAN.");
message("This database contains no RMAN backup catalogs."); message("This database contains no RMAN backup catalogs.");
} }
logit("Exit subroutine backupMenu"); logit("Exit subroutine backupMenu");
} }
sub memoryAdvice {
logit("Enter subroutine memoryAdvice");
my ($sql,$text,$link,$infotext);
$sql = "
Select
memory_size \"Memory size\",
memory_size_factor \"Memory size factor\",
estd_db_time \"Est. DB time\",
estd_db_time_factor \"Est. DB time factor\",
version \"Version\",
con_id \"Con ID\"
from v\$memory_target_advice order by memory_size";
$text = "SGA sizing advice";
$link = "";
$infotext = "";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine memoryAdvice");
}
sub memoryResizeOps {
logit("Enter subroutine memoryAdvice");
my ($sql,$text,$link,$infotext);
$sql = "
Select
component \"Component\",
oper_type \"Operation type\",
status \"Status\",
to_char(start_time,'MM/DD/YYYY HH24:MI:SS') \"Start time\",
to_char(end_time,'MM/DD/YYYY HH24:MI:SS') \"End time\",
target_size \"Target size\",
initial_size \"Initial size\",
final_size \"Final size\"
from dba_hist_memory_resize_ops
where start_time > sysdate-30
order by start_time desc";
$text = "SGA resize operations, last 30 days";
$link = "";
$infotext = "No resize operations in the last 30 days";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine memoryAdvice");
}
sub perfMenu { sub perfMenu {
logit("Enter subroutine perfMenu"); logit("Enter subroutine perfMenu");
my ($user); my ($user);
Button("$scriptname?database=$database&object_type=PERFORMANCE TARGET=body"," Memory allocation & resources","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=PERFORMANCE TARGET=body"," Memory allocation & resources","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=SQLAREALIST TARGET=body"," Shared SQL area","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=SQLAREALIST TARGET=body"," Shared SQL area","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=SESSIONWAIT TARGET=body"," Session wait info","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=SESSIONWAIT TARGET=body"," Session wait info","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=MTSINFO TARGET=body","Mult i Threaded Server","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=MTSINFO TARGET=body","Mult i Threaded Server","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=MEMORYADVICE TARGET=body",
"SGA Advice","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=RESIZEOPS TARGET=body","Me
mory resize ops","$headingcolor","CENTER","200");
if (checkPriv("ALTER SYSTEM")) { if (checkPriv("ALTER SYSTEM")) {
print <<"EOF"; print <<"EOF";
<CENTER> <CENTER>
<P><HR WIDTH=90%><P> <P><HR WIDTH=90%><P>
<FORM METHOD="GET" ACTION="$scriptname"> <FORM METHOD="GET" ACTION="$scriptname">
<input type="HIDDEN" NAME="database" VALUE="$database"> <input type="HIDDEN" NAME="database" VALUE="$database">
<input type="HIDDEN" NAME="schema" VALUE="$schema"> <input type="HIDDEN" NAME="schema" VALUE="$schema">
<input type="HIDDEN" NAME="arg" VALUE="ALTER SYSTEM FLUSH SHARED_POOL"> <input type="HIDDEN" NAME="arg" VALUE="ALTER SYSTEM FLUSH SHARED_POOL">
skipping to change at line 11393 skipping to change at line 11974
} }
sub sessionMenu { sub sessionMenu {
logit("Enter subroutine sessionMenu"); logit("Enter subroutine sessionMenu");
print "<center>\n"; print "<center>\n";
Button("$scriptname?database=$database&object_type=TOPSESSIONS TARGET=body"," Session summary w/refresh","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=TOPSESSIONS TARGET=body"," Session summary w/refresh","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=DEVSESSIONS TARGET=body"," Developer sessions","$headingcolor","CENTER","200");
#Button("$scriptname?database=$database&object_type=ORATOP TARGET=body","oraT op","$headingcolor","CENTER","200"); #Button("$scriptname?database=$database&object_type=ORATOP TARGET=body","oraT op","$headingcolor","CENTER","200");
# This is a specialized button to allow a choice of the type of # This is a specialized button to allow a choice of the type of
# sessions to display. # sessions to display.
# #
# Does anyone use this any more? # Does anyone use this any more?
# Unless I get complaints, this stuff is gonna go away. # Unless I get complaints, this stuff is gonna go away.
# #
# print <<"EOF"; # print <<"EOF";
# <table class=\"button\"> # <table class=\"button\">
skipping to change at line 11449 skipping to change at line 12031
} }
$cursor->finish; $cursor->finish;
print <<"EOF"; print <<"EOF";
</SELECT> </SELECT>
<input type="SUBMIT" NAME="foobar" VALUE="Display sessions"> <input type="SUBMIT" NAME="foobar" VALUE="Display sessions">
</FORM> </FORM>
EOF EOF
$sql = " $sql = "
SELECT Select
INITCAP(STATUS) \"Status\", vs.machine \"Machine\",
TO_CHAR(COUNT(STATUS),'999,999,999') \"Count\" vs.username \"Username\",
FROM V\$SESSION WHERE USERNAME IS NOT NULL count(decode(vs.STATUS, 'ACTIVE', 1)) \"Active\",
GROUP BY STATUS count(decode(vs.STATUS, 'INACTIVE', 1)) \"Inactive\",
"; count(*) \"Total\"
from v\$session vs
where username is not null
and type <> 'BACKGROUND'
group by username, machine
order by 5 desc";
# $sql = "
#Select
# machine \"Machine\",
# count(*) \"# Sessions\"
#from v\$session
# where machine is not null
#group by machine order by 2 desc";
my $text = "# Active sessions, excluding background processes"; my $text = "Session count by client";
my $link = ""; my $link = "";
my $infotext = "No active sessions this time."; my $infotext = "No active sessions this time.";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
# $sql = "
#SELECT
# INITCAP(STATUS) \"Status\",
# TO_CHAR(COUNT(STATUS),'999,999,999') \"Count\"
#FROM V\$SESSION WHERE USERNAME IS NOT NULL
# GROUP BY STATUS
#";
#
# $text = "# Active sessions, excluding background processes";
# $link = "";
# $infotext = "No active sessions this time.";
# DisplayTable($sql,$text,$link,$infotext);
$sql = " $sql = "
SELECT SELECT
PROGRAM \"Program\", PROGRAM \"Program\",
OSUSER \"OS user\", OSUSER \"OS user\",
PROCESS \"OS PID\", PROCESS \"OS PID\",
STATUS \"Status\" STATUS \"Status\"
FROM V\$SESSION FROM V\$SESSION
WHERE USERNAME IS NULL WHERE USERNAME IS NULL
ORDER BY PROGRAM ORDER BY PROGRAM
"; ";
skipping to change at line 11484 skipping to change at line 12092
DisplayTable($sql,$text,$link); DisplayTable($sql,$text,$link);
logit("Exit subroutine sessionMenu"); logit("Exit subroutine sessionMenu");
} }
sub auditMenu { sub auditMenu {
logit("Enter subroutine auditMenu"); logit("Enter subroutine auditMenu");
my ($link,$text,$sql);
$sql = "
Select
parameter_name \"Name\",
parameter_value \"Value\",
audit_trail \"Pertains to\"
from dba_audit_mgmt_config_params
order by 3,2
";
$text = "Audit management parameters";
$link = "";
DisplayTable($sql,$text,$link);
print "<br>";
Button("$scriptname?database=$database&object_type=AUDITADMIN&command=schemao bjects TARGET=body","Schema object auditing","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=AUDITADMIN&command=schemao bjects TARGET=body","Schema object auditing","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=ENTERAUDITS&command=statem entobjects TARGET=body","SQL statement auditing","$headingcolor","CENTER","200") ; Button("$scriptname?database=$database&object_type=ENTERAUDITS&command=statem entobjects TARGET=body","SQL statement auditing","$headingcolor","CENTER","200") ;
Button("$scriptname?database=$database&object_type=ENTERAUDITS&command=system objects TARGET=body","System privilege auditing","$headingcolor","CENTER","200") ; Button("$scriptname?database=$database&object_type=ENTERAUDITS&command=system objects TARGET=body","System privilege auditing","$headingcolor","CENTER","200") ;
Button("$scriptname?database=$database&object_type=AUDITLIST TARGET=body","Re move audits","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=AUDITLIST TARGET=body","Re move audits","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=AUDITING TARGET=body","Dis play auditing records","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=AUDITING TARGET=body","Dis play auditing records","$headingcolor","CENTER","200");
logit("Exit subroutine auditMenu"); logit("Exit subroutine auditMenu");
} }
skipping to change at line 11586 skipping to change at line 12211
} else { } else {
Button("","Parameter administration","$headingcolor","CENTER","200"); Button("","Parameter administration","$headingcolor","CENTER","200");
$count++; $count++;
} }
Button("$scriptname?database=$database&object_type=JOBSCHEDULER&command=menu TARGET=body","Job Scheduler (DBMS_SCHEDULER)","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=JOBSCHEDULER&command=menu TARGET=body","Job Scheduler (DBMS_SCHEDULER)","$headingcolor","CENTER","200");
text("Database reports"); text("Database reports");
Button("$scriptname?database=$database&object_type=AWRREPORT TARGET=body","AW R report","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=AWRREPORT TARGET=body","AW R report","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=ORACLEADVISOR&command=exec utions TARGET=body","Oracle advisor","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=ORACLEADVISOR&command=exec utions TARGET=body","Oracle advisor","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=FEATUREUSAGEREPORT TARGET= body","Feature usage report","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=TEMPSEGMENTREPORT TARGET=b ody","Temporary segment report","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=TEMPSEGMENTREPORT TARGET=b ody","Temporary segment report","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=USERSPACEREPORT TARGET=bod y","Space report by user","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=USERSPACEREPORT TARGET=bod y","Space report by user","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=TSSPACEREPORT TARGET=body" ,"Space report by tablespace / user","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=TSSPACEREPORT TARGET=body" ,"Space report by tablespace / user","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=FILEFRAGREPORT TARGET=body ","Datafile fragmentation report","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=FILEFRAGREPORT TARGET=body ","Datafile fragmentation report","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=HEALTHCHECKMENU TARGET=bod y","Multi instance reports","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=HEALTHCHECKMENU TARGET=bod y","Multi instance reports","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=PATCHES TARGET=body","Inst alled patches","$headingcolor","CENTER","200");
message("<CENTER>You ($username) do not have authority to enter one or more o f the DBA areas.") if $count; message("<CENTER>You ($username) do not have authority to enter one or more o f the DBA areas.") if $count;
logit("Exit subroutine taskMenu"); logit("Exit subroutine taskMenu");
} }
sub objectAdmin { sub objectAdmin {
logit("Enter subroutine objectAdmin"); logit("Enter subroutine objectAdmin");
skipping to change at line 12212 skipping to change at line 12839
AND STATUS IN ('INVALID','UNUSABLE') AND STATUS IN ('INVALID','UNUSABLE')
" if ($username); " if ($username);
$cursor = $dbh->prepare($sql); $cursor = $dbh->prepare($sql);
$cursor->execute; $cursor->execute;
$count = $cursor->fetchrow_array; $count = $cursor->fetchrow_array;
$cursor->finish; $cursor->finish;
unless ($count) { unless ($count) {
message("There are no invalid objects to compile"); message("There are no invalid objects to compile");
footer(); Footer();
} }
Button("$scriptname?database=$database&object_type=DBADMIN&arg=compileall TAR GET=body","Compile all invalid","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=DBADMIN&arg=compileall TAR GET=body","Compile all invalid","$headingcolor","CENTER","200");
text("Or, select the objects you would like to compile.\n"); text("Or, select the objects you would like to compile.\n");
$sql = "$copyright $sql = "$copyright
SELECT SELECT
OWNER, OWNER,
OBJECT_TYPE, OBJECT_TYPE,
skipping to change at line 13067 skipping to change at line 13694
} }
sub showDataguard { sub showDataguard {
logit("Enter subroutine showDataguard"); logit("Enter subroutine showDataguard");
my ($sql,$text,$link,$infotext); my ($sql,$text,$link,$infotext);
$sql = " $sql = "
Select Select
protection_mode \"Protection mode\"
from v\$database";
$text = "";
$link = "";
$infotext = "";
DisplayTable($sql,$text,$link,$infotext);
$sql = "
Select
dest_id \"ID\", dest_id \"ID\",
dest_name \"Name\", dest_name \"Name\",
status \"Status\", status \"Status\",
binding \"Binding\", binding \"Binding\",
target \"Target\", target \"Target\",
destination \"Destination\", destination \"Destination\",
log_sequence \"Current sequence\", log_sequence \"Current sequence\",
valid_role \"Role\", valid_role \"Role\",
db_unique_name \"DB unique name\" db_unique_name \"DB unique name\"
from v\$archive_dest from v\$archive_dest
skipping to change at line 13099 skipping to change at line 13736
high_sequence# \"High\" high_sequence# \"High\"
from v\$archive_gap"; from v\$archive_gap";
$text = "Archive gap."; $text = "Archive gap.";
$link = ""; $link = "";
$infotext = "No gap in archive found."; $infotext = "No gap in archive found.";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
$sql = " $sql = "
Select Select
name \"Name\",
value \"Value\"
from v\$parameter
where name in ('dg_broker_config_file1','dg_broker_config_file2','compatible'
,'control_file_record_keep_time','db_file_name_convert','log_file_name_convert',
'db_unique_name','fal_client','fal_server','instance_name','log_archive_config',
'log_archive_format','log_archive_local_first','log_archive_max_processes','log_
archive_min_succeed_dest','log_archive_trace','remote_login_passwordfile','stand
by_archive_dest','standby_file_management')
or name like 'log_archive_dest%'
and value is not null
and isdefault='FALSE'
order by 1";
$text = "Dataguard related initialization parameters.";
$link = "";
$infotext = "";
DisplayTable($sql,$text,$link,$infotext);
$sql = "
Select
facility \"Facility\", facility \"Facility\",
severity \"Severity\", severity \"Severity\",
error_code \"Error code\", error_code \"Error code\",
to_char(timestamp,'MM/DD/YYYY HH24:MI:SS') \"Timestamp\", to_char(timestamp,'MM/DD/YYYY HH24:MI:SS') \"Timestamp\",
message \"Message\" message \"Message\"
from v\$dataguard_status from v\$dataguard_status
where timestamp > sysdate-7 where timestamp > sysdate-7
order by message_num desc"; order by message_num desc";
$text = "Log from last 7 days."; $text = "Log from last 7 days.";
$link = ""; $link = "";
$infotext = "No dataguard log messages for the last 7 days."; $infotext = "No dataguard log messages for the last 7 days.";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showDataguard"); logit("Exit subroutine showDataguard");
} }
sub dataguardPrimary { sub dataguardMember {
logit("Enter subroutine dataguardPrimary"); logit("Enter subroutine dataguardMember");
# #
# Let's find out if this is a dataguard configuration # Let's find out if this is a dataguard configuration
# #
my $count = recordCount($dbh,"Select * from v\$dataguard_config"); my $count = recordCount($dbh,"Select db_unique_name from v\$dataguard_config" );
if ($count > 1) { if ($count > 1) {
logit("This appears to be part of a dataguard configuration"); logit("This appears to be part of a dataguard configuration");
return(1); return(1);
} else { } else {
return(0); return(0);
} }
logit("Exit subroutine dataguardPrimary"); logit("Exit subroutine dataguardMember");
} }
sub shortMenu { sub shortMenu {
logit("Enter subroutine shortMenu"); logit("Enter subroutine shortMenu");
my $schema = shift; my $schema = shift;
my ($bgline,$key); my ($bgline,$key);
$bgline = "<BODY LINK=$linkcolor ALINK=$linkcolor VLINK=$linkcolor BGCOLOR=$b gcolor>\n"; $bgline = "<BODY LINK=$linkcolor ALINK=$linkcolor VLINK=$linkcolor BGCOLOR=$b gcolor>\n";
skipping to change at line 13168 skipping to change at line 13821
</style> </style>
$bgline $bgline
<CENTER> <CENTER>
<P> <P>
<table class="noborder"> <table class="noborder">
<tr> <tr>
<td> <td>
EOF EOF
Button("$scriptname?database=$database&object_type=WORKSHEET&schema=$schema TARG ET=body","SQL Worksheet","$headingcolor"); Button("$scriptname?database=$database&object_type=WORKSHEET&schema=$schema TARG ET=body","SQL Worksheet","$headingcolor");
Button("$scriptname?database=$database&object_type=RMANMONITOR TARGET=body","Mon itor RMAN","$headingcolor"); Button("$scriptname?database=$database&object_type=RMANMONITOR TARGET=body","Mon itor RMAN","$headingcolor");
Button("$scriptname?database=$database&object_type=REDOLOGS TARGET=body","Redo /
Flashback","$headingcolor");
if (dataguardMember()) {
Button("$scriptname?database=$database&object_type=DATAGUARD TARGET=body","Da
taguard","$headingcolor");
}
Button("$scriptname TARGET=_top","Change connection","$headingcolor");
print <<"EOF"; print <<"EOF";
</td> </td>
</tr> </tr>
</table> </table>
EOF EOF
logit("Exit subroutine shortMenu"); logit("Exit subroutine shortMenu");
exit; exit;
skipping to change at line 13198 skipping to change at line 13856
print <<"EOF"; print <<"EOF";
Content-type: Text/html\n\n Content-type: Text/html\n\n
<html> <html>
<head> <head>
$stylecss $stylecss
</head> </head>
<body> <body>
<center> <center>
<table><tr><td></td></tr></table> <table><tr><td></td></tr></table>
EOF EOF
Button("","> $database <","$headingcolor");
Button("$scriptname?database=$database&object_type=MYORACLETOOL TARGET=body","My Oracletool","$headingcolor"); Button("$scriptname?database=$database&object_type=MYORACLETOOL TARGET=body","My Oracletool","$headingcolor");
if (my $ownerid = PeoplesoftInstalled()) { if (my $ownerid = PeoplesoftInstalled()) {
Button("$scriptname?database=$database&object_type=PSOFTMENU TARGET=body","Pe oplesoft","$headingcolor"); Button("$scriptname?database=$database&object_type=PSOFTMENU TARGET=body","Pe oplesoft","$headingcolor");
} }
Button("$scriptname?database=$database&object_type=LISTUSERS TARGET=body","Schem a list","$headingcolor"); Button("$scriptname?database=$database&object_type=LISTUSERS TARGET=body","Schem a list","$headingcolor");
Button("$scriptname?database=$database&object_type=SESSIONMENU TARGET=body","Ses sion info","$headingcolor"); Button("$scriptname?database=$database&object_type=SESSIONMENU TARGET=body","Ses sion info","$headingcolor");
if (usesASM()) { if (usesASM()) {
Button("$scriptname?database=$database&object_type=ASM TARGET=body","ASM","$h eadingcolor"); Button("$scriptname?database=$database&object_type=ASM TARGET=body","ASM","$h eadingcolor");
} }
Button("$scriptname?database=$database&object_type=TABLESPACES TARGET=body","Tab lespaces","$headingcolor"); Button("$scriptname?database=$database&object_type=TABLESPACES TARGET=body","Tab lespaces","$headingcolor");
Button("$scriptname?database=$database&object_type=DATAFILES TARGET=body","Dataf iles","$headingcolor"); Button("$scriptname?database=$database&object_type=DATAFILES TARGET=body","Dataf iles","$headingcolor");
Button("$scriptname?database=$database&object_type=REDOLOGS TARGET=body","Redo / Flashback","$headingcolor"); Button("$scriptname?database=$database&object_type=REDOLOGS TARGET=body","Redo / Flashback","$headingcolor");
if (dataguardPrimary()) { if (dataguardMember()) {
Button("$scriptname?database=$database&object_type=DATAGUARD TARGET=body","Da taguard","$headingcolor"); Button("$scriptname?database=$database&object_type=DATAGUARD TARGET=body","Da taguard","$headingcolor");
} }
Button("$scriptname?database=$database&object_type=ROLLBACKMENU TARGET=body","Un do segments","$headingcolor"); Button("$scriptname?database=$database&object_type=ROLLBACKMENU TARGET=body","Un do segments","$headingcolor");
Button("$scriptname?database=$database&object_type=PERFMENU TARGET=body","Perf / memory","$headingcolor"); Button("$scriptname?database=$database&object_type=PERFMENU TARGET=body","Perf / memory","$headingcolor");
Button("$scriptname?database=$database&object_type=CONTENTION TARGET=body","Lock s / contends","$headingcolor"); Button("$scriptname?database=$database&object_type=CONTENTION TARGET=body","Lock s / contends","$headingcolor");
Button("$scriptname?database=$database&object_type=EXPLAIN TARGET=body","Explain plan","$headingcolor"); Button("$scriptname?database=$database&object_type=EXPLAIN TARGET=body","Explain plan","$headingcolor");
Button("$scriptname?database=$database&object_type=WORKSHEET&schema=$schema TARG ET=body","SQL Worksheet","$headingcolor"); Button("$scriptname?database=$database&object_type=WORKSHEET&schema=$schema TARG ET=body","SQL Worksheet","$headingcolor");
Button("$scriptname?database=$database&object_type=SECURITY TARGET=body","Securi ty","$headingcolor"); Button("$scriptname?database=$database&object_type=SECURITY TARGET=body","Securi ty","$headingcolor");
Button("$scriptname?database=$database&object_type=PARAMETERS TARGET=body","Para ms / Registry","$headingcolor"); Button("$scriptname?database=$database&object_type=PARAMETERS TARGET=body","Para ms / Registry","$headingcolor");
# #
skipping to change at line 13886 skipping to change at line 14545
EOF EOF
logit("Exit subroutine showTSgraph"); logit("Exit subroutine showTSgraph");
} }
sub traceSession { sub traceSession {
logit("Enter subroutine traceSession"); logit("Enter subroutine traceSession");
my ($sql,$sid,$serial,$trace,$boolean,$tracemsg); my ($sql,$sid,$serial,$trace,$boolean,$tracemsg,$level,$event);
$sid = $query->param('sid'); $sid = $query->param('sid');
$serial = $query->param('serial'); $serial = $query->param('serial');
$trace = $query->param('trace'); $trace = $query->param('trace');
$boolean = 1;
$event = 10046;
if ($trace eq "enable") { if ($trace eq "enable") {
$trace = "ENABLE"; $level = 4;
$tracemsg = "enabled"; $tracemsg = "enabled";
} }
if ($trace eq "disable") { if ($trace eq "disable") {
$trace = "DISABLE"; $level = 0;
$tracemsg = "disabled"; $tracemsg = "disabled";
} }
$sql = " #
BEGIN # Changed from using DBMS_MONITOR 04/2016
DBMS_MONITOR.SESSION_TRACE_$trace(?,?); # to easily change levels
END; # Default for here is 4, which enables trace
# with binds but not waits (8 for waits)
#
$sql = "
Begin
dbms_system.set_ev(?,?,?,?,?);
End;
"; ";
logit(" Preparing - SID = $sid SERIAL = $serial"); logit(" Preparing - SID = $sid SERIAL = $serial");
$cursor = $dbh->prepare($sql) || logit("$DBI::errstr"); $cursor = $dbh->prepare($sql) || logit("$DBI::errstr");
logit(" Error: $DBI::errstr") if ($DBI::errstr); logit(" Error: $DBI::errstr") if ($DBI::errstr);
$cursor->bind_param(1,$sid); $cursor->bind_param(1,$sid);
logit(" Error: $DBI::errstr") if ($DBI::errstr); logit(" Error: $DBI::errstr") if ($DBI::errstr);
$cursor->bind_param(2,$serial); $cursor->bind_param(2,$serial);
logit(" Error: $DBI::errstr") if ($DBI::errstr); logit(" Error: $DBI::errstr") if ($DBI::errstr);
$cursor->bind_param(3,$event);
logit(" Error: $DBI::errstr") if ($DBI::errstr);
$cursor->bind_param(4,$level);
logit(" Error: $DBI::errstr") if ($DBI::errstr);
$cursor->bind_param(5,'');
logit(" executing"); logit(" executing");
$cursor->execute; $cursor->execute;
logit(" Error: $DBI::errstr") if ($DBI::errstr); logit(" Error: $DBI::errstr") if ($DBI::errstr);
$cursor->finish; $cursor->finish;
logit(" Error: $DBI::errstr") if ($DBI::errstr); logit(" Error: $DBI::errstr") if ($DBI::errstr);
logit($sql); logit($sql);
# $sql = "
#BEGIN
# DBMS_MONITOR.SESSION_TRACE_$trace(?,?,?,?,?);
#END;
#";
# logit(" Preparing - SID = $sid SERIAL = $serial");
# $cursor = $dbh->prepare($sql) || logit("$DBI::errstr");
# logit(" Error: $DBI::errstr") if ($DBI::errstr);
# $cursor->bind_param(1,$sid);
# logit(" Error: $DBI::errstr") if ($DBI::errstr);
# $cursor->bind_param(2,$serial);
# logit(" Error: $DBI::errstr") if ($DBI::errstr);
# $cursor->bind_param(3,$boolean);
# logit(" Error: $DBI::errstr") if ($DBI::errstr);
# $cursor->bind_param(4,$boolean);
# logit(" Error: $DBI::errstr") if ($DBI::errstr);
# $cursor->bind_param(5,$never);
# logit(" Error: $DBI::errstr") if ($DBI::errstr);
# logit(" executing");
# $cursor->execute;
# logit(" Error: $DBI::errstr") if ($DBI::errstr);
# $cursor->finish;
# logit(" Error: $DBI::errstr") if ($DBI::errstr);
# logit($sql);
#message("SQL trace $tracemsg for SID $sid Serial# $serial<P>Check for trace file in user_dump_dest."); #message("SQL trace $tracemsg for SID $sid Serial# $serial<P>Check for trace file in user_dump_dest.");
#$sql = "Select * from v\$session where sid=$sid and serial# = $serial"; #$sql = "Select * from v\$session where sid=$sid and serial# = $serial";
#my $text = "Test"; #my $text = "Test";
#my $link = ""; #my $link = "";
#my $infotext = ""; #my $infotext = "";
#DisplayTable($sql,$text,$link,$infotext); #DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine traceSession"); logit("Exit subroutine traceSession");
skipping to change at line 14419 skipping to change at line 15117
sub text { sub text {
logit("Enter subroutine text"); logit("Enter subroutine text");
# Print a message to the user # Print a message to the user
my $message = shift; my $message = shift;
$message = "$message"; $message = "$message";
print "<P><B>$message</B></P>\n"; print "<p><b>$message</b></p>\n";
logit("Exit subroutine text"); logit("Exit subroutine text");
} }
sub commify { sub commify {
logit("Enter subroutine commify"); logit("Enter subroutine commify");
# Puts commas in a numeral # Puts commas in a numeral
skipping to change at line 15317 skipping to change at line 16015
oracletool.pl version $VERSION<BR> oracletool.pl version $VERSION<BR>
<hr align=center width=75% noshade size=1> <hr align=center width=75% noshade size=1>
$encstring<br> $encstring<br>
Your theme is set to $theme. Your theme is set to $theme.
$text $text
</td> </td>
</tr> </tr>
</table> </table>
<br> <br>
Written and maintained by Adam vonNieda in Kansas, USA.<p> Written and maintained by Adam vonNieda in Kansas, USA.<p>
Copyright 1998 - 2014 Adam vonNieda<br> Copyright 1998 - 2016 Adam vonNieda<br>
You may distribute under the terms of either the GNU General Public<br> 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> 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> 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> for commercial distribution without the prior approval of the author.<p>
Home site: <a href="http://www.oracletool.com" target="_blank">http://www.oracle tool.com</a><br><br> Home site: <a href="http://www.oracletool.com" target="_blank">http://www.oracle tool.com</a><br><br>
Questions, comments, bug reports, and suggestions are encouraged!<br> Questions, comments, bug reports, and suggestions are encouraged!<br>
Tell me what to do to make it better!<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> 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> 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> project be it through suggestions, criticism, or code contributions.<br>
Oracletool is a useful product because of you! Oracletool is a useful product because of you!
EOF EOF
logit("Exit subroutine about"); logit("Exit subroutine about");
exit; Footer();
} }
sub advrep { sub advrep {
logit("Enter subroutine advrep"); logit("Enter subroutine advrep");
# Find out if this server is an advanced replication # Find out if this server is an advanced replication
# master server. # master server.
my ($sql,$cursor,$count); my ($sql,$cursor,$count);
skipping to change at line 15700 skipping to change at line 16399
DisplayTable($sql,$text,$link); DisplayTable($sql,$text,$link);
logit("Exit subroutine showIndextype"); logit("Exit subroutine showIndextype");
} }
sub showACL { sub showACL {
logit("Enter subroutine showACL"); logit("Enter subroutine showACL");
my ($sql,$text,$link); my ($sql,$text,$link,$infotext);
$sql = " $sql = "
Select Select
acl \"Acl\", acl \"ACL\",
host \"Host\", host \"Host\",
lower_port \"Lower port\", lower_port \"Lower port\",
upper_port \"Upper port\" upper_port \"Upper port\"
from dba_network_acls from dba_network_acls
where acl = '$object_name' where acl = '$object_name'
"; ";
$text = "$object_name"; $text = "Network ACL summary";
$link = ""; $link = "";
DisplayTable($sql,$text,$link); DisplayTable($sql,$text,$link);
$sql = " $sql = "
Select Select
principal \"Principal\", principal \"Principal\",
privilege \"Privilege\", privilege \"Privilege\",
decode(is_grant, decode(is_grant,
'true','True', 'true','True',
'false','False') \"Grant?\", 'false','False') \"Grant?\",
decode(invert, decode(invert,
'true','True', 'true','True',
'false','False') \"Invert?\" 'false','False') \"Invert?\"
from dba_network_acl_privileges from dba_network_acl_privileges
where acl = '$object_name' where acl = '$object_name'
"; ";
$text = "$object_name"; $text = "Privileges";
$link = "";
DisplayTable($sql,$text,$link);
$sql = "
Select
wallet_path \"Wallet path\"
from dba_wallet_acls
where acl = '$object_name'
";
$text = "Wallets";
$link = ""; $link = "";
$infotext = "No wallets assigned to this ACL";
DisplayTable($sql,$text,$link); DisplayTable($sql,$text,$link);
logit("Exit subroutine showACL"); logit("Exit subroutine showACL");
} }
sub showLibrary { sub showLibrary {
logit("Enter subroutine showLibrary"); logit("Enter subroutine showLibrary");
skipping to change at line 16718 skipping to change at line 17429
"; ";
$sql .= " WITH ADMIN OPTION" if ($admin_option eq "YES"); $sql .= " WITH ADMIN OPTION" if ($admin_option eq "YES");
runSQL($dbh,$sql); runSQL($dbh,$sql);
} }
} }
logit("Exit subroutine dbAdmin"); logit("Exit subroutine dbAdmin");
} }
sub showPatches {
logit("Enter subroutine showPatches");
my ($sql,$cursor,$text,$link,$infotext,$inventory);
$sql = "
With function getInv return varchar2
is
v_xml XMLType;
v_clob clob;
v_blob blob;
v_patchcount number := 1;
v_bugcount number := 1;
v_patchid varchar2(255);
v_patchdesc varchar2(4000);
v_inventory varchar2(32767);
Begin
Select sys.dbms_qopatch.get_opatch_list into v_xml from dual;
While v_xml.existsNode('/patches/patch['||v_patchcount||']') = 1
loop
v_patchid := v_XML.extract('/patches/patch['||v_patchcount||']/patchID/t
ext()').getStringVal();
v_inventory := v_inventory||v_patchid||' ';
--
-- All patches do not have descriptions
--
Begin
v_patchdesc := v_XML.extract('/patches/patch['||v_patchcount||']/patchD
escription/text()').getStringVal();
Exception
when self_is_null then
v_patchdesc := 'No description';
End;
v_inventory := v_inventory||v_patchdesc||chr(13) || chr(10);
v_patchcount := v_patchcount+1;
end loop;
return v_inventory;
end;
Select getInv from dual";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$inventory = $cursor->fetchrow_array;
$cursor->finish;
print <<"EOF";
<textarea name=inventory rows=$textarea_h cols=$textarea_w wrap=off>
$inventory
</textarea>
EOF
#text($inventory);
#$infotext = "No patch inventory to display";
#$link = "";
#DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showPatches");
}
sub showDependencies { sub showDependencies {
logit("Enter subroutine showDependencies"); logit("Enter subroutine showDependencies");
my $object_id = shift; my $object_id = shift;
my ($sql,$cursor,$text,$link,$infotext,$object_type,$owner,$object_name); my ($sql,$cursor,$text,$link,$infotext,$object_type,$owner,$object_name);
logit("Looking up object ID: $object_id");
$sql = " $sql = "
SELECT SELECT
OBJECT_NAME, OBJECT_NAME,
OBJECT_TYPE, OBJECT_TYPE,
OWNER OWNER
FROM DBA_OBJECTS FROM DBA_OBJECTS
WHERE OBJECT_ID = $object_id WHERE OBJECT_ID = $object_id
"; ";
$cursor = $dbh->prepare($sql); $cursor = $dbh->prepare($sql);
$cursor->execute; $cursor->execute;
skipping to change at line 16951 skipping to change at line 17729
} }
logit("Exit subroutine oracleAdvisors"); logit("Exit subroutine oracleAdvisors");
} }
sub jobScheduler { sub jobScheduler {
logit("Enter subroutine jobScheduler"); logit("Enter subroutine jobScheduler");
my ($sql,$cursor,$command,$username,$password,$database); my ($sql,$cursor,$command,$username,$password,$database,$count);
my ($jobnum,$what,$next_date,$interval,$no_parse,$nls_date_format); my ($jobnum,$what,$next_date,$interval,$no_parse,$nls_date_format);
my ($link,$text,$infotext,$owner); my ($link,$text,$infotext,$job_name,$owner,$session_id,$os_process,$elapsed);
$command = $query->param('command') || ""; $command = $query->param('command') || "";
$username = $query->param('username') || ""; $username = $query->param('username') || "";
$password = $query->param('password') || ""; $password = $query->param('password') || "";
$database = $query->param('database') || ""; $database = $query->param('database') || "";
$owner = $query->param('arg2') || ""; $owner = $query->param('arg2') || "";
$nls_date_format = "Mon DD YYYY HH24:MI"; $nls_date_format = "Mon DD YYYY HH24:MI";
logit(" Command is $command"); logit(" Command is $command");
logit(" Date format is $nls_date_format"); logit(" Date format is $nls_date_format");
if ($command eq "stopjob") {
my $owner = $query->param('owner');
my $jobname = $query->param('jobname');
my $stoptype = $query->param('stoptype');
logit("Attempting to stop job $owner.$jobname ($stoptype)");
if ($stoptype eq "graceful") {
$sql = "
Begin
dbms_scheduler.stop_job('$owner.$jobname');
End;
";
$cursor = $dbh->prepare($sql);
logit("Error: $DBI::errstr");
$cursor->execute;
logit("Error: $DBI::errstr");
$cursor->finish;
logit("Error: $DBI::errstr");
logit("Stop job $owner.$jobname ($stoptype) complete");
}
if ($stoptype eq "force") {
$sql = "
Begin
dbms_scheduler.stop_job('$owner.$jobname',TRUE);
End;
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$cursor->finish;
logit("Stop job $owner.$jobname ($stoptype) complete");
}
$command = "menu";
}
if ($command eq "menu") { if ($command eq "menu") {
refreshButton(); refreshButton();
Button("$scriptname?database=$database&object_type=JOBSCHEDULER&command=di splay","Defined job info","$headingcolor","CENTER","200"); Button("$scriptname?database=$database&object_type=JOBSCHEDULER&command=di splay","Defined job info","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=JOBSCHEDULER&command=jo Button("$scriptname?database=$database&object_type=JOBSCHEDULER&command=jo
blog","Job log by run duration","$headingcolor","CENTER","200"); blogbyduration","Job log by run duration","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=JOBSCHEDULER&command=jo
blogbydate","Job log last 24 hours","$headingcolor","CENTER","200");
# Button("$scriptname?database=$database&object_type=JOBSCHEDULER&command= add","Add a new job","$headingcolor","CENTER","200"); # Button("$scriptname?database=$database&object_type=JOBSCHEDULER&command= add","Add a new job","$headingcolor","CENTER","200");
$sql = " $count = recordCount($dbh,"Select count(*) from dba_scheduler_running_jobs
");
if ($count) {
message("Currently running scheduler jobs");
print <<"EOF";
<table class="ot">
<th class="center">Job name</th>
<th class="center">Owner</th>
<th class="center">Session ID</th>
<th class="center">OS process</th>
<th class="center">Elapsed time</th>
<th class="center">Stop gracefully</th>
<th class="center">Stop forcefully</th>
EOF
$sql = "
Select Select
job_name \"Job name\", job_name,
owner \"Owner\", owner,
session_id \"Session ID\", session_id,
slave_os_process_id \"OS process ID\", slave_os_process_id,
to_char(elapsed_time,'MM/DD/YYYY HH24:MI:SS') \"Elapsed time\" to_char(elapsed_time,'MM/DD/YYYY HH24:MI:SS')
from dba_scheduler_running_jobs from dba_scheduler_running_jobs
order by 1 desc"; order by 1 desc";
$text = "Currently running scheduler jobs"; $cursor = $dbh->prepare($sql);
$infotext = "No jobs are currently running"; $cursor->execute;
$link = ""; while (($job_name,$owner,$session_id,$os_process,$elapsed) = $cursor->f
DisplayTable($sql,$text,$link,$infotext); etchrow_array) {
print <<"EOF";
<tr>
<td class="center">$job_name</td>
<td class="center">$owner</td>
<td class="center">$session_id</td>
<td class="center">$os_process</td>
<td class="center">$elapsed</td>
<td class="center"><a href="$scriptname?database=$database&object_type=JOBS
CHEDULER&command=stopjob&stoptype=graceful&owner=$owner&jobname=$job_name">Grace
ful</a></td>
<td class="center"><a href="$scriptname?database=$database&object_type=JOBS
CHEDULER&command=stopjob&stoptype=force&owner=$owner&jobname=$job_name">Force</a
></td>
</tr>
EOF
}
$cursor->finish;
print "</table>\n";
} else {
message("There are no jobs currently running");
}
$sql = " $sql = "
Select Select
client_name \"Client name\", client_name \"Client name\",
status \"Status\", status \"Status\",
attributes \"Attributes\", attributes \"Attributes\",
window_group \"Window group\", window_group \"Window group\",
service_name \"Service name\" service_name \"Service name\"
from dba_autotask_client"; from dba_autotask_client";
skipping to change at line 17097 skipping to change at line 17956
from dba_scheduler_jobs order by 2, 3 desc, 4 desc"; from dba_scheduler_jobs order by 2, 3 desc, 4 desc";
$text = "Current dbms_scheduler jobs"; $text = "Current dbms_scheduler jobs";
$link = "$scriptname?database=$database&object_type=JOBSCHEDULER&command=d etail"; $link = "$scriptname?database=$database&object_type=JOBSCHEDULER&command=d etail";
DisplayTable($sql,$text,$link); DisplayTable($sql,$text,$link);
Footer(); Footer();
} }
if ($command eq "joblog") { if ($command eq "joblogbydate") {
$sql = "
Select
job_name \"Job name\",
owner \"Owner\",
to_char(log_date,'MM/DD/YYYY HH24:MI:SS') \"Log date\",
status \"Status\",
error# \"Error#\",
actual_start_date \"Start date / time\",
run_duration \"Run duration\",
additional_info \"Additional info\"
from dba_scheduler_job_run_details
where actual_start_date > sysdate-1
order by actual_start_date desc";
$text = "Job log last 24 hours";
$infotext = "No jobs have started in the last 24 hours";
$link = "";
DisplayTable($sql,$text,$link,$infotext);
}
if ($command eq "joblogbyduration") {
$sql = " $sql = "
Select Select
job_name \"Job name\", job_name \"Job name\",
owner \"Owner\", owner \"Owner\",
to_char(log_date,'MM/DD/YYYY HH24:MI:SS') \"Log date\", to_char(log_date,'MM/DD/YYYY HH24:MI:SS') \"Log date\",
status \"Status\", status \"Status\",
error# \"Error#\", error# \"Error#\",
actual_start_date \"Start date / time\", actual_start_date \"Start date / time\",
run_duration \"Run duration\", run_duration \"Run duration\",
additional_info \"Additional info\" additional_info \"Additional info\"
from dba_scheduler_job_run_details from dba_scheduler_job_run_details
where actual_start_date > sysdate-2 where actual_start_date > sysdate-1
order by run_duration desc"; order by run_duration desc";
$text = "Job log, last 48 hours by duration"; $text = "Job log, last 24 hours by duration";
$infotext = "No jobs have started in the last 48 hours"; $infotext = "No jobs have started in the last 24 hours";
$link = ""; $link = "";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
} }
if ($command eq "detail") { if ($command eq "detail") {
my ($status,$href,$ddl); my ($status,$href,$ddl);
$sql = " $sql = "
skipping to change at line 17175 skipping to change at line 18057
print <<"EOF"; print <<"EOF";
<textarea name=ddl rows=$textarea_h cols=$textarea_w wrap=off> <textarea name=ddl rows=$textarea_h cols=$textarea_w wrap=off>
$ddl $ddl
</textarea> </textarea>
EOF EOF
$sql = " $sql = "
Select Select
job_name \"Job name\", job_name \"Job name\",
owner \"Owner\", owner \"Owner\",
to_char(log_date,'MM/DD/YYYY HH24:MI:SS') \"Log date\",
status \"Status\", status \"Status\",
error# \"Error#\", error# \"Error#\",
run_duration \"Run duration\", to_char(req_start_date,'MM/DD/YYYY HH24:MI:SS') \"Requested Start Date\",
to_char(actual_start_date,'MM/DD/YYYY HH24:MI:SS') \"Actual Start Da
te\",
to_char(run_duration,'MM/DD/YYYY HH24:MI:SS') \"Run Dur
ation\",
additional_info \"Additional info\" additional_info \"Additional info\"
from dba_scheduler_job_run_details from dba_scheduler_job_run_details
where job_name = '$object_name' where job_name = '$object_name'
order by log_date desc"; order by log_date desc";
$text = "Log entries for $object_name"; $text = "Log entries for $object_name";
$infotext = "There are no run log entries for $object_name"; $infotext = "There are no run log entries for $object_name";
$link = "$scriptname?database=$database&object_type=JOBSCHEDULER&command=a ddoredit"; $link = "$scriptname?database=$database&object_type=JOBSCHEDULER&command=a ddoredit";
DisplayTable($sql,$text,$link,$infotext); DisplayTable($sql,$text,$link,$infotext);
skipping to change at line 18184 skipping to change at line 19067
$sql = "DELETE FROM OT_NOTE_TEXT WHERE ID = $id"; $sql = "DELETE FROM OT_NOTE_TEXT WHERE ID = $id";
logit(" SQL: $sql"); logit(" SQL: $sql");
$mydbh->do($sql); $mydbh->do($sql);
# Keep the ID number, to update any attachments for this note. # Keep the ID number, to update any attachments for this note.
$oldid = $id; $oldid = $id;
} }
logit("Adding note name $name Text: $notetext"); logit("Adding note name $name\n");
$sql = "SELECT MAX(ID)+1 FROM OT_NOTES"; $sql = "SELECT MAX(ID)+1 FROM OT_NOTES";
$cursor = $mydbh->prepare($sql); $cursor = $mydbh->prepare($sql);
$cursor->execute; $cursor->execute;
$id = $cursor->fetchrow_array; $id = $cursor->fetchrow_array;
$cursor->finish; $cursor->finish;
$id = 1 unless $id; $id = 1 unless $id;
logit(" ID of new note is $id"); logit(" ID of new note is $id");
skipping to change at line 18220 skipping to change at line 19103
$piece = substr($notetext,0,$piece_length); $piece = substr($notetext,0,$piece_length);
substr($notetext,0,$piece_length) = ""; substr($notetext,0,$piece_length) = "";
$sql = "INSERT INTO OT_NOTE_TEXT (ID,LINE,TEXT) VALUES(?,?,?)"; $sql = "INSERT INTO OT_NOTE_TEXT (ID,LINE,TEXT) VALUES(?,?,?)";
logit(" Preparing insert piece $piece_count"); logit(" Preparing insert piece $piece_count");
my $foo = length($piece); my $foo = length($piece);
logit(" Length of piece is $foo"); logit(" Length of piece is $foo");
$foo = length($name); $foo = length($name);
logit(" Length of name is $foo"); logit(" Length of name is $foo");
$cursor = $mydbh->prepare($sql) or logit("$DBI::errstr"); $cursor = $mydbh->prepare($sql) or logit("$DBI::errstr");
logit(" Executing insert piece $piece_count"); logit(" Executing insert piece $piece_count");
#if ($piece_count eq 1) {
# logit("Piece: $piece\n");
#}
$cursor->execute($id,$piece_count,$piece) or logit("$DBI::errstr"); $cursor->execute($id,$piece_count,$piece) or logit("$DBI::errstr");
logit(" Finishing insert piece $piece_count"); logit(" Finishing insert piece $piece_count");
$cursor->finish or logit("$DBI::errstr"); $cursor->finish or logit("$DBI::errstr");
$piece_count++; $piece_count++;
} }
# If a file was attached, go add that as well. # If a file was attached, go add that as well.
if ($filename) { if ($filename) {
logit(" File $filename was attached, going to go add that."); logit(" File $filename was attached, going to go add that.");
$command = "addfile"; $command = "addfile";
} else { } else {
skipping to change at line 19276 skipping to change at line 20162
EOF EOF
} }
$cursor->finish; $cursor->finish;
print <<"EOF"; print <<"EOF";
</form> </form>
</table> </table>
EOF EOF
} else { } else {
text("No SQL scripts match the search criteria"); text("No SQL scripts match the search criteria");
} }
Footer();
} }
logit("Exit subroutine myOracletool"); logit("Exit subroutine myOracletool");
Footer();
} }
sub unique { sub unique {
my (@nonunique,@unique,%seen,$item); my (@nonunique,@unique,%seen,$item);
@nonunique = shift; @nonunique = shift;
%seen = (); %seen = ();
foreach $item(@nonunique) { foreach $item(@nonunique) {
skipping to change at line 19900 skipping to change at line 20786
# logit("VAR: $key SETTING: $ENV{$key}"); # logit("VAR: $key SETTING: $ENV{$key}");
# } # }
} }
sub AlertLog { sub AlertLog {
logit("Enter subroutine AlertLog"); logit("Enter subroutine AlertLog");
my ($count,$sql,$lines,$cursor,$alertlog,$line); my ($count,$sql,$lines,$cursor,$alertlog,$line);
my ($errors); my ($errors,$alters);
$lines = $alertlogrows || 100; $lines = $alertlogrows || 100;
logit(" AlertLog: The GET_ALERTLOG_LINES procedure appears to exist. Contin uing"); logit(" AlertLog: The GET_ALERTLOG_LINES procedure appears to exist. Contin uing");
$sql = " $sql = "
BEGIN BEGIN
SYS.GET_ALERTLOG_LINES(:lines,:alertlog); SYS.GET_ALERTLOG_LINES(:lines,:alertlog);
END; END;
"; ";
skipping to change at line 19924 skipping to change at line 20810
$cursor->execute or message("Error: $DBI::errstr"); $cursor->execute or message("Error: $DBI::errstr");
$cursor->finish or message("Error: $DBI::errstr"); $cursor->finish or message("Error: $DBI::errstr");
message("The last $lines lines of the alert log"); message("The last $lines lines of the alert log");
print "<textarea name=alertlog rows=$textarea_h cols=$textarea_w wrap=off>$al ertlog</textarea>\n"; print "<textarea name=alertlog rows=$textarea_h cols=$textarea_w wrap=off>$al ertlog</textarea>\n";
# Check for ORA- messages # Check for ORA- messages
my @lines = split /\n/, $alertlog; my @lines = split /\n/, $alertlog;
foreach $_(@lines) { foreach $_(@lines) {
if (/Errors in file/) { if (/ORA-/ or /PLS-/ or /TNS-/ or /Errors in file/ or /Incident details/ o
logit("Possible Oracle error: $_"); r /Dumping diagnostic data/) {
$errors .= $_ . "\n";
}
if (/ORA-/) {
logit("Possible Oracle error: $_");
$errors .= $_ . "\n";
}
if (/TNS-/) {
logit("Possible Oracle error: $_"); logit("Possible Oracle error: $_");
$errors .= $_ . "\n"; $errors .= $_ . "\n";
} }
} }
if ($errors) { if ($errors) {
print "<p>"; print "<p>";
message("Warning! Possible errors found in the alert log!"); message("Warning! Possible errors found in the alert log!");
print "<p><textarea name=alertlog rows=$textarea_h cols=$textarea_w wrap=o ff>$errors</textarea>\n"; print "<p><textarea name=alertlog rows=$textarea_h cols=$textarea_w wrap=o ff>$errors</textarea>\n";
} }
# Check for alter statements
my @lines = split /\n/, $alertlog;
foreach $_(@lines) {
if (/ALTER SYSTEM/) {
logit("Possible alter system statements issued: $_");
$alters .= $_ . "\n";
}
}
if ($alters) {
print "<p>";
message("Warning! Possible alter system statements found in the alert log!
");
print "<p><textarea name=alertlog rows=$textarea_h cols=$textarea_w wrap=o
ff>$alters</textarea>\n";
}
# #
#You need to create a procedure owned by the SYS user, and grant EXECUTE on that procedure to the connected user ($username). #You need to create a procedure owned by the SYS user, and grant EXECUTE on that procedure to the connected user ($username).
#Oracletool expects it to be called GET_ALERTLOG_LINES. #Oracletool expects it to be called GET_ALERTLOG_LINES.
# #
#Here is the source. #Here is the source.
# #
#Create or replace procedure get_alertlog_lines (num_lines in number, alertlog o ut clob) #Create or replace procedure get_alertlog_lines (num_lines in number, alertlog o ut clob)
# #
#/* #/*
# #
skipping to change at line 20527 skipping to change at line 21421
<META HTTP-EQUIV="pragma" CONTENT="nocache"> <META HTTP-EQUIV="pragma" CONTENT="nocache">
$stylecss $stylecss
<TITLE>$title</TITLE> <TITLE>$title</TITLE>
</HEAD> </HEAD>
$bgline $bgline
EOF EOF
print << "EOF"; print << "EOF";
<BR><BR> <BR><BR>
<CENTER> <CENTER>
<div id="loadingDiv"><p>.. loading ..<p></div>
<style>#loadingDiv{display:block;}</style>
EOF EOF
# The following was added to send a message to people I noticed were # The following was added to send a message to people I noticed were
# trying to break into my demo database. # trying to break into my demo database.
my (@forbidden,$forbidden_message,$forbidden_ip); my (@forbidden,$forbidden_message,$forbidden_ip);
@forbidden = (""); @forbidden = ("");
$forbidden_message = "Hello, $ENV{'REMOTE_ADDR'}. You have been denied acc ess until you start being more helpful."; $forbidden_message = "Hello, $ENV{'REMOTE_ADDR'}. You have been denied acc ess until you start being more helpful.";
skipping to change at line 20561 skipping to change at line 21457
sub Footer { sub Footer {
logit("Enter subroutine Footer"); logit("Enter subroutine Footer");
# Usage: Footer(); # Usage: Footer();
# Creates a HTML footer that refers back # Creates a HTML footer that refers back
print <<"EOF"; print <<"EOF";
<style>#loadingDiv{display:none;}</style>
</BODY> </BODY>
</HTML> </HTML>
EOF EOF
logit("Exit subroutine Footer and Oracletool. Bye!"); logit("Exit subroutine Footer and Oracletool. Bye!");
exit; exit;
} }
skipping to change at line 21053 skipping to change at line 21950
# Show Access Control Lists # Show Access Control Lists
if ($object_type eq "ACL") {showACL();} if ($object_type eq "ACL") {showACL();}
# Archived redo log space report # Archived redo log space report
if ($object_type eq "ARCHIVELOGSPACE") {archiveLogSpace();} if ($object_type eq "ARCHIVELOGSPACE") {archiveLogSpace();}
# Temporary segment usage report # Temporary segment usage report
if ($object_type eq "TEMPSEGMENTREPORT") {tempSegmentReport();} if ($object_type eq "TEMPSEGMENTREPORT") {tempSegmentReport();}
# Temporary segment usage report
if ($object_type eq "FEATUREUSAGEREPORT") {featureUsageReport();}
# Directory object # Directory object
if ($object_type eq "DIRECTORY") {showDirectory();} if ($object_type eq "DIRECTORY") {showDirectory();}
# Dataguard primary # Dataguard primary
if ($object_type eq "DATAGUARD") {showDataguard();} if ($object_type eq "DATAGUARD") {showDataguard();}
# oraTop # oraTop
if ($object_type eq "ORATOP") {oraTop();} if ($object_type eq "ORATOP") {oraTop();}
# AWR # AWR
if ($object_type eq "AWRREPORT") {awrReport();} if ($object_type eq "AWRREPORT") {awrReport();}
# Oracle advisor # Oracle advisor
if ($object_type eq "ORACLEADVISOR") {oracleAdvisor();} if ($object_type eq "ORACLEADVISOR") {oracleAdvisor();}
# Oracle advisor # Oracle advisor
if ($object_type eq "PARAMCOMPARE") {paramCompare();} if ($object_type eq "PARAMCOMPARE") {paramCompare();}
# Oracle advisor
if ($object_type eq "PATCHES") {showPatches();}
# Memory advice
if ($object_type eq "MEMORYADVICE") {memoryAdvice();}
# Memory resize operations
if ($object_type eq "RESIZEOPS") {memoryResizeOps();}
# Developer sessions
if ($object_type eq "DEVSESSIONS") {developerSessions();}
# Login records from audit trail
if ($object_type eq "LOGINAUDITRECORDS") {loginAuditRecords();}
logit("Exit subroutine Director"); logit("Exit subroutine Director");
} }
 End of changes. 144 change blocks. 
169 lines changed or deleted 1124 lines changed or added

Home  |  About  |  Features  |  All  |  Newest  |  Dox  |  Diffs  |  RSS Feeds  |  Screenshots  |  Comments  |  Imprint  |  Privacy  |  HTTP(S)