"Fossies" - the Fresh Open Source Software Archive

Member "oracletool-3.0.2/oracletool.pl" (7 Jul 2020, 641840 Bytes) of package /linux/privat/oracletool-3.0.2.tgz:


The requested HTML page contains a <FORM> tag that is unusable on "Fossies" in "automatic" (rendered) mode so that page is shown as HTML source code syntax highlighting (style: standard) with prefixed line numbers and code folding option. Alternatively you can here view or download the uninterpreted source code file. For more information about "oracletool.pl" see the Fossies "Dox" file reference documentation and the latest Fossies "Diffs" side-by-side code changes report: 3.0.1_vs_3.0.2.

    1 #!/apps/perl/bin/perl
    2 
    3 #   Copyright (c) 1998 - 2020 Adam vonNieda - Kansas USA
    4 #
    5 #   You may distribute under the terms of either the GNU General Public
    6 #   License or the Artistic License, as specified in the Perl README file,
    7 #   with the exception that it cannot be placed on a CD-ROM or similar media
    8 #   for commercial distribution without the prior approval of the author.
    9 
   10 #   This software is provided without warranty of any kind. If your server
   11 #   melts as a result of using this script, that's a bummer. But it won't.
   12 
   13 require 5.003;
   14 
   15 use strict;
   16 use CGI qw(:standard);
   17 use File::Basename;
   18 use FileHandle;
   19 
   20 if (! eval "require DBI") {
   21    ErrorPage("It appears that the DBI module is not installed!");
   22 }
   23 
   24 use DBD::Oracle qw(:ora_session_modes);
   25 
   26 use vars qw($VERSION $scriptname $query $database $namesdatabase $schema $textarea_w);
   27 use vars qw($debug $object_type $object_name $statement_id $user $whereclause $textarea_h);
   28 use vars qw($expire $username $password $dbh $sql $majversion $minversion $rowdisplay $banner);
   29 use vars qw($db_block_size $title $heading $cursor $upload_limit $nls_date_format $stylecss);
   30 use vars qw($logging $explainschema $bgcolor $headingcolor $fontcolor $infocolor $font $fontsize);
   31 use vars qw($linkcolor $cellcolor $bordercolor $description %themes $schema_cols $menufontsize);
   32 use vars qw($expiration $oraclenames $theme $repository $logfile %plugins $config_file);
   33 use vars qw($encryption_string $bgimage $menuimage $encryption_enabled $copyright $headingfont);
   34 use vars qw($headingfontcolor $encryption_method $dbstatus $myoracletool $mydbh $alertlogrows); 
   35 use vars qw($explainpassword $myoracletoolexpire $norefreshbutton $hostname $statspack_schema);
   36 use vars qw($dataguard);
   37 
   38 $VERSION = "3.0.2";
   39 
   40 # Edit the following if you want to use a config file not named "oracletool.ini".
   41 # The following assumes that the file is in the same directory as oracletool.pl.
   42 
   43 $config_file        = "oracletool.ini";
   44 
   45 $nls_date_format    = "Mon DD YYYY @ HH24:MI:SS";
   46 
   47 Main();
   48 
   49 #=============================================================
   50 # Nothing but subroutines from here on.
   51 #=============================================================
   52 
   53 sub Main {
   54 
   55    my ($foo);
   56 
   57 # Unbuffer STDOUT
   58    $|++;
   59 
   60 # Find out the name this script was invoked as.
   61    $scriptname = $ENV{'SCRIPT_NAME'};
   62 
   63 # Get the data from the elements passed in the URL.
   64    $query       = new CGI;
   65    $database        = $query->param('database');
   66    $namesdatabase   = $query->param('namesdatabase');
   67    $schema      = $query->param('schema');
   68    $explainschema   = $query->param('explainschema');
   69    $explainpassword = $query->param('explainpassword');
   70    $object_type     = $query->param('object_type');
   71    $object_name         = $query->param('arg');
   72    $statement_id    = $query->param('statement_id');
   73    $user        = $query->param('user');
   74    $whereclause     = $query->param('whereclause');
   75    $expire      = $query->param('expire');
   76    $myoracletoolexpire  = $query->param('myoracletoolexpire');
   77    $password        = $query->param('password');
   78    $alertlogrows    = $query->param('alertlogrows');
   79 
   80 # Set the page colors / font etc.
   81 # Attempt to get a cookie containing the users theme.
   82 # Set to a default theme if none is found.
   83 # Attempt to get a cookie containing MyOracletool info.
   84 
   85    $theme = cookie("OracletoolTheme");
   86    $theme = "Default" unless ($theme);
   87    $myoracletool = cookie("MyOracletool");
   88 
   89 # Get the settings from the config file.
   90    parseConfig();
   91 
   92 # Decide whether to display copyright in all SQL statements.
   93    if ($ENV{'DISPLAY_COPYRIGHT'}) {
   94       $copyright = "/* Oracletool v$VERSION is copyright 1998 - 2020 Adam vonNieda, Kansas USA */ ";
   95    } else {
   96       $copyright = "";
   97    }
   98 
   99    logit("Enter subroutine Main");
  100 
  101    logit("Database = $database Object type = $object_type ARG = $object_name");
  102 
  103 # Check for cookie encryption functionality.
  104 
  105    encryptionEnabled();
  106 
  107 # Set the properties that will override the default theme.
  108    doProperties();
  109 
  110 # If $namesdatabase is not null, then they have entered
  111 # a names-resolved database. Change the $database value
  112 # to the $namesdatabase value.
  113    $database = $namesdatabase if $namesdatabase;
  114 
  115 # If $database is "About_oracletool" then
  116 # show the "About" page.
  117    if ( $database && $database  eq "About_oracletool" ) {
  118       about();
  119    }
  120 
  121 # The $user variable will get passed to get session info
  122 # for an individual user. If no individual user is passed
  123 # then it defaults to % (All users)
  124    $user        = "%" unless $user;
  125 
  126 # Get rid of the +'s on multi-word object types.
  127    $object_type =~ s/\+/ / if $object_type;
  128 
  129 # If invoked standalone, show main page with database list.
  130    if ( ! defined $database ) {
  131       createMainPage();
  132       exit;
  133    }
  134 
  135 # Skip the password verification for setting theme. Theme
  136 # will be sent to browser as cookie.
  137    if ($object_type eq "SETTHEME") {
  138       setTheme();
  139    }
  140 
  141 # Skip the password verification for setting My Oracletool
  142 # parameters (cookies).
  143    if ($object_type eq "MYORACLETOOLCREATE" && $query->param('command') eq "savecookie") {
  144       logit("Redirecting to myOracletoolCreate");
  145       myOracletoolCreate();
  146    }
  147 
  148 # Skip the password verification for setting Properties. Properties
  149 # will be sent to browser as cookie.
  150    if ($object_type eq "SETPROPS") {
  151       setProperties();
  152    }
  153 
  154 # Skip the password verification for explain plan. Password
  155 # will be entered on the explain plan screen.
  156    if ($object_type eq "EXPLAIN") {
  157       enterExplainPlan();
  158    }
  159 
  160 # Add a password if no cookie is found, or if incorrect. 
  161    if ($object_type eq "ADDPASSWORD")     {
  162       $username = $query->param('username');
  163       $password = $query->param('password');
  164       addPasswd($database,$username,$password);
  165    }
  166 
  167 # Attempt to get username and password cookies for connecting to the specified database. 
  168    ($username,$password) = split / /, GetPasswd($database);
  169 
  170 # If no cookie is found, do not try to connect to the database,
  171 # just go directly to the password screen.
  172    unless ($username && $password) {
  173       EnterPasswd($database);
  174    }
  175 
  176 # Make connection to the database
  177    $dbh = dbConnect($database,$username,$password);
  178 
  179 # Find out what version of Oracle we are dealing with.
  180 
  181    getDbVersion($dbh);
  182 
  183 # Set the client info column in v$session.
  184 
  185    setClientInfo("Oracletool v$VERSION");
  186 
  187 # If invoked the first time after selecting the database,
  188 # start creating the frames.
  189 
  190    if ( $object_type eq "FRAMEPAGE" ) {
  191       framePage();
  192    }
  193 
  194 # Run an explain plan after determining the database version.
  195 # This is required to determine which PLAN_TABLE sql should
  196 # be executed, should a PLAN_TABLE not exist for the schema
  197 # name passed, and said schema may not have privileges to see
  198 # what version the database is.
  199    if ($object_type eq "RUNEXPLAINPLAN") {
  200       runExplainPlan();
  201    }
  202 
  203 # See what status the database is in (OPEN,MOUNTED etc...). 
  204 
  205    $dbstatus = dbStatus();
  206 
  207 # Display the menu on the left side of the screen.
  208 # This connects to the database as well, hence the
  209 # $username variable. Connection is for determining 
  210 # version, OPS etc. Certain buttons will or will not
  211 # be display based on some queries.
  212 
  213    if ( $object_type eq "MENU" ) {
  214 # If the database is MOUNTED (not open), show 
  215 # a partial menu
  216       if ($dbstatus eq "MOUNTED") {
  217          shortMenu($username);
  218       } else {
  219          showMenu($username);
  220       }
  221    }
  222 
  223    if ($dbstatus eq "OPEN") {
  224 
  225 # Find out the database block size
  226 
  227       $db_block_size = getDBblocksize();
  228 
  229 # Get the Server banner to display the version info.
  230 
  231       $banner = getBanner();
  232 
  233    }
  234 
  235 # Create the header for the HTML page.
  236 
  237    $title      = "$database: Oracletool v$VERSION connected as $username";
  238    $heading    = "";
  239 
  240    Header($title,$heading,$font,$fontsize,$fontcolor,$bgcolor);
  241 
  242 # The Director subroutine will direct the script to the appropriate
  243 # subroutines based on the parameters passed, namely $object_type
  244 
  245    Director();
  246 
  247 # Disconnect from the database
  248 
  249    $dbh->disconnect;
  250 
  251 # Finish the HTML page.
  252 
  253    Footer();
  254 
  255    logit("Exit subroutine Main");
  256 }
  257 
  258 sub getDbVersion {
  259 
  260    logit("Enter subroutine getDbVersion");
  261 
  262    my $dbh = shift;
  263    my ($value);
  264 
  265 # Find out if we are dealing with Oracle7 or Oracle8
  266    logit("   Getting Oracle version");
  267 #   $sql = "$copyright
  268 #SELECT MAX(SUBSTR(RELEASE,1,1)),
  269 #       MAX(SUBSTR(RELEASE,3,1))
  270 #   FROM V\$COMPATIBILITY
  271 #";
  272 
  273 #
  274 # Changed this 04/2015
  275 #
  276 #   $sql = "$copyright
  277 #SELECT 
  278 #   VALUE
  279 #FROM V\$PARAMETER 
  280 #   WHERE NAME = 'compatible'
  281 #";
  282 
  283    $sql = "Select version from v\$instance";
  284 
  285    $cursor = $dbh->prepare($sql);
  286    if (defined $cursor) {
  287       $cursor->execute;
  288       $value = $cursor->fetchrow_array;
  289       logit("   Version is $value");
  290       ($majversion,$minversion) = split(/\./,$value);
  291       $cursor->finish;
  292       logit("   Major version = $majversion, Minor = $minversion");
  293       if ( $majversion eq "7" ) {
  294          logit("   This is an Oracle7 database.");
  295          logit("   Why are you still on version 7?.");
  296          message("Oracle version 7 is not longer supported.");
  297          exit;
  298       }
  299       if ( $majversion eq "8" ) {
  300          logit("   This is an Oracle8 database.");
  301          if ($minversion eq "1") {
  302             logit("   This is an Oracle8i database.");
  303          }
  304          logit("   Why are you still on version 8?.");
  305          message("Oracle version 8 is not longer supported.");
  306          exit;
  307       }
  308       if ( $majversion eq "9" ) {
  309          logit("   This is an Oracle9i database.");
  310          if ($minversion eq "2") {
  311             logit("   This is 9i release 2 (9.2).");
  312          }
  313       }
  314       if ( $majversion eq "10" ) {
  315          logit("   This is an Oracle10g database.");
  316       }
  317       if ( $majversion eq "11" ) {
  318          logit("   This is an Oracle11g database.");
  319       }
  320    } else {
  321       logit("Object type is $object_type");
  322       if ($object_type eq "FRAMEPAGE") {
  323          ErrorPage("<HR>The user you connected as does not have sufficient database privileges to run " .
  324          "Oracletool. Please log in as a different user, preferably one with CREATE SESSION / SELECT ANY TABLE / SELECT ANY DICTIONARY / SELECT_CATALOG_ROLE privileges.<HR>");
  325          Footer();
  326       }
  327    }
  328    logit("Exit subroutine getDbVersion");
  329 }
  330 
  331 sub dbClosed {
  332 
  333    logit("Enter subroutine dbClosed");
  334    
  335    Header($title,$heading,$font,$fontsize,$fontcolor,$bgcolor);
  336    if ($object_name) {
  337       logit("   SQL passed to dbClosed: \n$object_name");
  338       runSQL($dbh,$object_name);
  339    } else {
  340       logit("   No SQL passed, displaying worksheet.");
  341       enterWorksheet();
  342    }
  343 
  344    logit("Exit subroutine dbClosed");
  345 
  346    exit;
  347 }
  348 
  349 sub dbStatus {
  350 
  351 # See what status the database is in.
  352 
  353    my ($cursor,$sql,$dbstatus);
  354 
  355    logit("Enter subroutine dbStatus");
  356 
  357    logit("   Checking database status.");
  358    $sql = "$copyright
  359 SELECT
  360    STATUS
  361 FROM V\$INSTANCE
  362 ";
  363    $cursor = $dbh->prepare($sql) or ErrorPage("Error: $DBI::errstr");
  364    logit("   Error from status SQL preparation.. $DBI::errstr") if ($DBI::errstr);
  365    $cursor->execute;
  366    $dbstatus = $cursor->fetchrow_array;
  367    $cursor->finish;
  368 
  369    logit("   Database was found to be $dbstatus.");
  370 
  371 #   if ($dbstatus ne "OPEN") {
  372 #      dbClosed();
  373 #   }
  374 
  375    logit("Exit subroutine dbStatus");
  376 
  377    return($dbstatus);
  378 }
  379 
  380 sub statsPackInstalled {
  381 
  382    logit("Enter subroutine statsPackInstalled");
  383 
  384    my ($sql,$count);
  385 
  386    $sql = "
  387 SELECT
  388    COUNT(*) 
  389 FROM DBA_OBJECTS
  390    WHERE OBJECT_NAME = 'STATSPACK'
  391 AND OBJECT_TYPE = 'PACKAGE'
  392 ";
  393 
  394    $count = recordCount($dbh,$sql);
  395 
  396    logit("Exit subroutine statsPackInstalled");
  397 
  398    return($count);
  399 
  400 }
  401 
  402 sub createMainPage() {
  403 
  404    logit("Enter subroutine createMainPage");
  405 
  406 # This sub will be called if this script is invoked without a 'database=....'
  407 # element in the URL.  
  408 
  409 # Get the connection strings from the tnsnames.ora file.
  410 
  411    my @sids = GetTNS();
  412 
  413 # Start creating main page
  414 
  415    my $bgline = "<BODY BGCOLOR=$bgcolor>\n";
  416 
  417    if ($bgimage) {
  418       if ((-e "$ENV{'DOCUMENT_ROOT'}/$bgimage") && (-r "$ENV{'DOCUMENT_ROOT'}/$bgimage")) {
  419          logit("Background image is $ENV{'DOCUMENT_ROOT'}/$bgimage and is readable");
  420          $bgline = "<BODY BACKGROUND=$bgimage>\n";
  421       }
  422    }
  423 
  424    # Get a cookie containing the most recent connection, so it may be highlighted.
  425 
  426    my $recent = cookie("OracletoolRecent");
  427    logit("The last connection was to $recent");
  428 
  429 print << "EOF";
  430 Content-type: Text/html\n\n
  431 <html>
  432   <head>
  433     <title>Oracletool v$VERSION</title>
  434   $stylecss
  435   </head>
  436     $bgline
  437     <center>
  438     <h2>
  439       Oracletool v$VERSION
  440     </h2>
  441     </center>
  442     <br><br>
  443     <table class=\"ot\">
  444       <tr>
  445         <td class=\"left\" style=\"width:300px; height:100px\">
  446 EOF
  447 print "<div style=\"font-size: 120%; font-weight: bold\">&nbsp;&nbsp;Select an instance..</div>\n" if (! $oraclenames);
  448 print "<div style=\"font-size: 120%; font-weight: bold\">&nbsp;&nbsp;Select or enter an instance name.</div>\n" if ($oraclenames);
  449 print <<"EOF";
  450                       <form method="POST" actionscriptname">
  451                       <p>
  452                       <select NAME="database">
  453 EOF
  454 
  455 my $sid;
  456 foreach $sid (@sids) {
  457    if ($sid eq $recent) {
  458       print "                <option value=\"$sid\" SELECTED>$sid</OPTION>\n";
  459    } else {
  460       print "                <option value=\"$sid\">$sid</OPTION>\n";
  461    }
  462 }
  463 
  464 print <<"EOF";
  465                       </select>
  466 EOF
  467    if ($oraclenames) {
  468       print <<"EOF";
  469                   <p>
  470                       <input type="TEXT" NAME="namesdatabase" size="20">
  471 EOF
  472    }
  473    print <<"EOF";
  474                   <p>
  475                       <input type="HIDDEN" NAME="object_type" VALUE="FRAMEPAGE">
  476                       <input type="SUBMIT" VALUE="Connect">
  477                       <input type="CHECKBOX" NAME="expire" VALUE="Yep">Expire password cookie
  478                       </P>
  479           </form>
  480               <p>
  481         </td>
  482       </tr>
  483     </table>
  484 EOF
  485 #   Button("$scriptname?object_type=MYORACLETOOL","My Oracletool","$headingcolor");
  486    print <<"EOF";
  487   </body>
  488 </html>
  489 EOF
  490 
  491    logit("Exit subroutine createMainPage");
  492 }
  493 
  494 sub setTheme {
  495 
  496    logit("Enter subroutine setTheme");
  497 
  498    my ($message,$duration,$url,$cookie,$path,$bgline);
  499 
  500    $theme = $object_name;
  501    $path   = dirname($scriptname);
  502    
  503    $cookie = cookie(-name=>"OracletoolTheme",-value=>"$theme",-expires=>"+10y");
  504    print header(-cookie=>[$cookie]);
  505    $message     = "Your personal theme has been set to $theme.<BR>Oracletool will restart with a connection to instance $database.";
  506    $duration    = "4";
  507    $url         = "$scriptname?database=$database&object_type=FRAMEPAGE";
  508 
  509    $bgline = "<BODY BGCOLOR=$bgcolor>\n";
  510 
  511    if ($bgimage) {
  512       if ((-e "$ENV{'DOCUMENT_ROOT'}/$bgimage") && (-r "$ENV{'DOCUMENT_ROOT'}/$bgimage")) {
  513          logit("   Background image is $ENV{'DOCUMENT_ROOT'}/$bgimage and is readable");
  514          $bgline = "<BODY BACKGROUND=$bgimage>\n";
  515       }
  516    }
  517 
  518    print <<"EOF";
  519 <HTML>
  520   <HEAD>
  521     <TITLE>Theme is set to $theme.</TITLE>
  522     <META HTTP-EQUIV="Refresh" Content="$duration;URL=$url">
  523   $stylecss
  524   </HEAD>
  525    $bgline
  526     <CENTER>
  527       $message
  528     </CENTER
  529   </BODY
  530 </HTML>
  531 EOF
  532 
  533    logit("Exit subroutine setTheme");
  534 
  535    exit;
  536 }
  537 
  538 sub doProperties {
  539 
  540    logit("Enter subroutine doProperties");
  541 
  542    my $properties = cookie("OracletoolProps");
  543 
  544    #
  545    # Don't set the properties if we're being invoked specifically to reset the properties values.
  546    #
  547    unless ($object_type eq "SETPROPS") {
  548 
  549       if ($properties) {
  550          ($schema_cols,$fontsize,$menufontsize,$textarea_w,$textarea_h,$rowdisplay,$alertlogrows) = split (/%/, $properties);
  551       } else {
  552       # Set the variables that are not taken care of by a theme.
  553          $menufontsize  = "11";
  554          $schema_cols   = "5";
  555          $textarea_w    = "80";
  556          $textarea_h    = "20";
  557          $rowdisplay    = "25";
  558          $alertlogrows  = "25";
  559       }
  560    }
  561 
  562    # Set the CSS
  563    $stylecss = "
  564 <style type=\"text/css\">
  565 
  566 a{text-decoration: none;} a:link{color: $linkcolor;} a:visited{color: $linkcolor; }
  567 
  568 /* Main body */
  569 
  570 body
  571   { font-family: $font; font-size: ${fontsize}px; color: $fontcolor; background-color: $bgcolor; }
  572 
  573 /* Standard Oracletool table */
  574 table.ot
  575   {font-family: $font; font-size: ${fontsize}px; color: $fontcolor; border: 1px solid; border-color: $bordercolor; border-collapse: collapse; box-shadow: 2px 2px 2px #AAAAAA;}
  576 
  577 /* Menu Buttons */
  578 
  579 table.menubutton
  580   { font-family: $font; font-size: ${menufontsize}px; font-weight:bold; color: $fontcolor; border: 1px solid; border-color: $bordercolor; border-collapse: collapse; width:100px; box-shadow: 2px 2px 2px #AAAAAA;}
  581 
  582 td.menubutton
  583   { color: $bordercolor; background-color: $cellcolor; text-align: center; }
  584 
  585 /* Standard Buttons */
  586 
  587 table.button
  588   { font-family: $font; font-size: ${fontsize}px; color: $fontcolor; border: 1px solid; border-color: $bordercolor; border-collapse: collapse; width:150px; box-shadow: 2px 2px 2px #AAAAAA;}
  589 
  590 td.button
  591   { color: $bordercolor; background-color: $cellcolor; text-align: center; border-color: $bordercolor; }
  592 
  593 /* No Border tables */
  594 /* These have no background color (same as page back ground) */
  595 
  596 table.noborder
  597   { font-family: $font; font-size: ${fontsize}px; color: $fontcolor; border-collapse: collapse; }
  598 
  599 td.noborder
  600   { vertical-align: top; background-color: $bgcolor; }
  601 
  602 td.noborderleft
  603   { background-color: $bgcolor; text-align: left;}
  604 
  605 td.nobordercenter
  606   { background-color: $bgcolor; text-align: center;}
  607 
  608 td.noborderright
  609   { background-color: $bgcolor; text-align: right;}
  610 
  611 /* Left justified elements */
  612 
  613 td.left
  614   { padding: 2px; border: 1px solid; background-color: $cellcolor; text-align: left; border-color: $bordercolor; }
  615 
  616 th.left
  617   { padding: 2px; border: 1px solid; background-color: $headingcolor; text-align: left; border-color: $bordercolor;}
  618 
  619 /* Center justified elements */
  620 
  621 td.center
  622   { padding: 2px; border: 1px solid; background-color: $cellcolor; text-align: center; border-color: $bordercolor; }
  623 
  624 th.center
  625   { padding: 2px; border: 1px solid; background-color: $headingcolor; text-align: center; border-color: $bordercolor;}
  626 
  627 /* Right justified elements */
  628 
  629 td.right
  630   { padding: 2px; border: 1px solid; background-color: $cellcolor; text-align: right; border-color: $bordercolor; }
  631 
  632 th.right
  633   { padding: 2px; border: 1px solid; background-color: $headingcolor; text-align: right; border-color: $bordercolor;}
  634 
  635 </style>
  636 ";
  637 
  638    logit("Exit subroutine doProperties");
  639 
  640 }
  641 
  642 sub setProperties {
  643 
  644    logit("Enter subroutine setProperties");
  645 
  646    my ($cookie,$properties,$message,$duration,$url,$path,$bgline);
  647 
  648    # Compare the selected properties with the ones set in this users
  649    # default theme, where applicable. If they are different, then
  650    # update a properties cookie. These parameters were passed in by
  651    # names that make no sense, in order to cut down on global variables.
  652 
  653    # $schema holds the value for $schema_cols.
  654    # $schema_cols is the number of columns wide to display the toplevel
  655    # schema list.
  656    $schema_cols = $schema;
  657 
  658    # $explainschema holds the value for $fontsize.
  659    # If not set, use value from theme.
  660    $fontsize = $explainschema;
  661 
  662    # $expire holds the value for $menufontsize.
  663    # If not set, default to '2'.
  664    $menufontsize = $expire;
  665 
  666    # $statement_id holds the value for TEXTAREA width
  667    $textarea_w = $statement_id;
  668 
  669    # $user holds the value for TEXTAREA height
  670    $textarea_h = $user;
  671 
  672    # $whereclause holds the value for how many rows to display.
  673    $rowdisplay = $whereclause;
  674 
  675    $properties = "$schema_cols%$fontsize%$menufontsize%$textarea_w%$textarea_h%$rowdisplay%$alertlogrows";
  676    logit("Setting properties to $properties");
  677    $path = dirname($scriptname);
  678 
  679    $cookie = cookie(-name=>"OracletoolProps",-value=>"$properties",-expires=>"+10y");
  680    print header(-cookie=>[$cookie]);
  681    $message     = "Your personal Oracletool preferences have been updated.<BR>Oracletool will restart with a connection to instance $database.";
  682    $duration    = "4";
  683    $url         = "$scriptname?database=$database&object_type=FRAMEPAGE";
  684 
  685    $bgline = "<BODY BGCOLOR=$bgcolor>\n";
  686 
  687    if ($bgimage) {
  688       if ((-e "$ENV{'DOCUMENT_ROOT'}/$bgimage") && (-r "$ENV{'DOCUMENT_ROOT'}/$bgimage")) {
  689          logit("   Background image is $ENV{'DOCUMENT_ROOT'}/$bgimage and is readable");
  690          $bgline = "<BODY BACKGROUND=$bgimage>\n";
  691       }
  692    }
  693 
  694    print <<"EOF";
  695 <HTML>
  696   <HEAD>
  697     <TITLE>Properties have been reset.</TITLE>
  698     <META HTTP-EQUIV="Refresh" Content="$duration;URL=$url">
  699     $stylecss
  700   </HEAD>
  701   $bgline
  702     <CENTER>
  703       $message
  704     </CENTER
  705   </BODY
  706 </HTML>
  707 EOF
  708 
  709    logit("Exit subroutine setProperties");
  710 
  711    exit;
  712 }
  713 
  714 sub showProps {
  715 
  716    logit("Enter subroutine showProps");
  717 
  718    # Display a menu for selecting non-default properties for the tool.
  719    # These will be stored as cookies.
  720 
  721    message("Oracletool preferences<BR>Setting these values will override values set in any theme.<BR>Submit changes or select theme at the bottom of this screen.");
  722 
  723    my ($fontsizeoverride,$fontoverride,$val);
  724 
  725    print <<"EOF";
  726 <FORM METHOD="POST" ACTION="$scriptname" TARGET="_top">
  727 <table class="noborder">
  728   <tr>
  729     <td>
  730       <input type=HIDDEN NAME='database' VALUE='$database'>
  731       <input type=HIDDEN NAME='object_type' VALUE='SETPROPS'>
  732       <input type=SUBMIT NAME='foobar' VALUE='Save preferences'>
  733     </td>
  734   </tr>
  735   <tr>
  736     <td>
  737       <B>
  738       Number of lines of the alert log to display:<BR>
  739 EOF
  740    # Loop through the values, in order to check the box which is the
  741    # value of what is set now.
  742       foreach $val ('20','50','100','250','500') {
  743          print "      <input type=RADIO NAME='alertlogrows' VALUE='$val'";
  744          if ($val == $alertlogrows) {
  745             print " CHECKED>$val\n";
  746          } else {
  747             print " >$val\n";
  748          }
  749       }
  750 print <<"EOF";
  751       <HR WIDTH='50%' ALIGN='LEFT'>
  752     </td>
  753   </tr>
  754   <tr>
  755     <td>
  756       <B>
  757       Schema list column number:<BR>
  758       This sets the number of columns in the schema list table.<BR>
  759 EOF
  760    # Loop through the values, in order to check the box which is the
  761    # value of what is set now.
  762       foreach $val ('3','4','5','6','7') {
  763          print "      <input type=RADIO NAME='schema' VALUE='$val'";
  764          if ($val == $schema_cols) {
  765             print " CHECKED>$val\n";
  766          } else {
  767             print " >$val\n";
  768          }
  769       }
  770 print <<"EOF";
  771       <HR WIDTH='50%' ALIGN='LEFT'>
  772     </td>
  773   </tr>
  774   <tr>
  775     <td>
  776       <B>
  777       Font size override:<BR>
  778       This will override the font size set by your theme.<BR>
  779 EOF
  780    # Loop through the values, in order to check the box which is the
  781    # value of what is set now.
  782       foreach $val ('1','2','3','4','5','6','7','8','9','10','11','12') {
  783          print "<input type=RADIO NAME='explainschema' VALUE='$val'";
  784          if ($val == $fontsize) {
  785             print " CHECKED>$val\n";
  786          } else {
  787             print " >$val\n";
  788          }
  789       }
  790 print <<"EOF";
  791       <HR WIDTH='50%' ALIGN='LEFT'>
  792     </td>
  793   </TR>
  794   <TR>
  795     <td>
  796       <B>
  797       Menu button font size override:<BR>
  798       This will override the menu button font size set by your theme.<BR>
  799 EOF
  800    # Loop through the values, in order to check the box which is the
  801    # value of what is set now.
  802       foreach $val ('1','2','3','4','5','6','7','8','9','10','11','12') {
  803          print "<input type=RADIO NAME='expire' VALUE='$val'";
  804          if ($val == $menufontsize) {
  805             print " CHECKED>$val\n";
  806          } else {
  807             print " >$val\n";
  808          }
  809       }
  810 print <<"EOF";
  811       <HR WIDTH='50%' ALIGN='LEFT'>
  812     </td>
  813   </TR>
  814   <TR>
  815     <td>
  816       <B>
  817       Textarea width:<BR>
  818       Width in characters of the SQL editing area.<BR>
  819 EOF
  820       foreach $val ('30','40','50','60','70','80','100','125','150') {
  821          print "<input type=RADIO NAME='statement_id' VALUE='$val'";
  822          if ($val == $textarea_w) {
  823             print " CHECKED>$val\n";
  824          } else {
  825             print " >$val\n";
  826          }
  827       }
  828 print <<"EOF";
  829       <HR WIDTH='50%' ALIGN='LEFT'>
  830     </td>
  831   </TR>
  832   <TR>
  833     <td>
  834       <B>
  835       Textarea height:<BR>
  836       Height in characters of the SQL editing area.<BR>
  837 EOF
  838       foreach $val ('5','10','15','20','25','30','35','40','45','50') {
  839          print "<input type=RADIO NAME='user' VALUE='$val'";
  840          if ($val == $textarea_h) {
  841             print " CHECKED>$val\n";
  842          } else {
  843             print " >$val\n";
  844          }
  845       }
  846 print <<"EOF";
  847       <HR WIDTH='50%' ALIGN='LEFT'>
  848     </td>
  849   </TR>
  850   <TR>
  851     <td>
  852       <B>
  853       Row display default:<BR>
  854       Number of rows to return on a table/view row display.<BR>
  855 EOF
  856       foreach $val ('1','5','25','50','100','250','500','all') {
  857          print "<input type=RADIO NAME='whereclause' VALUE='$val'";
  858          if ($val eq $rowdisplay) {
  859             print " CHECKED>$val\n";
  860          } else {
  861             print " >$val\n";
  862          }
  863       }
  864 print <<"EOF";
  865       <HR WIDTH='50%' ALIGN='LEFT'>
  866     </td>
  867   </tr>
  868   </form>
  869 </table>
  870 EOF
  871 
  872    logit("Exit subroutine showProps");
  873 
  874 }
  875 
  876 sub showThemes {
  877 
  878    logit("Enter subroutine showThemes");
  879 
  880    # Display all of the themes.
  881 
  882    my ($currenttheme,@themevars);
  883 
  884    $currenttheme = $theme;
  885 
  886    text("Select a color theme for your default.<BR>Your current theme is \"$currenttheme\".");
  887 
  888    foreach $theme (sort keys %themes) {
  889       logit("   Displaying theme $theme");
  890       @themevars        = @{ $themes{$theme} };
  891       $description      = $themevars[0]  or $description      = "undefined";
  892       $bgcolor          = $themevars[1]  or $bgcolor          = "undefined";
  893       $menuimage        = $themevars[2]  or $menuimage        = "undefined";
  894       $bgimage          = $themevars[3]  or $bgimage          = "undefined";
  895       $fontcolor        = $themevars[4]  or $fontcolor        = "undefined";
  896       $headingfontcolor = $themevars[5]  or $fontcolor        = "undefined";
  897       $infocolor        = $themevars[6]  or $infocolor        = "undefined";
  898       $linkcolor        = $themevars[7]  or $linkcolor        = "undefined";
  899       $font             = $themevars[8]  or $font             = "undefined";
  900       $headingfont      = $themevars[9]  or $font             = "undefined";
  901       $fontsize         = $themevars[10] or $fontsize         = "undefined";
  902       $headingcolor     = $themevars[11] or $headingcolor     = "undefined";
  903       $cellcolor        = $themevars[12] or $cellcolor        = "undefined";
  904       $bordercolor      = $themevars[13] or $bordercolor      = "undefined";
  905 
  906       print <<"EOF";
  907 <table style="font-family: $font; align:center; font-size: ${fontsize}px; color: $fontcolor; background-color: $bgcolor; border: 1px solid; border-color: $bordercolor;">
  908   <tr>
  909     <td style="align:center;">
  910       <table style="padding:20px;">
  911         <th style="padding: 2px; border: 1px solid; color: $bordercolor; background-color: $headingcolor;"><a href=$scriptname?database=$database&object_type=SETTHEME&arg=$theme TARGET=_top>$theme</a></th>
  912         <tr>
  913           <td>
  914             <table style="font-family: $font; font-size: ${fontsize}px; color: $fontcolor; background-color: $bgcolor; border: 1px solid; border-color: $bordercolor;">
  915               <th style="padding: 2px; border: 1px solid; color: $bordercolor; background-color: $headingcolor;">Table headings</th>
  916               <tr>
  917                 <td style="padding: 2px; border: 1px solid; background-color: $cellcolor; text-align: center;">Table cells</td>
  918               </tr>
  919             </table>
  920           </td>
  921         </tr>
  922       </table>
  923     link color
  924     </td>
  925   </tr>
  926 </table>
  927 <P>
  928 EOF
  929 
  930    }
  931 
  932    logit("Exit subroutine showThemes");
  933 
  934 }
  935 
  936 sub validateIndex {
  937 
  938    logit("Enter subroutine validateIndex");
  939 
  940    my ($sql,$text,$link);
  941 
  942    $sql = "
  943 VALIDATE INDEX $schema.$object_name
  944 ";
  945 
  946    runSQL($dbh,$sql);
  947 
  948    $sql = "$copyright
  949 SELECT
  950    HEIGHT                       \"Height\",
  951    TO_CHAR(BLOCKS,'999,999,999,999')            \"Blocks\",
  952    TO_CHAR(LF_ROWS,'999,999,999,999')           \"Leaf rows\",
  953    TO_CHAR(LF_BLKS,'999,999,999,999')           \"Leaf blocks\",
  954    TO_CHAR(DEL_LF_ROWS,'999,999,999,999')       \"Deleted leaf rows #\",
  955    TO_CHAR((DEL_LF_ROWS/LF_ROWS)*100,'999.99')      \"Ratio of deleted leaf rows\",
  956    TO_CHAR(DISTINCT_KEYS,'999,999,999,999')     \"Distinct keys #\",
  957    TO_CHAR(BTREE_SPACE,'999,999,999,999')       \"Total space allocated\",
  958    TO_CHAR(USED_SPACE,'999,999,999,999')        \"Total space used\",
  959    TO_CHAR(PCT_USED,'999')||'%'             \"Percent used\"
  960 FROM INDEX_STATS
  961 ";
  962 
  963 #   $sql = "$copyright
  964 #SELECT
  965 #   HEIGHT                      \"Height\",
  966 #   TO_CHAR(BLOCKS,'999,999,999,999')           \"Blocks\",
  967 #   TO_CHAR(LF_ROWS,'999,999,999,999')          \"Leaf rows\",
  968 #   TO_CHAR(LF_BLKS,'999,999,999,999')          \"Leaf blocks\",
  969 #   TO_CHAR(LF_ROWS_LEN,'999,999,999,999')      \"Leaf row sum\",
  970 #   TO_CHAR(LF_BLK_LEN,'999,999,999,999')       \"Leaf block usable space\",
  971 #   TO_CHAR(BR_ROWS,'999,999,999,999')          \"Branch rows #\",
  972 #   TO_CHAR(BR_BLKS,'999,999,999,999')          \"Branch blocks #\",
  973 #   TO_CHAR(BR_ROWS_LEN,'999,999,999,999')      \"Blocks length sum\",
  974 #   TO_CHAR(BR_BLK_LEN,'999,999,999,999')       \"Branch block usable space\",
  975 #   TO_CHAR(DEL_LF_ROWS,'999,999,999,999')      \"Deleted leaf rows #\",
  976 #   TO_CHAR(DEL_LF_ROWS_LEN,'999,999,999,999')      \"Deleted rows length\",
  977 #   TO_CHAR((DEL_LF_ROWS/LF_ROWS)*100),'999.99')        \"Ratio\",
  978 #   TO_CHAR(DISTINCT_KEYS,'999,999,999,999')        \"Distinct keys #\",
  979 #   TO_CHAR(MOST_REPEATED_KEY,'999,999,999,999')        \"Most repeated key #\",
  980 #   TO_CHAR(BTREE_SPACE,'999,999,999,999')      \"Total space allocated\",
  981 #   TO_CHAR(USED_SPACE,'999,999,999,999')       \"Total space used\",
  982 #   TO_CHAR(PCT_USED,'999')||'%'                \"Percent used\",
  983 #   TO_CHAR(ROWS_PER_KEY,'999,999,999,999')     \"Rows per distinct key\",
  984 #   TO_CHAR(BLKS_GETS_PER_ACCESS,'999,999,999,999') \"Block gets per access\"
  985 #FROM INDEX_STATS
  986 #";
  987 
  988    $text = "Index statistics.";
  989    $link = "";
  990    DisplayTable($sql,$text,$link);
  991 
  992    $sql = "$copyright
  993 SELECT
  994    REPEAT_COUNT                 \"Repeat count\",
  995    KEYS_WITH_REPEAT_COUNT           \"Keys with repeat count\"
  996 FROM INDEX_HISTOGRAM
  997 ";
  998 
  999    $text = "This table shows the number of times that one or more index keys is repeated in the table, and the number of index keys that are repeated that many times.";
 1000    $link = "";
 1001    DisplayTable($sql,$text,$link);
 1002 
 1003    logit("Exit subroutine validateIndex");
 1004 }
 1005 
 1006 sub showIndex {
 1007 
 1008    logit("Enter subroutine showIndex");
 1009 
 1010    my ($sql,$text,$link,$foo,$partitioned);   
 1011 
 1012 # Index structure
 1013    $sql = "$copyright 
 1014 SELECT 
 1015    TABLE_NAME                   \"Table name\",
 1016    TABLE_OWNER                  \"Owner\",
 1017    COLUMN_NAME                  \"Column name\", 
 1018    COLUMN_LENGTH                \"Column length\" 
 1019 FROM DBA_IND_COLUMNS 
 1020    WHERE INDEX_NAME = '$object_name' 
 1021 AND INDEX_OWNER = '$schema' 
 1022    ORDER BY COLUMN_POSITION
 1023 ";
 1024    $object_type = lc $object_type;
 1025    $text = "Structure of $object_type $object_name";
 1026    $link = "";
 1027    DisplayTable($sql,$text,$link);
 1028 
 1029 # General
 1030    $sql = "$copyright
 1031 SELECT  
 1032    A.TABLESPACE_NAME                    \"Tablespace name\", 
 1033    TO_CHAR(B.CREATED,'Month DD, YYYY - HH24:MI')        \"Date created\",
 1034    TO_CHAR(B.LAST_DDL_TIME,'Month DD, YYYY - HH24:MI')  \"Last DDL time\",
 1035    TO_CHAR(A.EXTENTS,'999,999,999,999')         \"Extents\", 
 1036    TO_CHAR(A.INITIAL_EXTENT,'999,999,999,999')      \"Initial extent\", 
 1037    TO_CHAR(A.NEXT_EXTENT,'999,999,999,999')     \"Next extent\",
 1038    TO_CHAR(A.MAX_EXTENTS,'999,999,999,999')     \"Max extents\",
 1039    TO_CHAR(A.BYTES,'999,999,999,999')           \"Bytes\",
 1040    B.STATUS                     \"Status\",
 1041    C.STATUS                     \"State\"
 1042 FROM DBA_SEGMENTS A, DBA_OBJECTS B, DBA_INDEXES C
 1043    WHERE A.SEGMENT_NAME = '$object_name' 
 1044    AND A.SEGMENT_TYPE = 'INDEX' 
 1045    AND A.OWNER = '$schema'
 1046    AND B.OBJECT_NAME = '$object_name'
 1047    AND B.OBJECT_TYPE = 'INDEX'
 1048    AND B.OWNER = '$schema'
 1049    AND B.OWNER = C.OWNER
 1050    AND B.OBJECT_NAME = C.INDEX_NAME
 1051 ";
 1052    $object_type = lc $object_type;
 1053    $text = "General info: $object_type $schema.$object_name";
 1054    $link = "";
 1055    DisplayTable($sql,$text,$link);
 1056 
 1057 # Check to see if index is partitioned
 1058 
 1059    $sql = "$copyright
 1060 SELECT
 1061    PARTITIONED
 1062 FROM DBA_INDEXES
 1063    WHERE INDEX_NAME = '$object_name'
 1064    AND OWNER = '$schema'
 1065 ";
 1066 
 1067    $cursor=$dbh->prepare($sql);
 1068    $cursor->execute;
 1069    $foo = $cursor->fetchrow_array;
 1070    $cursor->finish;
 1071    if ($foo eq "YES") {
 1072       $partitioned = "Yep";
 1073    }
 1074 
 1075 # If partitioned, show some additional info.
 1076 
 1077    if ($partitioned) {
 1078       $sql = "$copyright
 1079 SELECT
 1080    PARTITION_NAME                               \"Partition name\",
 1081    TABLESPACE_NAME                              \"Tablespace\",
 1082    PARTITION_POSITION                           \"Position\",
 1083    TO_CHAR(INITIAL_EXTENT,'999,999,999,999')    \"Initial\",
 1084    TO_CHAR(NEXT_EXTENT,'999,999,999,999')       \"Next\",
 1085    TO_CHAR(MAX_EXTENT,'999,999,999,999')        \"Max extents\",
 1086    PCT_INCREASE                                 \"Pct increase\",
 1087    HIGH_VALUE                                   \"High value\",
 1088    HIGH_VALUE_LENGTH                            \"High value length\",
 1089    LOGGING                                      \"Logging\"
 1090 FROM DBA_IND_PARTITIONS
 1091    WHERE INDEX_NAME = '$object_name'
 1092    AND INDEX_OWNER = '$schema'
 1093 ORDER BY PARTITION_POSITION
 1094 ";
 1095 
 1096       $text = "Partitions contained in this index";
 1097       $link = "$scriptname?database=$database&schema=$schema&object_type=INDEX+PARTITION&index_name=$object_name";
 1098       DisplayTable($sql,$text,$link);
 1099    }
 1100 
 1101    if (checkPriv("ANALYZE ANY")) {
 1102       print <<"EOF";
 1103 <BR>
 1104 <FORM METHOD="GET" ACTION="$scriptname">
 1105   <input type="HIDDEN" NAME="database" VALUE="$database">
 1106   <input type="HIDDEN" NAME="schema" VALUE="$schema">
 1107   <input type="HIDDEN" NAME="arg" VALUE="$object_name">
 1108   <input type="HIDDEN" NAME="object_type" VALUE="VALIDATEINDEX">
 1109   <input type="SUBMIT" NAME="foo" VALUE="Validate index for detailed statistics.">
 1110 </FORM>
 1111 EOF
 1112    }
 1113 
 1114    logit("Exit subroutine showIndex");
 1115 
 1116 }
 1117 
 1118 sub showIndexPart {
 1119 
 1120    logit("Enter subroutine showIndexPart");
 1121 
 1122    my ($sql,$cursor,$isanalyzed,$text,$link,$infotext,$index_name);
 1123 
 1124    $index_name = $query->param('index_name');
 1125 
 1126    $sql = "$copyright
 1127 SELECT
 1128    PARTITION_NAME                               \"Partition name\",
 1129    INDEX_NAME                                   \"Index name\",
 1130    INDEX_OWNER                                  \"Owner\",
 1131    TABLESPACE_NAME                              \"Tablespace\",
 1132    PARTITION_POSITION                           \"Position\",
 1133    TO_CHAR(INITIAL_EXTENT,'999,999,999,999')    \"Initial\",
 1134    TO_CHAR(NEXT_EXTENT,'999,999,999,999')       \"Next\",
 1135    TO_CHAR(MAX_EXTENT,'999,999,999,999')        \"Max extents\",
 1136    PCT_INCREASE                                 \"Pct increase\",
 1137    HIGH_VALUE                                   \"High value\",
 1138    HIGH_VALUE_LENGTH                            \"High value length\",
 1139    LOGGING                                      \"Logging\"
 1140 FROM DBA_IND_PARTITIONS
 1141    WHERE PARTITION_NAME = '$object_name'
 1142    AND INDEX_NAME = '$index_name'
 1143    AND INDEX_OWNER = '$schema'
 1144 ";
 1145 
 1146    $object_type = lc $object_type;
 1147    $text = "General info: $object_type $schema.$object_name";
 1148    $link = "";
 1149    DisplayTable($sql,$text,$link);
 1150 
 1151    logit("Exit subroutine showIndexPart");
 1152 
 1153 }
 1154 
 1155 sub showTablePart {
 1156 
 1157    logit("Enter subroutine showTablePart");
 1158 
 1159    my ($sql,$cursor,$isanalyzed,$text,$link,$infotext,$table_name);
 1160 
 1161    $table_name = $query->param('table_name');
 1162 
 1163 # General info
 1164 
 1165    $sql = "$copyright
 1166 SELECT 
 1167    PARTITION_NAME               \"Partition name\",
 1168    TABLE_NAME                   \"Table name\",
 1169    TABLE_OWNER                  \"Owner\",
 1170    TABLESPACE_NAME              \"Tablespace\",
 1171    PARTITION_POSITION               \"Position\",
 1172    TO_CHAR(INITIAL_EXTENT,'999,999,999,999')    \"Initial\",
 1173    TO_CHAR(NEXT_EXTENT,'999,999,999,999')   \"Next\",
 1174    TO_CHAR(MAX_EXTENT,'999,999,999,999')    \"Max extents\",
 1175    PCT_INCREASE                 \"Pct increase\",
 1176    HIGH_VALUE                   \"High value\",
 1177    HIGH_VALUE_LENGTH                \"High value length\",
 1178    LOGGING                  \"Logging\"
 1179 FROM DBA_TAB_PARTITIONS
 1180    WHERE PARTITION_NAME = '$object_name'
 1181    AND TABLE_NAME = '$table_name'
 1182    AND TABLE_OWNER = '$schema'
 1183 ";
 1184 
 1185    $object_type = lc $object_type;
 1186    $text = "General info: $object_type $schema.$object_name";
 1187    $link = "";
 1188    DisplayTable($sql,$text,$link);
 1189 
 1190 # Check to see if partition has been analyzed.
 1191 
 1192    $sql = "$copyright
 1193 SELECT 
 1194    DISTINCT LAST_ANALYZED
 1195 FROM DBA_TAB_PARTITIONS
 1196    WHERE PARTITION_NAME = '$object_name'
 1197    AND TABLE_OWNER = '$schema'
 1198 ";
 1199 
 1200    $cursor=$dbh->prepare($sql);
 1201    logit("   Error: $DBI::errstr") if $DBI::errstr;
 1202    $cursor->execute;
 1203    $isanalyzed = $cursor->fetchrow_array;
 1204    logit("   Isanalyzed for partition $schema.$object_name is $isanalyzed");
 1205    $cursor->finish;
 1206 
 1207    if ($isanalyzed) {
 1208 
 1209       $sql = "$copyright
 1210 SELECT
 1211    TO_CHAR((BLOCKS / (EMPTY_BLOCKS+BLOCKS)) *100,'999.99')||'%'      \"Percent used\",
 1212    TO_CHAR(NUM_ROWS,'999,999,999,999')                          \"Row count\",
 1213    TO_CHAR(BLOCKS,'999,999,999,999')                            \"Blocks\",
 1214    TO_CHAR(EMPTY_BLOCKS,'999,999,999,999')                      \"Empty blocks\",
 1215    TO_CHAR(AVG_SPACE,'999,999,999,999')                         \"Average space\",
 1216    TO_CHAR(AVG_ROW_LEN,'999,999,999,999')                       \"Average row length\",
 1217    TO_CHAR(CHAIN_CNT,'999,999,999,999')                         \"Chain count\",
 1218    TO_CHAR(LAST_ANALYZED,'Month DD, YYYY - HH24:MI')            \"Last analyzed\"
 1219 FROM DBA_TAB_PARTITIONS
 1220    WHERE PARTITION_NAME = '$object_name'
 1221    AND TABLE_OWNER = '$schema'
 1222 ";
 1223 
 1224       $text = "Analyzation info: $object_type $object_name";
 1225       $link = "";
 1226       DisplayTable($sql,$text,$link);
 1227    } else {
 1228       message("Partition has never been analyzed. Extended info will not be shown.");
 1229    }
 1230 
 1231    logit("Exit subroutine showTablePart");
 1232 
 1233 }
 1234 
 1235 sub showCluster {
 1236 
 1237    logit("Enter subroutine showCluster");
 1238 
 1239    my ($sql,$text,$link,$infotext);
 1240 
 1241    $sql = "$copyright
 1242 SELECT
 1243    TABLESPACE_NAME                  \"Tablespace name\",
 1244    TO_CHAR(INITIAL_EXTENT,'999,999,999,999')        \"Initial extent\",
 1245    TO_CHAR(NEXT_EXTENT,'999,999,999,999')       \"Next extent\",
 1246    TO_CHAR(MAX_EXTENTS,'999,999,999,999')       \"Max extents\",
 1247    CLUSTER_TYPE                     \"Cluster type\",
 1248    FUNCTION                     \"Function\",
 1249    INSTANCES                        \"Instances\",
 1250    SINGLE_TABLE                     \"Single table\"
 1251 FROM DBA_CLUSTERS 
 1252    WHERE CLUSTER_NAME = '$object_name'
 1253    AND OWNER = '$schema'
 1254 ";
 1255 
 1256    logit ("   $sql");
 1257 
 1258    $text = "General info: Cluster $schema.$object_name";
 1259    $link = "";
 1260    DisplayTable($sql,$text,$link);
 1261 
 1262    $sql = "$copyright
 1263 SELECT
 1264    TABLE_NAME                       \"Table_name\",
 1265    TO_CHAR(INITIAL_EXTENT,'999,999,999,999')        \"Initial extent\",
 1266    TO_CHAR(NEXT_EXTENT,'999,999,999,999')       \"Next extent\",
 1267    TO_CHAR(MAX_EXTENTS,'999,999,999,999')       \"Max extents\"
 1268 FROM DBA_TABLES
 1269    WHERE CLUSTER_NAME = '$object_name'
 1270    AND OWNER = '$schema'
 1271 ";
 1272 
 1273    $text = "Tables belonging to cluster $object_name";
 1274    $infotext = "No tables belong to cluster $object_name";
 1275    $link = "$scriptname?database=$database&schema=$schema&object_type=TABLE";
 1276    DisplayTable($sql,$text,$link,$infotext);
 1277     
 1278    logit("Exit subroutine showCluster");
 1279 
 1280 }
 1281    
 1282 
 1283 sub showTable {
 1284 
 1285    logit("Enter subroutine showTable");
 1286 
 1287    my ($sql,$text,$link,$infotext,$cursor,$isanalyzed,$partitioned,$grantcount,$foo);
 1288    my (@columns,$cols,$constraint_name,$column_name,$status,$index_name,$tablespace_name);
 1289    my ($indexes,$cursor1,$sql1,$uniqueness,$initial_extent,$next_extent,$max_extents);
 1290    my ($r_owner,$r_constraint_name,$count,$r_table_name,$iot_type,$temporary,$index_type);
 1291 
 1292    print <<"EOF";
 1293 <table class="ot">
 1294   <tr>
 1295     <td class="center">
 1296       <form method="GET" action="$scriptname">
 1297         <input type="HIDDEN" name="database" VALUE="$database">
 1298         <input type="HIDDEN" name="object_type" VALUE="TABLEROWS">
 1299         <input type="HIDDEN" name="schema" VALUE="$schema">
 1300         <input type="HIDDEN" name="arg" VALUE="$object_name">
 1301         <input type="SUBMIT" name="tablerows" VALUE="Display $rowdisplay rows of this table">
 1302         <br>
 1303         where
 1304         <br>
 1305         <input type="TEXT" SIZE=30 NAME="whereclause">
 1306       </form>
 1307     </td>
 1308   </tr>
 1309 </table>
 1310 EOF
 1311 
 1312    $grantcount = showGrantButton();
 1313 
 1314    unless ($grantcount) {
 1315       print "<BR>";
 1316    }
 1317 
 1318    print <<"EOF";
 1319 <table>
 1320   <tr>
 1321     <td>
 1322       <form method=POST action="$scriptname" target="_blank">
 1323         <input type="HIDDEN" NAME="database" VALUE="$database">
 1324         <input type="HIDDEN" NAME="object_type" VALUE="OBJECTDDL">
 1325         <input type="HIDDEN" NAME="schema" VALUE="$schema">
 1326         <input type="HIDDEN" NAME="object_name" VALUE="$object_name">
 1327         <input type="HIDDEN" NAME="objecttype" VALUE="TABLE">
 1328         <input type="HIDDEN" NAME="everything" VALUE="Yep">
 1329         <input type="SUBMIT" NAME="foo" VALUE="Generate DDL">
 1330       </form>
 1331     </td>
 1332     <td>
 1333       <form method=POST action=$scriptname>
 1334         <input type="HIDDEN" NAME="database" VALUE="$database">
 1335         <input type="HIDDEN" NAME="object_type" VALUE="OBJECTFRAGMAP">
 1336         <input type="HIDDEN" NAME="schema" VALUE="$schema">
 1337         <input type="HIDDEN" NAME="arg" VALUE="$object_name">
 1338         <input type="SUBMIT" NAME="foo" VALUE="Extent mapping">
 1339       </form>
 1340     </td>
 1341   </tr>
 1342 </table>
 1343 EOF
 1344 
 1345    #
 1346    #  Flashback query
 1347    #
 1348 #   $sql = "Select column_name from dba_tab_columns where table_name = '$object_name' and owner='$schema' order by column_id asc";
 1349 #   $cursor = $dbh->prepare($sql);
 1350 #   $cursor->execute;
 1351 #   while ($column_name = $cursor->fetchrow_array) {
 1352 #      $cols = "$cols,$column_name";
 1353 #   }
 1354 #   $cursor->finish;
 1355 #   $cols =~ s/^,//;
 1356 #   logit("$cols");
 1357 #
 1358 #   $sql = "Select min(versions_starttime), max(versions_endtime) from $schema.$object_name versions";
 1359 
 1360 #   $sql = "Select versions_starttime, versions_endtime, versions_xid, versions_operation, $cols from $schema.$object_name versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME";
 1361 #   $text = "Flashback query";
 1362 #   $link = "";
 1363 #   DisplayTable($sql,$text,$link);
 1364 
 1365 # Table comments
 1366 
 1367    $sql = "$copyright
 1368 SELECT
 1369    COMMENTS                                      \"Comment\"
 1370    FROM DBA_TAB_COMMENTS
 1371 WHERE TABLE_NAME = '$object_name'
 1372 AND OWNER = '$schema'
 1373 ";
 1374 
 1375    $object_type = lc($object_type);
 1376    $text = "Comment on $object_type $object_name";
 1377    $link = "";
 1378    DisplayTable($sql,$text,$link) if( recordCount($dbh,$sql) );
 1379 
 1380 # Table structure
 1381 
 1382    $sql = "
 1383 Select 
 1384    dtc.column_name                      \"Column name\",
 1385    dtc.data_type                        \"Type\",
 1386    dtc.data_length                      \"Length\",
 1387    dtc.nullable                         \"Nullable\",
 1388    dtc.data_default                         \"Default\",
 1389    to_char(dtc.last_analyzed,'Month DD, YYYY - HH24:MI')    \"Last analyzed\",
 1390    dcc.comments                         \"Comments\"
 1391 from dba_tab_columns dtc, dba_col_comments dcc
 1392  where dtc.table_name = dcc.table_name
 1393  and dtc.column_name = dcc.column_name
 1394  and dtc.table_name = '$object_name'
 1395  and dtc.owner = '$schema'
 1396  order by column_id";
 1397 
 1398 #   $sql = "$copyright
 1399 #SELECT
 1400 #   COLUMN_NAME                  \"Column name\",
 1401 #   DECODE(
 1402 #      NULLABLE,
 1403 #         'N','Not Null',
 1404 #         'Y',''
 1405 #   )                            \"Null?\",
 1406 #   DATA_TYPE                    \"Type\",
 1407 #   DATA_LENGTH                  \"Data length\",
 1408 #   DATA_PRECISION               \"Precision\"
 1409 #FROM
 1410 #   DBA_TAB_COLUMNS
 1411 #WHERE TABLE_NAME = '$object_name'
 1412 #   $moresql
 1413 #ORDER BY COLUMN_ID
 1414 #";
 1415 
 1416 
 1417    $object_type = lc $object_type;
 1418    $text = "Structure of $object_type $object_name";
 1419    $link = "";
 1420    DisplayTable($sql,$text,$link);
 1421 
 1422 # Check to see if table has been analyzed.
 1423 
 1424    $sql = "$copyright
 1425 SELECT 
 1426    DISTINCT LAST_ANALYZED
 1427 FROM DBA_TAB_COLUMNS
 1428    WHERE TABLE_NAME = '$object_name'
 1429    AND OWNER = '$schema'
 1430 ";
 1431 
 1432    $cursor=$dbh->prepare($sql);
 1433    $cursor->execute;
 1434    $isanalyzed = $cursor->fetchrow_array;
 1435    $cursor->finish;
 1436 
 1437 # If the table has been analyzed, show some additional information
 1438 
 1439    if ($isanalyzed) {
 1440 
 1441       $sql = "$copyright
 1442 SELECT 
 1443    TO_CHAR((BLOCKS / (EMPTY_BLOCKS+BLOCKS)) *100,'999.99')||'%' \"Percent used\",
 1444    TO_CHAR(NUM_ROWS,'999,999,999,999')              \"Row count\",
 1445    TO_CHAR(BLOCKS,'999,999,999,999')                \"Blocks\",
 1446    TO_CHAR(EMPTY_BLOCKS,'999,999,999,999')          \"Empty blocks\",
 1447    TO_CHAR(AVG_SPACE,'999,999,999,999')             \"Average space\",
 1448    TO_CHAR(AVG_ROW_LEN,'999,999,999,999')           \"Average row length\",
 1449    TO_CHAR(CHAIN_CNT,'999,999,999,999')             \"Chain count\"
 1450 FROM DBA_TABLES 
 1451    WHERE TABLE_NAME = '$object_name'
 1452    AND OWNER = '$schema'
 1453 ";
 1454 
 1455       $text = "Analyzation info: $object_type $object_name";
 1456       $link = "";
 1457       DisplayTable($sql,$text,$link);
 1458    } else {
 1459       message("Table has never been analyzed. Extended info will not be shown.");
 1460    }
 1461 
 1462 # Gather some info for later queries
 1463 # These can be used to determine what 
 1464 # type of table we are dealing with.
 1465 # PARTITIONED: YES/NO
 1466 # IOT_TYPE: IOT/NULL
 1467 # TEMPORARY: Y/N
 1468 
 1469    $sql = "$copyright
 1470 SELECT
 1471    PARTITIONED,
 1472    IOT_TYPE,
 1473    TEMPORARY
 1474 FROM DBA_TABLES
 1475    WHERE TABLE_NAME = '$object_name'
 1476    AND OWNER = '$schema'
 1477 ";
 1478    $cursor = $dbh->prepare($sql);
 1479    $cursor->execute;
 1480    ($partitioned,$iot_type,$temporary) = $cursor->fetchrow_array;
 1481    $cursor->finish;
 1482 
 1483    if (defined $iot_type && $iot_type eq "IOT") {
 1484 
 1485       message("Table $object_name is an Index Organized Table.");
 1486 
 1487    } else {
 1488 
 1489 # General info
 1490 
 1491       $sql = "$copyright
 1492 SELECT 
 1493    A.TABLESPACE_NAME                    \"Tablespace\", 
 1494    TO_CHAR(B.CREATED,'Month DD, YYYY - HH24:MI')    \"Date created\",
 1495    TO_CHAR(B.LAST_DDL_TIME,'Month DD, YYYY - HH24:MI')  \"Last DDL time\",
 1496    TO_CHAR(A.EXTENTS,'999,999,999,999')         \"Extents\", 
 1497    TO_CHAR(A.INITIAL_EXTENT,'999,999,999,999')      \"Initial extent\", 
 1498    TO_CHAR(A.NEXT_EXTENT,'999,999,999,999')     \"Next extent\", 
 1499    TO_CHAR(A.MAX_EXTENTS,'999,999,999,999')     \"Max extents\",
 1500    TO_CHAR(A.BYTES,'999,999,999,999')           \"Bytes\",
 1501    A.PCT_INCREASE                   \"% increase\",
 1502    DECODE(C.CACHE,
 1503         '    Y','Yes',
 1504                 '    N','No')               \"Cache?\"
 1505 FROM DBA_SEGMENTS A, DBA_OBJECTS B, DBA_TABLES C
 1506    WHERE A.SEGMENT_NAME = '$object_name' 
 1507    AND A.SEGMENT_TYPE = 'TABLE' 
 1508    AND A.OWNER = '$schema'
 1509    AND B.OBJECT_NAME = '$object_name'
 1510    AND B.OBJECT_TYPE = 'TABLE'
 1511    AND B.OWNER = '$schema'
 1512    AND C.TABLE_NAME = '$object_name'
 1513    AND C.OWNER = '$schema'
 1514 ";
 1515 
 1516       $object_type = lc $object_type;
 1517       $text = "General info: $object_type $schema.$object_name";
 1518       $link = "$scriptname?database=$database&object_type=TSINFO";
 1519       DisplayTable($sql,$text,$link);
 1520 
 1521    }
 1522 
 1523 # Check to see if table is partitioned
 1524 
 1525    if ($partitioned) {
 1526       
 1527       $sql = "$copyright
 1528 SELECT
 1529    PARTITIONED
 1530 FROM DBA_TABLES
 1531    WHERE TABLE_NAME = '$object_name'
 1532    AND OWNER = '$schema'
 1533 ";
 1534 
 1535       $cursor=$dbh->prepare($sql);
 1536       $cursor->execute;
 1537       $foo = $cursor->fetchrow_array;
 1538       $cursor->finish;
 1539       if ($foo eq "YES") {
 1540          $partitioned = "Yep";
 1541       }
 1542    }
 1543 
 1544 # If partitioned, show some additional info.
 1545 
 1546    if ($partitioned) {
 1547       $sql = "$copyright
 1548 SELECT
 1549    PARTITION_NAME                               \"Partition name\",
 1550    TABLESPACE_NAME                              \"Tablespace\",
 1551    PARTITION_POSITION                           \"Position\",
 1552    TO_CHAR(INITIAL_EXTENT,'999,999,999,999')    \"Initial\",
 1553    TO_CHAR(NEXT_EXTENT,'999,999,999,999')       \"Next\",
 1554    TO_CHAR(MAX_EXTENT,'999,999,999,999')        \"Max extents\",
 1555    TO_CHAR(NUM_ROWS,'999,999,999,999')          \"Num rows\",
 1556    PCT_INCREASE                                 \"Pct increase\",
 1557    HIGH_VALUE                                   \"High value\",
 1558    HIGH_VALUE_LENGTH                            \"High value length\",
 1559    LOGGING                                      \"Logging\"
 1560 FROM DBA_TAB_PARTITIONS
 1561    WHERE TABLE_NAME = '$object_name'
 1562    AND TABLE_OWNER = '$schema'
 1563 ORDER BY PARTITION_POSITION
 1564 ";
 1565 
 1566       $text = "Partitions contained in this table";
 1567       $link = "$scriptname?database=$database&schema=$schema&object_type=TABLE+PARTITION&table_name=$object_name";
 1568       DisplayTable($sql,$text,$link);
 1569    }
 1570 
 1571 # Show primary key (if)
 1572 
 1573    $sql = "$copyright
 1574 SELECT 
 1575    CONSTRAINT_NAME              \"Constraint name\",
 1576    STATUS                   \"Status\"
 1577 FROM DBA_CONSTRAINTS 
 1578    WHERE  CONSTRAINT_TYPE = 'P' 
 1579 AND TABLE_NAME = '$object_name' 
 1580 AND OWNER = '$schema'
 1581 ";
 1582    $cursor = $dbh->prepare($sql);
 1583    $cursor->execute;
 1584    (($constraint_name,$status) = $cursor->fetchrow);
 1585    $cursor->finish;
 1586    if ($constraint_name) {
 1587       $sql = "$copyright
 1588 SELECT COLUMN_NAME
 1589    FROM DBA_CONS_COLUMNS
 1590 WHERE CONSTRAINT_NAME = '$constraint_name'
 1591 AND OWNER = '$schema'
 1592    ORDER BY POSITION
 1593 ";
 1594       $cursor = $dbh->prepare($sql);
 1595       $cursor->execute;
 1596       while ($column_name = $cursor->fetchrow_array) {
 1597          push @columns, $column_name;
 1598       }
 1599       $cursor->finish;
 1600       if ($#columns > 0) {
 1601          $cols = join(",", @columns);
 1602          $cols =~ s/^,//;
 1603       } else {
 1604          $cols = $columns[0];
 1605       }
 1606       text("Primary key");
 1607    print <<"EOF";
 1608 <table class="ot">
 1609   <th class="center">Constraint name</th>
 1610   <th class="center">Status</th>
 1611   <th class="center">Column(s)</th>
 1612   <tr>
 1613      <td class="center"><A href=$scriptname?database=$database&schema=$schema&object_type=INDEX&arg=$constraint_name>$constraint_name</A></td>
 1614      <td class="center">$status</td>
 1615      <td class="center">$cols</td>
 1616   </tr>
 1617 </table>
 1618 EOF
 1619    } else {
 1620       message("This table has no primary key.");
 1621    }
 1622 
 1623 # Count indexes
 1624 
 1625    $sql = "$copyright
 1626 SELECT COUNT(*) 
 1627    FROM DBA_INDEXES
 1628 WHERE TABLE_NAME = '$object_name'
 1629    AND OWNER = '$schema'
 1630 ";
 1631    $cursor = $dbh->prepare($sql);
 1632    $cursor->execute;
 1633    while ($index_name = $cursor->fetchrow_array) {
 1634       $indexes++;
 1635    }
 1636    $cursor->finish;
 1637    
 1638    if ($indexes) {
 1639 
 1640       text("Indexes");
 1641 
 1642       print <<"EOF";
 1643 <table class="ot">
 1644   <th class="center">Index name</th>
 1645   <th class="center">Index type</th>
 1646   <th class="center">Status</th>
 1647   <th class="center">Column(s)</th>
 1648   <th class="center">Tablespace name</th>
 1649   <th class="center">Unique?</th>
 1650   <th class="center">Initial</th>
 1651   <th class="center">Next</th>
 1652   <th class="center">Max</th>
 1653 EOF
 1654       $sql = "$copyright
 1655 SELECT 
 1656    INDEX_NAME                   \"Index name\",
 1657    INDEX_TYPE                   \"Index type\",
 1658    STATUS                   \"Status\",
 1659    TABLESPACE_NAME              \"Tablespace name\",
 1660    DECODE(UNIQUENESS,
 1661       'UNIQUE','Yes',
 1662       'NONUNIQUE','No')             \"Unique?\",
 1663    TO_CHAR(INITIAL_EXTENT,'999,999,999,999')    \"Initial extent\",
 1664    TO_CHAR(NEXT_EXTENT,'999,999,999,999')   \"Next extent\",
 1665    TO_CHAR(MAX_EXTENTS,'999,999,999,999')   \"Max extents\"
 1666    FROM DBA_INDEXES
 1667 WHERE TABLE_NAME = '$object_name'
 1668    AND OWNER = '$schema'
 1669 ORDER BY 1
 1670 ";
 1671       $cursor = $dbh->prepare($sql);
 1672       $cursor->execute;
 1673       while (($index_name,$index_type,$status,$tablespace_name,$uniqueness,$initial_extent,$next_extent,$max_extents) = $cursor->fetchrow) {
 1674          undef @columns;
 1675          $indexes++;
 1676          $sql1 = "$copyright
 1677 SELECT COLUMN_NAME
 1678    FROM DBA_IND_COLUMNS
 1679 WHERE INDEX_NAME = '$index_name'
 1680 AND INDEX_OWNER = '$schema'
 1681    ORDER BY COLUMN_POSITION
 1682 ";
 1683          $cursor1 = $dbh->prepare($sql1);
 1684          $cursor1->execute;
 1685          while ($column_name = $cursor1->fetchrow_array) {
 1686             push @columns, $column_name;
 1687          }
 1688          $cursor1->finish;
 1689          if ($#columns > 0) {
 1690             $cols = join(",", @columns);
 1691             $cols =~ s/^,//;
 1692          } else {
 1693            $cols = $columns[0];
 1694          }
 1695          print <<"EOF";
 1696         <tr>
 1697           <td class="center"><a href=$scriptname?database=$database&schema=$schema&object_type=INDEX&arg=$index_name>$index_name</a></td>
 1698           <td class="center">$index_type</td>
 1699           <td class="center">$status</td>
 1700           <td class="center">$cols</td>
 1701           <td class="center">$tablespace_name</td>
 1702           <td class="center">$uniqueness</td>
 1703           <td class="center">$initial_extent</td>
 1704           <td class="center">$next_extent</td>
 1705           <td class="center">$max_extents</td>
 1706         </tr>
 1707 EOF
 1708       }
 1709          print <<"EOF";
 1710 </table>
 1711 EOF
 1712    } else {
 1713       message("This table has no indexes");
 1714    }
 1715 
 1716 # Show column constraints (if)
 1717 
 1718    $sql = "$copyright
 1719 SELECT 
 1720    CONSTRAINT_NAME              \"Constraint name\", 
 1721    SEARCH_CONDITION             \"Search condition\", 
 1722    STATUS                   \"Status\" 
 1723 FROM DBA_CONSTRAINTS 
 1724    WHERE CONSTRAINT_TYPE NOT IN ('P','R')
 1725 AND TABLE_NAME = '$object_name' 
 1726 AND OWNER = '$schema'
 1727    ORDER BY TABLE_NAME, CONSTRAINT_NAME
 1728 ";
 1729    $text = "Column constraints";
 1730    $link = "";
 1731    $infotext = "This table has no column constraints.";
 1732    DisplayTable($sql,$text,$link,$infotext);
 1733 
 1734 # Show foreign key constraints (if)
 1735 
 1736    $count = "";
 1737 
 1738    $sql = "$copyright
 1739 SELECT 
 1740    COUNT(*)
 1741 FROM DBA_CONSTRAINTS
 1742    WHERE CONSTRAINT_TYPE = 'R'
 1743    AND OWNER = '$schema'
 1744    AND TABLE_NAME = '$object_name'
 1745 ";
 1746    $cursor = $dbh->prepare($sql);
 1747    $cursor->execute;
 1748    $count = $cursor->fetchrow_array;
 1749    $cursor->finish;
 1750 
 1751    if ($count) {
 1752       text("Foreign key constraints");
 1753       print <<"EOF";
 1754 <table class="ot">
 1755    <th class="center">Constraint name</th>
 1756    <th class="center">Status</th>
 1757    <th class="center">Column(s)</th>
 1758    <th class="center">Ref owner</th>
 1759    <th class="center">Ref table</th>
 1760    <th class="center">Ref constraint</th>
 1761 EOF
 1762       $sql = "$copyright
 1763 SELECT
 1764    CONSTRAINT_NAME,
 1765    STATUS,
 1766    R_OWNER,
 1767    R_CONSTRAINT_NAME
 1768 FROM DBA_CONSTRAINTS
 1769    WHERE CONSTRAINT_TYPE = 'R'
 1770    AND OWNER = '$schema'
 1771    AND TABLE_NAME = '$object_name'
 1772 ";
 1773       $cursor = $dbh->prepare($sql);
 1774       $cursor->execute;
 1775       while (($constraint_name,$status,$r_owner,$r_constraint_name) = $cursor->fetchrow_array) {
 1776 # Add the columns
 1777          $sql1 = "$copyright
 1778    SELECT
 1779 COLUMN_NAME
 1780    FROM DBA_CONS_COLUMNS
 1781 WHERE OWNER = '$schema'
 1782 AND CONSTRAINT_NAME = '$constraint_name'
 1783 ";
 1784          $cursor1 = $dbh->prepare($sql1);
 1785          $cursor1->execute;
 1786          undef @columns;
 1787          while ($column_name = $cursor1->fetchrow_array) {
 1788             push @columns, $column_name;
 1789          }
 1790          $cursor1->finish;
 1791          if ($#columns > 0) {
 1792             $cols = join(",", @columns);
 1793             $cols =~ s/^,//;
 1794          } else {
 1795             $cols = $columns[0];
 1796          }
 1797 # Get the referenced table name 
 1798          $sql1 = "$copyright
 1799 SELECT
 1800    TABLE_NAME
 1801 FROM DBA_CONSTRAINTS
 1802    WHERE OWNER = '$r_owner'
 1803    AND CONSTRAINT_NAME = '$r_constraint_name'
 1804 ";
 1805          $cursor1 = $dbh->prepare($sql1);
 1806          $cursor1->execute;
 1807          $r_table_name = $cursor1->fetchrow_array;
 1808          $cursor1->finish;
 1809          print <<"EOF";
 1810         <tr>
 1811           <td class="center">$constraint_name</td>
 1812           <td class="center">$status</td>
 1813           <td class="center">$cols</td>
 1814           <td class="center">$r_owner</td>
 1815           <td class="center">$r_table_name</td>
 1816           <td class="center">$r_constraint_name</td>
 1817         </tr>
 1818 EOF
 1819       }
 1820       print <<"EOF";
 1821 </table>
 1822 EOF
 1823    } else {
 1824       message("This table has no foreign key constraints.");
 1825    }
 1826 
 1827 # Foreign keys referencing this table (if)
 1828 
 1829    $sql = "$copyright
 1830 SELECT
 1831    CONSTRAINT_NAME              \"Child constraint name\",
 1832    OWNER                    \"Child owner\",
 1833    TABLE_NAME                   \"Child table name\",
 1834    STATUS                   \"Status\",
 1835    R_CONSTRAINT_NAME                \"Local constraint name\"
 1836 FROM DBA_CONSTRAINTS
 1837    WHERE R_OWNER = '$schema'
 1838    AND R_CONSTRAINT_NAME IN 
 1839    ( SELECT
 1840         CONSTRAINT_NAME
 1841      FROM DBA_CONSTRAINTS
 1842         WHERE TABLE_NAME = '$object_name'
 1843         AND OWNER = '$schema')
 1844 ";
 1845 
 1846    $text = "Foreign key constraints referencing $object_name";
 1847    $link = "";
 1848    $infotext = "There are no foreign key constraints referencing this table.";
 1849    DisplayTable($sql,$text,$link,$infotext);
 1850 
 1851    $sql = "$copyright
 1852 SELECT
 1853    SYNONYM_NAME         \"Synonym name\",
 1854    OWNER            \"Owner\",
 1855    DB_LINK          \"DB link\"
 1856 FROM DBA_SYNONYMS
 1857    WHERE TABLE_NAME = '$object_name'
 1858    AND TABLE_OWNER = '$schema'
 1859 ";
 1860 
 1861    $text = "Synonyms pointing to this table.";
 1862    $link = "";
 1863    $infotext = "There are no synonyms pointing to this table.";
 1864    DisplayTable($sql,$text,$link,$infotext);
 1865    
 1866 
 1867    $sql = "$copyright
 1868 SELECT 
 1869    TRIGGER_NAME         \"Trigger name\",
 1870    STATUS           \"Status\",
 1871    NVL(DESCRIPTION,'No description') \"Description\",
 1872    TRIGGERING_EVENT     \"Event\",
 1873    WHEN_CLAUSE          \"When clause\"
 1874 FROM DBA_TRIGGERS
 1875    WHERE TABLE_NAME = '$object_name'
 1876    AND OWNER = '$schema'
 1877 ";
 1878    $text = "Triggers";
 1879    $link = "$scriptname?database=$database&schema=$schema&object_type=TRIGGER";
 1880    $infotext = "This table has no triggers.";
 1881    DisplayTable($sql,$text,$link,$infotext);
 1882 
 1883    logit("Exit subroutine showTable");
 1884 
 1885 }
 1886 
 1887 sub userDDL {
 1888 
 1889    logit("Enter subroutine userDDL");
 1890 
 1891 # This sub generates DDL to recreate a user.
 1892 # This sub needs to updated for Oracle8 / 8i
 1893 
 1894    my ($sql,$cursor,$password,$default_tablespace,$temporary_tablespace,$profile);
 1895    my ($max_bytes,$tablespace_name,$granted_role,$admin_option,$default_role,$ddl);
 1896    my ($privilege,$owner,$table_name,$grantable,$grantor,$sql1,$cursor1,@default_roles);
 1897    my ($roles);
 1898 
 1899    $sql = "$copyright
 1900 SELECT 
 1901    PASSWORD,
 1902    DEFAULT_TABLESPACE,
 1903    TEMPORARY_TABLESPACE,
 1904    PROFILE
 1905 FROM DBA_USERS 
 1906    WHERE USERNAME = '$schema'
 1907 ";   
 1908 
 1909    $cursor = $dbh->prepare($sql);
 1910    $cursor->execute;
 1911    ($password,$default_tablespace,$temporary_tablespace,$profile) = $cursor->fetchrow_array;
 1912    $cursor->finish;
 1913 
 1914 # Put in some remarks
 1915 
 1916    $ddl  = "/*\nDDL generated by Oracletool v$VERSION\n";
 1917    $ddl .= "for user $schema \n*/\n\n";
 1918    
 1919 # Create the SQL
 1920 
 1921    $ddl .= "
 1922 CREATE USER $schema
 1923    IDENTIFIED BY VALUES '$password'
 1924    DEFAULT TABLESPACE $default_tablespace
 1925    TEMPORARY TABLESPACE $temporary_tablespace
 1926    PROFILE $profile;
 1927 
 1928 ";
 1929 
 1930 # Add quotas
 1931 
 1932    $sql = "$copyright
 1933 SELECT 
 1934    MAX_BYTES,
 1935    TABLESPACE_NAME
 1936 FROM DBA_TS_QUOTAS
 1937    WHERE USERNAME = '$schema'
 1938 ";
 1939 
 1940    $cursor=$dbh->prepare($sql);
 1941    $cursor->execute;
 1942    while (($max_bytes,$tablespace_name) = $cursor->fetchrow_array) {
 1943       if ($max_bytes eq "-1") {
 1944          $max_bytes = "UNLIMITED";
 1945       }
 1946       $ddl .= "
 1947 ALTER USER $schema QUOTA $max_bytes ON $tablespace_name;";
 1948    }
 1949    $cursor->finish;
 1950 
 1951 # Add grants
 1952 # Roles first
 1953 
 1954    $sql = "$copyright
 1955 SELECT 
 1956    GRANTED_ROLE,
 1957    ADMIN_OPTION,
 1958    DEFAULT_ROLE
 1959 FROM DBA_ROLE_PRIVS
 1960    WHERE GRANTEE = '$schema'
 1961 ";
 1962 
 1963    $cursor = $dbh->prepare($sql);
 1964    $cursor->execute;
 1965    while (($granted_role,$admin_option,$default_role) = $cursor->fetchrow_array) {
 1966       $ddl .= "
 1967 GRANT $granted_role TO $schema";
 1968       if ($admin_option eq "YES") {
 1969          $ddl .= " WITH ADMIN OPTION;";
 1970       } else {
 1971          $ddl .= ";";
 1972       }
 1973       if ($default_role eq "YES") {
 1974          push @default_roles, $granted_role;
 1975       }
 1976    }
 1977    $cursor->finish;
 1978    if (@default_roles) {
 1979       $roles = join(",",@default_roles);
 1980       $ddl .= "\nALTER USER $schema DEFAULT ROLE $roles;";
 1981    }
 1982 
 1983 # Explicit system privileges
 1984 
 1985    $sql = "$copyright
 1986 SELECT 
 1987    PRIVILEGE,
 1988    ADMIN_OPTION
 1989 FROM DBA_SYS_PRIVS
 1990    WHERE GRANTEE = '$schema'
 1991 ";
 1992 
 1993    $cursor = $dbh->prepare($sql);
 1994    $cursor->execute;
 1995    while (($privilege,$admin_option) = $cursor->fetchrow_array) {
 1996             $ddl .= "
 1997 GRANT $privilege TO $schema";
 1998       if ($admin_option eq "YES") {
 1999          $ddl .= " WITH ADMIN OPTION;";
 2000       } else {
 2001          $ddl .= ";";
 2002       }
 2003    }
 2004 
 2005 # Explicit object privileges
 2006 
 2007    $sql = "$copyright
 2008 SELECT DISTINCT GRANTOR
 2009    FROM DBA_TAB_PRIVS 
 2010 WHERE GRANTEE = '$schema'
 2011 ";
 2012 
 2013    $cursor = $dbh->prepare($sql);
 2014    $cursor->execute;
 2015    while ($grantor = $cursor->fetchrow_array) {
 2016       $ddl .=  "\n\n/* Grants from $grantor */\n\n";
 2017       $ddl .=  "CONNECT $grantor/password\;n\n";
 2018       $sql1 = "$copyright
 2019 SELECT 
 2020    PRIVILEGE,
 2021    OWNER,
 2022    TABLE_NAME,
 2023    GRANTABLE
 2024 FROM DBA_TAB_PRIVS
 2025    WHERE GRANTOR = '$grantor'
 2026    AND GRANTEE = '$schema'
 2027    ORDER BY TABLE_NAME
 2028 ";
 2029 
 2030       $cursor1 = $dbh->prepare($sql1);
 2031       $cursor1->execute;
 2032       while (($privilege,$owner,$table_name,$grantable) = $cursor1->fetchrow) {
 2033          $ddl .=  "GRANT $privilege ON $owner.$table_name TO $schema";
 2034          if ($grantable eq "YES") {
 2035             $ddl .=  " WITH GRANT OPTION;\n";
 2036          } else {
 2037             $ddl .=  ";\n";
 2038          }
 2039       }
 2040       $cursor1->finish;
 2041    }
 2042    $cursor->finish;
 2043 
 2044    print <<"EOF";
 2045 <textarea name=ddl rows=$textarea_h cols=$textarea_w wrap=off>
 2046 $ddl
 2047 </textarea>
 2048 EOF
 2049 
 2050    logit("Exit subroutine userDDL");
 2051 
 2052 }
 2053 
 2054 sub Describe {
 2055 
 2056    logit("Enter subroutine Describe");
 2057 
 2058    my ($sql,$moresql,$text,$link,$infotext); 
 2059    my ($cursor,$object_type);
 2060 
 2061    my $object_name = shift;
 2062 
 2063 # Get owner if one is specified
 2064 
 2065    if ($object_name =~ /\./) {
 2066       logit("   Object requested has schema name prepended.");
 2067       ($schema, $object_name) = split(/\./,$object_name);
 2068       $schema = uc($schema);
 2069       $moresql = "AND OWNER = '$schema'";
 2070    } else {
 2071       $schema = uc($schema);
 2072       $moresql = "AND OWNER = '$schema'";
 2073    }
 2074    $object_name = uc($object_name);
 2075 
 2076    logit("   Describing object $schema.$object_name");
 2077 
 2078    $sql = "$copyright
 2079 SELECT
 2080    OBJECT_TYPE
 2081 FROM DBA_OBJECTS WHERE
 2082    OBJECT_NAME = '$object_name'
 2083    $moresql
 2084 ";
 2085   
 2086    $cursor = $dbh->prepare($sql);
 2087    $cursor->execute;
 2088    $object_type = $cursor->fetchrow_array;
 2089    $cursor->finish;
 2090 
 2091    if ($object_type) {
 2092       logit("   Object type is $object_type");
 2093    } else {
 2094       logit("   Object was not found.");
 2095       logit("   Checking for a public synonym.");
 2096       $sql = "$copyright
 2097 SELECT
 2098    OBJECT_TYPE
 2099 FROM DBA_OBJECTS WHERE
 2100    OBJECT_NAME = '$object_name'
 2101    AND OWNER = 'PUBLIC'
 2102 ";
 2103       $cursor = $dbh->prepare($sql);
 2104       $cursor->execute;
 2105       $object_type = $cursor->fetchrow_array;
 2106       $cursor->finish;
 2107       if ($object_type) {
 2108          $schema = "PUBLIC";
 2109       }
 2110    }
 2111 
 2112    if ($object_type eq "SYNONYM") {
 2113 
 2114        logit("   Object $schema.$object_name is a synonym.");
 2115        $sql = "
 2116 SELECT
 2117    TABLE_OWNER,
 2118    TABLE_NAME
 2119 FROM DBA_SYNONYMS
 2120    WHERE SYNONYM_NAME = '$object_name'
 2121    AND OWNER = '$schema'
 2122 ";
 2123 
 2124       $cursor = $dbh->prepare($sql);
 2125       $cursor->execute;
 2126       ($schema,$object_name) = $cursor->fetchrow_array;
 2127       $moresql = "AND OWNER = UPPER('$schema')";
 2128       $cursor->finish;
 2129    }
 2130 
 2131    $sql = "$copyright
 2132 SELECT 
 2133    COLUMN_NAME          \"Column name\",
 2134    DECODE(
 2135       NULLABLE,
 2136          'N','Not Null',
 2137          'Y',''
 2138    )                \"Null?\",
 2139    DATA_TYPE            \"Type\",
 2140    DATA_LENGTH          \"Data length\",
 2141    DATA_PRECISION       \"Precision\"
 2142 FROM 
 2143    DBA_TAB_COLUMNS
 2144 WHERE TABLE_NAME = '$object_name'
 2145    $moresql
 2146 ORDER BY COLUMN_ID
 2147 ";
 2148 
 2149    $text = "Description of $schema.$object_name";
 2150    $link = "";
 2151    $infotext = "Object to be described does not exist";
 2152    my $err = DisplayTable($sql,$text,$link,$infotext);
 2153 
 2154    print "<BR><HR WIDTH=\"10%\"><BR>\n";
 2155 
 2156    logit("Exit subroutine Describe");
 2157 
 2158 }
 2159 
 2160 sub objectSearch {
 2161 
 2162    logit("Enter subroutine objectSearch");
 2163 
 2164    my ($sql,$text,$link,$infotext,$moresql,$count);
 2165    my ($obj_name,$object_type,$owner,$object_id);
 2166    my ($object_found,$filenum,$block_id,$obj_name_url);
 2167    my ($object_type_url);
 2168 
 2169 # Search for an object in the entire database
 2170 
 2171 # Check for a null value
 2172 
 2173    if ($object_name eq "") {
 2174       message("You must enter an object name!\n");
 2175       Footer();
 2176       exit;
 2177    } else {
 2178       $object_name = uc($object_name);
 2179    }
 2180   
 2181    if ($object_name =~ /,/) {
 2182 # Search for an object based on FILE#, BOCK_ID
 2183       ($filenum,$block_id) = split(",",$object_name);
 2184       logit("   Filenum is $filenum, Block ID is $block_id");
 2185       $sql = "
 2186 SELECT
 2187    SEGMENT_TYPE                 \"Object type\",
 2188    SEGMENT_NAME                 \"Object name\",
 2189    OWNER                    \"Owner\",
 2190    TABLESPACE_NAME              \"Tablespace name\",
 2191    EXTENT_ID                    \"Extent ID\",
 2192    TO_CHAR(BYTES,'999,999,999,999')     \"Bytes\",
 2193    BLOCK_ID                 \"Block ID\",
 2194    BLOCK_ID+BLOCKS-1                \"Blocks\"
 2195 FROM DBA_EXTENTS
 2196    WHERE FILE_ID = $filenum
 2197    AND $block_id BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1
 2198 ";
 2199 
 2200       $text = "Object found with FILE# $filenum BLOCK_ID $block_id";
 2201       $link = "";
 2202       $infotext = "No object found with FILE# $filenum BLOCK_ID $block_id";
 2203       DisplayTable($sql,$text,$link,$infotext);
 2204 
 2205       $sql = "
 2206 SELECT
 2207    FILE_NAME
 2208 FROM DBA_DATA_FILES
 2209    WHERE FILE_ID = $filenum
 2210 ";
 2211 
 2212       $text = "Object exists in this datafile.";
 2213       $link = "";
 2214       $infotext = "";
 2215       DisplayTable($sql,$text,$link,$infotext);
 2216      
 2217       Footer();
 2218 
 2219    }
 2220 
 2221 # Check to see if it is a username
 2222 
 2223    $sql = "$copyright
 2224 SELECT  
 2225    USERNAME         \"Username\"
 2226 FROM DBA_USERS 
 2227    WHERE USERNAME = UPPER('$object_name')
 2228 ";
 2229 
 2230    $text = "A username matches your search.";
 2231    $link = "$scriptname?database=$database&schema=$object_name&object_type=USERINFO";
 2232    $infotext = "No usernames match your search keyword";
 2233    DisplayTable($sql,$text,$link,$infotext);
 2234 
 2235 # Check to see if it is a tablespace
 2236 
 2237    $sql = "$copyright
 2238 SELECT  
 2239    TABLESPACE_NAME      \"Tablespace name\"
 2240 FROM DBA_TABLESPACES 
 2241    WHERE TABLESPACE_NAME = UPPER('$object_name')
 2242 ";
 2243 
 2244    $text = "A tablespace name matches your search.";
 2245    $link = "$scriptname?database=$database&schema=$object_name&object_type=TSINFO";
 2246    $infotext = "No tablespace names match your search keyword";
 2247    DisplayTable($sql,$text,$link,$infotext);
 2248 
 2249 # Check to see if it is a constraint
 2250 
 2251    $sql = "$copyright
 2252 Select 
 2253    table_name           \"Table name\",
 2254    r_owner          \"Owner\",
 2255    constraint_name      \"Constraint name\",
 2256    decode(constraint_type,
 2257       'C','Check',
 2258       'P','Primary key',
 2259       'U','Unique',
 2260       'R','Referential',
 2261       'V','View - Check option',
 2262       'O','View - Read-only',
 2263       'H','Hash expression',
 2264       'F','Constraint that involves a REF column',
 2265       'S','Supplemental logging')   \"Constraint type\"
 2266    from dba_constraints where constraint_name like upper('%$object_name%')";
 2267 
 2268    $text = "A constraint name matches your search.";
 2269    $link = "";
 2270    $infotext = "No constraint names match your search keyword";
 2271    DisplayTable($sql,$text,$link,$infotext);
 2272 
 2273 # Get owner if one is specified
 2274 
 2275    $_ = $object_name;
 2276    if (/\./) {
 2277       ($schema, $object_name) = split /\./;
 2278       $moresql = "AND OWNER = UPPER('$schema')";
 2279    }
 2280 
 2281    $sql = "$copyright
 2282 SELECT
 2283    OBJECT_NAME                  \"Object name\",
 2284    OBJECT_TYPE                  \"Object type\",
 2285    OWNER                        \"Owner\",
 2286    OBJECT_ID            \"Object ID\"
 2287 FROM DBA_OBJECTS
 2288    WHERE UPPER(OBJECT_NAME) LIKE UPPER('\%$object_name\%')
 2289    AND OBJECT_TYPE NOT LIKE '%PARTITION'
 2290    $moresql
 2291 ORDER BY 1,2,3
 2292 ";
 2293 
 2294    $cursor = $dbh->prepare($sql);
 2295    $cursor->execute;
 2296    $object_found = $cursor->fetchrow_array;
 2297    $cursor->finish;
 2298 
 2299    if ($object_found) {
 2300 
 2301       text("The following 'LIKE' objects were found.");
 2302 
 2303    # Print the heading
 2304 
 2305       print <<"EOF";
 2306   <FORM METHOD=POST ACTION=$scriptname>
 2307     
 2308     <input type="SUBMIT" NAME="foobar" VALUE="Show marked object dependencies">
 2309     <input type="HIDDEN" NAME="database" VALUE="$database">
 2310     <input type="HIDDEN" NAME="object_type" VALUE="DBADMIN">
 2311     <input type="HIDDEN" NAME="arg" VALUE="dependencies">
 2312 <p>
 2313 <table class="ot">
 2314   <th class="center">Mark</th>
 2315   <th class="center">Object name</th>
 2316   <th class="center">Object type</th>
 2317   <th class="center">Owner</th>
 2318 EOF
 2319 
 2320       $cursor = $dbh->prepare($sql);
 2321       $cursor->execute;
 2322 
 2323       while (($obj_name,$object_type,$owner,$object_id) = $cursor->fetchrow) {
 2324          $object_type_url = $object_type;
 2325          $object_type_url =~ s/ /+/g;
 2326          $obj_name_url = $obj_name;
 2327          $obj_name_url =~ s/ /+/g;
 2328          $obj_name_url =~ s/#/\%23/g;
 2329          $obj_name_url =~ s/</\%3C/g;
 2330          $obj_name_url =~ s/>/\%3E/g;
 2331          $obj_name_url =~ s/{/\%7B/g;
 2332          $obj_name_url =~ s/\|/\%7C/g;
 2333          $obj_name_url =~ s/}/\%7D/g;
 2334          $obj_name_url =~ s/\\/\%5C/g;
 2335          $obj_name_url =~ s/\^/\%5E/g;
 2336          $obj_name_url =~ s/#/\%23/g;
 2337 # Object ID's are sometimes not returned because of database link naming conventions...
 2338          if ($object_id) {
 2339             logit("Object name: $obj_name Type: $object_type ID: $object_id");
 2340             print <<"EOF";
 2341   <tr>
 2342     <td class="center"><input type=CHECKBOX NAME=dependency~$object_id></td>
 2343     <td class="left"><A href=$scriptname?database=$database&arg=$obj_name_url&object_type=$object_type_url&schema=$owner>$obj_name</A></td>
 2344     <td class="center">$object_type</td>
 2345     <td class="center">$owner</td>
 2346   </tr>
 2347 EOF
 2348          }
 2349       }
 2350       $cursor->finish;
 2351       print <<"EOF";
 2352   </form>
 2353 </table>
 2354 EOF
 2355    } else {
 2356       message("No objects \"LIKE\" $object_name were found.");
 2357    }
 2358 
 2359 # If $object_name is a number, search by object_id as well.
 2360 
 2361    $_ = $object_name;
 2362    if ( ! /\D/ ) {
 2363 
 2364       $sql = "$copyright
 2365 SELECT
 2366    OBJECT_NAME                  \"Object name\",
 2367    OBJECT_TYPE                  \"Object type\",
 2368    OWNER                        \"Owner\"
 2369 FROM DBA_OBJECTS
 2370    WHERE OBJECT_ID = '$object_name'
 2371 ";
 2372 
 2373       $text = "The following object was found with object_id $object_name";
 2374       $link = "";
 2375       $infotext = "No objects were found with object_id $object_name";
 2376       ObjectTable($sql,$text,$infotext);
 2377    }
 2378 
 2379    if ($count && ! $ENV{'LIMIT_SEARCH'}) {
 2380       $sql = "$copyright
 2381 SELECT 
 2382    A.USERNAME       \"User accessing\",
 2383    A.OSUSER     \"OS Username\",
 2384    A.PROCESS        \"Process ID\", 
 2385    A.PROGRAM        \"Program\", 
 2386    B.SID        \"SID\",
 2387    A.SERIAL#        \"Serial#\",
 2388    B.OBJECT     \"Object name\",
 2389    B.OWNER      \"Owner\",
 2390    B.TYPE       \"Object type\"
 2391 FROM V\$SESSION A, V\$ACCESS B
 2392    WHERE B.OBJECT IN
 2393 (SELECT OBJECT_NAME 
 2394    FROM DBA_OBJECTS
 2395 WHERE OBJECT_NAME LIKE UPPER('\%$object_name\%') $moresql
 2396   AND A.SID = B.SID AND A.STATUS = 'ACTIVE')
 2397 ";
 2398 
 2399       $text = "Objects currently being accessed that match your search";
 2400       $link = "";
 2401       $infotext = "No objects that match your search are currently being accessed";
 2402       DisplayTable($sql,$text,$link,$infotext);
 2403 
 2404       logit("Exit subroutine objectSearch");
 2405 
 2406    }
 2407 }
 2408 
 2409 sub showConstraint {
 2410 
 2411    logit("Enter subroutine showConstraint");
 2412 
 2413    my ($sql,$text,$link);
 2414 
 2415 # Constraint info
 2416 
 2417    $sql = "$copyright
 2418 SELECT * FROM
 2419    (SELECT
 2420       TABLE_NAME                \"Table_name\",
 2421       CONSTRAINT_NAME               \"Constraint name\"
 2422     FROM DBA_CONSTRAINTS
 2423        WHERE OWNER = '$schema'
 2424        AND CONSTRAINT_NAME = '$object_name'),
 2425    (SELECT 
 2426        TABLE_NAME               \"Parent table\",
 2427        CONSTRAINT_NAME              \"Parent constraint\",
 2428        OWNER                    \"Parent owner\"
 2429     FROM DBA_CONSTRAINTS
 2430        WHERE CONSTRAINT_NAME = 
 2431     (SELECT R_CONSTRAINT_NAME 
 2432         FROM DBA_CONSTRAINTS
 2433      WHERE CONSTRAINT_NAME = '$object_name'
 2434         AND OWNER = '$schema'))
 2435 ";
 2436 
 2437    $text = "General info: Constraint $schema.$object_name";
 2438    $link = "";
 2439    DisplayTable($sql,$text,$link);
 2440 
 2441    logit("Exit subroutine showConstraint");
 2442 
 2443 }
 2444 
 2445 sub showView() {
 2446 
 2447    logit("Enter subroutine showView");
 2448 
 2449    my ($sql,$cursor,$status,$text,$infotext,$link,$foo,$object_id);
 2450 
 2451 print <<"EOF";
 2452 <table class="ot">
 2453   <tr>
 2454     <td class="center">
 2455       <form method="GET" action="$scriptname">
 2456         <input type="HIDDEN" name="database" VALUE="$database">
 2457         <input type="HIDDEN" name="object_type" VALUE="TABLEROWS">
 2458         <input type="HIDDEN" name="schema" VALUE="$schema">
 2459         <input type="HIDDEN" name="arg" VALUE="$object_name">
 2460         <input type="SUBMIT" name="tablerows" VALUE="Display $rowdisplay rows of this view">
 2461         <br>
 2462         where
 2463         <br>
 2464         <input type="TEXT" SIZE=30 NAME="whereclause">
 2465       </form>
 2466     </td>
 2467   </tr>
 2468 </table>
 2469 EOF
 2470 
 2471 showGrantButton();
 2472 
 2473 #  Comments.
 2474 
 2475    $sql = "$copyright
 2476 SELECT
 2477    COMMENTS                                      \"Comment\"
 2478    FROM DBA_TAB_COMMENTS
 2479 WHERE (TABLE_NAME = '$object_name')
 2480 AND (OWNER = '$schema')
 2481 ";
 2482 
 2483 $object_type = lc $object_type;
 2484 $text = "Comment on $object_type $object_name";
 2485 $link = "";
 2486 DisplayTable($sql,$text,$link) if( recordCount($dbh,$sql) );
 2487 
 2488 $object_type = uc $object_type;
 2489 
 2490 # General info
 2491 
 2492    $sql = "$copyright
 2493 SELECT
 2494    TO_CHAR(CREATED,'Month DD, YYYY - HH24:MI')          \"Date created\",
 2495    TO_CHAR(LAST_DDL_TIME,'Month DD, YYYY - HH24:MI')    \"Last compiled\",
 2496    STATUS                                               \"Status\"
 2497 FROM DBA_OBJECTS
 2498    WHERE OBJECT_NAME = '$object_name'
 2499    AND OBJECT_TYPE = '$object_type'
 2500    AND OWNER = '$schema'
 2501 ";
 2502 
 2503    $text = "General info: $object_type $schema.$object_name";
 2504    DisplayTable($sql,$text);
 2505 
 2506    checkValidity();
 2507 
 2508    $object_type = lc $object_type;
 2509 
 2510 # View structure
 2511 
 2512    $sql = "$copyright
 2513 SELECT
 2514    A.COLUMN_NAME                                  \"Column name\",
 2515    A.DATA_TYPE                                    \"Type\",
 2516    A.DATA_LENGTH                                  \"Length\",
 2517    B.COMMENTS                                     \"Comments\"
 2518 FROM DBA_TAB_COLUMNS A, DBA_COL_COMMENTS B
 2519    WHERE A.TABLE_NAME = '$object_name'
 2520    AND A.OWNER = '$schema'
 2521    AND A.TABLE_NAME = B.TABLE_NAME
 2522    AND A.OWNER = B.OWNER
 2523    AND A.COLUMN_NAME = B.COLUMN_NAME
 2524 ORDER BY A.COLUMN_ID
 2525 ";
 2526 
 2527    $object_type = lc $object_type;
 2528    $text = "Structure of $object_type $object_name";
 2529    $link = "";
 2530    DisplayTable($sql,$text,$link);
 2531 
 2532    $sql = "$copyright
 2533 SELECT
 2534    SYNONYM_NAME                 \"Synonym name\",
 2535    OWNER                        \"Owner\",
 2536    DB_LINK                      \"DB link\"
 2537 FROM DBA_SYNONYMS
 2538    WHERE TABLE_NAME = '$object_name'
 2539    AND TABLE_OWNER = '$schema'
 2540 ";
 2541 
 2542    $text = "Synonyms pointing to this view.";
 2543    $link = "";
 2544    $infotext = "There are no synonyms pointing to this view.";
 2545    DisplayTable($sql,$text,$link,$infotext);
 2546 
 2547    $sql = "
 2548 SELECT
 2549    OBJECT_ID 
 2550 FROM DBA_OBJECTS
 2551    WHERE
 2552 OBJECT_NAME = '$object_name'
 2553 AND OWNER = '$schema'
 2554 AND OBJECT_TYPE = 'VIEW'
 2555 ";
 2556    $cursor = $dbh->prepare($sql);
 2557    $cursor->execute;
 2558    $object_id = $cursor->fetchrow_array;
 2559    $cursor->finish;
 2560 
 2561    showDependencies($object_id);
 2562 
 2563 # View source
 2564 
 2565    $sql = "$copyright
 2566 SELECT 
 2567    TEXT                     \"Text\"    
 2568 FROM DBA_VIEWS 
 2569    WHERE VIEW_NAME = '$object_name' 
 2570    AND OWNER = '$schema'";
 2571    $text = "Text: $object_type $object_name";
 2572    DisplayPiecedData($sql,$text);
 2573 
 2574    logit("Exit subroutine showView");
 2575 
 2576 }
 2577 
 2578 sub checkValidity {
 2579 
 2580    logit("Enter subroutine checkValidity");
 2581 
 2582    my ($sql,$cursor,$status,$text);
 2583 
 2584    # Check for validity. If invalid, show additional info.
 2585 
 2586    $object_type = uc($object_type);
 2587 
 2588    $sql = "$copyright
 2589 SELECT
 2590    STATUS
 2591 FROM DBA_OBJECTS
 2592    WHERE OBJECT_NAME = '$object_name'
 2593    AND OBJECT_TYPE = '$object_type'
 2594    AND OWNER = '$schema'
 2595 ";
 2596 
 2597    $cursor = $dbh->prepare($sql);
 2598    $cursor->execute;
 2599    $status = $cursor->fetchrow_array;
 2600    $cursor->finish;
 2601 
 2602    if ($status eq "INVALID" or $status eq "UNUSABLE") {
 2603 
 2604       $sql = "$copyright
 2605 SELECT
 2606    LINE         \"Line\",
 2607    POSITION     \"Position\",
 2608    TEXT         \"Text\"
 2609 FROM DBA_ERRORS
 2610    WHERE NAME = '$object_name'
 2611    AND TYPE = '$object_type'
 2612    AND OWNER = '$schema'
 2613 ORDER BY SEQUENCE
 2614 ";
 2615       $text = "Errors";
 2616       DisplayTable($sql,$text);
 2617    }
 2618 
 2619    logit("Exit subroutine checkValidity");
 2620 }
 2621 
 2622 sub showTrigger {
 2623 
 2624    logit("Enter subroutine showTrigger");
 2625 
 2626    my ($sql,$text,$link);
 2627 
 2628 # General info
 2629 
 2630 $sql = "$copyright
 2631 SELECT 
 2632    TRIGGER_NAME                 \"Trigger name\",
 2633    TRIGGER_TYPE                 \"Trigger type\",
 2634    TRIGGERING_EVENT             \"Triggering event\",
 2635    REFERENCING_NAMES                \"Referencing names\",
 2636    WHEN_CLAUSE                  \"When clause\",
 2637    STATUS                   \"Status\"
 2638 FROM DBA_TRIGGERS
 2639    WHERE TRIGGER_NAME = '$object_name'
 2640    AND OWNER = '$schema'
 2641 ";
 2642 
 2643    $text = "Trigger: $object_name";
 2644    DisplayTable($sql,$text,$link);
 2645 
 2646    checkValidity();
 2647 
 2648 # Source
 2649 
 2650 $sql = "$copyright
 2651 SELECT
 2652     TRIGGER_BODY                \"Trigger body\"
 2653 FROM DBA_TRIGGERS
 2654    WHERE TRIGGER_NAME = '$object_name'
 2655 AND OWNER = '$schema'
 2656 ";
 2657 
 2658    $text = "Trigger body";
 2659    DisplayPiecedData($sql,$text);
 2660 
 2661    logit("Exit subroutine showTrigger");
 2662 
 2663 }
 2664 
 2665 sub showDBlink() {
 2666 
 2667    logit("Enter subroutine showDBlink");
 2668 
 2669    my ($sql,$text,$link,$infotext);
 2670 
 2671 # General info
 2672 
 2673    $sql = "$copyright
 2674 SELECT 
 2675    DB_LINK                  \"Link name\", 
 2676    USERNAME                 \"Username\", 
 2677    HOST                     \"Host\", 
 2678    CREATED                  \"Created\" 
 2679 FROM DBA_DB_LINKS 
 2680    WHERE DB_LINK = '$object_name' 
 2681    AND OWNER = '$schema'"
 2682 ;
 2683    $text = "Database link: $object_name";
 2684    $link = "";
 2685    DisplayTable($sql,$text,$link);
 2686 
 2687    $sql = "
 2688 Select distinct
 2689    owner    \"Owner\", 
 2690    name     \"Object name\",
 2691    type     \"Object type\"
 2692 from dba_dependencies 
 2693    where referenced_link_name = '$object_name'
 2694    and referenced_owner = '$schema'";
 2695 
 2696    $text    = "The following objects have a dependency on this database link (does not include views!)";
 2697    $infotext    = "There are no objects with dependencies on this link. Note that the dba_dependencies view that this depends on does not include views.";
 2698    DisplayTable($sql,$text,$link,$infotext);
 2699 
 2700    logit("Exit subroutine showDBlink");
 2701 
 2702 }
 2703 
 2704 sub showSource() {
 2705 
 2706    logit("Enter subroutine showSource");
 2707 
 2708    my ($sql,$cursor,$status,$text,$infotext,$link,$object_id);
 2709 
 2710    showGrantButton();
 2711 
 2712 # General info
 2713 
 2714    $sql = "$copyright
 2715 SELECT
 2716    TO_CHAR(CREATED,'Month DD, YYYY - HH24:MI')          \"Date created\",
 2717    TO_CHAR(LAST_DDL_TIME,'Month DD, YYYY - HH24:MI')    \"Last compiled\",
 2718    STATUS                       \"Status\"
 2719 FROM DBA_OBJECTS
 2720    WHERE OBJECT_NAME = '$object_name'
 2721    AND OBJECT_TYPE = '$object_type'
 2722    AND OWNER = '$schema'
 2723 ";
 2724 
 2725    $text = "General info: $object_type $schema.$object_name";
 2726    DisplayTable($sql,$text);
 2727 
 2728    checkValidity();
 2729 
 2730    $sql = "$copyright
 2731 SELECT
 2732    SYNONYM_NAME                 \"Synonym name\",
 2733    OWNER                        \"Owner\",
 2734    DB_LINK                      \"DB link\"
 2735 FROM DBA_SYNONYMS
 2736    WHERE TABLE_NAME = '$object_name'
 2737    AND TABLE_OWNER = '$schema'
 2738 ";
 2739 
 2740    $text = "Synonyms pointing to this object.";
 2741    $link = "";
 2742    $infotext = "There are no synonyms pointing to this object.";
 2743    DisplayTable($sql,$text,$link,$infotext);
 2744 
 2745    $sql = "
 2746 Select object_id from dba_objects
 2747    where object_name = '$object_name'
 2748    and object_type = '$object_type'
 2749    and owner = '$schema'";
 2750 
 2751    $cursor = $dbh->prepare($sql);
 2752    $cursor->execute;
 2753    $object_id = $cursor->fetchrow_array;
 2754    $cursor->finish;
 2755 
 2756    showDependencies($object_id);
 2757 
 2758 # Source of object (package, procedure, etc.)
 2759 
 2760    $sql = "$copyright
 2761 SELECT 
 2762    TEXT
 2763 FROM 
 2764    DBA_SOURCE 
 2765 WHERE TYPE = '$object_type' 
 2766    AND OWNER = '$schema' 
 2767    AND NAME = '$object_name' 
 2768 ORDER BY LINE
 2769 ";
 2770    $text = "Text: $object_type $object_name";
 2771    $link = "";
 2772    DisplayPiecedData($sql,$text,$link);
 2773 
 2774    logit("Exit subroutine showSource");
 2775 }
 2776 
 2777 sub showSequence() {
 2778 
 2779    my ($sql,$text,$link);
 2780 
 2781    logit("Enter subroutine showSequence");
 2782 
 2783    showGrantButton();
 2784 
 2785 # General info
 2786 
 2787    $sql = "$copyright
 2788 SELECT 
 2789    MIN_VALUE                    \"Min value\", 
 2790    MAX_VALUE                    \"Max value\", 
 2791    INCREMENT_BY                 \"Increment by\", 
 2792    CYCLE_FLAG                   \"Cycle flag\", 
 2793    ORDER_FLAG                   \"Order flag\", 
 2794    CACHE_SIZE                   \"Cache size\", 
 2795    LAST_NUMBER                  \"Last number\" 
 2796 FROM DBA_SEQUENCES 
 2797    WHERE SEQUENCE_NAME = '$object_name' 
 2798    AND SEQUENCE_OWNER = '$schema'
 2799 ";
 2800    $text = "$object_type $object_name";
 2801    $link = "";
 2802    DisplayTable($sql,$text,$link);
 2803 
 2804    logit("Enter subroutine showSequence");
 2805 
 2806 }
 2807 
 2808 sub showGrantsto() {
 2809 
 2810    my ($sql,$text,$link,$infotext);
 2811 
 2812    logit("Enter subroutine showGrantsto");
 2813 
 2814 # System privileges
 2815 
 2816    $sql = "$copyright
 2817 SELECT 
 2818    PRIVILEGE                    \"Privilege\", 
 2819    ADMIN_OPTION                 \"Admin option\"
 2820 FROM DBA_SYS_PRIVS 
 2821    WHERE GRANTEE = '$schema'
 2822 ";
 2823    $text = "System privileges granted to $schema";
 2824    $link = "";
 2825    $infotext = "There are no system privileges granted to $schema.";
 2826    DisplayTable($sql,$text,$link,$infotext);
 2827 
 2828 # Granted roles
 2829 
 2830    $sql = "$copyright
 2831 SELECT 
 2832    GRANTED_ROLE                 \"Granted role\", 
 2833    ADMIN_OPTION                 \"Admin option\", 
 2834    DEFAULT_ROLE                 \"Default role\" 
 2835 FROM DBA_ROLE_PRIVS 
 2836    WHERE GRANTEE = '$schema'
 2837 ";
 2838    $text = "Roles granted to $schema";
 2839    $link = "$scriptname?database=$database&schema=$schema&object_type=ROLES";
 2840    $infotext = "There are no roles granted to $schema.";
 2841    DisplayTable($sql,$text,$link,$infotext);
 2842 
 2843 # Directory privileges
 2844 
 2845    $sql = "
 2846 Select
 2847    table_name                                   \"Directory name\",
 2848    privilege                                    \"Privilege\"
 2849 from dba_tab_privs 
 2850    where grantee = '$schema'
 2851    and table_name in 
 2852       (Select directory_name from dba_directories)
 2853 order by 1";
 2854 
 2855    $text = "Directory privileges granted to $schema";
 2856    $link = "";
 2857    $infotext = "There are no directory privileges granted to $schema.";
 2858    DisplayTable($sql,$text,$link,$infotext);
 2859 
 2860 # Granted java privileges (explicit)
 2861 
 2862    $sql = "
 2863 Select 
 2864    kind                     \"Kind\",
 2865    type_name                    \"Type name\",
 2866    name                     \"Name\",
 2867    action                   \"Action\",
 2868    enabled                  \"Enabled?\",
 2869    seq                      \"Sequence\"
 2870 from dba_java_policy
 2871    where grantee = '$schema'
 2872 ";
 2873 
 2874    $text = "Java privileges granted to $schema<br>Use the sequence to drop permissions<br>Example:<br>dbms_java.disable_permission(sequence);<br>dbms_java.delete_permission(sequence);";
 2875    $link = "";
 2876    $infotext = "There are no java privileges granted to $schema.";
 2877    DisplayTable($sql,$text,$link,$infotext);
 2878 
 2879 # Granted object privileges (explicit)
 2880 
 2881    $sql = "$copyright
 2882 SELECT 
 2883    PRIVILEGE                    \"Privilege\", 
 2884    TABLE_NAME                   \"Table name\", 
 2885    GRANTOR                  \"Grantor\", 
 2886    GRANTABLE                    \"Grantable\" 
 2887 FROM DBA_TAB_PRIVS 
 2888    WHERE GRANTEE = '$schema' 
 2889 ORDER BY 2, 1, 3
 2890 ";
 2891    $text = "Explicit grants to $schema";
 2892    $link = "";
 2893    $infotext = "There are no explicit grants to $schema.";
 2894    DisplayTable($sql,$text,$link,$infotext);
 2895 
 2896    logit("Exit subroutine showGrantsto");
 2897 
 2898 }
 2899 
 2900 sub showRoles {
 2901 
 2902    logit("Enter subroutine showRoles");
 2903 
 2904    my ($sql,$text,$link,$infotext);
 2905 
 2906    $sql = "$copyright
 2907 SELECT
 2908    GRANTEE      \"Granted user\"
 2909 FROM DBA_ROLE_PRIVS
 2910    WHERE GRANTED_ROLE = '$object_name'
 2911    AND GRANTEE IN (
 2912 SELECT USERNAME 
 2913    FROM DBA_USERS
 2914 ) ORDER BY 1
 2915 ";
 2916 
 2917    $text = "Users which are granted this role.";
 2918    $link = "$scriptname?database=$database&object_type=USERINFO";
 2919    $infotext = "No users are granted this role.";
 2920    DisplayColTable($sql,$text,$link,$infotext,$schema_cols);
 2921 
 2922    $sql = "$copyright
 2923 SELECT
 2924    GRANTEE      \"Granted user\"
 2925 FROM DBA_ROLE_PRIVS
 2926    WHERE GRANTED_ROLE = '$object_name'
 2927    AND GRANTEE IN (
 2928 SELECT ROLE 
 2929    FROM DBA_ROLES
 2930 )
 2931 ";
 2932 
 2933    $text = "Roles which are granted this role.";
 2934    $link = "";
 2935    $infotext = "No roles are granted this role.";
 2936    DisplayTable($sql,$text,$link,$infotext);
 2937 
 2938 # Roles granted to this role
 2939 
 2940    $sql = "$copyright
 2941 SELECT 
 2942    GRANTED_ROLE                 \"Granted role\", 
 2943    ADMIN_OPTION                 \"Admin option\", 
 2944    DEFAULT_ROLE                 \"Default role\" 
 2945 FROM DBA_ROLE_PRIVS 
 2946    WHERE GRANTEE = '$object_name'
 2947 ";
 2948    $text = "Roles granted to role $object_name"; 
 2949    $link = "$scriptname?database=$database&object_type=ROLES"; 
 2950    $infotext = "There are no roles granted to this role.";
 2951    DisplayTable($sql,$text,$link,$infotext);
 2952 
 2953 # System privileges granted to this role
 2954 
 2955    $sql = "$copyright
 2956 SELECT 
 2957    PRIVILEGE                    \"Privilege\", 
 2958    ADMIN_OPTION                 \"Admin option\" 
 2959 FROM DBA_SYS_PRIVS 
 2960    WHERE GRANTEE = '$object_name' 
 2961 ORDER BY PRIVILEGE
 2962 ";
 2963    $text = "System privileges granted to role $object_name";
 2964    $link = "";
 2965    $infotext = "There are no system privileges granted to this role.";
 2966    DisplayTable($sql,$text,$link,$infotext);
 2967 
 2968 # Object privileges granted to this role
 2969 
 2970    $sql = "$copyright
 2971 SELECT 
 2972    PRIVILEGE                    \"Privilege\", 
 2973    TABLE_NAME                   \"Table name\", 
 2974    GRANTOR                  \"Grantor\", 
 2975    GRANTABLE                    \"Grantable?\" 
 2976 FROM DBA_TAB_PRIVS 
 2977    WHERE GRANTEE = '$object_name' 
 2978 ORDER BY GRANTOR, TABLE_NAME
 2979 ";
 2980    $text = "Object privileges granted to role $object_name";
 2981    $link = "";
 2982    $infotext = "There are no object privileges granted to this role.";
 2983    DisplayTable($sql,$text,$link,$infotext);
 2984 
 2985 
 2986    logit("Exit subroutine showRoles");
 2987 
 2988 }
 2989 
 2990 sub showGrantsfrom {
 2991 
 2992    logit("Enter subroutine showGrantsfrom");
 2993 
 2994    my ($sql,$text,$link,$infotext);
 2995  
 2996 # Object privileges granted from this user
 2997 
 2998    $sql = "$copyright
 2999 SELECT 
 3000    GRANTEE, 
 3001    PRIVILEGE, 
 3002    TABLE_NAME, 
 3003    GRANTABLE 
 3004 FROM DBA_TAB_PRIVS 
 3005    WHERE GRANTOR = '$schema' 
 3006 ORDER BY GRANTEE, TABLE_NAME
 3007 ";
 3008    $text = "Object privileges granted from user $schema";
 3009    $link = "";
 3010    $infotext = "$schema has not granted any privileges to other users. $schema is a stingy user.";
 3011    DisplayTable($sql,$text,$link,$infotext);
 3012 
 3013    logit("Exit subroutine showGrantsfrom");
 3014 
 3015 }
 3016 
 3017 sub opsMenu {
 3018 
 3019    logit("Enter subroutine opsMenu");
 3020 
 3021    my ($sql,$cursor,$text,$link,$infotext,$instance,$color);
 3022    my ($instance_name,$instance_number,$thread,$hostname,$startup_time);
 3023    my $highlight = "#FFFFC6";
 3024 
 3025 # All instance info
 3026 # The instance name wil be a hyperlink to connect to that database.
 3027 # Instance that you are connected to will be highlighted.
 3028 
 3029    text("Active instances.");
 3030 
 3031    print <<"EOF";
 3032 <table class="ot">
 3033   <th class="center">Instance name</th>
 3034   <th class="center">Instance number</th>
 3035   <th class="center">Thread#</th>
 3036   <th class="center">Hostname</th>
 3037   <th class="center">Startup time</th>
 3038 EOF
 3039 
 3040    $sql = "
 3041 SELECT INSTANCE_NAME
 3042    FROM V\$INSTANCE
 3043 ";
 3044 
 3045    $cursor = $dbh->prepare($sql);
 3046    $cursor->execute;
 3047    $instance = $cursor->fetchrow_array;
 3048    $cursor->finish;
 3049 
 3050    $sql = "$copyright
 3051 SELECT INSTANCE_NAME                        \"Instance name\",
 3052    INSTANCE_NUMBER                      \"Instance #\",
 3053    THREAD#                          \"Thread\",
 3054    HOST_NAME                            \"Hostname\",
 3055    TO_CHAR(STARTUP_TIME,'Day, Month DD YYYY - HH24:MI:SS')  \"Startup time\"
 3056 FROM GV\$INSTANCE
 3057    ORDER BY INSTANCE_NAME
 3058 ";
 3059 
 3060    $cursor = $dbh->prepare($sql);
 3061    $cursor->execute;
 3062    while (($instance_name,$instance_number,$thread,$hostname,$startup_time) = $cursor->fetchrow_array) {
 3063       if ($instance_name eq $instance) {
 3064          $color = $highlight;
 3065       } else {
 3066          $color = $cellcolor;
 3067       }
 3068       print "        <tr><td class=\"left\"><a href=$scriptname?database=$instance_name&object_type=FRAMEPAGE TARGET=_top>$instance_name</a></td>\n";
 3069       print <<"EOF";
 3070     <td class=\"right\">$instance_number</td>
 3071     <td class=\"right\"></td>
 3072     <td class=\"center\">$hostname</td>
 3073     <td class=\"center\">$startup_time</td></tr>
 3074 EOF
 3075    }
 3076    $cursor->finish;
 3077 
 3078    print <<"EOF";
 3079 </table>
 3080 <P>
 3081 EOF
 3082 
 3083    Button("$scriptname?database=$database&object_type=OPSINFO&command=sessions TARGET=body","Global session info","$headingcolor","CENTER","200");
 3084    Button("$scriptname?database=$database&object_type=OPSINFO&command=sessionwait TARGET=body","Global session wait info","$headingcolor","CENTER","200");
 3085    Button("$scriptname?database=$database&object_type=OPSINFO&command=sessionwaitbyevent TARGET=body","Global session wait info by event","$headingcolor","CENTER","200");
 3086    Button("$scriptname?database=$database&object_type=OPSINFO&command=transactions TARGET=body","Global transaction info","$headingcolor","CENTER","200");
 3087    Button("$scriptname?database=$database&object_type=OPSINFO&command=locks TARGET=body","Global lock info","$headingcolor","CENTER","200");
 3088    Button("$scriptname?database=$database&object_type=OPSINFO&command=dlm TARGET=body","Lock manager info","$headingcolor","CENTER","200");
 3089 
 3090    logit("Enter subroutine opsMenu");
 3091 
 3092 }
 3093 
 3094 sub opsInfo {
 3095 
 3096    logit("Enter subroutine opsInfo");
 3097 
 3098    my ($sql,$cursor,$text,$link,$infotext);
 3099    my ($instance_name,$instance_number,$thread,$hostname,$startup_time);
 3100    my $command = $query->param('command');
 3101 
 3102 # Global session wait info.
 3103 
 3104    if ($command eq "sessionwait") {
 3105 
 3106 #      $sql = "$copyright
 3107 #SELECT
 3108 #   VS.INST_ID                                           \"Instance ID\",
 3109 #   VS.USERNAME                                          \"Username\",
 3110 #   VS.OSUSER                                            \"OS user\",
 3111 #   VSW.SID                                              \"SID\",
 3112 #   VSW.EVENT                                            \"Waiting on..\",
 3113 #   TO_CHAR(VSW.SECONDS_IN_WAIT,'999,999,999,999')       \"Seconds waiting\",
 3114 #   TO_CHAR(VSW.SECONDS_IN_WAIT/60,'999,999,999,999')    \"Minutes waiting\",
 3115 #   VST.SQL_TEXT                                         \"SQL text\"
 3116 #FROM GV\$SESSION_WAIT VSW,
 3117 #     GV\$SQLTEXT VST,
 3118 #     GV\$SESSION VS
 3119 #WHERE VS.INST_ID = VSW.INST_ID
 3120 #AND VS.INST_ID = VST.INST_ID
 3121 #AND VS.STATUS = 'ACTIVE'
 3122 #AND VSW.SID = VS.SID
 3123 #AND VS.USERNAME IS NOT NULL
 3124 #AND VS.SQL_ADDRESS = VST.ADDRESS
 3125 #AND VST.PIECE = 0
 3126 #ORDER BY VSW.SECONDS_IN_WAIT DESC
 3127 #";
 3128 
 3129       $sql = "$copyright
 3130 SELECT
 3131    INST_ID                                           \"Instance ID\",
 3132    SID                                              \"SID\",
 3133    EVENT                                            \"Waiting on..\",
 3134    TO_CHAR(SECONDS_IN_WAIT,'999,999,999,999')       \"Seconds waiting\",
 3135    TO_CHAR(SECONDS_IN_WAIT/60,'999,999,999,999')    \"Minutes waiting\"
 3136 FROM GV\$SESSION_WAIT
 3137 ORDER BY SECONDS_IN_WAIT DESC
 3138 ";
 3139 
 3140       $text = "Session wait information for active sessions.";
 3141       $link = "";
 3142       $infotext = "There are no sessions in a wait state.";
 3143       DisplayTable($sql,$text,$link,$infotext);
 3144 
 3145    }
 3146 
 3147    if ($command eq "sessionwaitbyevent") {
 3148 
 3149       $sql = "$copyright
 3150 SELECT
 3151    INST_ID      \"Instance\",
 3152    EVENT                \"Waiting on\",
 3153    MAX(SECONDS_IN_WAIT) \"Seconds waiting\"
 3154 FROM GV\$SESSION_WAIT
 3155    GROUP BY INST_ID, EVENT
 3156    ORDER BY 1 ASC,3 DESC
 3157 ";
 3158       $text = "Session wait information by event.";
 3159       $link = "";
 3160       $infotext = "There are no sessions in a wait state.";
 3161       DisplayTable($sql,$text,$link,$infotext);
 3162    }
 3163 
 3164    if ($command eq "locks") {
 3165 
 3166 # Locked objects
 3167 
 3168       $sql = "$copyright
 3169 SELECT
 3170    DO.OBJECT_NAME       \"Object name\",
 3171    DO.OBJECT_TYPE       \"Object type\",
 3172    DO.OWNER         \"Owner\",
 3173    VLO.INST_ID          \"Instance ID\",
 3174    VLO.SESSION_ID       \"SID\",
 3175    VLO.ORACLE_USERNAME      \"Ora user\",
 3176    VLO.OS_USER_NAME     \"OS user\",
 3177    VLO.PROCESS          \"Process\",
 3178    VLO.LOCKED_MODE      \"Mode\"
 3179 FROM GV\$LOCKED_OBJECT VLO, DBA_OBJECTS DO
 3180    WHERE VLO.OBJECT_ID = DO.OBJECT_ID
 3181 ";
 3182       $text = "Objects which currently have locks.";
 3183       $infotext = "There are currently no locked objects.";
 3184       ObjectTable($sql,$text,$infotext);
 3185 
 3186    }
 3187 
 3188    if ($command eq "transactions") {
 3189 
 3190    refreshButton();
 3191 
 3192 # Active transactions
 3193 
 3194       $sql = "$copyright
 3195 SELECT
 3196    SA.INST_ID               \"Inst ID\",
 3197    OSUSER                               \"OS user\",
 3198    USERNAME                             \"Ora user\",
 3199    SID                                  \"SID\",
 3200    SERIAL#                              \"Serial#\",
 3201    SEGMENT_NAME                         \"RBS\",
 3202    SA.SQL_TEXT                          \"SQL Text\"
 3203 FROM   GV\$SESSION S,
 3204        GV\$TRANSACTION T,
 3205        DBA_ROLLBACK_SEGS R,
 3206        GV\$SQLAREA SA
 3207 WHERE    S.TADDR = T.ADDR
 3208 AND    T.XIDUSN = R.SEGMENT_ID(+)
 3209 AND    S.SQL_ADDRESS = SA.ADDRESS(+)
 3210 ";
 3211 
 3212       $text = "Global transaction info";
 3213       $link = "";
 3214       $infotext = "No current transactions on any segments";
 3215       DisplayTable($sql,$text,$link,$infotext);
 3216 
 3217    }
 3218 
 3219    if ($command eq "sessions") {
 3220 
 3221 # Session list
 3222 
 3223          $sql = "$copyright
 3224 SELECT
 3225    GVS.INST_ID                                  \"Instance\",
 3226    GVS.USERNAME                                 \"Ora user\",
 3227    GVS.OSUSER                                   \"OS user\",
 3228    GVS.SID                                      \"SID\",
 3229    GVS.SERIAL#                                  \"Serial#\",
 3230    GVS.STATUS                                   \"Status\",
 3231    GVS.PROCESS                                  \"Process\",
 3232    GVS.PROGRAM                                  \"Program\",
 3233    TO_CHAR(GVS.LOGON_TIME,'Day MM/DD/YY HH24:MI')       \"Logon time\",
 3234    GVST.SQL_TEXT                                \"SQL text\"
 3235 FROM GV\$SESSION GVS, GV\$SQLTEXT GVST
 3236    WHERE GVS.USERNAME IS NOT NULL
 3237    AND GVST.ADDRESS = GVS.SQL_ADDRESS
 3238    AND GVST.INST_ID = GVS.INST_ID
 3239    AND GVST.PIECE = 0
 3240    ORDER BY GVS.INST_ID, GVS.USERNAME, GVS.STATUS
 3241 ";
 3242 
 3243       $text = "Global session summary.";
 3244       $link = "";
 3245       $infotext = "";
 3246       DisplayTable($sql,$text,$link,$infotext);
 3247 
 3248    }
 3249 
 3250    if ($command eq "dlm") {
 3251 
 3252 # Lock (IDLM) information
 3253 
 3254       $sql = "$copyright
 3255 SELECT
 3256    A.INSTANCE_NAME          \"Instance Name\",
 3257    B.FROM_VAL               \"From\",
 3258    B.TO_VAL             \"To\",
 3259    B.ACTION_VAL             \"Action\",
 3260    TO_CHAR(B.COUNTER,'999,999,999,999') \"Counter\"
 3261 FROM GV\$INSTANCE A, GV\$LOCK_ACTIVITY B
 3262    WHERE B.INST_ID = A.INST_ID
 3263    ORDER BY A.INSTANCE_NAME, B.COUNTER DESC
 3264 ";
 3265 
 3266       $text = "Lock conversions by instance.";
 3267       $link = "";
 3268       $infotext = "No lock conversions";
 3269       DisplayTable($sql,$text,$link,$infotext);
 3270 
 3271    }
 3272 
 3273    logit("Exit subroutine opsInfo");
 3274 
 3275 }
 3276    
 3277 sub showUsers {
 3278 
 3279    logit("Enter subroutine showUsers");
 3280 
 3281    my ($sql,$user,$dbid,@dbausers,@connectedusers,$dba,$connected,$text,$cursor);
 3282    my (@lockedusers,$locked,$skip,$counter,$row,$usercount,$i,$moretext,$dbname);
 3283 
 3284    my $highlight = "#FFFFC6";
 3285    my $redlight  = "#DEBDDE";
 3286 
 3287 # Show database connection info
 3288   logit("   Showing connection information"); 
 3289 
 3290   $sql = "
 3291 SELECT
 3292    DBID, NAME
 3293 FROM V\$DATABASE
 3294 ";
 3295    $cursor = $dbh->prepare($sql);
 3296    $cursor->execute;
 3297    ($dbid,$dbname) = $cursor->fetchrow_array;
 3298    $cursor->finish;
 3299 
 3300    if ($hostname) {
 3301       $moretext = "Hostname : $hostname";
 3302    } else {
 3303       $moretext = "";
 3304    }
 3305 
 3306    if ($dbid) {
 3307       $moretext = "$moretext (DBID : $dbid)";
 3308    }
 3309 
 3310    print <<"EOF";
 3311 Connected to database : $dbname $moretext<BR>
 3312 $banner
 3313 <P>
 3314 EOF
 3315 
 3316 # If database is not open, show a message explaining this.
 3317 
 3318    if ($dbstatus ne "OPEN") {
 3319       logit("Database is not open, displaying warning message.");
 3320       message("Warning: this database is in $dbstatus mode. Operations will be limited.");
 3321       return;
 3322    }
 3323 
 3324   logit("   Done showing connection information"); 
 3325 
 3326   logit("   Getting list of users with DBA role"); 
 3327 
 3328 # Get all users who have the DBA role granted to them
 3329 
 3330    $sql = "$copyright
 3331 SELECT GRANTEE 
 3332    FROM DBA_ROLE_PRIVS
 3333 WHERE GRANTED_ROLE='DBA'
 3334 ";
 3335 
 3336 
 3337    $cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr");
 3338    $cursor->execute or ErrorPage ("$DBI::errstr");
 3339 
 3340    while ($user = $cursor->fetchrow_array) { 
 3341       push (@dbausers,$user);
 3342    }
 3343 
 3344    $cursor->finish;
 3345 
 3346    logit("   Done getting list of users with DBA role"); 
 3347 
 3348 # Get all users whose account status is not "OPEN", if Oracle8
 3349 
 3350    logit("   Getting users with non-open accounts");
 3351 
 3352    $sql = "$copyright
 3353 SELECT USERNAME
 3354    FROM DBA_USERS
 3355 WHERE ACCOUNT_STATUS <> 'OPEN'
 3356 ";
 3357    $cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr");
 3358    $cursor->execute or ErrorPage ("$DBI::errstr");
 3359 
 3360    while ($user = $cursor->fetchrow_array) {
 3361       push (@lockedusers,$user);
 3362    }
 3363 
 3364    $cursor->finish;
 3365    logit("   Done getting users with non-open accounts");
 3366 
 3367 # Get all users that are currently connected.
 3368 
 3369    logit("   Getting list of currently connected users");
 3370 
 3371    $sql = "$copyright
 3372 SELECT DISTINCT USERNAME
 3373    FROM V\$SESSION
 3374 WHERE USERNAME IS NOT NULL
 3375 AND STATUS in ('ACTIVE','INACTIVE')
 3376 ";
 3377 
 3378    $cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr");
 3379    $cursor->execute or ErrorPage ("$DBI::errstr");
 3380 
 3381    while ($user = $cursor->fetchrow_array) {
 3382       push (@connectedusers,$user);
 3383    }
 3384 
 3385    $cursor->finish;
 3386 
 3387    logit("   Done getting list of currently connected users");
 3388 
 3389 # Display a count of all users.
 3390 
 3391    logit("   Getting count of all users");
 3392 
 3393    $sql = "$copyright
 3394 SELECT COUNT(*)
 3395    FROM DBA_USERS
 3396 ";
 3397 
 3398    $cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr");
 3399    $cursor->execute or ErrorPage ("$DBI::errstr");
 3400    $usercount = $cursor->fetchrow_array;
 3401    $cursor->finish;
 3402 
 3403    logit("   Done getting count of all users");
 3404 
 3405 # Get all usernames
 3406 
 3407    $sql = "$copyright
 3408 SELECT 
 3409    USERNAME 
 3410 FROM DBA_USERS 
 3411    ORDER BY USERNAME
 3412 ";
 3413    logit("   Check for account status");
 3414    $text = "Select a schema by clicking on it.<BR>Yellow: User is connected. Red: Account locked / expired<BR>Bold text in parenthesis indicates DBA authority.";
 3415    $cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr");
 3416    $cursor->execute or ErrorPage ("$DBI::errstr");
 3417    $counter=0;
 3418    logit("   Generating HTML");
 3419    print "<b>$text</b><P>\n";
 3420    print "<table class=\"ot\">\n";
 3421    print "  <th class=\"center\" colspan=$schema_cols>Total # users: $usercount</th>\n"; 
 3422 
 3423    while ($row = $cursor->fetchrow_array) {
 3424       undef $dba;
 3425       foreach $user (@dbausers) {
 3426          if ( $row eq $user ) {
 3427             $dba = "yes";
 3428             last;
 3429          }
 3430       }
 3431       undef $connected;
 3432       foreach $user (@connectedusers) {
 3433          if ( $row eq $user ) {
 3434             $connected = "yes";
 3435             last;
 3436          }
 3437       }
 3438       undef $locked;
 3439       foreach $user (@lockedusers) {
 3440          if ( $row eq $user ) {
 3441             $locked = "yes";
 3442             last;
 3443          }
 3444       }
 3445       print "  <tr>\n" if $counter == 0;
 3446       logit("   Counter is zero, start row");
 3447       if (($connected) && ($dba)) {
 3448          print "    <td class=\"center\" style=\"background-color: $highlight; font-weight:bold;\"><a href=$scriptname?database=$database&schema=$row&object_type=USERINFO>(${row})</a></td>\n";
 3449       } elsif ($connected) {
 3450          print "    <td class=\"center\" style=\"background-color: $highlight;\"><a href=$scriptname?database=$database&schema=$row&object_type=USERINFO>$row</a></td>\n";
 3451       } elsif ($locked) {
 3452          print "    <td class=\"center\" style=\"background-color: $redlight;\"><a href=$scriptname?database=$database&schema=$row&object_type=USERINFO>$row</a></td>\n";
 3453       } elsif ($dba) {
 3454          print "    <td class=\"center\" style=\"font-weight:bold;\"><a href=$scriptname?database=$database&schema=$row&object_type=USERINFO>(${row})</a></td>\n";
 3455       } else {
 3456          print "    <td class=\"center\"><a href=$scriptname?database=$database&schema=$row&object_type=USERINFO>$row</a></td>\n";
 3457       }
 3458       $counter++;
 3459       print "   </tr>\n" if $counter == 0;
 3460       $skip = "";
 3461       if ($counter == $schema_cols) { 
 3462          print "  </tr>\n";
 3463          $counter = 0;
 3464          $skip = "Y";
 3465        }
 3466    }
 3467    if ((! $skip) && ($counter < $schema_cols)) {
 3468       for ($i = $counter; $i < $schema_cols; $i++) {
 3469          print "    <td class=\"center\"&nbsp;</td>\n";
 3470       }
 3471    }
 3472    print "  </tr>\n";
 3473    print "</table>\n";
 3474 
 3475    $cursor->finish or ErrorPage ("$DBI::errstr");
 3476 
 3477    logit("Exit subroutine showUsers");
 3478 
 3479 } 
 3480 
 3481 sub showAuditTrail {
 3482 
 3483    logit("Enter subroutine showAuditTrail");
 3484 
 3485    my ($sql,$text,$link,$infotext,$command);
 3486 
 3487    $command = $query->param('command');
 3488 
 3489    logit("   Command: $command");
 3490    logit("   Object name: $object_name");
 3491 
 3492    if ($command eq "statement") {
 3493 
 3494       $sql = "$copyright
 3495 SELECT
 3496    OS_USERNAME                      \"OS user\",
 3497    USERNAME                     \"Username\",
 3498    USERHOST                     \"Host\",
 3499    TERMINAL                     \"Terminal\",
 3500    TO_CHAR(TIMESTAMP,'Day, Month DD YYYY - HH24:MI:SS') \"Timestamp\",
 3501    OWNER                        \"Owner\",
 3502    OBJ_NAME                     \"Object name\",
 3503    ACTION_NAME                      \"Action\",
 3504    PRIV_USED                        \"Priv used\"
 3505 FROM DBA_AUDIT_TRAIL
 3506    WHERE PRIV_USED = '$object_name'
 3507    ORDER BY TIMESTAMP DESC, USERNAME
 3508 ";
 3509 
 3510    $text = "Audit records: $object_name.";
 3511    $link = "";
 3512    $infotext = "No audit records for $object_name.";
 3513    DisplayTable($sql,$text,$link,$infotext);
 3514 
 3515    }
 3516 
 3517    if ($command eq "object") {
 3518 
 3519       $sql = "$copyright
 3520 SELECT
 3521    OS_USERNAME                      \"OS user\",
 3522    USERNAME                     \"Username\",
 3523    USERHOST                     \"Host\",
 3524    TERMINAL                     \"Terminal\",
 3525    TO_CHAR(TIMESTAMP,'Day, Month DD YYYY - HH24:MI:SS') \"Timestamp\",
 3526    OWNER                        \"Owner\",
 3527    OBJ_NAME                     \"Object name\",
 3528    ACTION_NAME                      \"Action\",
 3529    PRIV_USED                        \"Priv used\"
 3530 FROM DBA_AUDIT_TRAIL
 3531    WHERE OBJ_NAME = '$object_name'
 3532    ORDER BY TIMESTAMP DESC, OWNER
 3533 ";
 3534 
 3535    $text = "Audit records: $object_name.";
 3536    $link = "";
 3537    $infotext = "No audit records for $object_name.";
 3538    DisplayTable($sql,$text,$link,$infotext);
 3539 
 3540    }
 3541 
 3542    logit("Exit subroutine showAuditTrail");
 3543 
 3544 } 
 3545 
 3546 sub showAllAuditing {
 3547 
 3548    logit("Enter subroutine showAllAuditing");
 3549 
 3550    my ($sql,$text,$link,$infotext);
 3551 
 3552    refreshButton();
 3553 
 3554    $sql = "$copyright
 3555 SELECT 
 3556    AUDIT_OPTION     \"Audit option\",
 3557    USER_NAME        \"Username\",
 3558    SUCCESS      \"Success\",
 3559    FAILURE      \"Failure\"
 3560 FROM DBA_STMT_AUDIT_OPTS
 3561    ORDER BY USER_NAME
 3562 ";
 3563 
 3564    $text        = "Statements / system privileges which are being audited.";
 3565    $link        = "$scriptname?database=$database&object_type=SHOWAUDITTRAIL&command=statement";
 3566    $infotext    = "No SQL statement / system privileges are being audited.";
 3567 
 3568    DisplayTable($sql,$text,$link,$infotext); 
 3569 
 3570    $sql = "$copyright
 3571 SELECT
 3572    OBJECT_NAME         \"Object name\",
 3573    OBJECT_TYPE         \"Object type\",
 3574    OWNER               \"Owner\",
 3575    ALT                 \"Alter\",
 3576    AUD                 \"Audit\",
 3577    COM                 \"Comment\",
 3578    DEL                 \"Delete\",
 3579    GRA                 \"Grant\",
 3580    IND                 \"Index\",
 3581    INS                 \"Insert\",
 3582    LOC                 \"Lock\",
 3583    REN                 \"Rename\",
 3584    SEL                 \"Select\",
 3585    UPD                 \"Update\",
 3586    EXE                 \"Execute\",
 3587    CRE                 \"Create\",
 3588    REA                 \"Read\",
 3589    WRI                 \"Write\"
 3590 FROM DBA_OBJ_AUDIT_OPTS
 3591 WHERE ALT != '-/-'
 3592 OR AUD != '-/-'
 3593 OR COM != '-/-'
 3594 OR DEL != '-/-'
 3595 OR GRA != '-/-'
 3596 OR IND != '-/-'
 3597 OR INS != '-/-'
 3598 OR LOC != '-/-'
 3599 OR REN != '-/-'
 3600 OR SEL != '-/-'
 3601 OR UPD != '-/-'
 3602 OR EXE != '-/-'
 3603 OR CRE != '-/-'
 3604 OR REA != '-/-'
 3605 OR WRI != '-/-'
 3606 ORDER BY OWNER
 3607 ";
 3608 
 3609    $text        = "Auditing options pertaining to individual objects.";
 3610    $link        = "$scriptname?database=$database&object_type=SHOWAUDITTRAIL&command=object";
 3611    $infotext    = "No schema objects are being audited.";
 3612 
 3613    DisplayTable($sql,$text,$link,$infotext); 
 3614 
 3615    $sql = "
 3616 SELECT DISTINCT 
 3617    OBJ_NAME \"Object name\",
 3618    OWNER    \"Owner\",
 3619    ACTION_NAME  \"Action name\",
 3620    COUNT(*) \"Count\"
 3621 FROM DBA_AUDIT_TRAIL
 3622    GROUP BY OBJ_NAME, OWNER, ACTION_NAME
 3623 ORDER BY 4 DESC
 3624 ";
 3625 
 3626    $text        = "Audit trail counts from individual objects.";
 3627    $link        = "$scriptname?database=$database&object_type=SHOWAUDITTRAIL&command=object";
 3628 
 3629    DisplayTable($sql,$text,$link); 
 3630 
 3631    logit("Exit subroutine showAllAuditing");
 3632 
 3633 }
 3634 
 3635 sub Auditing {
 3636 
 3637    logit("Enter subroutine Auditing");
 3638 
 3639    my ($sql,$cursor,$value);
 3640 
 3641    $sql = "$copyright
 3642 SELECT
 3643    VALUE FROM V\$PARAMETER
 3644 WHERE NAME = 'audit_trail'
 3645 ";
 3646 
 3647    $cursor=$dbh->prepare($sql);
 3648    $cursor->execute;
 3649    $value = $cursor->fetchrow_array;
 3650 
 3651    if ( (uc($value) ne "FALSE") && (uc($value) ne "NONE")) {
 3652       return(1);
 3653    } else {
 3654       return(0);
 3655    }
 3656    logit("Exit subroutine Auditing");
 3657 }
 3658 
 3659 
 3660 sub showSecurity {
 3661 
 3662    logit("Enter subroutine showSecurity");
 3663 
 3664    my ($sql,$cursor,$value,$text,$link,$infotext,$cols,$count);
 3665 
 3666    if ( Auditing() ) { 
 3667 
 3668       print <<"EOF";
 3669       <form method="GET" ACTION="$scriptname">
 3670         <input type="HIDDEN" NAME="database" VALUE="$database">
 3671         <input type="HIDDEN" NAME="object_type" VALUE="AUDITING">
 3672         <input type="SUBMIT" NAME="auditing" VALUE="Auditing information">
 3673       </form>
 3674 EOF
 3675     } else {
 3676       message("Database auditing is not enabled.");
 3677    } 
 3678 
 3679    if ( $majversion eq "11" ) {
 3680       $sql = "
 3681 Select
 3682    version                  \"DB version\",
 3683    to_char(action_time,'MM/DD/YYYY HH24:MI')    \"Date applied\",
 3684    comments                 \"Patch\"
 3685 from sys.registry\$history
 3686    where action_time =
 3687       (Select max(action_time)
 3688           from sys.registry\$history
 3689        where comments like 'PSU%' 
 3690        or comments like 'CPU%' 
 3691        or comments like 'SPU%'
 3692        or comments like 'APPLIED jvmpsu.sql')
 3693 ";
 3694 
 3695    }
 3696    if ( $majversion eq "12" ) {
 3697       if ( $minversion eq "1" ) {
 3698          #
 3699          # This format, putting both patches in the same column
 3700          # Started with the Jan 2017 PSU
 3701          #
 3702          $sql = "
 3703 Select
 3704    version                                      \"DB version\",
 3705    to_char(action_time,'MM/DD/YYYY HH24:MI')    \"Date applied\",
 3706    description                                  \"Patch\"
 3707 from dba_registry_sqlpatch
 3708    where description like '%Database PSU%'
 3709    and action_time =
 3710       (Select max(action_time)
 3711          from dba_registry_sqlpatch
 3712        where description like '%Database PSU%')
 3713 ";
 3714       }
 3715       if ( $minversion eq "2" ) {
 3716          $sql = "
 3717 Select
 3718    version                                      \"DB version\",
 3719    to_char(action_time,'MM/DD/YYYY HH24:MI')    \"Date applied\",
 3720    description                                  \"Patch\"
 3721 from dba_registry_sqlpatch
 3722    where description like 'DATABASE%RELEASE UPDATE%'
 3723    and action_time =
 3724       (Select max(action_time)
 3725          from dba_registry_sqlpatch
 3726        where description like 'DATABASE%RELEASE UPDATE%')
 3727 ";
 3728       }
 3729    }
 3730    if ( $majversion eq "19" ) {
 3731       $sql = "
 3732 Select
 3733    target_version                               \"DB version\",
 3734    to_char(action_time,'MM/DD/YYYY HH24:MI')    \"Date applied\",
 3735    description                                  \"Patch\"
 3736 from dba_registry_sqlpatch
 3737    where description like '%Database Release Update%'
 3738    and action_time =
 3739       (Select max(action_time)
 3740          from dba_registry_sqlpatch
 3741        where description like '%Database Release Update%')
 3742 ";
 3743    }
 3744 
 3745    $text = "Most recent Critical Patch / Patch Set found";
 3746    $infotext = "No Critical Patch Update info found";
 3747    $link = "";
 3748    DisplayTable($sql,$text,$link,$infotext);
 3749 
 3750 #
 3751 # Need to come back to this. Public db links dependency listing is acting funky
 3752 #
 3753    $count = recordCount($dbh,"Select db_link from dba_db_links where owner='PUBLIC'");
 3754 
 3755    if ($count) {
 3756 
 3757       $sql = "
 3758 Select
 3759    db_link
 3760 from dba_db_links
 3761    where owner='PUBLIC'
 3762 order by 1 asc
 3763 ";
 3764 
 3765       $text        = "Public Database Links";
 3766       $link        = "$scriptname?database=$database&schema=PUBLIC&object_type=DATABASE+LINK";
 3767       $infotext    = "There are no public database links defined";
 3768 
 3769       DisplayColTable($sql,$text,$link,$infotext,3);
 3770    }
 3771 
 3772    $count = recordCount($dbh,"Select acl from dba_network_acls");
 3773 
 3774    if ($count) {
 3775 
 3776       $sql = "
 3777 Select
 3778    acl
 3779 from dba_network_acls
 3780    order by 1 asc
 3781 ";
 3782 
 3783       $text        = "Access Control Lists";
 3784       $link        = "$scriptname?database=$database&object_type=ACL";
 3785       $infotext    = "There are no Access Control Lists defined";
 3786 
 3787       DisplayColTable($sql,$text,$link,$infotext,$schema_cols);
 3788    }
 3789 
 3790    $sql = "
 3791 Select
 3792    directory_name   \"Name\",
 3793    owner        \"Owner\",
 3794    directory_path   \"Path\"
 3795 from dba_directories order by 1,2";
 3796 
 3797    $text    = "Directories";
 3798    $link    = "$scriptname?database=$database&object_type=DIRECTORY";
 3799    $infotext    = "No directories defined.";
 3800 
 3801    DisplayTable($sql,$text,$link,$infotext); 
 3802 
 3803    $sql = "$copyright
 3804 SELECT 
 3805    ROLE
 3806 FROM DBA_ROLES
 3807    ORDER BY ROLE
 3808 ";
 3809 
 3810    $text        = "Security roles";
 3811    $link    = "$scriptname?database=$database&object_type=ROLES";
 3812    $infotext    = "There are no roles in this database";
 3813 
 3814    DisplayColTable($sql,$text,$link,$infotext,$schema_cols); 
 3815 
 3816    $sql = "$copyright
 3817 SELECT DISTINCT
 3818    PROFILE
 3819 FROM DBA_PROFILES
 3820    ORDER BY PROFILE
 3821 ";
 3822 
 3823    $text        = "Security profiles";
 3824    $link    = "$scriptname?database=$database&object_type=PROFILE";
 3825    $infotext    = "There are no profiles in this database";
 3826 
 3827    DisplayColTable($sql,$text,$link,$infotext,$schema_cols); 
 3828 
 3829    $sql = "$copyright
 3830 SELECT GRANTEE
 3831    FROM DBA_ROLE_PRIVS
 3832 WHERE GRANTED_ROLE='DBA'
 3833    AND GRANTEE IN
 3834 (SELECT USERNAME 
 3835    FROM DBA_USERS)
 3836 "; 
 3837 
 3838    $text        = "Users with the \"DBA\" role granted to them";
 3839    $link    = "$scriptname?database=$database&object_type=USERINFO";
 3840    $infotext    = "There are no users with the \"DBA\" role in this database";
 3841 
 3842    DisplayColTable($sql,$text,$link,$infotext,$schema_cols); 
 3843 
 3844    $sql = "$copyright
 3845 SELECT GRANTEE
 3846         FROM DBA_ROLE_PRIVS
 3847 WHERE GRANTED_ROLE='DBA'
 3848    AND GRANTEE IN
 3849 (SELECT ROLE
 3850    FROM DBA_ROLES)
 3851 ";
 3852 
 3853    $text        = "Roles with the \"DBA\" role granted to them";
 3854    $link        = "$scriptname?database=$database&object_type=ROLES";
 3855    $infotext    = "There are no roles with the \"DBA\" role in this database";
 3856 
 3857    DisplayColTable($sql,$text,$link,$infotext,$schema_cols);
 3858 
 3859    $count = recordCount($dbh,"Select count(*) from dba_audit_session");
 3860 
 3861    logit("   There are $count records in the dba_audit_session table");
 3862 
 3863    logit("Exit subroutine showSecurity");
 3864 
 3865 }
 3866 
 3867 sub showProfile {
 3868 
 3869    logit("Enter subroutine showProfile");
 3870 
 3871    my ($sql,$text,$link,$infotext);
 3872 
 3873    $sql = "$copyright
 3874 SELECT 
 3875    RESOURCE_NAME    \"Resource name\",
 3876    RESOURCE_TYPE    \"Resource type\",
 3877    LIMIT        \"Limit\"
 3878 FROM DBA_PROFILES
 3879    WHERE PROFILE = '$object_name'
 3880 ORDER BY RESOURCE_NAME
 3881 ";
 3882 
 3883    $text    = "Profile $object_name";
 3884    $link    = "";
 3885    $infotext    = "";
 3886 
 3887    DisplayTable($sql,$text,$link,$infotext); 
 3888 
 3889    $sql = "
 3890 Select
 3891    username 
 3892 from dba_users
 3893    where profile='$object_name'
 3894 order by 1
 3895 ";
 3896 
 3897    $text = "The following users are granted the profile $object_name";
 3898    $link = "$scriptname?database=$database&schema=$schema&object_type=USERINFO";
 3899    $infotext = "No users are granted the profile $object_name";
 3900 
 3901    DisplayColTable($sql,$text,$link,$infotext,$schema_cols);
 3902 
 3903    logit("Exit subroutine showProfile");
 3904 
 3905 }
 3906 
 3907 sub userInfo {
 3908 
 3909    logit("Enter subroutine userInfo");
 3910 
 3911    # User info
 3912    # Get the data from the database
 3913 
 3914    my ($sql,$cursor,$count,$text,$link,$infotext,$cols);
 3915    my ($uname,$defts,$tmpts,$created,$profile,$objcount);
 3916    my ($status,$ldate,$edate);
 3917 
 3918    $schema = $object_name unless $schema;
 3919 
 3920    refreshButton();
 3921 
 3922 # General user info
 3923 
 3924    $sql = "$copyright
 3925 Select
 3926    username,
 3927    default_tablespace,
 3928    temporary_tablespace,
 3929    to_char(created,'MM/DD/YYYY - HH24:MI'),
 3930    to_char(lock_date,'MM/DD/YYYY - HH24:MI'),
 3931    to_char(expiry_date,'MM/DD/YYYY - HH24:MI'),
 3932    profile,
 3933    account_status
 3934 from dba_users
 3935    where username = '$schema'
 3936 ";
 3937 
 3938    $status = "";
 3939    $cursor=$dbh->prepare($sql);
 3940    $cursor->execute;
 3941    ($uname,$defts,$tmpts,$created,$ldate,$edate,$profile,$status) = $cursor->fetchrow;
 3942    $cursor->finish;
 3943 
 3944    print <<"EOF";
 3945 <table class="ot">
 3946   <tr>
 3947     <th class="center">User name</th>
 3948     <th class="center">Default tablespace</th>
 3949     <th class="center">Temp tablespace</th>
 3950     <th class="center">Account status</th>
 3951     <th class="center">Creation date</th>
 3952     <th class="center">Lock date</th>
 3953     <th class="center">Expiration date</th>
 3954     <th class="center">Profile</th>
 3955   </tr>
 3956   <tr>
 3957     <td class="center"><A HREF=$scriptname?database=$database&object_type=USERDDL&schema=$schema>$uname</td>
 3958     <td class="center"><A HREF=$scriptname?database=$database&object_type=TSINFO&schema=$schema&arg=$defts>$defts</td>
 3959     <td class="center"><A HREF=$scriptname?database=$database&object_type=TSINFO&schema=$schema&arg=$tmpts>$tmpts</td>
 3960     <td class="center">$status</td>
 3961     <td class="center">$created</td>
 3962     <td class="center">$ldate</td>
 3963     <td class="center">$edate</td>
 3964     <td class="center"><A HREF=$scriptname?database=$database&object_type=PROFILE&arg=$profile>$profile</A></td>
 3965   </tr>
 3966 </table>
 3967 EOF
 3968     
 3969 # Tablespace quotas
 3970 
 3971    $sql = "$copyright
 3972 SELECT 
 3973    TABLESPACE_NAME                       \"Tablespace\", 
 3974    TO_CHAR(BYTES,'999,999,999,999')      \"Bytes used\", 
 3975 DECODE 
 3976 (
 3977    MAX_BYTES,
 3978       '-1','Unlimited', TO_CHAR(MAX_BYTES,'999,999,999,999')
 3979 )                                        \"Quota\"  
 3980 FROM DBA_TS_QUOTAS 
 3981    WHERE USERNAME = '$schema'
 3982 ORDER BY TABLESPACE_NAME
 3983 ";
 3984    $text = "Tablespace quotas";
 3985    $link = "$scriptname?database=$database&object_type=TSINFO&schema=$schema";
 3986    $infotext = "$schema has no individual tablespace quotas.";
 3987 
 3988    DisplayTable($sql,$text,$link,$infotext);
 3989 
 3990 # Buttons for displaying grants / session info
 3991 
 3992 print <<"EOF";
 3993 <P>
 3994 <table class="noborder">
 3995   <tr>
 3996     <td class="noborder">
 3997 EOF
 3998       Button("$scriptname?database=$database&schema=$schema&object_type=GRANTSTO TARGET=body","Display grants <i>to</i> $schema","$headingcolor","CENTER",250);
 3999 print <<"EOF";
 4000     </td>
 4001     <td class="noborder">
 4002 EOF
 4003       Button("$scriptname?database=$database&schema=$schema&object_type=GRANTSFROM TARGET=body","Display grants <i>from</i> $schema","$headingcolor","CENTER",250);
 4004 print <<"EOF";
 4005     </td>
 4006   </tr>
 4007 </table>
 4008 EOF
 4009 
 4010 # Display a button if the user currently has sessions in this instance.
 4011 
 4012    $sql = "$copyright
 4013 SELECT COUNT(*) 
 4014    FROM V\$SESSION 
 4015 WHERE USERNAME = '$schema'
 4016 ";
 4017    $cursor=$dbh->prepare($sql);
 4018    $cursor->execute;
 4019    $count = $cursor->fetchrow_array;
 4020    $cursor->finish;
 4021 
 4022    if ($count > 0) {
 4023       Button("$scriptname?database=$database&schemaname=$schema&object_type=TOPSESSIONS TARGET=body","Display $schema session info","$headingcolor","CENTER",200);
 4024    } else {
 4025       message("$schema has no sessions in this instance.");
 4026    }
 4027 
 4028    Button("$scriptname?database=$database&schemaname=$schema&object_type=LOGINAUDITRECORDS TARGET=body","Display $schema connection history","$headingcolor","CENTER",200);
 4029 
 4030 # Check to see if there are any public synonyms pointing
 4031 # to objects owned by the schema selected.
 4032 
 4033    $sql = "$copyright
 4034 SELECT 
 4035    COUNT(*) 
 4036 FROM DBA_SYNONYMS
 4037    WHERE TABLE_OWNER = '$schema'
 4038    AND OWNER = 'PUBLIC'
 4039 ";
 4040    $cursor = $dbh->prepare($sql);
 4041    $cursor->execute;
 4042    $objcount = $cursor->fetchrow_array || "";
 4043    $cursor->finish;
 4044 
 4045 # Get object types owned by user
 4046 
 4047    if ($objcount) {
 4048 
 4049       $sql = "$copyright
 4050 SELECT DISTINCT 
 4051    OBJECT_TYPE              \"Object type\" 
 4052 FROM DBA_OBJECTS 
 4053    WHERE OWNER = '$schema'
 4054    AND OBJECT_TYPE != 'UNDEFINED'
 4055 UNION
 4056    SELECT
 4057 DECODE(DUMMY,'X','PUBLIC SYNONYMS')
 4058    FROM DUAL
 4059 ";
 4060       $text = "Object types owned by $schema, + public synonyms.<BR>Click an object type for a list.";
 4061 
 4062    } else {
 4063     
 4064       $sql = "$copyright
 4065 SELECT DISTINCT
 4066    OBJECT_TYPE                          \"Object type\"
 4067 FROM DBA_OBJECTS
 4068    WHERE OWNER = '$schema'
 4069    AND OBJECT_TYPE != 'UNDEFINED'
 4070 ";
 4071       $text = "Object types owned by $schema.<BR>Click an object type for a list.";
 4072 
 4073    }
 4074 
 4075    $link = "$scriptname?database=$database&schema=$schema&object_type=LISTOBJECTS";
 4076    $infotext = "There are no objects owned by $schema in this database.";
 4077    DisplayColTable($sql,$text,$link,$infotext,$schema_cols);
 4078 
 4079    $sql = "$copyright
 4080 Select segment_type                     \"Object type\",
 4081 to_char(sum(bytes)/1048576,'999,999,999,999,999,999')       \"Space used in MB\"
 4082 from dba_segments where owner='$schema' group by segment_type
 4083 ";
 4084 
 4085    $link = "";
 4086    $text = "Segment space usage (MB)";
 4087    $infotext = "";
 4088    DisplayTable($sql,$text,$link,$infotext,$schema_cols);
 4089 
 4090    $sql = "$copyright
 4091 SELECT COUNT(*) FROM DBA_OBJECTS WHERE OWNER = '$schema'
 4092 ";
 4093    $cursor = $dbh->prepare($sql);
 4094    $cursor->execute;
 4095    $objcount = $cursor->fetchrow_array;
 4096    $cursor->finish;
 4097 
 4098    if ($objcount) {
 4099 
 4100       $sql = "$copyright
 4101 SELECT
 4102    COUNT(DECODE(TYPE#, 2, OBJ#, '')) \"Table\",
 4103    COUNT(DECODE(TYPE#, 1, OBJ#, '')) \"Index\",
 4104    COUNT(DECODE(TYPE#, 5, OBJ#, '')) \"Synonym\",
 4105    COUNT(DECODE(TYPE#, 4, OBJ#, '')) \"View\",
 4106    COUNT(DECODE(TYPE#, 6, OBJ#, '')) \"Sequence\",
 4107    COUNT(DECODE(TYPE#, 7, OBJ#, '')) \"Procedure\",
 4108    COUNT(DECODE(TYPE#, 8, OBJ#, '')) \"Function\",
 4109    COUNT(DECODE(TYPE#, 9, OBJ#, '')) \"Package\",
 4110    COUNT(DECODE(TYPE#,12, OBJ#, '')) \"Trigger\"
 4111 FROM SYS.OBJ\$
 4112    WHERE OWNER# = 
 4113 (
 4114 SELECT USER_ID 
 4115    FROM DBA_USERS 
 4116 WHERE USERNAME = '$schema'
 4117 )
 4118 ";
 4119 
 4120       $text = "Object count";
 4121       $link = "";
 4122 
 4123       DisplayTable($sql,$text,$link);
 4124 
 4125    }
 4126 
 4127    $sql = "$copyright
 4128 SELECT 
 4129    COUNT(*)
 4130 FROM DBA_OBJECTS
 4131    WHERE OWNER = '$schema'
 4132 AND OBJECT_TYPE IN ('TABLE','INDEX','CLUSTER')
 4133 ";
 4134 
 4135    $cursor=$dbh->prepare($sql);
 4136    $cursor->execute;
 4137    $count = $cursor->fetchrow_array;
 4138 
 4139    if ($count > 0) {
 4140 
 4141       print <<"EOF";
 4142 <BR>
 4143 
 4144 <FORM METHOD="GET" ACTION="$scriptname">
 4145   
 4146   <input type="HIDDEN" NAME="database" VALUE="$database">
 4147   <input type="HIDDEN" NAME="schema" VALUE="$schema">
 4148   <input type="HIDDEN" NAME="object_type" VALUE="OBJECTREPORT">
 4149   <input type="SUBMIT" NAME="objectreport" VALUE="Object report by tablespace.">
 4150 </FORM>
 4151 EOF
 4152    }
 4153 
 4154    if ($objcount) {
 4155 
 4156       $sql = "$copyright
 4157 SELECT COUNT(*)
 4158    FROM DBA_OBJECTS
 4159 WHERE OWNER = '$schema'
 4160 AND STATUS IN ('INVALID','UNUSABLE')
 4161 ";
 4162       $cursor = $dbh->prepare($sql);
 4163       $cursor->execute;
 4164       $count = $cursor->fetchrow_array;
 4165       $cursor->finish;
 4166       if ($count > 0) {
 4167          if (checkPriv("ALTER ANY PROCEDURE")) {
 4168             print <<"EOF";
 4169 <br>
 4170 <FORM METHOD="GET" ACTION="$scriptname">
 4171   
 4172   <input type="HIDDEN" NAME="database" VALUE="$database">
 4173   <input type="HIDDEN" NAME="schema" VALUE="$schema">
 4174   <input type="HIDDEN" NAME="object_type" VALUE="SHOWINVALIDOBJECTS">
 4175   <input type="SUBMIT" NAME="objectreport" VALUE="Display $count invalid objects.">
 4176 </FORM>
 4177 EOF
 4178          } else {
 4179             message("There are $count invalid objects in this schema.\n");
 4180          }
 4181       } else {
 4182          message("There are no invalid objects in this schema.\n");
 4183       }
 4184    }
 4185 
 4186    logit("Exit subroutine userInfo");
 4187 
 4188 }
 4189 
 4190 sub showInvalidObjects {
 4191 
 4192    invalidObjectList($schema);
 4193 
 4194 }
 4195 
 4196 sub userSpaceReport {
 4197 
 4198    logit("Enter subroutine userSpaceReport");
 4199 
 4200    my ($sql,$link,$text,$sortfield,$owner,$bytes,$highlight,$color,$count);
 4201 
 4202    $sortfield = $query->param('sortfield') || "3";
 4203    $highlight = "#FFFFC6";
 4204 
 4205    text("Click on a column name to change sort order.");
 4206 
 4207    print << "EOF";
 4208 <table class="ot">
 4209   <tr>
 4210 EOF
 4211    if ($sortfield eq "1") {
 4212       $color = $highlight;
 4213    } else {
 4214       $color = $headingcolor;
 4215    }
 4216    print "   <th class=\"left\"><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=1>Owner</A></th>\n";
 4217    if ($sortfield eq "2") {
 4218       $sortfield = "2 DESC";
 4219       $color = $highlight;
 4220    } else {
 4221       $color = $headingcolor;
 4222    }
 4223    print "   <th class=\"center\"><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=2>Object count</A></th>\n";
 4224    if ($sortfield eq "3") {
 4225       $sortfield = "3 DESC";
 4226       $color = $highlight;
 4227    } else {
 4228       $color = $headingcolor;
 4229    }
 4230    print "   <th class=\"center\"><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=3>Bytes</A></th>\n";
 4231 
 4232    $sql = "$copyright
 4233 SELECT
 4234    OWNER,
 4235    TO_CHAR(COUNT(*),'999,999,999,999'),
 4236    TO_CHAR(SUM(BYTES),'999,999,999,999')
 4237 FROM DBA_SEGMENTS
 4238    GROUP BY OWNER
 4239    ORDER by $sortfield
 4240 ";
 4241 
 4242    $cursor = $dbh->prepare($sql);
 4243    $cursor->execute;
 4244    while (($owner,$count,$bytes) = $cursor->fetchrow_array) {
 4245       print "<tr><td class=\"center\"><A HREF=$scriptname?database=$database&object_type=OBJECTREPORT&arg=$owner>$owner</A></td>\n";
 4246       print "<td class=\"right\">$count</td>\n";
 4247       print "<td class=\"right\">$bytes</td></TR>\n";
 4248    }
 4249    $cursor->finish;
 4250    print <<"EOF";
 4251       </TABLE>
 4252     </td>
 4253   </TR>
 4254 </TABLE>
 4255 EOF
 4256 
 4257    logit("Exit subroutine userSpaceReport");
 4258 
 4259 }
 4260 
 4261 sub fileFragReport {
 4262 
 4263    logit("Enter subroutine fileFragReport");
 4264 
 4265    my ($sql,$link,$text,$sortfield,$file_name,$bytes,$largest,$smallest,$frags);
 4266    my ($highlight,$color,$count,$tablespace_name,@needs_coalescing);
 4267 
 4268    $sortfield = $query->param('sortfield') || "4";
 4269    $highlight = "#FFFFC6";
 4270 
 4271    text("Click on a column name to change sort order.");
 4272 
 4273    print << "EOF";
 4274 <table class="ot">
 4275 EOF
 4276    if ($sortfield eq "1") {
 4277       $color = $highlight;
 4278    } else {
 4279       $color = $headingcolor;
 4280    }
 4281    print "   <th class=\"center\"><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=1>File name</A></th>\n";
 4282    if ($sortfield eq "2") {
 4283       $sortfield = "2 DESC";
 4284       $color = $highlight;
 4285    } else {
 4286       $color = $headingcolor;
 4287    }
 4288    print "   <th class=\"center\"><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=2>Bytes</A></th>\n";
 4289    if ($sortfield eq "3") {
 4290       $color = $highlight;
 4291    } else {
 4292       $color = $headingcolor;
 4293    }
 4294    print "   <th class=\"center\"><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=3>Tablespace name</A></th>\n";
 4295    if ($sortfield eq "4") {
 4296       $sortfield = "4 DESC";
 4297       $color = $highlight;
 4298    } else {
 4299       $color = $headingcolor;
 4300    }
 4301    print "   <th class=\"center\"><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=4>Fragments</A></th>\n";
 4302    if ($sortfield eq "5") {
 4303       $sortfield = "5 DESC";
 4304       $color = $highlight;
 4305    } else {
 4306       $color = $headingcolor;
 4307    }
 4308    print "   <th class=\"center\"><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=5>Largest free chunk</A></th>\n";
 4309    if ($sortfield eq "6") {
 4310       $color = $highlight;
 4311    } else {
 4312       $color = $headingcolor;
 4313    }
 4314    print "   <th class=\"center\"><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=6>Smallest free chunk</A></th>\n";
 4315 $sql = "$copyright
 4316 SELECT 
 4317    A.FILE_NAME                      \"File name\",
 4318    TO_CHAR(A.BYTES,'999,999,999,999')           \"Bytes\",
 4319    A.TABLESPACE_NAME                    \"Tablespace name\",
 4320    COUNT(*)                     \"Pieces\",
 4321    TO_CHAR(NVL(MAX(B.BYTES),'0'),'999,999,999,999') \"Largest free chunk\", 
 4322    TO_CHAR(NVL(MIN(B.BYTES),'0'),'999,999,999,999') \"Smallest free chunk\"
 4323 FROM DBA_DATA_FILES A, DBA_FREE_SPACE B
 4324 WHERE A.FILE_ID = B.FILE_ID(+)
 4325 GROUP BY A.FILE_NAME, A.BYTES, A.TABLESPACE_NAME
 4326 ORDER BY $sortfield
 4327 ";
 4328 
 4329    logit("   SQL = $sql");
 4330    $cursor = $dbh->prepare($sql);
 4331    logit("   Error: $DBI::errstr") if $DBI::errstr;
 4332    $cursor->execute;
 4333    while (($file_name,$bytes,$tablespace_name,$frags,$largest,$smallest) = $cursor->fetchrow_array) {
 4334       print "<tr><td class=\"left\"><A HREF=$scriptname?database=$database&object_type=DATAFILE&arg=$file_name>$file_name</A></td>\n";
 4335       print "<td class=\"right\">$bytes</td>\n";
 4336       print "<td class=\"center\"><A HREF=$scriptname?database=$database&object_type=TSINFO&arg=$tablespace_name>$tablespace_name</A></td>\n";
 4337       print "<td class=\"right\">$frags</td>\n";
 4338       print "<td class=\"right\">$largest</td>\n";
 4339       print "<td class=\"right\">$smallest</td></TR>\n";
 4340    }
 4341    $cursor->finish;
 4342    print <<"EOF";
 4343 </table>
 4344 EOF
 4345 
 4346    logit("Exit subroutine fileFragReport");
 4347 
 4348 }
 4349 
 4350 
 4351 sub tsSpaceReport {
 4352 
 4353    logit("Enter subroutine tsSpaceReport");
 4354 
 4355    my ($sql,$link,$text,$sortfield,$owner,$tablespace_name,$bytes);
 4356    my ($highlight,$color,$count);
 4357 
 4358    $sortfield = $query->param('sortfield') || "4";
 4359    $highlight = "#FFFFC6";
 4360 
 4361    text("Click on a column name to change sort order.");
 4362 
 4363    print << "EOF";
 4364 <table class="ot">
 4365 EOF
 4366    if ($sortfield eq "1") {
 4367       $color = $highlight;
 4368    } else {
 4369       $color = $headingcolor;
 4370    }
 4371    print "   <th class=\"center\"><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=1>Owner</A></th>\n";
 4372    if ($sortfield eq "2") {
 4373       $color = $highlight;
 4374    } else {
 4375       $color = $headingcolor;
 4376    }
 4377    print "   <th class=\"center\"><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=2>Tablespace name</A></th>\n";
 4378    if ($sortfield eq "3") {
 4379       $sortfield = "3 DESC";
 4380       $color = $highlight;
 4381    } else {
 4382       $color = $headingcolor;
 4383    }
 4384    print "   <th class=\"center\"><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=3>Object count</A></th>\n";
 4385    if ($sortfield eq "4") {
 4386       $sortfield = "4 DESC";
 4387       $color = $highlight;
 4388    } else {
 4389       $color = $headingcolor;
 4390    }
 4391    print "   <th class=\"center\"><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=4>Bytes used</A></th>\n";
 4392 
 4393    $sql = "$copyright
 4394 SELECT
 4395    OWNER,
 4396    TABLESPACE_NAME,
 4397    TO_CHAR(COUNT(*),'999,999,999,999'),
 4398    TO_CHAR(SUM(BYTES),'999,999,999,999')
 4399 FROM DBA_SEGMENTS
 4400    GROUP BY OWNER, TABLESPACE_NAME
 4401    ORDER BY $sortfield
 4402 ";
 4403 
 4404    logit("   SQL = $sql");
 4405    $cursor = $dbh->prepare($sql);
 4406    $cursor->execute;
 4407    while (($owner,$tablespace_name,$count,$bytes) = $cursor->fetchrow_array) {
 4408       print "<tr><td class=\"center\"><A HREF=$scriptname?database=$database&object_type=OBJECTREPORT&arg=$owner>$owner</A></td>\n";
 4409       print "<td class=\"center\"><A HREF=$scriptname?database=$database&object_type=TSINFO&arg=$tablespace_name>$tablespace_name</td></A>\n";
 4410       print "<td class=\"right\">$count</td>\n";
 4411       print "<td class=\"right\">$bytes</td></tr>\n";
 4412    }
 4413    $cursor->finish;
 4414    print <<"EOF";
 4415 </table>
 4416 EOF
 4417 
 4418    logit("Exit subroutine tsSpaceReport");
 4419    
 4420 }
 4421 
 4422 sub objectReport {
 4423 
 4424    logit("Enter subroutine objectReport");
 4425 
 4426    my ($sql1,$sql2,$count,$counter,$cursor1,$cursor2);
 4427    my ($tablespace_name,$object_type,@tablespaces,$text,$link);
 4428    my ($segment_name,$segment_type,$created,$last_ddl_time,$bytes,$extents);
 4429 
 4430 # Show all objects for a particular user ordered by tablespace.
 4431 # This can be helpful for examining which tablespaces are expected
 4432 # to exist should you need to import this user's schema into a
 4433 # different database.
 4434 
 4435    $schema = $object_name unless ($schema);
 4436 
 4437    text("Object report for schema $schema ordered by tablespace.");
 4438 
 4439   $sql1 = "$copyright
 4440 SELECT DISTINCT 
 4441    TABLESPACE_NAME 
 4442 FROM DBA_SEGMENTS
 4443    WHERE OWNER = '$schema'
 4444    AND SEGMENT_TYPE NOT IN ('CACHE',
 4445                 'ROLLBACK',
 4446                 'TEMPORARY')
 4447 ORDER BY TABLESPACE_NAME
 4448 ";
 4449 
 4450    $count=0;
 4451    $cursor1 = $dbh->prepare($sql1); 
 4452    $cursor1->execute;
 4453    while ( $tablespace_name = $cursor1->fetchrow_array ) { 
 4454       push @tablespaces, $tablespace_name;
 4455       $count++;
 4456    }
 4457    $cursor1->finish;
 4458 
 4459 # Exit if user has no objects anywhere.
 4460    
 4461    if ($count == 0) {
 4462       print "<BR>$schema has no objects in this database.<BR>\n";
 4463       Footer();
 4464       exit;
 4465    } else {
 4466       print "<BR>Objects of type CACHE or TEMPORARY not shown.<BR>\n";
 4467       print "$schema has objects in $count tablespace(s).<P></CENTER>\n";
 4468       print "<B>Summary report:</B><P><CENTER>\n";
 4469    }
 4470 
 4471 # Print a summary report with object types and counts for each tablespace.
 4472 
 4473 print "<table>\n";
 4474 
 4475    foreach $tablespace_name (@tablespaces) {
 4476       print "  <tr>\n" if $counter == 0;
 4477       print <<"EOF";
 4478     <td style="vertical-align:top;">
 4479       <table class="ot">
 4480         <tr>
 4481           <td class="center" colspan=2">
 4482           $tablespace_name
 4483           </td>
 4484         </tr>
 4485         <th class="left">Object type</th>
 4486         <th class="left">Count</th>
 4487 EOF
 4488 
 4489       $sql1 = "$copyright
 4490 SELECT DISTINCT SEGMENT_TYPE 
 4491    FROM DBA_SEGMENTS
 4492 WHERE OWNER = '$schema'
 4493 AND TABLESPACE_NAME = '$tablespace_name'
 4494 AND SEGMENT_TYPE NOT IN ('CACHE',
 4495              'ROLLBACK',
 4496              'TEMPORARY')
 4497 ";
 4498 
 4499       $cursor1=$dbh->prepare($sql1);
 4500       $cursor1->execute;
 4501       while ($object_type = $cursor1->fetchrow_array) {
 4502          print "          <tr>\n";
 4503          print "            <td class=\"left\">\n";
 4504          print "              $object_type\n";
 4505          print "            </td>\n";
 4506          $sql2 = "$copyright
 4507 SELECT COUNT(*) 
 4508    FROM DBA_SEGMENTS 
 4509 WHERE OWNER = '$schema'
 4510 AND SEGMENT_TYPE = '$object_type'
 4511 AND TABLESPACE_NAME = '$tablespace_name'
 4512 ";
 4513          $cursor2=$dbh->prepare($sql2);
 4514          $cursor2->execute;
 4515          $count = $cursor2->fetchrow_array;
 4516          $cursor2->finish;
 4517          print "            <td class=\"right\">\n";
 4518          print "              $count\n";
 4519          print "            </td>\n";
 4520          print "          </tr>\n";
 4521       }
 4522       $cursor1->finish;
 4523       print "            </table>\n";
 4524       $counter++;
 4525 #      print "          </td>\n";
 4526 #      print "        </tr>\n";
 4527 #      print "      </table>\n";
 4528       print "    </tr>\n" if $counter == 0;
 4529       if ( $counter == 6 ) { $counter = 0 };
 4530    }
 4531    print "  </tr>\n";
 4532    print "</table>\n";
 4533 
 4534       print "</CENTER><P><HR WIDTH=100%><P><B>Detailed report:</B><P>\n";
 4535       foreach $tablespace_name (@tablespaces) {
 4536    
 4537       $sql2 = "$copyright
 4538 SELECT
 4539    A.SEGMENT_NAME                                       \"Object name\",
 4540    A.SEGMENT_TYPE                                       \"Object type\",
 4541    TO_CHAR(B.CREATED,'Month DD, YYYY - HH24:MI')          \"Created\",
 4542    TO_CHAR(B.LAST_DDL_TIME,'Month DD, YYYY - HH24:MI')    \"Last DDL time\",
 4543    TO_CHAR(A.BYTES,'999,999,999,999')                   \"Bytes\",
 4544    TO_CHAR(A.EXTENTS,'999,999,999,999')                 \"Extents\"
 4545 FROM DBA_SEGMENTS A, DBA_OBJECTS B
 4546    WHERE A.TABLESPACE_NAME = '$tablespace_name'
 4547    AND A.OWNER = '$schema'
 4548    AND B.OWNER = '$schema'
 4549    AND A.SEGMENT_NAME = B.OBJECT_NAME
 4550 ORDER BY 5 DESC, 2, 1
 4551 ";
 4552 #ORDER BY A.SEGMENT_TYPE, A.SEGMENT_NAME
 4553 
 4554       $text = "Tablespace $tablespace_name";
 4555       $link = "";
 4556       DisplayTable($sql2,$text,$link);
 4557    }
 4558 
 4559 # Show text based report
 4560 
 4561    print "<P>\n";
 4562 
 4563    format STDOUT = 
 4564 @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<< @>>>>>>>>>>>>>>>>>>> @>>>>>>>>>>>>>>
 4565 $segment_name,$segment_type,$created,$bytes,$extents
 4566 .
 4567 
 4568    print <<"EOF";
 4569 <table class="ot">
 4570   <tr>
 4571     <td>
 4572     <pre>
 4573 EOF
 4574    print "Object usage for user $schema database $database\n\n";
 4575    foreach $tablespace_name (@tablespaces) {
 4576 
 4577       print "Tablespace $tablespace_name\n";
 4578       print "===========================\n\n";
 4579       print "Object name                          Object type    Date created                       # Bytes       # Extents\n";
 4580       print "===========                          ===========    ============                       =======       =========\n";
 4581 
 4582       $sql = "$copyright
 4583 SELECT
 4584    A.SEGMENT_NAME                                       \"Object name\",
 4585    A.SEGMENT_TYPE                                       \"Object type\",
 4586    TO_CHAR(B.CREATED,'Mon DD, YYYY - HH24:MI')          \"Created\",
 4587    TO_CHAR(B.LAST_DDL_TIME,'Mon DD, YYYY - HH24:MI')    \"Last DDL time\",
 4588    TO_CHAR(A.BYTES,'999,999,999,999')                   \"Bytes\",
 4589    TO_CHAR(A.EXTENTS,'999,999,999,999')                 \"Extents\"
 4590 FROM DBA_SEGMENTS A, DBA_OBJECTS B
 4591    WHERE A.TABLESPACE_NAME = '$tablespace_name'
 4592    AND A.OWNER = '$schema'
 4593    AND B.OWNER = '$schema'
 4594    AND A.SEGMENT_NAME = B.OBJECT_NAME
 4595 ORDER BY A.SEGMENT_TYPE, A.SEGMENT_NAME
 4596 ";
 4597       $cursor = $dbh->prepare($sql) or print "$DBI::errstr\n";
 4598       $cursor->execute;
 4599       while (($segment_name,$segment_type,$created,$last_ddl_time,$bytes,$extents) = $cursor->fetchrow_array) {
 4600          $segment_name  =~ s/ //g;
 4601          $segment_type  =~ s/ //g;
 4602          $bytes     =~ s/ //g;
 4603          $extents   =~ s/ //g;
 4604 #         print "$segment_name,$segment_type,$created,$last_ddl_time,$bytes,$extents\n";
 4605          write;
 4606       }
 4607       $cursor->finish;
 4608       print "\n";
 4609    }
 4610    print<<"EOF";
 4611       </pre>
 4612     </td>
 4613   </tr>
 4614 </table> 
 4615 EOF
 4616       
 4617 
 4618    logit("Exit subroutine objectReport");
 4619 
 4620 }
 4621 
 4622 sub showObjects {
 4623 
 4624    logit("Enter subroutine showObjects");
 4625 
 4626    my ($sql,$text,$link,$infotext,$nulltext,$count,$table_name);
 4627    my ($cursor);
 4628 
 4629    logit("   Object is a $object_name");
 4630 
 4631 # Object types with spaces are not +'d at this point.
 4632    $object_type = $object_name;
 4633 
 4634    if ($object_type eq "DIRECTORY") {
 4635 
 4636       $sql = "
 4637 Select
 4638    directory_name       \"Name\",
 4639    owner                \"Owner\",
 4640    directory_path       \"Path\"
 4641 from dba_directories
 4642    where owner = '$schema'
 4643 ";
 4644 
 4645       $infotext = "No directories.";
 4646       $text = "Directories";
 4647       $link = "$scriptname?database=$database&schema=$schema&object_type=$object_type";
 4648       DisplayTable($sql,$text,$link,$infotext);
 4649 
 4650       Footer();
 4651 
 4652    }
 4653  
 4654    if ($object_type eq "SYNONYM") {
 4655 
 4656       $sql = "$copyright
 4657 SELECT
 4658    SYNONYM_NAME     \"Synonym name\",
 4659    TABLE_OWNER      \"Object owner\",
 4660    TABLE_NAME       \"Object name\",
 4661    DB_LINK      \"DB Link\"
 4662 FROM DBA_SYNONYMS
 4663    WHERE OWNER = '$schema'
 4664 ";
 4665 
 4666       $infotext = "No synonyms.";
 4667       $text = "Synonyms";
 4668       $link = "$scriptname?database=$database&schema=$schema&object_type=$object_type";
 4669       DisplayTable($sql,$text,$link,$infotext);
 4670 
 4671       Footer();
 4672    }
 4673    
 4674 # Check for the different types of tables.
 4675 
 4676    if ($object_type eq "TABLE") {
 4677 
 4678       $sql = "$copyright
 4679 SELECT
 4680    TABLE_NAME                           \"Table name\",
 4681    DEF_TABLESPACE_NAME                  \"Def. tablespace name\",
 4682    PARTITIONING_TYPE                    \"Partitioning type\",
 4683    PARTITION_COUNT                      \"Partition count\"
 4684 FROM DBA_PART_TABLES
 4685    WHERE OWNER = '$schema'
 4686 ORDER BY TABLE_NAME
 4687 ";
 4688 
 4689       $infotext = "No partitioned tables in this schema.";
 4690       $text = "Partitioned tables.";
 4691       $link = "$scriptname?database=$database&schema=$schema&object_type=$object_type";
 4692       DisplayTable($sql,$text,$link,$infotext);
 4693 
 4694 # Check for Index Organized Tables, because they won't show up in DBA_SEGMENTS
 4695 
 4696       $sql = "$copyright
 4697 SELECT
 4698    TABLE_NAME       \"Table name\"
 4699 FROM DBA_TABLES
 4700    WHERE OWNER = '$schema'
 4701 AND IOT_TYPE = 'IOT'
 4702 ORDER BY TABLE_NAME
 4703 ";
 4704       $infotext = "No Index Organized Tables in this schema.";
 4705       $text = "Index Organized Tables.";
 4706       $link = "$scriptname?database=$database&schema=$schema&object_type=$object_name";
 4707       DisplayTable($sql,$text,$link,$infotext);
 4708 
 4709 # Check for global temporary tables
 4710 
 4711       $sql = "$copyright
 4712 SELECT
 4713    TABLE_NAME       \"Table name\"
 4714 FROM DBA_TABLES
 4715    WHERE OWNER = '$schema'
 4716 AND TEMPORARY = 'Y'
 4717 ORDER BY TABLE_NAME
 4718 ";
 4719       $infotext = "No global temporary tables in this schema.";
 4720       $text = "Global temporary tables.";
 4721       $link = "$scriptname?database=$database&schema=$schema&object_type=$object_name";
 4722       DisplayTable($sql,$text,$link,$infotext);
 4723 
 4724 # Now, show the normal tables.
 4725 
 4726       logit("   Start standard tables");
 4727 
 4728       $sql = "$copyright
 4729 Select 
 4730    dt.table_name                            \"Table name\",
 4731    dt.tablespace_name                           \"Tablespace name\",
 4732    nvl(to_char(ds.bytes,'999,999,999,999'),'No space allocated')    \"Bytes\"
 4733 from dba_tables dt, dba_segments ds
 4734    where dt.owner = '$schema'
 4735    and dt.owner = ds.owner (+)
 4736    and dt.table_name = ds.segment_name (+)
 4737 order by segment_name
 4738 ";
 4739 
 4740 #
 4741 # This did not return tables when there werre no extents allocated
 4742 #
 4743 #      $sql = "$copyright
 4744 #SELECT
 4745 #   SEGMENT_NAME                         \"Object name\",
 4746 #   TABLESPACE_NAME                      \"Tablespace name\",
 4747 #   TO_CHAR(BYTES,'999,999,999,999')     \"Bytes\"
 4748 #FROM DBA_SEGMENTS
 4749 #   WHERE OWNER = '$schema'
 4750 #   AND SEGMENT_TYPE = '$object_type'
 4751 #ORDER BY SEGMENT_NAME
 4752 #";
 4753 
 4754       $text = "Standard tables.";
 4755       $infotext = "No standard tables in this schema.";
 4756       $link = "$scriptname?database=$database&schema=$schema&object_type=$object_name";
 4757       DisplayTable($sql,$text,$link,$infotext);
 4758 
 4759       Footer();
 4760    }
 4761 
 4762 # If object is an index, show the space used
 4763 
 4764    if ($object_type eq "INDEX") {
 4765 
 4766 # Show partitioned indexes.
 4767 
 4768       $sql = "$copyright
 4769 SELECT
 4770    INDEX_NAME                           \"Index name\",
 4771    DEF_TABLESPACE_NAME                  \"Def. tablespace name\",
 4772    PARTITIONING_TYPE                    \"Partitioning type\",
 4773    PARTITION_COUNT                      \"Partition count\"
 4774 FROM DBA_PART_INDEXES
 4775    WHERE OWNER = '$schema'
 4776 ";
 4777 
 4778       $infotext = "No partitioned indexes in this schema.";
 4779       $text = "Partitioned indexes.";
 4780       $link = "$scriptname?database=$database&schema=$schema&object_type=$object_type";
 4781       DisplayTable($sql,$text,$link,$infotext);
 4782 
 4783 # Show bitmapped indexes
 4784 
 4785       $sql = "$copyright
 4786 SELECT
 4787    INDEX_NAME               \"Index name\",
 4788    TABLESPACE_NAME          \"Tablespace name\"
 4789 FROM DBA_INDEXES
 4790    WHERE OWNER = '$schema'
 4791    AND INDEX_TYPE = 'BITMAP'
 4792 ORDER BY INDEX_NAME
 4793 ";
 4794       $text = "Bitmapped indexes.";
 4795       $infotext = "No bitmapped indexes in this schema.";
 4796       $link = "$scriptname?database=$database&schema=$schema&object_type=$object_type";
 4797       DisplayTable($sql,$text,$link,$infotext);
 4798 
 4799 # Show IOT indexes
 4800 
 4801       $sql = "$copyright
 4802 SELECT
 4803    INDEX_NAME               \"Index name\",
 4804    TABLESPACE_NAME          \"Tablespace name\"
 4805 FROM DBA_INDEXES
 4806    WHERE OWNER = '$schema'
 4807    AND INDEX_TYPE LIKE '\%IOT\%'
 4808 ORDER BY INDEX_NAME
 4809 ";
 4810       $text = "Index Organized Table indexes.";
 4811       $infotext = "No Index Organized Table indexes in this schema.";
 4812       $link = "$scriptname?database=$database&schema=$schema&object_type=$object_type";
 4813       DisplayTable($sql,$text,$link,$infotext);
 4814 
 4815 # Show standard indexes
 4816 
 4817       $sql = "$copyright
 4818 SELECT
 4819    INDEX_NAME               \"Object name\",
 4820    TABLESPACE_NAME          \"Tablespace name\"
 4821 FROM DBA_INDEXES
 4822    WHERE OWNER = '$schema'
 4823    AND INDEX_TYPE = 'NORMAL'
 4824    AND PARTITIONED = 'NO'
 4825 ";
 4826       $text = "Standard indexes.";
 4827       $infotext = "No standard indexes in this schema.";
 4828       $link = "$scriptname?database=$database&schema=$schema&object_type=$object_type";
 4829       DisplayTable($sql,$text,$link,$infotext);
 4830 
 4831       exit;
 4832 
 4833    }
 4834 
 4835 # If object is of type partitioned, then
 4836 # show the subobject as well.
 4837 
 4838    if ($object_type eq "TABLE PARTITION") {
 4839 
 4840       $sql = "$copyright
 4841 SELECT
 4842    TABLE_NAME                                   \"Table name\",
 4843    PARTITION_NAME                               \"Partition name\",
 4844    TABLE_OWNER                                  \"Owner\",
 4845    TABLESPACE_NAME                              \"Tablespace\",
 4846    PARTITION_POSITION                           \"Position\",
 4847    TO_CHAR(INITIAL_EXTENT,'999,999,999,999')    \"Initial\",
 4848    TO_CHAR(NEXT_EXTENT,'999,999,999,999')       \"Next\",
 4849    TO_CHAR(MAX_EXTENT,'999,999,999,999')        \"Max extents\",
 4850    PCT_INCREASE                                 \"Pct increase\",
 4851    HIGH_VALUE                                   \"High value\",
 4852    HIGH_VALUE_LENGTH                            \"High value length\",
 4853    LOGGING                                      \"Logging\"
 4854 FROM DBA_TAB_PARTITIONS
 4855    WHERE TABLE_OWNER = '$schema'
 4856 ORDER BY TABLE_NAME, PARTITION_POSITION
 4857 ";
 4858 
 4859       $text = "Select a partition for info about the parent table.";
 4860       $link = "$scriptname?database=$database&schema=$schema&object_type=TABLE";
 4861       DisplayTable($sql,$text,$link,$infotext);
 4862       logit("   Link = $link");
 4863 
 4864       exit;
 4865 
 4866    }
 4867 
 4868    if ($object_type eq "INDEX PARTITION") {
 4869 
 4870       $sql = "$copyright
 4871 SELECT
 4872    INDEX_NAME                   \"Index name\",
 4873    PARTITION_NAME                               \"Partition name\",
 4874    TABLESPACE_NAME                              \"Tablespace\",
 4875    PARTITION_POSITION                           \"Position\",
 4876    TO_CHAR(INITIAL_EXTENT,'999,999,999,999')    \"Initial\",
 4877    TO_CHAR(NEXT_EXTENT,'999,999,999,999')       \"Next\",
 4878    TO_CHAR(MAX_EXTENT,'999,999,999,999')        \"Max extents\",
 4879    PCT_INCREASE                                 \"Pct increase\",
 4880    HIGH_VALUE                                   \"High value\",
 4881    HIGH_VALUE_LENGTH                            \"High value length\",
 4882    LOGGING                                      \"Logging\"
 4883 FROM DBA_IND_PARTITIONS
 4884    WHERE INDEX_OWNER = '$schema'
 4885 ORDER BY INDEX_NAME
 4886 ";
 4887 
 4888       $text = "Select a partition for info about the parent index.";
 4889       $link = "$scriptname?database=$database&schema=$schema&object_type=INDEX";
 4890       DisplayTable($sql,$text,$link,$infotext);
 4891       logit("   Link = $link");
 4892 
 4893       exit;
 4894 
 4895    }
 4896 
 4897 # Show all for sequences
 4898 
 4899    if ($object_type eq "SEQUENCE") {
 4900 
 4901        $sql = "$copyright
 4902 SELECT
 4903    SEQUENCE_NAME                \"Sequence name\",
 4904    MIN_VALUE                                    \"Min value\",
 4905    MAX_VALUE                                    \"Max value\",
 4906    INCREMENT_BY                                 \"Increment by\",
 4907    CYCLE_FLAG                                   \"Cycle flag\",
 4908    ORDER_FLAG                                   \"Order flag\",
 4909    CACHE_SIZE                                   \"Cache size\",
 4910    LAST_NUMBER                                  \"Last number\"
 4911 FROM DBA_SEQUENCES
 4912    WHERE SEQUENCE_OWNER = '$schema'
 4913 ";
 4914       $text = "Sequences owned by $schema..";
 4915       DisplayTable($sql,$text);
 4916 
 4917       exit;
 4918 
 4919    }
 4920 
 4921 # Show public synonym info
 4922 
 4923    if ($object_type eq "PUBLIC SYNONYMS") {
 4924 
 4925       $sql = "$copyright
 4926 SELECT
 4927    SYNONYM_NAME     \"Synonym name\",
 4928    TABLE_NAME       \"Object name\",
 4929    TABLE_OWNER      \"Object owner\",
 4930    DB_LINK      \"DB link\"
 4931 FROM DBA_SYNONYMS
 4932    WHERE OWNER = 'PUBLIC'
 4933    AND TABLE_OWNER = '$schema'
 4934 ";
 4935       my $text = "All public synonyms pointing to $schema objects.";
 4936       my $link = "";
 4937       my $infotext = "No public synonyms are pointing to $schema objects.";
 4938       DisplayTable($sql,$text,$link,$infotext);
 4939    }
 4940 
 4941 # Default sql
 4942 
 4943       $sql = "$copyright
 4944 SELECT 
 4945    OBJECT_NAME                  \"Object name\" 
 4946 FROM DBA_OBJECTS 
 4947    WHERE OWNER = '$schema' 
 4948    AND OBJECT_TYPE = '$object_type'
 4949    ORDER BY OBJECT_NAME
 4950 ";
 4951 
 4952    $link = "$scriptname?database=$database&schema=$schema&object_type=$object_name";
 4953    DisplayTable($sql,$text,$link);
 4954 
 4955    logit("Exit subroutine showObjects");
 4956 
 4957 }
 4958 
 4959 sub showSynonym {
 4960 
 4961    logit("Enter subroutine showSynonym");
 4962 
 4963    my ($sql,$text,$link);
 4964 
 4965 # General synonym info
 4966 
 4967    $sql = "$copyright
 4968 SELECT 
 4969    SYNONYM_NAME             \"Synonym name\", 
 4970    TABLE_NAME               \"Object name\", 
 4971    TABLE_OWNER              \"Table owner\", 
 4972    DB_LINK              \"Database link\"
 4973 FROM DBA_SYNONYMS 
 4974    WHERE SYNONYM_NAME = '$object_name' 
 4975    AND OWNER = '$schema'
 4976 ";
 4977    $text = "";
 4978    $link = "";
 4979    DisplayTable($sql,$text,$link);
 4980 
 4981    logit("Exit subroutine showSynonym");
 4982 
 4983 }
 4984 
 4985 sub showTablespaces {
 4986 
 4987    logit("Enter subroutine showTablespaces");
 4988 
 4989    my ($sql,$text,$link,$temp_groups);
 4990 
 4991 # Tablespace graph button
 4992 
 4993    Button("$scriptname?database=$database&object_type=TSGRAPH TARGET=body","Allocation graph","$headingcolor");
 4994 
 4995 
 4996 #  Started adding for temporary tablespace groups.
 4997 
 4998    $sql = "Select count(*) from dba_tablespace_groups";
 4999    $temp_groups = recordCount($dbh,$sql);
 5000    if ($temp_groups) {
 5001       logit("There are temporary tablespace groups. Adding a button.");
 5002       print <<"EOF";
 5003 <P>
 5004 <table>
 5005   <tr>
 5006     <td class="nobordercenter">
 5007       <form method="GET" ACTION="$scriptname">
 5008         
 5009         <input type="HIDDEN" NAME="database" VALUE="$database">
 5010         <input type="HIDDEN" NAME="object_type" VALUE="TEMP_TS_GROUPS">
 5011         <input type="SUBMIT" NAME="tsgraph" VALUE="Temp tablespace groups">
 5012       </form>
 5013     </td>
 5014   </tr>
 5015 </table>
 5016 EOF
 5017    }
 5018 
 5019    $sql = "$copyright
 5020 SELECT
 5021    TO_CHAR(SUM(BYTES),'999,999,999,999,999')    \"Total allocated space\"
 5022 FROM DBA_DATA_FILES
 5023 ";
 5024 
 5025    $text = "";
 5026    $link = "";
 5027    DisplayTable($sql,$text,$link);
 5028 
 5029 # General tablespace information
 5030 
 5031    $sql = "$copyright
 5032 SELECT
 5033    TABLESPACE_NAME                      \"Tablespace name\",
 5034    TO_CHAR(INITIAL_EXTENT,'999,999,999,999')            \"Initial extent\",
 5035    TO_CHAR(NEXT_EXTENT,'999,999,999,999')           \"Next extent\",
 5036    TO_CHAR(MAX_EXTENTS,'999,999,999,999')           \"Max extents\",
 5037    TO_CHAR(MIN_EXTLEN,'999,999,999,999')            \"Minimum extent\",
 5038    PCT_INCREASE                         \"% increase\",
 5039    STATUS                           \"Status\",
 5040    CONTENTS                         \"Contents\",
 5041    LOGGING                          \"Logging?\",
 5042    SEGMENT_SPACE_MANAGEMENT                 \"Seg. Mgmt\",
 5043    EXTENT_MANAGEMENT                        \"Ext. Mgmt\",
 5044    ALLOCATION_TYPE                      \"Alloc type\",
 5045    PLUGGED_IN                           \"Plugged?\",
 5046    BIGFILE                          \"Bigfile?\",
 5047    RETENTION                            \"Retention\"
 5048 FROM DBA_TABLESPACES 
 5049 ORDER BY TABLESPACE_NAME
 5050 ";
 5051 
 5052    $text = "Tablespace information: Database $database";
 5053    $link = "$scriptname?database=$database&object_type=TSINFO";
 5054    DisplayTable($sql,$text,$link);
 5055 
 5056    logit("Exit subroutine showTablespaces");
 5057 
 5058 }
 5059 
 5060 sub tempTsGroups {
 5061 
 5062    logit("Enter subroutine tempTsGroups");
 5063 
 5064    my ($sql,$text,$link);
 5065 
 5066    $sql = "$copyright
 5067 SELECT
 5068    TABLESPACE_NAME                      \"Tablespace name\",
 5069    GROUP_NAME                           \"Group name\"
 5070 FROM DBA_TABLESPACE_GROUPS
 5071 ORDER BY TABLESPACE_NAME
 5072 ";
 5073 
 5074    $text = "Temporary tablespace groups";
 5075    $link = "$scriptname?database=$database&object_type=TSINFO";
 5076    DisplayTable($sql,$text,$link);
 5077 
 5078 
 5079    logit("Exit subroutine tempTsGroups");
 5080 }
 5081 
 5082    
 5083 
 5084 sub showTSinfo {
 5085 
 5086    logit("Enter subroutine showTSinfo");
 5087 
 5088    my ($sql,$cursor,$count,$foo,$text,$link,$infotext,$contents,$extent_management);
 5089    my ($tempfiles_used);
 5090 
 5091    refreshButton();
 5092 
 5093    # Check to see if tablespace uses tempfiles, if OracleI.
 5094 
 5095    $sql = "
 5096 SELECT CONTENTS, EXTENT_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = ?
 5097 ";
 5098 
 5099    $cursor = $dbh->prepare($sql);
 5100    $cursor->execute($object_name);
 5101    ($contents,$extent_management) = $cursor->fetchrow_array;
 5102    $cursor->finish;
 5103    if (($contents eq "TEMPORARY") && ($extent_management eq "LOCAL")) {
 5104       $tempfiles_used = "Yep";
 5105    }
 5106 
 5107    logit("Contents of tablespace $object_name are $contents, extent management $extent_management.");
 5108 
 5109 # Tablespace information
 5110 
 5111    $sql = "$copyright
 5112 SELECT
 5113    DTS.TABLESPACE_NAME                      \"Tablespace name\",
 5114    TO_CHAR(DTS.INITIAL_EXTENT,'999,999,999,999')        \"Initial extent\",
 5115    TO_CHAR(DTS.NEXT_EXTENT,'999,999,999,999')           \"Next extent\",
 5116    TO_CHAR(DTS.MAX_EXTENTS,'999,999,999,999')           \"Max extents\",
 5117    TO_CHAR(TSD.DFLMINLEN*$db_block_size,'999,999,999,999')  \"Minimum extent\",
 5118    DTS.PCT_INCREASE                     \"% increase\",
 5119    DTS.STATUS                           \"Status\",
 5120    DTS.CONTENTS                         \"Contents\",
 5121    DTS.LOGGING                          \"Logging?\",
 5122    DTS.SEGMENT_SPACE_MANAGEMENT                 \"Seg. Mgmt\",
 5123    DTS.EXTENT_MANAGEMENT                    \"Ext. Mgmt\",
 5124    DTS.ALLOCATION_TYPE                      \"Alloc type\",
 5125    DTS.PLUGGED_IN                       \"Plugged?\"
 5126 FROM DBA_TABLESPACES DTS, SYS.TS\$ TSD
 5127    WHERE DTS.TABLESPACE_NAME = '$object_name'
 5128    AND TSD.NAME = '$object_name'
 5129 ";
 5130 
 5131    $text = "General information: Tablespace $object_name";
 5132    $link = "$scriptname?database=$database&object_type=TSDDL";
 5133    DisplayTable($sql,$text,$link);
 5134 
 5135 # Space allocation
 5136 
 5137    $sql = "$copyright
 5138 SELECT
 5139    DF.TABLESPACE_NAME                       \"Tablespace name\",
 5140    TO_CHAR(DF.BYTES,'999,999,999,999,999,999')              \"Bytes allocated\",
 5141    NVL(TO_CHAR(DF.BYTES-SUM(FS.BYTES),'999,999,999,999,999,999'),    
 5142         TO_CHAR(DF.BYTES,'999,999,999,999,999,999'))            \"Bytes used\", 
 5143    NVL(TO_CHAR(SUM(FS.BYTES),'999,999,999,999,999,999'),0)      \"Bytes free\",
 5144    NVL(ROUND((DF.BYTES-SUM(FS.BYTES))*100/DF.BYTES),100)||'%'   \"Percent used\",
 5145    NVL(ROUND(SUM(FS.BYTES)*100/DF.BYTES),0)||'%'            \"Percent free\" 
 5146 FROM DBA_FREE_SPACE FS,
 5147    (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY
 5148 TABLESPACE_NAME ) DF
 5149 WHERE FS.TABLESPACE_NAME (+) = DF.TABLESPACE_NAME
 5150 AND DF.TABLESPACE_NAME = '$object_name'
 5151 GROUP BY DF.TABLESPACE_NAME, DF.BYTES
 5152 ORDER BY \"Percent free\"
 5153 ";
 5154 
 5155    # Added for temporary files which are managed locally. Space stats come from 
 5156    # V$TEMP_EXTENT_POOL and V$TEMP_EXTENT_MAP. Oracle"I" only.
 5157 
 5158    if ($tempfiles_used) {
 5159       logit("   Checking space for temp files");
 5160       $sql = "$copyright
 5161 SELECT
 5162    DF.TABLESPACE_NAME                                           \"Tablespace name\",
 5163    TO_CHAR(DF.BYTES,'999,999,999,999')                          \"Bytes allocated\",
 5164    NVL(TO_CHAR(SUM(FS.BYTES_USED),'999,999,999,999'),0)         \"Bytes used\",
 5165    NVL(TO_CHAR(SUM(FS.BYTES_CACHED),'999,999,999,999'),0)   \"Bytes cached\",
 5166    NVL(TO_CHAR(DF.BYTES-SUM(FS.BYTES_USED),'999,999,999,999'),
 5167         TO_CHAR(DF.BYTES,'999,999,999,999'))                    \"Bytes free\",
 5168    NVL(ROUND(SUM(FS.BYTES_USED)*100/DF.BYTES),0)||'%'           \"Percent used\",
 5169    NVL(ROUND((DF.BYTES-SUM(FS.BYTES_USED))*100/DF.BYTES),100)||'%' \"Percent free\"
 5170 FROM V\$TEMP_EXTENT_POOL FS,
 5171    (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_TEMP_FILES GROUP BY
 5172 TABLESPACE_NAME ) DF
 5173 WHERE FS.TABLESPACE_NAME (+) = DF.TABLESPACE_NAME
 5174 AND DF.TABLESPACE_NAME = '$object_name'
 5175 GROUP BY DF.TABLESPACE_NAME, DF.BYTES
 5176 ORDER BY \"Percent free\"
 5177 ";
 5178 
 5179       $text = "Space allocation";
 5180       $link = "";
 5181       DisplayTable($sql,$text,$link);
 5182 
 5183       logit("Checking temp segment usage");
 5184       $sql = "
 5185 SELECT 
 5186    TO_CHAR(B.BLOCKS*P.VALUE,'999,999,999,999,999')  \"Size\",
 5187            A.SID                                        \"Sid\",
 5188            A.SERIAL#                    \"Serial#\",
 5189            A.USERNAME                   \"Username\",
 5190            A.PROGRAM                    \"Program\"
 5191     FROM SYS.V_\$SESSION A,
 5192            SYS.V_\$SORT_USAGE B,
 5193            SYS.V_\$PARAMETER P
 5194     WHERE B.TABLESPACE = '$object_name'
 5195     AND P.NAME  = 'db_block_size'
 5196     AND A.SADDR = B.SESSION_ADDR
 5197     ORDER BY B.TABLESPACE, B.BLOCKS
 5198 ";
 5199 
 5200       $text = "Temp segment usage";
 5201       $link = "";
 5202       $infotext = "No temp segments in use for this tablespace right now.";
 5203       DisplayTable($sql,$text,$link,$infotext);
 5204 
 5205       logit("Done checking temp segment usage");
 5206    }
 5207 
 5208 # Fragmentation / general info
 5209 
 5210    $sql = "$copyright
 5211 SELECT
 5212    A.FILE_NAME                          \"File name\",
 5213    A.FILE_ID                            \"File #\",
 5214    TO_CHAR(A.BYTES,'999,999,999,999')               \"Bytes\",
 5215    TO_CHAR(NVL(MAX(B.BYTES),'0'),'999,999,999,999')     \"Largest free chunk\",
 5216    TO_CHAR(NVL(MIN(B.BYTES),'0'),'999,999,999,999')     \"Smallest free chunk\",
 5217    COUNT(*)                         \"Pieces\",
 5218    DECODE(A.AUTOEXTENSIBLE,
 5219                            'YES','Yes',
 5220                            'NO','No')               \"Xtend?\",
 5221    TO_CHAR(A.MAXBYTES,'999,999,999,999')            \"Max bytes\",
 5222    TO_CHAR(A.INCREMENT_BY*$db_block_size,'999,999,999,999') \"Increment\"
 5223 FROM DBA_DATA_FILES A, DBA_FREE_SPACE B
 5224    WHERE A.FILE_ID = B.FILE_ID(+)
 5225    AND A.TABLESPACE_NAME = '$object_name'
 5226    GROUP BY A.FILE_NAME, A.FILE_ID,
 5227    A.BYTES,A.AUTOEXTENSIBLE,A.MAXBYTES,A.INCREMENT_BY
 5228 ";
 5229 
 5230    $text = "Tablespace (datafile) fragmentation";
 5231 
 5232    # Added for temporary files which are managed locally. Space stats come from 
 5233    # V$TEMP_EXTENT_POOL and V$TEMP_EXTENT_MAP. Oracle"I" only.
 5234 
 5235    if ($tempfiles_used) {
 5236       logit("   Checking space for temp files");
 5237       $sql = "$copyright
 5238 SELECT 
 5239    FILE_NAME                            \"Tempfile name\",
 5240    FILE_ID                          \"File #\", 
 5241    TO_CHAR(BYTES,'999,999,999,999')             \"Bytes\",
 5242    DECODE(AUTOEXTENSIBLE,
 5243                            'YES','Yes',
 5244                            'NO','No')               \"Xtend?\",
 5245    TO_CHAR(MAXBYTES,'999,999,999,999')          \"Max bytes\",
 5246    TO_CHAR(INCREMENT_BY*$db_block_size,'999,999,999,999')   \"Increment\"
 5247 FROM DBA_TEMP_FILES
 5248 WHERE TABLESPACE_NAME = '$object_name'
 5249 ORDER BY FILE_NAME
 5250 ";
 5251 
 5252    $text = "Temporary datafile information";
 5253 
 5254    }
 5255 
 5256    $link = "$scriptname?database=$database&object_type=DATAFILE";
 5257    DisplayTable($sql,$text,$link);
 5258 
 5259    unless ($tempfiles_used) {
 5260 
 5261       print <<"EOF";
 5262 <br>
 5263 <table class="noborder">
 5264   <tr>
 5265     <td style="vertical-align:top;">
 5266     
 5267 EOF
 5268 
 5269       $sql = "$copyright
 5270 SELECT DISTINCT
 5271    TO_CHAR(BYTES,'999,999,999,999')     \"Extent size\",
 5272    TO_CHAR(COUNT(*),'999,999,999,999')      \"# extents\"
 5273 FROM DBA_EXTENTS
 5274    WHERE TABLESPACE_NAME = '$object_name'
 5275 GROUP BY BYTES 
 5276 ORDER BY 1 DESC
 5277 ";
 5278 
 5279       $text = "Used extent sizes / counts.";
 5280       DisplayTable($sql,$text);
 5281 
 5282       print <<"EOF";
 5283     </td>
 5284     <td style="width:25px;"><td>
 5285     <td style="vertical-align:top;">
 5286     
 5287 EOF
 5288 
 5289 
 5290    $sql = "$copyright
 5291 SELECT DISTINCT
 5292    TO_CHAR(BYTES,'999,999,999,999')     \"Extent size\",
 5293    TO_CHAR(COUNT(*),'999,999,999,999')      \"# extents\"
 5294 FROM DBA_FREE_SPACE
 5295    WHERE TABLESPACE_NAME = '$object_name'
 5296 GROUP BY BYTES 
 5297 ORDER BY 1 DESC
 5298 ";
 5299 
 5300       $text = "Free extent sizes / counts.";
 5301       DisplayTable($sql,$text);
 5302 
 5303       print <<"EOF";
 5304           </td>
 5305         </tr>
 5306       </table>
 5307 EOF
 5308 
 5309    # Extent info for temp file based tablespaces
 5310 
 5311     } else {
 5312 
 5313    # Tempfile extent information..
 5314 
 5315 #      $sql = "$copyright
 5316 #SELECT DISTINCT
 5317 #   TO_CHAR(BYTES_USED,'999,999,999,999')   \"Extent size\",
 5318 #   TO_CHAR(COUNT(*),'999,999,999,999')     \"# extents\"
 5319 #FROM V\$TEMP_EXTENT_POOL
 5320 #   WHERE TABLESPACE_NAME = '$object_name'
 5321 #   AND BYTES_USED > 0
 5322 #GROUP BY BYTES_USED
 5323 #ORDER BY 1 DESC
 5324 #";
 5325 
 5326    }
 5327 
 5328    unless ($tempfiles_used) {
 5329 
 5330 # Added this to check for DBA_FREE_SPACE
 5331 # returning a null value if there is no
 5332 # free space
 5333 
 5334       $sql = "$copyright
 5335 SELECT MAX(BYTES)
 5336    FROM DBA_FREE_SPACE
 5337 WHERE TABLESPACE_NAME = '$object_name'
 5338 ";
 5339       $cursor=$dbh->prepare($sql);
 5340       $cursor->execute;
 5341       $foo = $cursor->fetchrow_array;
 5342       if ($foo) {
 5343    
 5344 
 5345 # Objects in the tablespace with next extent sizes larger than the largest
 5346 # free extent in the tablespace. Allocating a next extent for these objects
 5347 # will fail.
 5348 
 5349          $sql = "$copyright
 5350 SELECT 
 5351    SEGMENT_NAME                 \"Object name\", 
 5352    OWNER                    \"Owner\",
 5353    TO_CHAR(NEXT_EXTENT,'999,999,999,999')   \"Next extent\"
 5354 FROM DBA_SEGMENTS
 5355    WHERE TABLESPACE_NAME = '$object_name'
 5356    AND NEXT_EXTENT > (SELECT MAX(BYTES) FROM DBA_FREE_SPACE
 5357 WHERE TABLESPACE_NAME = '$object_name')
 5358 ";
 5359          $text = "Objects that will fail to allocate a next extent";
 5360          $link = "";
 5361          $infotext = "No objects in $object_name will fail to allocate a next extent.";
 5362          DisplayTable($sql,$text,$link,$infotext);
 5363        } else {
 5364          message("Warning: No objects can allocate an extent. Add a datafile.");
 5365       }
 5366       undef $foo;
 5367 
 5368 # Objects in the tablespace that are approaching their max_extents limit.
 5369 
 5370       $sql = "$copyright
 5371 SELECT 
 5372    SEGMENT_NAME                 \"Object name\",
 5373    OWNER                    \"Owner\",
 5374    SEGMENT_TYPE                 \"Object type\",
 5375    TO_CHAR(EXTENTS,'999,999,999,999')       \"Extents\",
 5376    TO_CHAR(MAX_EXTENTS,'999,999,999,999')   \"Max extents\"
 5377 FROM DBA_SEGMENTS
 5378    WHERE TABLESPACE_NAME = '$object_name'
 5379    AND EXTENTS > (MAX_EXTENTS-25)
 5380    AND SEGMENT_TYPE != 'CACHE'
 5381 ";
 5382 
 5383       $text = "Objects that are approaching their max_extents limit";
 5384       $link = "";
 5385       $infotext = "No objects in $object_name have extents > ( max_extents - 25 )";
 5386       DisplayTable($sql,$text,$link,$infotext);
 5387 
 5388 # Display a button for a screen with a datafile fragmentation map.
 5389   
 5390       Button("$scriptname?database=$database&object_type=FRAGMAP&arg=$object_name&whereclause=tablespace TARGET=body","Fragmentation map","$headingcolor","CENTER","200");
 5391       Button("$scriptname?database=$database&object_type=FRAGLIST&arg=$object_name&whereclause=tablespace TARGET=body","Extent listing","$headingcolor","CENTER","200");
 5392       Button("$scriptname?database=$database&object_type=TSFILEGRAPH&schema=$object_name TARGET=body","Datafile information","$headingcolor","CENTER","200");
 5393    } else {
 5394       print "<P>";
 5395       Button("$scriptname?database=$database&object_type=TSFILEGRAPH&schema=$object_name&tempfiles=yep TARGET=body","Datafile information","$headingcolor","CENTER","200");
 5396    }
 5397 
 5398    $sql = "SELECT COUNT(*) FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = '$object_name'";
 5399    $cursor = $dbh->prepare($sql);
 5400    $cursor->execute;
 5401    $count = $cursor->fetchrow_array;
 5402    $cursor->finish;
 5403 
 5404    if ($count) {
 5405       Button("$scriptname?database=$database&object_type=SHOWTSOBJECTS&arg=$object_name TARGET=body","Display $count object(s)","$headingcolor","CENTER","200");
 5406    }
 5407 
 5408    logit("Exit subroutine showTSinfo");
 5409 
 5410 }
 5411 
 5412 sub showTSobjects {
 5413 
 5414    logit("Enter subroutine showTSobjects");
 5415 
 5416    my ($sql,$text,$link,$infotext);
 5417 
 5418    $sql = "$copyright
 5419 SELECT 
 5420    A.SEGMENT_NAME                   \"Object name\",
 5421    A.SEGMENT_TYPE                   \"Object type\",
 5422    A.OWNER                      \"Owner\",
 5423    TO_CHAR(B.CREATED,'Month DD, YYYY - HH24:MI')        \"Created\",
 5424    TO_CHAR(B.LAST_DDL_TIME,'Month DD, YYYY - HH24:MI')    \"Last DDL time\",
 5425    TO_CHAR(A.BYTES,'999,999,999,999')           \"Bytes\",
 5426    TO_CHAR(A.INITIAL_EXTENT,'999,999,999,999')      \"Initial extent\",
 5427    TO_CHAR(A.NEXT_EXTENT,'999,999,999,999')     \"Next extent\",
 5428    TO_CHAR(A.EXTENTS,'999,999,999,999')         \"Extents\"
 5429 FROM DBA_SEGMENTS A, DBA_OBJECTS B
 5430    WHERE A.TABLESPACE_NAME = '$object_name'
 5431    AND A.SEGMENT_NAME = B.OBJECT_NAME
 5432    AND A.SEGMENT_TYPE = B.OBJECT_TYPE
 5433    AND A.OWNER = B.OWNER
 5434 ORDER BY A.OWNER, A.SEGMENT_TYPE, A.SEGMENT_NAME
 5435 ";
 5436    $text = "Object list for tablespace $object_name";
 5437    $link = "";
 5438    $infotext = "Tablespace $object_name has no objects.";
 5439    ObjectTable($sql,$text,$infotext);
 5440 
 5441    logit("Exit subroutine showTSobjects");
 5442 
 5443 }
 5444 
 5445 sub sessionWaitByEvent {
 5446 
 5447    logit("Enter subroutine sessionWaitByEvent");
 5448 
 5449    my ($sql,$text,$link,$infotext);
 5450 
 5451    refreshButton();
 5452 
 5453    $sql = "$copyright
 5454 SELECT
 5455    EVENT        \"Waiting on\",
 5456    MAX(SECONDS_IN_WAIT) \"Seconds waiting\"
 5457 FROM V\$SESSION_WAIT
 5458    GROUP BY EVENT
 5459    ORDER BY 2 DESC
 5460 ";
 5461    $text = "Session wait information by event / time.";
 5462    $link = "";
 5463    $infotext = "There are no sessions in a wait state.";
 5464    DisplayTable($sql,$text,$link,$infotext);
 5465 
 5466    logit("Exit subroutine sessionWaitByEvent");
 5467 }
 5468 
 5469 sub sessionWait {
 5470 
 5471    logit("Enter subroutine sessionWait");
 5472 
 5473    my ($sql,$text,$link,$infotext,$refreshrate);
 5474 
 5475    $refreshrate = $ENV{'AUTO_REFRESH'} || "10";
 5476 
 5477    unless ($norefreshbutton) {
 5478 
 5479       print <<"EOF";
 5480   <FORM METHOD="POST" ACTION="$scriptname">
 5481     
 5482     <input type=HIDDEN NAME=database    VALUE=$database>
 5483     <input type=HIDDEN NAME=object_type VALUE=$object_type>
 5484     <input type=HIDDEN NAME=arg         VALUE=$object_name>
 5485     <input type=HIDDEN NAME=refreshrate VALUE=$refreshrate>
 5486     <input type=SUBMIT NAME=foobar      VALUE=\"AutoRefresh ($refreshrate)\">
 5487   </FORM>
 5488   <P>
 5489 EOF
 5490 
 5491    }
 5492 
 5493    $sql = "$copyright
 5494 SELECT 
 5495    VS.USERNAME                      \"Username\",
 5496    VS.OSUSER                        \"OS user\",
 5497    VSW.SID                      \"SID\",
 5498    VSW.EVENT                        \"Waiting on..\",
 5499    TO_CHAR(VSW.SECONDS_IN_WAIT,'999,999,999,999')   \"Seconds waiting\",
 5500    TO_CHAR(VSW.SECONDS_IN_WAIT/60,'999,999,999,999')    \"Minutes waiting\",
 5501    NVL(VSA.SQL_TEXT,'No SQL')               \"SQL text\"
 5502 FROM V\$SESSION_WAIT VSW,
 5503      V\$SQLAREA VSA,
 5504      V\$SESSION VS
 5505 WHERE VS.STATUS = 'ACTIVE'
 5506 AND VSW.SID = VS.SID
 5507 AND VS.USERNAME IS NOT NULL
 5508 AND VS.SQL_ADDRESS = VSA.ADDRESS
 5509 ORDER BY VSW.SECONDS_IN_WAIT DESC
 5510 ";
 5511 
 5512    $text = "Session wait information for active sessions with SQL.";
 5513    $link = "";
 5514    $infotext = "There are no sessions in a wait state.";
 5515    DisplayTable($sql,$text,$link,$infotext);
 5516 
 5517    $sql = "$copyright
 5518 SELECT 
 5519    VS.USERNAME                      \"Username\",
 5520    VS.OSUSER                        \"OS user\",
 5521    VSW.SID                      \"SID\",
 5522    VSW.EVENT                        \"