Oracle数据库

开发平台：

Perl

oracletool.pl：源码内容

#!/usr/bin/perl
#
# You may distribute under the terms of either the GNU General Public
# License or the Artistic License, as specified in the Perl README file,
# with the exception that it cannot be placed on a CD-ROM or similar media
# for commercial distribution without the prior approval of the author.
# This software is provided without warranty of any kind. If your server
# melts as a result of using this script, that's a bummer. But it won't.
require 5.003;
use strict;
use CGI qw(:standard);
use File::Basename;
use FileHandle;
if (! eval "require DBI") {
ErrorPage("It appears that the DBI module is not installed!");
}
my ($VERSION,$scriptname,$query,$database,$namesdatabase,$schema,$textarea_w);
my ($debug,$object_type,$object_name,$statement_id,$user,$whereclause,$textarea_h);
my ($expire,$username,$password,$dbh,$sql,$majversion,$minversion,$rowdisplay);
my ($oracle7,$oracle8,$oracle8i,$db_block_size,$title,$heading,$cursor,$banner);
my ($logging,$explainschema,$bgcolor,$headingcolor,$fontcolor,$infocolor,$font,$fontsize);
my ($linkcolor,$cellcolor,$bordercolor,$description,%themes,$schema_cols,$menufontsize);
my ($expiration,$oraclenames,$theme,$repository,$logfile,%plugins,$config_file);
my ($encryption_string,$bgimage,$menuimage,$encryption_enabled,$copyright,$headingfont);
my ($headingfontcolor);
$VERSION = "1.2.0";
# Edit the following if you want to use a config file not named "oracletool.ini".
$config_file = "oracletool.ini";
Main();
#=============================================================
# Nothing but subroutines from here on.
#=============================================================
sub Main {
my ($dbstatus);
# Unbuffer STDOUT
$|++;
# Find out the name this script was invoked as.
$scriptname = $ENV{'SCRIPT_NAME'};
# Get the data from the elements passed in the URL.
$query = new CGI;
$database = $query->param('database');
$namesdatabase = $query->param('namesdatabase');
$schema = $query->param('schema');
$explainschema = $query->param('explainschema');
$object_type = $query->param('object_type');
$object_name = $query->param('arg');
$statement_id = $query->param('statement_id');
$user = $query->param('user');
$whereclause = $query->param('whereclause');
$expire = $query->param('expire');
$password = $query->param('password');
# Set the page colors / font etc.
# Attempt to get a cookie containing the users theme.
# Set to a default theme if none is found.
$theme = cookie("OracletoolTheme");
$theme = "Default1" unless ($theme);
# Get the settings from the config file.
parseConfig();
# Decide whether to display copyright in all SQL statements.
if ($ENV{'DISPLAY_COPYRIGHT'}) {
$copyright = "/* Oracletool v$VERSION is copyright 1998,1999,2000 Adam vonNieda, Kansas USA */ ";
} else {
$copyright = "";
}
logit("Enter subroutine Main");
# Check for cookie encryption functionality.
encryptionEnabled();
# Set the properties that will override the default theme.
doProperties();
# If $namesdatabase is not null, then they have entered
# a names-resolved database. Change the $database value
# to the $namesdatabase value.
$database = $namesdatabase if $namesdatabase;
# If $database is "About_oracletool" then
# show the "About" page.
if ( $database && $database eq "About_oracletool" ) {
about();
}
# The $user variable will get passed to get session info
# for an individual user. If no individual user is passed
# then it defaults to % (All users)
$user = "%" unless $user;
# Get rid of the +'s on multi-word object types.
$object_type =~ s/+/ / if $object_type;
# If invoked standalone, show main page with database list.
if ( ! defined $database ) {
createMainPage();
exit;
}
# Skip the password verification for setting theme. Theme
# will be sent to browser as cookie.
if ($object_type eq "SETTHEME") {
setTheme();
}
# Skip the password verification for setting Properties. Properties
# will be sent to browser as cookie.
if ($object_type eq "SETPROPS") {
setProperties();
}
# Skip the password verification for explain plan. Password
# will be entered on the explain plan screen.
if ($object_type eq "EXPLAIN") {
enterExplainPlan();
}
# Skip the password verification for running explain plan.
# Password will be passed (hidden, and no cookie).
if ($object_type eq "RUNEXPLAINPLAN") {
runExplainPlan();
}
# Add a password if no cookie is found, or if incorrect.
if ($object_type eq "ADDPASSWORD") {
$username = $query->param('username');
$password = $query->param('password');
addPasswd($database,$username,$password);
}
# Attempt to get username and password cookies for connecting to the specified database.
($username,$password) = split / /, GetPasswd($database);
# If no cookie is found, do not try to connect to the database,
# just go directly to the password screen.
unless ($username && $password) {
EnterPasswd($database);
}
# Make connection to the database
$dbh = dbConnect($database,$username,$password);
# If invoked the first time after selecting the database,
# start creating the frames.
if ( $object_type eq "FRAMEPAGE" ) {
framePage();
}
# Find out if we are dealing with Oracle7 or Oracle8
logit(" Getting oracle version");
$sql = "$copyright
SELECT MAX(SUBSTR(RELEASE,1,1)),
MAX(SUBSTR(RELEASE,3,1))
FROM SYS.V_$COMPATIBILITY
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
(($majversion,$minversion) = $cursor->fetchrow_array);
$cursor->finish;
if ( $majversion eq "7" ) {
logit(" This is an Oracle7 database.");
$oracle7 = "Yep";
} else {
$oracle8 = "Yep";
logit(" This is an Oracle8 database.");
if ($minversion eq "1") {
logit(" This is an Oracle8i database.");
$oracle8i = "Yep";
}
}
# See what status the database is in (OPEN,MOUNTED etc...).
$dbstatus = dbStatus();
# Display the menu on the left side of the screen.
# This connects to the database as well, hence the
# $username variable. Connection is for determining
# version, OPS etc. Certain buttons will or will not
# be display based on some queries.
if ( $object_type eq "MENU" ) {
showMenu($username);
}
# Find out the database block size
$db_block_size = getDBblocksize();
# Get the Server banner to display the version info.
$banner = getBanner();
# Create the header for the HTML page.
$title = "$database: Oracletool v$VERSION connected as $username";
$heading = "";
Header($title,$heading,$font,$fontsize,$fontcolor,$bgcolor);
# The Director subroutine will direct the script to the appropriate
# subroutines based on the parameters passed, namely $object_type
Director();
# Disconnect from the database
$dbh->disconnect;
# Finish the HTML page.
Footer();
logit("Exit subroutine Main");
}
sub dbClosed {
logit("Enter subroutine dbClosed");
Header($title,$heading,$font,$fontsize,$fontcolor,$bgcolor);
if ($object_name) {
logit(" SQL passed to dbClosed: n$object_name");
runSQL($object_name);
} else {
logit(" No SQL passed, displaying worksheet.");
enterWorksheet();
}
logit("Exit subroutine dbClosed");
exit;
}
sub dbStatus {
# See what status the database is in.
my ($cursor,$sql,$dbstatus);
logit("Enter subroutine dbStatus");
if ($oracle8) {
logit(" We are Oracle8, checking database status.");
$sql = "$copyright
SELECT
STATUS
FROM V$INSTANCE
";
$cursor = $dbh->prepare($sql) or ErrorPage("Error: $DBI::errstr");
$cursor->execute;
$dbstatus = $cursor->fetchrow_array;
$cursor->finish;
} else {
logit(" We are Oracle7, assuming database is open.");
$dbstatus = "OPEN";
}
logit(" Database was found to be $dbstatus.");
if ($dbstatus ne "OPEN") {
dbClosed();
}
logit("Exit subroutine dbStatus");
return($dbstatus);
}
sub statsPackInstalled {
logit("Enter subroutine statsPackInstalled");
my ($sql,$count);
$sql = "
SELECT
COUNT(*)
FROM DBA_OBJECTS
WHERE OBJECT_NAME = 'STATSPACK'
AND OBJECT_TYPE = 'PACKAGE'
";
$count = recordCount($sql);
logit("Exit subroutine statsPackInstalled");
return($count);
}
sub loginfo {
my $text = shift;
if ($logging) {
open (LOG,">>$logfile") or ErrorPage("Oracletool error! Cannot open log file "$logfile"! You need to disable logging or choose a filename that you have permission to write to.");
print LOG "$textn";
close (LOG);
}
}
sub logit {
my $text = shift;
if ($debug) {
open (LOG,">>$logfile") or ErrorPage("Oracletool error! Cannot open log file "$logfile"! You need to disable logging or choose a filename that you have permission to write to.");
print LOG "$textn";
close (LOG);
}
}
sub parseConfig {
my ($parameter,$eq,$val,$plugin);
my $mytheme = $theme;
my ($description,@themevars,$key,$themevarcount);
open(CONFIG,"$config_file")
or ErrorPage("Can't open config file $config_file. Reason: $!.");
while (<CONFIG>) {
next if ((/^$/) || (/^s+$/) || (/^s+#/) || (/^#/));
chop;
($parameter,$val) = split(/=/);
$parameter =~ s/^s+//;
$parameter =~ s/s+$//;
$val =~ s/^s+//;
$val =~ s/s+$//;
$parameter = uc($parameter);
if ($parameter eq "EXPIRATION") {
$expiration = "$val";
next;
}
if ($parameter eq "ORACLENAMES") {
$oraclenames = "Yep";
next;
}
if ($parameter eq "DEBUG") {
$debug = "Yep";
next;
}
if ($parameter eq "LOGGING") {
$logging = "Yep";
next;
}
if ($parameter eq "LOG") {
$logfile = "$val";
next;
}
if ($parameter eq "ENCRYPTION_STRING") {
$encryption_string = "$val";
next;
}
# Add plugins
if ($parameter eq "PLUGIN") {
$plugin = $val;
next;
}
if ($parameter eq "PROGRAM") {
$plugins{$plugin} = $val;
next;
}
# Add themes..
if ($parameter eq "THEME") {
$theme = "$val";
$themevarcount++;
next;
}
if ($parameter eq "DESCRIPTION") {
$description = "$val";
$themevarcount++;
next;
}
if ($parameter eq "BGCOLOR") {
$bgcolor = "$val";
$themevarcount++;
next;
}
if ($parameter eq "MENUIMAGE") {
$menuimage = "$val";
$themevarcount++;
next;
}
if ($parameter eq "BGIMAGE") {
$bgimage = "$val";
$themevarcount++;
next;
}
if ($parameter eq "FONTCOLOR") {
$fontcolor = "$val";
$themevarcount++;
next;
}
if ($parameter eq "HEADINGFONTCOLOR") {
$headingfontcolor = "$val";
$themevarcount++;
next;
}
if ($parameter eq "INFOCOLOR") {
$infocolor = "$val";
$themevarcount++;
next;
}
if ($parameter eq "LINKCOLOR") {
$linkcolor = "$val";
$themevarcount++;
next;
}
if ($parameter eq "FONT") {
$font = "$val";
$themevarcount++;
next;
}
if ($parameter eq "HEADINGFONT") {
$headingfont = "$val";
$themevarcount++;
next;
}
if ($parameter eq "FONTSIZE") {
$fontsize = "$val";
$themevarcount++;
next;
}
if ($parameter eq "HEADINGCOLOR") {
$headingcolor = "$val";
$themevarcount++;
next;
}
if ($parameter eq "CELLCOLOR") {
$cellcolor = "$val";
$themevarcount++;
next;
}
if ($parameter eq "BORDERCOLOR") {
$bordercolor = "$val";
$themevarcount++;
next unless ($themevarcount == 15);
}
if (($themevarcount) && ($themevarcount == 15)) {
push @{ $themes{$theme} }, $description;
push @{ $themes{$theme} }, $bgcolor;
push @{ $themes{$theme} }, $menuimage;
push @{ $themes{$theme} }, $bgimage;
push @{ $themes{$theme} }, $fontcolor;
push @{ $themes{$theme} }, $headingfontcolor;
push @{ $themes{$theme} }, $infocolor;
push @{ $themes{$theme} }, $linkcolor;
push @{ $themes{$theme} }, $font;
push @{ $themes{$theme} }, $headingfont;
push @{ $themes{$theme} }, $fontsize;
push @{ $themes{$theme} }, $headingcolor;
push @{ $themes{$theme} }, $cellcolor;
push @{ $themes{$theme} }, $bordercolor;
undef $themevarcount;
next;
}
# If a parameter does not match a "hard coded" parameter
# above, assume it is an environmental variable.
$ENV{$parameter} = $val;
}
close(CONFIG);
if ((($debug) || ($logging)) && (! $logfile)) {
undef $debug;
undef $logging;
}
logit("Done reading config file.");
# Check to be sure that a theme exists, in case it came
# from a cookie. If someone creates a personal theme and
# then installs a new version of the tool, their theme
# may not exist anymore, but it will still be in the
# OracletoolTheme cookie. Default, in this case.
logit("MyTheme is set to $mytheme");
foreach $key(keys %themes) {
if ($mytheme eq $key) {
$theme = $mytheme;
last;
} else {
$theme = "Default1";
}
}
logit("Theme is set to $theme");
foreach $key(keys %plugins) {
logit("Plugin $key: Program $plugins{$key}");
}
# Now set the variables for the selected theme.
@themevars = @{ $themes{$theme} };
$description = $themevars[0];
$bgcolor = $themevars[1];
$menuimage = $themevars[2];
$bgimage = $themevars[3];
$fontcolor = $themevars[4];
$headingfontcolor = $themevars[5];
$infocolor = $themevars[6];
$linkcolor = $themevars[7];
$font = $themevars[8];
$headingfont = $themevars[9];
$fontsize = $themevars[10];
$headingcolor = $themevars[11];
$cellcolor = $themevars[12];
$bordercolor = $themevars[13];
# Print environment to debug if enabled.
# logit("Summary of ENV settings");
# foreach $key(keys %ENV) {
# logit("VAR: $key SETTING: $ENV{$key}");
# }
}
sub createMainPage() {
logit("Enter subroutine createMainPage");
# This sub will be called if this script is invoked without a 'database=....'
# element in the URL.
# Get the connection strings from the tnsnames.ora file.
my @sids = GetTNS();
# Start creating main page
my $bgline = "<BODY BGCOLOR=$bgcolor>n";
if ($bgimage) {
if ((-e "$ENV{'DOCUMENT_ROOT'}/$bgimage") && (-r "$ENV{'DOCUMENT_ROOT'}/$bgimage")) {
logit("Background image is $ENV{'DOCUMENT_ROOT'}/$bgimage and is readable");
$bgline = "<BODY BACKGROUND=$bgimage>n";
}
}
print << "EOF";
Content-type: Text/htmlnn
<HTML>
<HEAD>
<TITLE>Oracletool v$VERSION</TITLE>
</HEAD>
$bgline
<CENTER>
<H2>
<FONT COLOR="$fontcolor" FACE="$font" SIZE="5">
Oracletool v$VERSION
</FONT>
</H2>
</CENTER>
<BR><BR>
<TABLE BGCOLOR="BLACK" WIDTH="400" CELLPADDING="1" CELLSPACING="0" BORDER="0">
<TR>
<TD VALIGN="TOP">
<TABLE BGCOLOR="$headingcolor" WIDTH="100%" CELLPADDING="2" CELLSPACING="1" BORDER="0">
<TR ALIGN="LEFT">
<TD>
<TABLE>
<TR>
<TD ALIGN="LEFT">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
EOF
print " <STRONG>Select an instance.</STRONG>n" if (! $oraclenames);
print " <STRONG>Select or enter an instance name.</STRONG>n" if ($oraclenames);
print <<"EOF";
<FORM METHOD="POST" ACTION="$scriptname">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<P>
<SELECT NAME="database">
EOF
my $sid;
foreach $sid (@sids) {
print " <OPTION VALUE="$sid">$sid</OPTION>n";
}
print <<"EOF";
</SELECT>
</TD>
EOF
if ($oraclenames) {
print <<"EOF";
</TR>
<TR>
<TD>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="TEXT" NAME="namesdatabase" SIZE="20">
</TD>
EOF
}
print <<"EOF";
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="TOP">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="FRAMEPAGE">
<INPUT TYPE="SUBMIT" VALUE="Connect">
<INPUT TYPE="CHECKBOX" NAME="expire" VALUE="Yep">Expire password cookie
</P>
</FORM>
<P>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>
EOF
logit("Exit subroutine createMainPage");
}
sub dbConnect {
logit("Enter subroutine dbConnect");
my $database = shift;
my $username = shift;
my $password = shift;
loginfo(" Log - Host: $ENV{'REMOTE_HOST'} IP: $ENV{'REMOTE_ADDR'} DB: $database Command: $object_type Theme: $theme") if $logging;
# Attempt to make connection to the database..
my $data_source = "dbi:Oracle:$database";
logit(" Datasource: $data_source");
logit(" ORACLE_HOME $ENV{'ORACLE_HOME'}");
logit(" TNS_ADMIN $ENV{'TNS_ADMIN'}");
logit(" Connecting...");
my $dbh = DBI->connect($data_source,$username,$password,{PrintError=>0});
# If it fails, act on a couple of different Oracle errors.
# Bring up the password screen for either of these errors.
# ORA-01017 - "Invalid username/password; logon denied."
# ORA-01004 - "Default username feature not supported; logon denied."
# ORA-01005 - "null password given; logon denied."
# Show an error message for these errors.
# ORA-12224 - "The connection request could not be completed because the listener is not running."
# ORA-01034 - "Oracle was not started up."
# ORA-01090 - "Shutdown in progress - connection is not permitted""
# ORA-12154 - "The service name specified is not defined correctly in the TNSNAMES.ORA file."
# ORA-12505 - "TNS:listener could not resolve SID given in connect descriptor."
# ORA-12545 - "TNS:name lookup failure."
unless ($dbh) {
logit(" Failed login with username "$username". $ENV{'REMOTE_HOST'} IP: $ENV{'REMOTE_ADDR'}");
loginfo(" Failed login with username "$username". $ENV{'REMOTE_HOST'} IP: $ENV{'REMOTE_ADDR'}");
if ( $DBI::errstr =~ /ORA-01017|ORA-01004|ORA-01005/ ) {
EnterPasswd($database);
exit;
}
if ( $DBI::errstr =~ /ORA-12224/ ) {
ErrorPage ("You received an ORA-12224, which usually means the listener is down, or your connection definition in your tnsnames.ora file is incorrect. Check both of these things and try again.");
exit;
}
if ( $DBI::errstr =~ /ORA-01034/ ) {
ErrorPage ("You received an ORA-01034, which usually means the database is down. Check to be sure the database is up and try again.");
exit;
}
if ( $DBI::errstr =~ /ORA-01090/ ) {
ErrorPage ("You received an ORA-01090, which means the database is in the process of coming down.");
exit;
}
if ( $DBI::errstr =~ /ORA-12154/ ) {
ErrorPage ("You received an ORA-12154, which probably means you have a mistake in your TNSNAMES.ORA file for the database that you chose.");
exit;
}
if ( $DBI::errstr =~ /ORA-12505/ ) {
ErrorPage ("You received an ORA-12505, which probably means you have a mistake in your TNSNAMES.ORA file for the database that you chose, or the database you are trying to connect to is not defined to the listener that is running on that node.");
exit;
}
if ( $DBI::errstr =~ /ORA-12545/ ) {
ErrorPage ("You received an ORA-12545, which probably means you have a mistake in your TNSNAMES.ORA file for the database that you chose. (Possibly the node name).");
exit;
}
ErrorPage ("Unable to connect to Oracle ($DBI::errstr)n");
exit;
}
logit(" Got successful connection");
logit("Exit subroutine dbConnect");
return ($dbh);
}
sub setTheme {
logit("Enter subroutine setTheme");
my ($message,$duration,$url,$cookie,$path,$bgline);
$theme = $object_name;
$path = dirname($scriptname);
$cookie = cookie(-name=>"OracletoolTheme",-value=>"$theme",-expires=>"+10y",-path=>"$path");
print header(-cookie=>[$cookie]);
$message = "Your personal theme has been set to $theme.<BR>Oracletool will restart with a connection to instance $database.";
$duration = "4";
$url = "$scriptname?database=$database&object_type=FRAMEPAGE";
$bgline = "<BODY BGCOLOR=$bgcolor>n";
if ($bgimage) {
if ((-e "$ENV{'DOCUMENT_ROOT'}/$bgimage") && (-r "$ENV{'DOCUMENT_ROOT'}/$bgimage")) {
logit(" Background image is $ENV{'DOCUMENT_ROOT'}/$bgimage and is readable");
$bgline = "<BODY BACKGROUND=$bgimage>n";
}
}
print <<"EOF";
<HTML>
<HEAD>
<TITLE>Theme is set to $theme.</TITLE>
<META HTTP-EQUIV="Refresh" Content="$duration;URL=$url">
</HEAD>
$bgline
<FONT FACE="$font" SIZE="$fontsize" COLOR="$fontcolor">
<CENTER>
$message
</CENTER
</BODY
</HTML>
EOF
logit("Exit subroutine setTheme");
exit;
}
sub doProperties {
logit("Enter subroutine doProperties");
my $properties = cookie("OracletoolProps");
if ($properties) {
($schema_cols,$fontsize,$menufontsize,$textarea_w,$textarea_h,$rowdisplay) = split (/%/, $properties);
} else {
# Set the variables that are not taken care of by a theme.
$menufontsize = "2";
$schema_cols = "5";
$textarea_w = "40";
$textarea_h = "20";
$rowdisplay = "25";
}
logit("Exit subroutine doProperties");
}
sub setProperties {
logit("Enter subroutine setProperties");
my ($cookie,$properties,$message,$duration,$url,$path,$bgline);
# Compare the selected properties with the ones set in this users
# default theme, where applicable. If they are different, then
# update a properties cookie. These parameters were passed in by
# names that make no sense, in order to cut down on global variables.
# $schema holds the value for $schema_cols.
# $schema_cols is the number of columns wide to display the toplevel
# schema list.
$schema_cols = $schema;
# $explainschema holds the value for $fontsize.
# If not set, use value from theme.
$fontsize = $explainschema;
# $expire holds the value for $menufontsize.
# If not set, default to '2'.
$menufontsize = $expire;
# $statement_id holds the value for TEXTAREA width
$textarea_w = $statement_id;
# $user holds the value for TEXTAREA height
$textarea_h = $user;
# $whereclause holds the value for how many rows to display.
$rowdisplay = $whereclause;
$properties = "$schema_cols%$fontsize%$menufontsize%$textarea_w%$textarea_h%$rowdisplay";
$path = dirname($scriptname);
$cookie = cookie(-name=>"OracletoolProps",-value=>"$properties",-path=>"$path",-expires=>"+10y");
print header(-cookie=>[$cookie]);
$message = "Your personal Oracletool preferences have been updated.<BR>Oracletool will restart with a connection to instance $database.";
$duration = "4";
$url = "$scriptname?database=$database&object_type=FRAMEPAGE";
$bgline = "<BODY BGCOLOR=$bgcolor>n";
if ($bgimage) {
if ((-e "$ENV{'DOCUMENT_ROOT'}/$bgimage") && (-r "$ENV{'DOCUMENT_ROOT'}/$bgimage")) {
logit(" Background image is $ENV{'DOCUMENT_ROOT'}/$bgimage and is readable");
$bgline = "<BODY BACKGROUND=$bgimage>n";
}
}
print <<"EOF";
<HTML>
<HEAD>
<TITLE>Properties have been reset.</TITLE>
<META HTTP-EQUIV="Refresh" Content="$duration;URL=$url">
</HEAD>
$bgline
<FONT FACE="$font" SIZE="$fontsize" COLOR="$fontcolor">
<CENTER>
$message
</CENTER
</BODY
</HTML>
EOF
logit("Exit subroutine setProperties");
exit;
}
sub showProps {
logit("Enter subroutine showProps");
# Display a menu for selecting non-default properties for the tool.
# These will be stored as cookies.
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.");
my ($fontsizeoverride,$fontoverride,$val);
print <<"EOF";
<FORM METHOD="POST" ACTION="$scriptname" TARGET="_top">
<TABLE BORDER=0 ALIGN=LEFT>
<TR WIDTH=50%>
<TD ALIGN=CENTER>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE=HIDDEN NAME='database' VALUE='$database'>
<INPUT TYPE=HIDDEN NAME='object_type' VALUE='SETPROPS'>
<INPUT TYPE=SUBMIT NAME='foobar' VALUE='Submit changes'>
</TD>
</TR>
<TR>
<TD VALIGN="TOP">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<B>
Schema list column number:<BR>
This sets the number of columns in the schema list table.<BR>
EOF
# Loop through the values, in order to check the box which is the
# value of what is set now.
foreach $val ('3','4','5','6','7') {
print " <INPUT TYPE=RADIO NAME='schema' VALUE='$val'";
if ($val == $schema_cols) {
print " CHECKED>$valn";
} else {
print " >$valn";
}
}
print <<"EOF";
<HR WIDTH='50%' ALIGN='LEFT'>
</TD>
</TR>
<TR>
<TD VALIGN="TOP">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<B>
Font size override:<BR>
This will override the font size set by your theme.<BR>
EOF
# Loop through the values, in order to check the box which is the
# value of what is set now.
foreach $val ('1','2','3','4','5','6','7') {
print "<INPUT TYPE=RADIO NAME='explainschema' VALUE='$val'";
if ($val == $fontsize) {
print " CHECKED>$valn";
} else {
print " >$valn";
}
}
print <<"EOF";
<HR WIDTH='50%' ALIGN='LEFT'>
</TD>
</TR>
<TR>
<TD VALIGN="TOP">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<B>
Menu button font size override:<BR>
This will override the menu button font size set by your theme.<BR>
EOF
# Loop through the values, in order to check the box which is the
# value of what is set now.
foreach $val ('1','2','3','4') {
print "<INPUT TYPE=RADIO NAME='expire' VALUE='$val'";
if ($val == $menufontsize) {
print " CHECKED>$valn";
} else {
print " >$valn";
}
}
print <<"EOF";
<HR WIDTH='50%' ALIGN='LEFT'>
</TD>
</TR>
<TR>
<TD VALIGN="TOP">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<B>
Textarea width:<BR>
Width in characters of the SQL editing area.<BR>
EOF
foreach $val ('30','40','50','60','70','80','100','125','150') {
print "<INPUT TYPE=RADIO NAME='statement_id' VALUE='$val'";
if ($val == $textarea_w) {
print " CHECKED>$valn";
} else {
print " >$valn";
}
}
print <<"EOF";
<HR WIDTH='50%' ALIGN='LEFT'>
</TD>
</TR>
<TR>
<TD VALIGN="TOP">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<B>
Textarea height:<BR>
Height in characters of the SQL editing area.<BR>
EOF
foreach $val ('5','10','15','20','25','30','35','40','45','50') {
print "<INPUT TYPE=RADIO NAME='user' VALUE='$val'";
if ($val == $textarea_h) {
print " CHECKED>$valn";
} else {
print " >$valn";
}
}
print <<"EOF";
<HR WIDTH='50%' ALIGN='LEFT'>
</TD>
</TR>
<TR>
<TD VALIGN="TOP">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<B>
Row display default:<BR>
Number of rows to return on a table/view row display.<BR>
EOF
foreach $val ('1','5','25','50','100','250','500','all') {
print "<INPUT TYPE=RADIO NAME='whereclause' VALUE='$val'";
if ($val eq $rowdisplay) {
print " CHECKED>$valn";
} else {
print " >$valn";
}
}
print <<"EOF";
<HR WIDTH='50%' ALIGN='LEFT'>
</TD>
</TR>
<TR WIDTH=50%>
<TD ALIGN=CENTER>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE=HIDDEN NAME='database' VALUE='$database'>
<INPUT TYPE=HIDDEN NAME='object_type' VALUE='SETPROPS'>
<INPUT TYPE=SUBMIT NAME='foobar' VALUE='Submit changes'>
</TD>
</TR>
</FORM>
<TR>
</TABLE>
EOF
logit("Exit subroutine showProps");
}
sub showThemes {
logit("Enter subroutine showThemes");
# Display all of the themes.
my ($currenttheme,@themevars);
$currenttheme = $theme;
text("Select a color theme for your default.<BR>Your current theme is "$currenttheme".");
foreach $theme (sort keys %themes) {
logit(" Displaying theme $theme");
@themevars = @{ $themes{$theme} };
$description = $themevars[0] or $description = "undefined";
$bgcolor = $themevars[1] or $bgcolor = "undefined";
$menuimage = $themevars[2] or $menuimage = "undefined";
$bgimage = $themevars[3] or $bgimage = "undefined";
$fontcolor = $themevars[4] or $fontcolor = "undefined";
$headingfontcolor = $themevars[5] or $fontcolor = "undefined";
$infocolor = $themevars[6] or $infocolor = "undefined";
$linkcolor = $themevars[7] or $linkcolor = "undefined";
$font = $themevars[8] or $font = "undefined";
$headingfont = $themevars[9] or $font = "undefined";
$fontsize = $themevars[10] or $fontsize = "undefined";
$headingcolor = $themevars[11] or $headingcolor = "undefined";
$cellcolor = $themevars[12] or $cellcolor = "undefined";
$bordercolor = $themevars[13] or $bordercolor = "undefined";
print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD VALIGN="TOP" WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1 WIDTH=100%>
<TR>
<TD BGCOLOR='$bgcolor'>
<TABLE CELLPADDING=20>
<TH><A HREF=$scriptname?database=$database&object_type=SETTHEME&arg=$theme TARGET=_top>$theme</A></TH>
<TR>
<TD BGCOLOR=$bgcolor ALIGN=CENTER><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD VALIGN="TOP" WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1 WIDTH=100%>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Table headings</TH>
<TR ALIGN="CENTER">
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
Table cells
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE><FONT COLOR='$linkcolor' SIZE='$fontsize' FACE='$font'><BR>link color
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
<P>
EOF
}
logit("Exit subroutine showThemes");
}
sub validateIndex {
logit("Enter subroutine validateIndex");
my ($sql,$text,$link);
$sql = "
VALIDATE INDEX $schema.$object_name
";
runSQL($sql);
$sql = "$copyright
SELECT
REPEAT_COUNT "Repeat count",
KEYS_WITH_REPEAT_COUNT "Keys with repeat count"
FROM INDEX_HISTOGRAM
";
$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.";
$link = "";
DisplayTable($sql,$text,$link);
$sql = "$copyright
SELECT
HEIGHT "Height",
TO_CHAR(BLOCKS,'999,999,999,999') "Blocks",
TO_CHAR(LF_ROWS,'999,999,999,999') "Leaf rows",
TO_CHAR(LF_BLKS,'999,999,999,999') "Leaf blocks",
TO_CHAR(LF_ROWS_LEN,'999,999,999,999') "Leaf row sum",
TO_CHAR(LF_BLK_LEN,'999,999,999,999') "Leaf block usable space",
TO_CHAR(BR_ROWS,'999,999,999,999') "Branch rows #",
TO_CHAR(BR_BLKS,'999,999,999,999') "Branch blocks #",
TO_CHAR(BR_ROWS_LEN,'999,999,999,999') "Blocks length sum",
TO_CHAR(BR_BLK_LEN,'999,999,999,999') "Branch block usable space",
TO_CHAR(DEL_LF_ROWS,'999,999,999,999') "Deleted leaf rows #",
TO_CHAR(DEL_LF_ROWS_LEN,'999,999,999,999') "Deleted rows length",
TO_CHAR(DISTINCT_KEYS,'999,999,999,999') "Distinct keys #",
TO_CHAR(MOST_REPEATED_KEY,'999,999,999,999') "Most repeated key #",
TO_CHAR(BTREE_SPACE,'999,999,999,999') "Total space allocated",
TO_CHAR(USED_SPACE,'999,999,999,999') "Total space used",
TO_CHAR(PCT_USED,'999')||'%' "Percent used",
TO_CHAR(ROWS_PER_KEY,'999,999,999,999') "Rows per distinct key",
TO_CHAR(BLKS_GETS_PER_ACCESS,'999,999,999,999') "Block gets per access"
FROM INDEX_STATS
";
$text = "Index statistics.";
$link = "";
DisplayTable($sql,$text,$link);
logit("Exit subroutine validateIndex");
}
sub showIndex {
logit("Enter subroutine showIndex");
my ($sql,$text,$link,$foo,$partitioned);
# Index structure
$sql = "$copyright
SELECT
TABLE_NAME "Table name",
TABLE_OWNER "Owner",
COLUMN_NAME "Column name",
COLUMN_LENGTH "Column length"
FROM DBA_IND_COLUMNS
WHERE INDEX_NAME = '$object_name'
AND INDEX_OWNER = '$schema'
ORDER BY COLUMN_POSITION
";
$object_type = lc $object_type;
$text = "Structure of $object_type $object_name";
$link = "";
DisplayTable($sql,$text,$link);
# General
$sql = "$copyright
SELECT
A.TABLESPACE_NAME "Tablespace name",
TO_CHAR(B.CREATED,'Month DD, YYYY - HH24:MI') "Date created",
TO_CHAR(B.LAST_DDL_TIME,'Month DD, YYYY - HH24:MI') "Last DDL time",
TO_CHAR(A.EXTENTS,'999,999,999,999') "Extents",
TO_CHAR(A.INITIAL_EXTENT,'999,999,999,999') "Initial extent",
TO_CHAR(A.NEXT_EXTENT,'999,999,999,999') "Next extent",
TO_CHAR(A.MAX_EXTENTS,'999,999,999,999') "Max extents",
TO_CHAR(A.BYTES,'999,999,999,999') "Bytes",
B.STATUS "Status"
FROM DBA_SEGMENTS A, DBA_OBJECTS B
WHERE A.SEGMENT_NAME = '$object_name'
AND A.SEGMENT_TYPE = 'INDEX'
AND A.OWNER = '$schema'
AND B.OBJECT_NAME = '$object_name'
AND B.OBJECT_TYPE = 'INDEX'
AND B.OWNER = '$schema'
";
$object_type = lc $object_type;
$text = "General info: $object_type $object_name";
$link = "";
DisplayTable($sql,$text,$link);
# Check to see if index is partitioned, if Oracle8
if ($oracle8) {
$sql = "$copyright
SELECT
PARTITIONED
FROM DBA_INDEXES
WHERE INDEX_NAME = '$object_name'
AND OWNER = '$schema'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$foo = $cursor->fetchrow_array;
$cursor->finish;
if ($foo eq "YES") {
$partitioned = "Yep";
}
}
# If partitioned, show some additional info.
if ($partitioned) {
$sql = "$copyright
SELECT
PARTITION_NAME "Partition name",
TABLESPACE_NAME "Tablespace",
PARTITION_POSITION "Position",
TO_CHAR(INITIAL_EXTENT,'999,999,999,999') "Initial",
TO_CHAR(NEXT_EXTENT,'999,999,999,999') "Next",
TO_CHAR(MAX_EXTENT,'999,999,999,999') "Max extents",
PCT_INCREASE "Pct increase",
HIGH_VALUE "High value",
HIGH_VALUE_LENGTH "High value length",
LOGGING "Logging"
FROM DBA_IND_PARTITIONS
WHERE INDEX_NAME = '$object_name'
AND INDEX_OWNER = '$schema'
ORDER BY PARTITION_POSITION
";
$text = "Partitions contained in this index";
$link = "$scriptname?database=$database&schema=$schema&object_type=INDEX+PARTITION&index_name=$object_name";
DisplayTable($sql,$text,$link);
}
if (checkPriv("ANALYZE ANY")) {
print <<"EOF";
<FORM METHOD="GET" ACTION="$scriptname">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="$object_name">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="VALIDATEINDEX">
<INPUT TYPE="SUBMIT" NAME="foo" VALUE="Validate index for detailed statistics.">
</FORM>
EOF
}
logit("Exit subroutine showIndex");
}
sub showIndexPart {
logit("Enter subroutine showIndexPart");
my ($sql,$cursor,$isanalyzed,$text,$link,$infotext,$index_name);
$index_name = $query->param('index_name');
$sql = "$copyright
SELECT
PARTITION_NAME "Partition name",
INDEX_NAME "Index name",
INDEX_OWNER "Owner",
TABLESPACE_NAME "Tablespace",
PARTITION_POSITION "Position",
TO_CHAR(INITIAL_EXTENT,'999,999,999,999') "Initial",
TO_CHAR(NEXT_EXTENT,'999,999,999,999') "Next",
TO_CHAR(MAX_EXTENT,'999,999,999,999') "Max extents",
PCT_INCREASE "Pct increase",
HIGH_VALUE "High value",
HIGH_VALUE_LENGTH "High value length",
LOGGING "Logging"
FROM DBA_IND_PARTITIONS
WHERE PARTITION_NAME = '$object_name'
AND INDEX_NAME = '$index_name'
AND INDEX_OWNER = '$schema'
";
$object_type = lc $object_type;
$text = "General info: $object_type $object_name";
$link = "";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showIndexPart");
}
sub showTablePart {
logit("Enter subroutine showTablePart");
my ($sql,$cursor,$isanalyzed,$text,$link,$infotext,$table_name);
$table_name = $query->param('table_name');
# General info
$sql = "$copyright
SELECT
PARTITION_NAME "Partition name",
TABLE_NAME "Table name",
TABLE_OWNER "Owner",
TABLESPACE_NAME "Tablespace",
PARTITION_POSITION "Position",
TO_CHAR(INITIAL_EXTENT,'999,999,999,999') "Initial",
TO_CHAR(NEXT_EXTENT,'999,999,999,999') "Next",
TO_CHAR(MAX_EXTENT,'999,999,999,999') "Max extents",
PCT_INCREASE "Pct increase",
HIGH_VALUE "High value",
HIGH_VALUE_LENGTH "High value length",
LOGGING "Logging"
FROM DBA_TAB_PARTITIONS
WHERE PARTITION_NAME = '$object_name'
AND TABLE_NAME = '$table_name'
AND TABLE_OWNER = '$schema'
";
$object_type = lc $object_type;
$text = "General info: $object_type $object_name";
$link = "";
DisplayTable($sql,$text,$link);
# Check to see if partition has been analyzed.
$sql = "$copyright
SELECT
DISTINCT LAST_ANALYZED
FROM DBA_TAB_PARTITIONS
WHERE PARTITION_NAME = '$object_name'
AND TABLE_OWNER = '$schema'
";
$cursor=$dbh->prepare($sql);
logit(" Error: $DBI::errstr") if $DBI::errstr;
$cursor->execute;
$isanalyzed = $cursor->fetchrow_array;
logit(" Isanalyzed for partition $schema.$object_name is $isanalyzed");
$cursor->finish;
if ($isanalyzed) {
$sql = "$copyright
SELECT
TO_CHAR((BLOCKS / (EMPTY_BLOCKS+BLOCKS)) *100,'999.99')||'%' "Percent used",
TO_CHAR(NUM_ROWS,'999,999,999,999') "Row count",
TO_CHAR(BLOCKS,'999,999,999,999') "Blocks",
TO_CHAR(EMPTY_BLOCKS,'999,999,999,999') "Empty blocks",
TO_CHAR(AVG_SPACE,'999,999,999,999') "Average space",
TO_CHAR(AVG_ROW_LEN,'999,999,999,999') "Average row length",
TO_CHAR(CHAIN_CNT,'999,999,999,999') "Chain count",
TO_CHAR(LAST_ANALYZED,'Month DD, YYYY') "Last analyzed"
FROM DBA_TAB_PARTITIONS
WHERE PARTITION_NAME = '$object_name'
AND TABLE_OWNER = '$schema'
";
$text = "Analyzation info: $object_type $object_name";
$link = "";
DisplayTable($sql,$text,$link);
} else {
message("Partition has never been analyzed. Extended info will not be shown.");
}
logit("Exit subroutine showTablePart");
}
sub showCluster {
logit("Enter subroutine showCluster");
my ($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
TABLESPACE_NAME "Tablespace name",
TO_CHAR(INITIAL_EXTENT,'999,999,999,999') "Initial extent",
TO_CHAR(NEXT_EXTENT,'999,999,999,999') "Next extent",
TO_CHAR(MAX_EXTENTS,'999,999,999,999') "Max extents",
CLUSTER_TYPE "Cluster type",
FUNCTION "Function",
INSTANCES "Instances",
SINGLE_TABLE "Single table"
FROM DBA_CLUSTERS
WHERE CLUSTER_NAME = '$object_name'
AND OWNER = '$schema'
";
logit (" $sql");
$text = "General info: Cluster $object_name";
$link = "";
DisplayTable($sql,$text,$link);
$sql = "$copyright
SELECT
TABLE_NAME "Table_name",
TO_CHAR(INITIAL_EXTENT,'999,999,999,999') "Initial extent",
TO_CHAR(NEXT_EXTENT,'999,999,999,999') "Next extent",
TO_CHAR(MAX_EXTENTS,'999,999,999,999') "Max extents"
FROM DBA_TABLES
WHERE CLUSTER_NAME = '$object_name'
AND OWNER = '$schema'
";
$text = "Tables belonging to cluster $object_name";
$infotext = "No tables belong to cluster $object_name";
$link = "$scriptname?database=$database&schema=$schema&object_type=TABLE";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showCluster");
}
sub showTable {
logit("Enter subroutine showTable");
my ($sql,$text,$link,$infotext,$cursor,$isanalyzed,$partitioned,$grantcount,$foo);
my (@columns,$cols,$constraint_name,$column_name,$status,$index_name,$tablespace_name);
my ($indexes,$cursor1,$sql1,$uniqueness,$initial_extent,$next_extent,$max_extents);
my ($r_owner,$r_constraint_name,$count,$r_table_name,$iot_type,$temporary,$index_type);
print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 cellpadding=5 cellspacing=1>
<TR>
<TD BGCOLOR=$headingcolor>
<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0>
<TR>
<TD ALIGN=CENTER>
<FORM METHOD="GET" ACTION="$scriptname">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="TABLEROWS">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="$object_name">
<INPUT TYPE="SUBMIT" NAME="tablerows" VALUE="Display $rowdisplay rows of this table">
</TD>
</TR>
<TR>
<TD ALIGN=CENTER><FONT COLOR=$fontcolor SIZE=$fontsize><B><I>where</I></B></FONT></TD>
</TR>
<TR>
<TD ALIGN=CENTER>
<INPUT TYPE="TEXT" SIZE=30 NAME="whereclause">
</TD>
</FORM>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
EOF
$grantcount = showGrantButton();
unless ($grantcount) {
print "<BR>";
}
print <<"EOF";
<TABLE BORDER=0 CELLPADDING=5 CELLSPACING=1>
<TR>
<TD ALIGN=CENTER>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="TABLEDDL">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="$schema.$object_name">
<INPUT TYPE="SUBMIT" NAME="foo" VALUE="Generate DDL">
</FORM>
</TD>
<TD ALIGN=CENTER>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="OBJECTFRAGMAP">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="$object_name">
<INPUT TYPE="SUBMIT" NAME="foo" VALUE="Extent mapping">
</FORM>
</TD>
</TR>
</TABLE>
EOF
# Table structure
$sql = "$copyright
SELECT
A.COLUMN_NAME "Column name",
A.DATA_TYPE "Type",
A.DATA_LENGTH "Length",
A.DATA_PRECISION "Precision",
B.DATA_DEFAULT "Default",
TO_CHAR(A.LAST_ANALYZED,'Month DD, YYYY') "Last analyzed"
FROM
(SELECT
TABLE_NAME,
OWNER,
COLUMN_ID,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
LAST_ANALYZED
FROM DBA_TAB_COLUMNS
WHERE (TABLE_NAME = '$object_name')
AND (OWNER = '$schema')
AND (DATA_TYPE <> 'NUMBER')
UNION
SELECT
TABLE_NAME,
OWNER,
COLUMN_ID,
COLUMN_NAME,
DATA_TYPE,
DATA_PRECISION,
DATA_SCALE,
LAST_ANALYZED
FROM DBA_TAB_COLUMNS
WHERE (TABLE_NAME = '$object_name')
AND (OWNER = '$schema')
AND (DATA_TYPE = 'NUMBER')
) A,
DBA_TAB_COLUMNS B
WHERE (A.TABLE_NAME = B.TABLE_NAME)
AND (A.OWNER = B.OWNER)
AND (A.COLUMN_ID = B.COLUMN_ID)
ORDER BY b.COLUMN_ID
";
$object_type = lc $object_type;
$text = "Structure of $object_type $object_name";
$link = "";
DisplayTable($sql,$text,$link);
# Check to see if table has been analyzed.
$sql = "$copyright
SELECT
DISTINCT LAST_ANALYZED
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$isanalyzed = $cursor->fetchrow_array;
$cursor->finish;
# If the table has been analyzed, show some additional information
if ($isanalyzed) {
$sql = "$copyright
SELECT
TO_CHAR((BLOCKS / (EMPTY_BLOCKS+BLOCKS)) *100,'999.99')||'%' "Percent used",
TO_CHAR(NUM_ROWS,'999,999,999,999') "Row count",
TO_CHAR(BLOCKS,'999,999,999,999') "Blocks",
TO_CHAR(EMPTY_BLOCKS,'999,999,999,999') "Empty blocks",
TO_CHAR(AVG_SPACE,'999,999,999,999') "Average space",
TO_CHAR(AVG_ROW_LEN,'999,999,999,999') "Average row length",
TO_CHAR(CHAIN_CNT,'999,999,999,999') "Chain count"
FROM DBA_TABLES
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
";
$text = "Analyzation info: $object_type $object_name";
$link = "";
DisplayTable($sql,$text,$link);
} else {
message("Table has never been analyzed. Extended info will not be shown.");
}
# Gather some info for later queries
# These can be used to determine what
# type of table we are dealing with.
# PARTITIONED: YES/NO
# IOT_TYPE: IOT/NULL
# TEMPORARY: Y/N
if ($oracle8) {
$sql = "$copyright
SELECT
PARTITIONED,
IOT_TYPE,
TEMPORARY
FROM DBA_TABLES
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
($partitioned,$iot_type,$temporary) = $cursor->fetchrow_array;
$cursor->finish;
}
if ($iot_type eq "IOT") {
message("Table $object_name is an Index Organized Table.");
} else {
# General info
$sql = "$copyright
SELECT
A.TABLESPACE_NAME "Tablespace",
TO_CHAR(B.CREATED,'Month DD, YYYY - HH24:MI') "Date created",
TO_CHAR(B.LAST_DDL_TIME,'Month DD, YYYY - HH24:MI') "Last DDL time",
TO_CHAR(A.EXTENTS,'999,999,999,999') "Extents",
TO_CHAR(A.INITIAL_EXTENT,'999,999,999,999') "Initial extent",
TO_CHAR(A.NEXT_EXTENT,'999,999,999,999') "Next extent",
TO_CHAR(A.MAX_EXTENTS,'999,999,999,999') "Max extents",
TO_CHAR(A.BYTES,'999,999,999,999') "Bytes",
A.PCT_INCREASE "% increase",
DECODE(C.CACHE,
' Y','Yes',
' N','No') "Cache?"
FROM DBA_SEGMENTS A, DBA_OBJECTS B, DBA_TABLES C
WHERE A.SEGMENT_NAME = '$object_name'
AND A.SEGMENT_TYPE = 'TABLE'
AND A.OWNER = '$schema'
AND B.OBJECT_NAME = '$object_name'
AND B.OBJECT_TYPE = 'TABLE'
AND B.OWNER = '$schema'
AND C.TABLE_NAME = '$object_name'
AND C.OWNER = '$schema'
";
$object_type = lc $object_type;
$text = "General info: $object_type $object_name";
$link = "$scriptname?database=$database&object_type=TSINFO";
DisplayTable($sql,$text,$link);
}
# Check to see if table is partitioned, if Oracle8
if ($partitioned) {
$sql = "$copyright
SELECT
PARTITIONED
FROM DBA_TABLES
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$foo = $cursor->fetchrow_array;
$cursor->finish;
if ($foo eq "YES") {
$partitioned = "Yep";
}
}
# If partitioned, show some additional info.
if ($partitioned) {
$sql = "$copyright
SELECT
PARTITION_NAME "Partition name",
TABLESPACE_NAME "Tablespace",
PARTITION_POSITION "Position",
TO_CHAR(INITIAL_EXTENT,'999,999,999,999') "Initial",
TO_CHAR(NEXT_EXTENT,'999,999,999,999') "Next",
TO_CHAR(MAX_EXTENT,'999,999,999,999') "Max extents",
PCT_INCREASE "Pct increase",
HIGH_VALUE "High value",
HIGH_VALUE_LENGTH "High value length",
LOGGING "Logging"
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME = '$object_name'
AND TABLE_OWNER = '$schema'
ORDER BY PARTITION_POSITION
";
$text = "Partitions contained in this table";
$link = "$scriptname?database=$database&schema=$schema&object_type=TABLE+PARTITION&table_name=$object_name";
DisplayTable($sql,$text,$link);
}
# Show primary key (if)
$sql = "$copyright
SELECT
CONSTRAINT_NAME "Constraint name",
STATUS "Status"
FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P'
AND TABLE_NAME = '$object_name'
AND OWNER = '$schema'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
(($constraint_name,$status) = $cursor->fetchrow);
$cursor->finish;
if ($constraint_name) {
$sql = "$copyright
SELECT COLUMN_NAME
FROM DBA_CONS_COLUMNS
WHERE CONSTRAINT_NAME = '$constraint_name'
AND OWNER = '$schema'
ORDER BY POSITION
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ($column_name = $cursor->fetchrow_array) {
push @columns, $column_name;
}
$cursor->finish;
if ($#columns > 0) {
$cols = join(",", @columns);
$cols =~ s/^,//;
} else {
$cols = $columns[0];
}
text("Primary key");
print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 cellpadding=2 cellspacing=1>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Constraint name</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Status</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Column(s)</TH>
<TR ALIGN=LEFT>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A href=$scriptname?database=$database&schema=$schema&object_type=INDEX&arg=$constraint_name>$constraint_name</A></TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$status</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$cols</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
EOF
} else {
message("This table has no primary key.");
}
# Count indexes
$sql = "$copyright
SELECT COUNT(*)
FROM DBA_INDEXES
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ($index_name = $cursor->fetchrow_array) {
$indexes++;
}
$cursor->finish;
if ($indexes) {
text("Indexes");
if ($oracle7) {
print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 cellpadding=2 cellspacing=1>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Index name</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Status</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Column(s)</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Tablespace name</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Unique?</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Initial</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Next</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Max</TH>
EOF
$sql = "$copyright
SELECT
INDEX_NAME "Index name",
STATUS "Status",
TABLESPACE_NAME "Tablespace name",
DECODE(UNIQUENESS,
'UNIQUE','Yes',
'NONUNIQUE','No') "Unique?",
TO_CHAR(INITIAL_EXTENT,'999,999,999,999') "Initial extent",
TO_CHAR(NEXT_EXTENT,'999,999,999,999') "Next extent",
TO_CHAR(MAX_EXTENTS,'999,999,999,999') "Max extents"
FROM DBA_INDEXES
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($index_name,$status,$tablespace_name,$uniqueness,$initial_extent,$next_extent,$max_extents) = $cursor->fetchrow) {
undef @columns;
$indexes++;
$sql1 = "$copyright
SELECT COLUMN_NAME
FROM DBA_IND_COLUMNS
WHERE INDEX_NAME = '$index_name'
AND INDEX_OWNER = '$schema'
ORDER BY COLUMN_POSITION
";
$cursor1 = $dbh->prepare($sql1);
$cursor1->execute;
while ($column_name = $cursor1->fetchrow_array) {
push @columns, $column_name;
}
$cursor1->finish;
if ($#columns > 0) {
$cols = join(",", @columns);
$cols =~ s/^,//;
} else {
$cols = $columns[0];
}
print <<"EOF";
<TR ALIGN=LEFT>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A href=$scriptname?database=$database&schema=$schema&object_type=INDEX&arg=$index_name>$index_name</A></TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$status</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$cols</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$tablespace_name</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$uniqueness</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$initial_extent</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$next_extent</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$max_extents</TD>
</TR>
EOF
}
print <<"EOF";
</TABLE>
</TD>
</TR>
</TABLE>
EOF
}
if ($oracle8) {
print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 cellpadding=2 cellspacing=1>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Index name</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Index type</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Status</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Column(s)</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Tablespace name</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Unique?</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Initial</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Next</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Max</TH>
EOF
$sql = "$copyright
SELECT
INDEX_NAME "Index name",
INDEX_TYPE "Index type",
STATUS "Status",
TABLESPACE_NAME "Tablespace name",
DECODE(UNIQUENESS,
'UNIQUE','Yes',
'NONUNIQUE','No') "Unique?",
TO_CHAR(INITIAL_EXTENT,'999,999,999,999') "Initial extent",
TO_CHAR(NEXT_EXTENT,'999,999,999,999') "Next extent",
TO_CHAR(MAX_EXTENTS,'999,999,999,999') "Max extents"
FROM DBA_INDEXES
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($index_name,$index_type,$status,$tablespace_name,$uniqueness,$initial_extent,$next_extent,$max_extents) = $cursor->fetchrow) {
undef @columns;
$indexes++;
$sql1 = "$copyright
SELECT COLUMN_NAME
FROM DBA_IND_COLUMNS
WHERE INDEX_NAME = '$index_name'
AND INDEX_OWNER = '$schema'
ORDER BY COLUMN_POSITION
";
$cursor1 = $dbh->prepare($sql1);
$cursor1->execute;
while ($column_name = $cursor1->fetchrow_array) {
push @columns, $column_name;
}
$cursor1->finish;
if ($#columns > 0) {
$cols = join(",", @columns);
$cols =~ s/^,//;
} else {
$cols = $columns[0];
}
print <<"EOF";
<TR ALIGN=LEFT>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A href=$scriptname?database=$database&schema=$schema&object_type=INDEX&arg=$index_name>$index_name</A></TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$index_type</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$status</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$cols</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$tablespace_name</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$uniqueness</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$initial_extent</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$next_extent</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$max_extents</TD>
</TR>
EOF
}
print <<"EOF";
</TABLE>
</TD>
</TR>
</TABLE>
EOF
}
} else {
message("This table has no indexes");
}
# Show column constraints (if)
$sql = "$copyright
SELECT
CONSTRAINT_NAME "Constraint name",
SEARCH_CONDITION "Search condition",
STATUS "Status"
FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_TYPE NOT IN ('P','R')
AND TABLE_NAME = '$object_name'
AND OWNER = '$schema'
ORDER BY TABLE_NAME, CONSTRAINT_NAME
";
$text = "Column constraints";
$link = "";
$infotext = "This table has no column constraints.";
DisplayTable($sql,$text,$link,$infotext);
# Show foreign key constraints (if)
$count = "";
$sql = "$copyright
SELECT
COUNT(*)
FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R'
AND OWNER = '$schema'
AND TABLE_NAME = '$object_name'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$count = $cursor->fetchrow_array;
$cursor->finish;
if ($count) {
text("Foreign key constraints");
print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 cellpadding=2 cellspacing=1>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Constraint name</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Status</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Column(s)</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Ref owner</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Ref table</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Ref constraint</TH>
EOF
$sql = "$copyright
SELECT
CONSTRAINT_NAME,
STATUS,
R_OWNER,
R_CONSTRAINT_NAME
FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R'
AND OWNER = '$schema'
AND TABLE_NAME = '$object_name'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($constraint_name,$status,$r_owner,$r_constraint_name) = $cursor->fetchrow_array) {
# Add the columns
$sql1 = "$copyright
SELECT
COLUMN_NAME
FROM DBA_CONS_COLUMNS
WHERE OWNER = '$schema'
AND CONSTRAINT_NAME = '$constraint_name'
";
$cursor1 = $dbh->prepare($sql1);
$cursor1->execute;
undef @columns;
while ($column_name = $cursor1->fetchrow_array) {
push @columns, $column_name;
}
$cursor1->finish;
if ($#columns > 0) {
$cols = join(",", @columns);
$cols =~ s/^,//;
} else {
$cols = $columns[0];
}
# Get the referenced table name
$sql1 = "$copyright
SELECT
TABLE_NAME
FROM DBA_CONSTRAINTS
WHERE OWNER = '$r_owner'
AND CONSTRAINT_NAME = '$r_constraint_name'
";
$cursor1 = $dbh->prepare($sql1);
$cursor1->execute;
$r_table_name = $cursor1->fetchrow_array;
$cursor1->finish;
print <<"EOF";
<TR ALIGN=LEFT>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$constraint_name</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$status</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$cols</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$r_owner</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$r_table_name</TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$r_constraint_name</TD>
</TR>
EOF
}
print <<"EOF";
</TABLE>
</TD>
</TR>
</TABLE>
EOF
} else {
message("This table has no foreign key constraints.");
}
# Foreign keys referencing this table (if)
$sql = "$copyright
SELECT
CONSTRAINT_NAME "Child constraint name",
OWNER "Child owner",
TABLE_NAME "Child table name",
STATUS "Status",
R_CONSTRAINT_NAME "Local constraint name"
FROM DBA_CONSTRAINTS
WHERE R_OWNER = '$schema'
AND R_CONSTRAINT_NAME IN
( SELECT
CONSTRAINT_NAME
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema')
";
$text = "Foreign key constraints referencing $object_name";
$link = "";
$infotext = "There are no foreign key constraints referencing this table.";
DisplayTable($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
SYNONYM_NAME "Synonym name",
OWNER "Owner",
DB_LINK "DB link"
FROM DBA_SYNONYMS
WHERE TABLE_NAME = '$object_name'
AND TABLE_OWNER = '$schema'
";
$text = "Synonyms pointing to this table.";
$link = "";
$infotext = "There are no synonyms pointing to this table.";
DisplayTable($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
TRIGGER_NAME "Trigger name",
TRIGGERING_EVENT "Event",
WHEN_CLAUSE "When clause"
FROM DBA_TRIGGERS
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
";
$text = "Triggers";
$link = "$scriptname?database=$database&schema=$schema&object_type=TRIGGER";
$infotext = "This table has no triggers.";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showTable");
}
sub userDDL {
logit("Enter subroutine userDDL");
# This sub generates DDL to recreate a user.
# This sub needs to updated for Oracle8 / 8i
my ($sql,$cursor,$password,$default_tablespace,$temporary_tablespace,$profile);
my ($max_bytes,$tablespace_name,$granted_role,$admin_option,$default_role,$ddl);
my ($privilege,$owner,$table_name,$grantable,$grantor,$sql1,$cursor1,@default_roles);
my ($roles);
$sql = "$copyright
SELECT
PASSWORD,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE,
PROFILE
FROM DBA_USERS
WHERE USERNAME = '$schema'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
($password,$default_tablespace,$temporary_tablespace,$profile) = $cursor->fetchrow_array;
$cursor->finish;
print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TR>
<TD BGCOLOR='$cellcolor'>
<PRE>
EOF
# Put in some remarks
$ddl = "/*nDDL generated by Oracletool v$VERSIONn";
$ddl .= "for user $schema n*/nn";
# Create the SQL
$ddl .= "
CREATE USER $schema
IDENTIFIED BY VALUES '$password'
DEFAULT TABLESPACE $default_tablespace
TEMPORARY TABLESPACE $temporary_tablespace
PROFILE $profile
/
";
# Add quotas
$sql = "$copyright
SELECT
MAX_BYTES,
TABLESPACE_NAME
FROM DBA_TS_QUOTAS
WHERE USERNAME = '$schema'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
while (($max_bytes,$tablespace_name) = $cursor->fetchrow_array) {
if ($max_bytes eq "-1") {
$max_bytes = "UNLIMITED";
}
$ddl .= "
ALTER USER $schema QUOTA $max_bytes ON $tablespace_name;";
}
$cursor->finish;
# Add grants
# Roles first
$sql = "$copyright
SELECT
GRANTED_ROLE,
ADMIN_OPTION,
DEFAULT_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = '$schema'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($granted_role,$admin_option,$default_role) = $cursor->fetchrow_array) {
$ddl .= "
GRANT $granted_role TO $schema";
if ($admin_option eq "YES") {
$ddl .= " WITH ADMIN OPTION;";
} else {
$ddl .= ";";
}
if ($default_role eq "YES") {
push @default_roles, $granted_role;
}
}
$cursor->finish;
if (@default_roles) {
$roles = join(",",@default_roles);
$ddl .= "nALTER USER $schema DEFAULT ROLE $roles;";
}
# Explicit system privileges
$sql = "$copyright
SELECT
PRIVILEGE,
ADMIN_OPTION
FROM DBA_SYS_PRIVS
WHERE GRANTEE = '$schema'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($privilege,$admin_option) = $cursor->fetchrow_array) {
$ddl .= "
GRANT $privilege TO $schema";
if ($admin_option eq "YES") {
$ddl .= " WITH ADMIN OPTION;";
} else {
$ddl .= ";";
}
}
print "$ddln";
# Explicit object privileges
$sql = "$copyright
SELECT DISTINCT GRANTOR
FROM DBA_TAB_PRIVS
WHERE GRANTEE = '$schema'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ($grantor = $cursor->fetchrow_array) {
print "n/* Grants from $grantor */nn";
print "CONNECT $grantor/passwordnn";
$sql1 = "$copyright
SELECT
PRIVILEGE,
OWNER,
TABLE_NAME,
GRANTABLE
FROM DBA_TAB_PRIVS
WHERE GRANTOR = '$grantor'
AND GRANTEE = '$schema'
ORDER BY TABLE_NAME
";
$cursor1 = $dbh->prepare($sql1);
$cursor1->execute;
while (($privilege,$owner,$table_name,$grantable) = $cursor1->fetchrow) {
print "GRANT $privilege ON $owner.$table_name TO $schema";
if ($grantable eq "YES") {
print " WITH GRANT OPTION;n";
} else {
print ";n";
}
}
$cursor1->finish;
}
$cursor->finish;
# finish the HTML
print <<"EOF";
</PRE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
EOF
logit("Exit subroutine userDDL");
}
sub rbsDDL {
logit("Enter subroutine rbsDDL");
my ($sql,$cursor,$ddl);
my ($owner,$tablespace_name,$initial_extent,$next_extent);
my ($min_extents,$max_extents,$pct_increase,$status,$optsize);
print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TR>
<TD BGCOLOR='$cellcolor'>
<PRE>
EOF
# Put in some remarks
$ddl = "/*nDDL generated by Oracletool v$VERSIONn";
$ddl .= "for tablespace $object_name n*/nn";
# Start the command
$ddl .= "CREATE ROLLBACK SEGMENT $object_namen";
$sql = "$copyright
SELECT
DRS.OWNER,
DRS.TABLESPACE_NAME,
DRS.INITIAL_EXTENT,
DRS.NEXT_EXTENT,
DRS.MIN_EXTENTS,
DRS.MAX_EXTENTS,
DRS.PCT_INCREASE,
DRS.STATUS,
NVL(TO_CHAR(VRS.OPTSIZE,'999,999,999,999'),'Not set')
FROM DBA_ROLLBACK_SEGS DRS, V$ROLLSTAT VRS
WHERE DRS.SEGMENT_NAME = '$object_name'
AND VRS.USN = DRS.SEGMENT_ID
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
($owner,$tablespace_name,$initial_extent,$next_extent,$min_extents,$max_extents,$pct_increase,$status,$optsize)
= $cursor->fetchrow_array;
$cursor->finish;
$ddl .= " TABLESPACE $tablespace_namen";
$ddl .= " STORAGEn";
$ddl .= " (n";
$ddl .= " INITIAL $initial_extentn";
$ddl .= " NEXT $next_extentn";
$ddl .= " MINEXTENTS $min_extentsn";
$ddl .= " MAXEXTENTS $max_extentsn";
if ($optsize) {
$ddl .= " OPTIMAL $optsizen";
}
$ddl .= " )n";
$ddl .= "/nn";
if ($status eq "ONLINE") {
$ddl .= "ALTER ROLLBACK SEGMENT $object_name ONLINEn/nn";
}
print $ddl;
# finish the HTML
print <<"EOF";
</PRE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
EOF
logit("Exit subroutine rbsDDL");
}
sub tsDDL {
logit("Enter subroutine tsDDL");
logit(" Tablespace: $object_name");
my ($sql,$cursor,$file_name,$file_id,$bytes,$ddl);
my ($initial_extent,$next_extent,$min_extents);
my ($max_extents,$pctincrease,$contents,$status,$not_first_file);
my ($min_extlen,$logging,$autoextensible,$increment_by,$maxbytes);
my ($extent_management,$allocation_type,$count);
print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TR>
<TD BGCOLOR='$cellcolor'>
<PRE>
EOF
# Put in some remarks
$ddl = "/*nDDL generated by Oracletool v$VERSIONn";
$ddl .= "for tablespace $object_name n*/nn";
# Start the command
$ddl .= "CREATE TABLESPACE $object_namen";
# Get the first datafile
if ($oracle7) {
$sql = "$copyright
SELECT
FILE_NAME,
BYTES
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '$object_name'
ORDER BY FILE_ID ASC
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
($file_name, $bytes) = $cursor->fetchrow;
$cursor->finish;
$ddl .= " DATAFILE '$file_name' SIZE $bytesn";
}
if (($oracle8) || ($oracle8i)) {
$sql = "$copyright
SELECT
FILE_NAME,
BYTES,
AUTOEXTENSIBLE,
MAXBYTES,
INCREMENT_BY
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '$object_name'
ORDER BY FILE_ID ASC
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
($file_name, $bytes,$autoextensible,$maxbytes,$increment_by)
= $cursor->fetchrow_array;
$cursor->finish;
$increment_by = $increment_by*$db_block_size;
$ddl .= " DATAFILE '$file_name' SIZE $bytesn";
if ($autoextensible eq "YES") {
$ddl .= " AUTOEXTEND ON NEXT $increment_by MAXSIZE $maxbytesn";
} else {
$ddl .= " AUTOEXTEND OFFn";
}
}
if ($oracle7) {
$sql = "$copyright
SELECT
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
CONTENTS
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = '$object_name'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
($initial_extent,$next_extent,$min_extents,$max_extents,
$pctincrease,$status,$contents) = $cursor->fetchrow_array;
$cursor->finish;
$ddl .= " DEFAULT STORAGE (n";
$ddl .= " INITIAL $initial_extentn";
$ddl .= " NEXT $initial_extentn";
$ddl .= " MINEXTENTS $min_extentsn";
$ddl .= " MAXEXTENTS $max_extentsn";
$ddl .= " PCTINCREASE $pctincreasen";
$ddl .= " )n";
$ddl .= " $contentsn";
$ddl .= " $statusn";
}
if (($oracle8) && (! $oracle8i)) {
$sql = "$copyright
SELECT
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
MIN_EXTLEN,
STATUS,
CONTENTS,
LOGGING
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = '$object_name'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
($initial_extent,$next_extent,$min_extents,$max_extents,$pctincrease,$min_extlen,
$status,$contents,$logging) = $cursor->fetchrow_array;
$cursor->finish;
$ddl .= " DEFAULT STORAGE (n";
$ddl .= " INITIAL $initial_extentn";
$ddl .= " NEXT $initial_extentn";
$ddl .= " MINEXTENTS $min_extentsn";
$ddl .= " MAXEXTENTS $max_extentsn";
$ddl .= " PCTINCREASE $pctincreasen";
$ddl .= " )n";
if ($min_extlen) {
$ddl .= " MINIMUM EXTENT $min_extlenn";
}
$ddl .= " $contentsn";
$ddl .= " $loggingn";
$ddl .= " $statusn";
}
if ($oracle8i) {
$sql = "$copyright
SELECT
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
MIN_EXTLEN,
STATUS,
CONTENTS,
LOGGING,
EXTENT_MANAGEMENT,
ALLOCATION_TYPE
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = '$object_name'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
($initial_extent,$next_extent,$min_extents,$max_extents,$pctincrease,$min_extlen,
$status,$contents,$logging,$extent_management,$allocation_type) = $cursor->fetchrow_array;
$cursor->finish;
if (($min_extlen) && ($allocation_type eq "USER")) {
$ddl .= " MINIMUM EXTENT $min_extlenn";
}
if ($extent_management eq "LOCAL") {
$ddl .= " EXTENT MANAGEMENT LOCALn";
if ($allocation_type eq "UNIFORM") {
$ddl .= " UNIFORM SIZE $initial_extentn";
} else {
$ddl .= " AUTOALLOCATEn";
}
} else {
$ddl .= " EXTENT MANAGEMENT DICTIONARYn";
$ddl .= " DEFAULT STORAGE (n";
$ddl .= " INITIAL $initial_extentn";
$ddl .= " NEXT $initial_extentn";
$ddl .= " MINEXTENTS $min_extentsn";
$ddl .= " MAXEXTENTS $max_extentsn";
$ddl .= " PCTINCREASE $pctincreasen";
$ddl .= " )n";
}
$ddl .= " $contentsn";
$ddl .= " $loggingn";
$ddl .= " $statusn";
}
$ddl .= "/nn";
print $ddl;
undef $ddl;
# Add the additional datafiles, if any
$sql = "$copyright
SELECT
COUNT(*)
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '$object_name'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$count = $cursor->fetchrow_array;
$cursor->finish;
if ($count > 1) {
if ($oracle7) {
$sql = "$copyright
SELECT
FILE_NAME,
BYTES
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '$object_name'
ORDER BY FILE_ID ASC
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($file_name, $bytes) = $cursor->fetchrow) {
if ($not_first_file) {
$ddl .= "ALTER TABLESPACE $object_namen ADD DATAFILE '$file_name' SIZE $bytesn/nn";
}
$not_first_file++;
}
$cursor->finish;
}
if (($oracle8) || ($oracle8i)) {
$sql = "$copyright
SELECT
FILE_NAME,
BYTES,
AUTOEXTENSIBLE,
MAXBYTES,
INCREMENT_BY
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '$object_name'
ORDER BY FILE_ID ASC
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($file_name,$bytes,$autoextensible,$maxbytes,$increment_by)
= $cursor->fetchrow_array) {
if ($not_first_file) {
$ddl .= "ALTER TABLESPACE $object_namen ADD DATAFILE '$file_name' SIZE $bytesn";
if ($autoextensible eq "YES") {
$increment_by = $increment_by*$db_block_size;
$ddl .= " AUTOEXTEND ON NEXT $increment_by MAXSIZE $maxbytesn";
} else {
$ddl .= " AUTOEXTEND OFFn";
}
$ddl .= "/nn";
}
$not_first_file++;
}
$cursor->finish;
}
print $ddl;
}
# finish the HTML
print <<"EOF";
</PRE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
EOF
logit("Exit subroutine tsDDL");
}
sub tableDDL {
logit("Enter subroutine tableDDL");
logit(" Table(s): $object_name");
my ($ddl,@columns,$column,$constraint,$primary_key,$diff,$i,$cursor1,$sql1);
my ($data_type,$data_length,$data_precision,$data_default,$column_name,$comments);
my ($tablespace_name,$pct_free,$pct_used,$initial_extent,$next_extent,$min_extents,$max_extents,$pct_increase);
my ($ini_trans,$max_trans,$constraint_name,$search_condition,$status,$constraint_found);
my ($delete_rule,$r_constraint_name,$r_owner,$r_table_name,$privilege,$grantee);
my ($index_name,$uniqueness,$owner,$cols,$table_name,@tables,$object);
my ($username,$param,@params,$foo,$count,$cache,$headertype);
$username = $query->param('username') || "";
$headertype = $query->param('headertype') || "";
# If username is set, then it is for a select list of tables
# for that schema.
if ($username) {
logit(" DDL is being generated for schema $username");
@params = $query->param;
foreach $param(@params) {
if ($param =~ /reverse~/) {
($foo,$table_name) = split("~", $param);
$object_name .= "$username.$table_name,";
}
}
}
logit(" Table names: $object_name");
# Get rid of spaces in the table names.
$object_name =~ s/ //g;
# Get rid of leading and trailing commas.
$object_name =~ s/^,//g;
$object_name =~ s/,$//g;
# Force uppercase
$object_name = uc($object_name);
# Put table names into an array.
@tables = split(',', $object_name);
# Start the HTML, just a table around the code.
# Unless the user is downloading the sql.
unless ($headertype eq "octet") {
print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TR>
<TD BGCOLOR='$cellcolor'>
<PRE>
EOF
}
# Extract the DDL for recreation, for each table.
foreach $object (@tables) {
($schema,$object_name) = split('.', $object);
logit(" Table: $object_name Schema: $schema");
$sql = "$copyright
SELECT COUNT(*)
FROM DBA_TABLES
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$count = $cursor->fetchrow;
if (! $count) {
print "n/* Table $schema.$object_name requested but does not exist. */nn";
next;
}
$cursor->finish;
# Skip if the table is partitioned.
if ($oracle8) {
$sql = "$copyright
SELECT PARTITIONED
FROM DBA_TABLES
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$foo = $cursor->fetchrow;
if ($foo eq "YES") {
print "n/* Table $schema.$object_name is partitioned, not supported at this time. */nn";
next;
}
$cursor->finish;
}
# Get the column info ordered by position
$sql = "$copyright
SELECT
A.COLUMN_NAME "Column name",
A.DATA_TYPE "Type",
A.DATA_LENGTH "Length",
A.DATA_PRECISION "Precision",
B.DATA_DEFAULT "Default"
FROM
(SELECT
TABLE_NAME,
COLUMN_NAME,
OWNER,
COLUMN_ID,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION
FROM DBA_TAB_COLUMNS
WHERE (TABLE_NAME = '$object_name')
AND (OWNER = '$schema')
AND (DATA_TYPE <> 'NUMBER')
UNION
SELECT
TABLE_NAME,
COLUMN_NAME,
OWNER,
COLUMN_ID,
DATA_TYPE,
DATA_PRECISION,
DATA_SCALE
FROM DBA_TAB_COLUMNS
WHERE (TABLE_NAME = '$object_name')
AND (OWNER = '$schema')
AND (DATA_TYPE = 'NUMBER')
) A,
DBA_TAB_COLUMNS B
WHERE (A.TABLE_NAME = B.TABLE_NAME)
AND (A.OWNER = B.OWNER)
AND (A.COLUMN_ID = B.COLUMN_ID)
ORDER BY B.COLUMN_ID
";
# Put in some remarks
$ddl = "/*nDDL generated by Oracletool v$VERSIONn";
$ddl .= "for table $schema.$object_name n*/nn";
# Start the CREATE TABLE statement
$ddl .= "CREATE TABLE $schema.$object_namen";
$ddl .= " (n";
# Get the column names, data lengths, and precision
# Also, add any DEFAULT VALUE clauses
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($column_name,$data_type,$data_length,$data_precision,$data_default) = $cursor->fetchrow_array) {
$ddl .= " $column_name";
$diff = (35 - length($column_name));
for ($i = 0; $i <= $diff; $i++) {
$ddl .= " ";
}
$ddl .= "$data_type";
if ($data_length && ($data_type ne "DATE")) {
$ddl .= "($data_length";
if ($data_precision) {
$ddl .= ",$data_precision)";
} else {
$ddl .= ")";
}
}
if ($data_default) {
$ddl .= " DEFAULT $data_default";
}
$sql1 = "$copyright
SELECT
CONSTRAINT_NAME,
SEARCH_CONDITION,
STATUS
FROM DBA_CONSTRAINTS
WHERE OWNER = '$schema'
AND TABLE_NAME = '$object_name'
AND CONSTRAINT_TYPE = 'C'
AND CONSTRAINT_NAME IN (
SELECT
CONSTRAINT_NAME
FROM DBA_CONS_COLUMNS
WHERE OWNER = '$schema'
AND TABLE_NAME = '$object_name'
AND COLUMN_NAME = '$column_name')
";
$cursor1 = $dbh->prepare($sql1);
$cursor1->execute;
while (($constraint_name,$search_condition,$status) = $cursor1->fetchrow) {
next unless ($search_condition =~ /NOT NULL$/);
$constraint_found = 1;
if ($constraint_name) {
if ($constraint_name =~ /SYS_/) {
$ddl .= "tNOT NULL";
} else {
$ddl .= "tCONSTRAINT $constraint_name NOT NULL";
}
if ($status =~ /ENABLE/) {
$ddl .= " ENABLE,n";
} else {
$ddl .= " DISABLE,n";
}
} else {
$ddl .= ",n";
}
}
unless ($constraint_found) {
$ddl .= ",n";
}
$constraint_found = 0;
$cursor1->finish;
}
$cursor->finish;
$ddl =~ s/,n$/n/;
$ddl .= " )n";
$sql = "$copyright
SELECT
TABLESPACE_NAME,
PCT_FREE,
PCT_USED,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
INI_TRANS,
MAX_TRANS,
CACHE
FROM DBA_TABLES
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
($tablespace_name,$pct_free,$pct_used,$initial_extent,$next_extent,$min_extents,$max_extents,$pct_increase,$ini_trans,$max_trans,$cache) = $cursor->fetchrow_array;
$cursor->finish;
$ddl .= " TABLESPACE $tablespace_namen";
$ddl .= " PCTFREE $pct_freen";
$ddl .= " PCTUSED $pct_usedn";
$ddl .= " INITRANS $ini_transn";
$ddl .= " MAXTRANS $max_transn";
$ddl .= " STORAGE (n";
$ddl .= " INITIAL $initial_extentn";
$ddl .= " NEXT $next_extentn";
$ddl .= " MINEXTENTS $min_extentsn";
$ddl .= " MAXEXTENTS $max_extentsn";
$ddl .= " PCTINCREASE $pct_increasen";
if ($cache eq " Y") {
$ddl .= " ) CACHE";
} else {
$ddl .= " ) NOCACHE";
}
$ddl .= "n/nn";
print "$ddl";
# Create the indexes
$sql = "$copyright
SELECT
INDEX_NAME,
OWNER,
TABLESPACE_NAME,
UNIQUENESS,
PCT_FREE,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
INI_TRANS,
MAX_TRANS
FROM DBA_INDEXES
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
while (($index_name,$owner,$tablespace_name,$uniqueness,$pct_free,$initial_extent,$next_extent,$min_extents,$max_extents,$pct_increase,$ini_trans,$max_trans) = $cursor->fetchrow_array) {
undef @columns;
$ddl = "CREATE ";
$sql1 = "$copyright
SELECT COLUMN_NAME
FROM DBA_IND_COLUMNS
WHERE INDEX_NAME = '$index_name'
AND INDEX_OWNER = '$owner'
ORDER BY COLUMN_POSITION
";
$cursor1 = $dbh->prepare($sql1);
$cursor1->execute;
while ($column_name = $cursor1->fetchrow_array) {
push @columns, $column_name;
}
$cursor1->finish;
if ($#columns > 0) {
$cols = join(",", @columns);
$cols =~ s/^,//;
} else {
$cols = $columns[0];
}
if ($uniqueness eq "UNIQUE") {
$ddl .= "UNIQUE INDEX $owner.$index_name ON $schema.$object_namen";
} else {
$ddl .= "INDEX $owner.$index_name ON $schema.$object_namen";
}
$ddl .= " ($cols)n";
$ddl .= " TABLESPACE $tablespace_namen";
$ddl .= " PCTFREE $pct_freen";
$ddl .= " INITRANS $ini_transn";
$ddl .= " MAXTRANS $max_transn";
$ddl .= " STORAGE (n";
$ddl .= " INITIAL $initial_extentn";
$ddl .= " NEXT $next_extentn";
$ddl .= " MINEXTENTS $min_extentsn";
$ddl .= " MAXEXTENTS $max_extentsn";
$ddl .= " PCTINCREASE $pct_increasen";
$ddl .= " )n/nn";
print "$ddl";
}
$cursor->finish;
# Add the primary key, if one exists
$sql = "$copyright
SELECT
CONSTRAINT_NAME
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
AND CONSTRAINT_TYPE = 'P'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$constraint_name = $cursor->fetchrow_array;
$cursor->finish;
if ($constraint_name) {
undef @columns;
$sql1 = "$copyright
SELECT COLUMN_NAME
FROM DBA_CONS_COLUMNS
WHERE CONSTRAINT_NAME = '$constraint_name'
AND OWNER = '$schema'
ORDER BY POSITION
";
$cursor1 = $dbh->prepare($sql1);
$cursor1->execute;
while ($column_name = $cursor1->fetchrow_array) {
push @columns, $column_name;
}
$cursor1->finish;
if ($#columns > 0) {
$cols = join(",", @columns);
$cols =~ s/^,//;
} else {
$cols = $columns[0];
}
$ddl = "ALTER TABLE $schema.$object_name ADD CONSTRAINT $constraint_name PRIMARY KEY ($cols) USING INDEXn/nn";
print "$ddl";
}
# Add unique constraints, if they exist.
$sql = "$copyright
SELECT
CONSTRAINT_NAME,
STATUS
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
AND CONSTRAINT_TYPE = 'U'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
while (($constraint_name,$status) = $cursor->fetchrow_array) {
undef @columns;
$sql1 = "$copyright
SELECT COLUMN_NAME
FROM DBA_CONS_COLUMNS
WHERE CONSTRAINT_NAME = '$constraint_name'
AND OWNER = '$schema'
ORDER BY POSITION
";
$cursor1 = $dbh->prepare($sql1);
$cursor1->execute;
while ($column_name = $cursor1->fetchrow_array) {
push @columns, $column_name;
}
$cursor1->finish;
if ($#columns > 0) {
$cols = join(",", @columns);
$cols =~ s/^,//;
} else {
$cols = $columns[0];
}
$ddl = "ALTER TABLE $schema.$object_name ADD CONSTRAINT $constraint_name UNIQUE ($cols)";
if ($status =~ /ENABLE/) {
$ddl .= " ENABLEn/nn";
} else {
$ddl .= " DISABLEn/nn";
}
print "$ddl";
}
# Add the additional check constraints.
$sql = "$copyright
SELECT
CONSTRAINT_NAME,
SEARCH_CONDITION,
STATUS
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
AND CONSTRAINT_TYPE = 'C'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
while (($constraint_name,$search_condition,$status) = $cursor->fetchrow) {
next unless ($search_condition !~ /NOT NULL$/);
$ddl = "ALTER TABLE $schema.$object_name ADD CONSTRAINT $constraint_name CHECK ($search_condition)";
if ($status =~ /ENABLE/) {
$ddl .= " ENABLEn/nn";
} else {
$ddl .= " DISABLEn/nn";
}
print $ddl;
}
$cursor->finish;
# Add the foreign key constraints.
$sql = "$copyright
SELECT
CONSTRAINT_NAME,
STATUS,
DELETE_RULE,
R_CONSTRAINT_NAME,
R_OWNER
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
AND CONSTRAINT_TYPE = 'R'
";
$cursor=$dbh->prepare($sql) or print "$DBI::errstrn";
$cursor->execute;
while (($constraint_name,$status,$delete_rule,$r_constraint_name,$r_owner) = $cursor->fetchrow) {
undef @columns;
$sql1 = "$copyright
SELECT COLUMN_NAME
FROM DBA_CONS_COLUMNS
WHERE CONSTRAINT_NAME = '$constraint_name'
AND OWNER = '$schema'
ORDER BY POSITION
";
$cursor1 = $dbh->prepare($sql1) or print "$DBI::errstrn";
$cursor1->execute;
while ($column_name = $cursor1->fetchrow_array) {
push @columns, $column_name;
}
$cursor1->finish;
if ($#columns > 0) {
$cols = join(",", @columns);
$cols =~ s/^,//;
} else {
$cols = $columns[0];
}
$ddl = "ALTER TABLE $schema.$object_name ADD CONSTRAINT $constraint_name FOREIGN KEY ($cols)";
undef @columns;
$sql1 = "$copyright
SELECT
COLUMN_NAME,
TABLE_NAME
FROM DBA_CONS_COLUMNS
WHERE CONSTRAINT_NAME = '$r_constraint_name'
AND OWNER = '$r_owner'
ORDER BY POSITION
";
$cursor1 = $dbh->prepare($sql1) or print "$DBI::errstrn";
$cursor1->execute;
while (($column_name,$r_table_name) = $cursor1->fetchrow_array) {
push @columns, $column_name;
$table_name = $r_table_name;
}
$cursor1->finish;
if ($#columns > 0) {
$cols = join(",", @columns);
$cols =~ s/^,//;
} else {
$cols = $columns[0];
}
$ddl .= " REFERENCES $r_owner.$table_name ($cols)";
undef $table_name;
if ($delete_rule eq "CASCADE") {
$ddl .= " ON DELETE CASCADE";
}
if ($status =~ /ENABLE/) {
$ddl .= " ENABLEn/nn";
} else {
$ddl .= " DISABLEn/nn";
}
print $ddl;
}
$cursor->finish;
# Add foreign key constraints to tables which reference this table
$sql = "$copyright
SELECT
TABLE_NAME,
OWNER,
CONSTRAINT_NAME,
R_CONSTRAINT_NAME,
R_OWNER,
DELETE_RULE,
STATUS
FROM DBA_CONSTRAINTS
WHERE R_CONSTRAINT_NAME IN
( SELECT
CONSTRAINT_NAME
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
)
AND R_OWNER = '$schema'
";
$cursor=$dbh->prepare($sql) or print "$DBI::errstrn";
$cursor->execute;
while (($table_name,$owner,$constraint_name,$r_constraint_name,$r_owner,$delete_rule,$status) = $cursor->fetchrow) {
undef @columns;
$sql1 = "$copyright
SELECT COLUMN_NAME
FROM DBA_CONS_COLUMNS
WHERE CONSTRAINT_NAME = '$constraint_name'
AND OWNER = '$owner'
ORDER BY POSITION
";
$cursor1 = $dbh->prepare($sql1) or print "$DBI::errstrn";
$cursor1->execute;
while ($column_name = $cursor1->fetchrow_array) {
push @columns, $column_name;
}
$cursor1->finish;
if ($#columns > 0) {
$cols = join(",", @columns);
$cols =~ s/^,//;
} else {
$cols = $columns[0];
}
logit(" Constraint name: $constraint_name, table $schema.$table_name: Columns: $colsn");
$ddl = "ALTER TABLE $owner.$table_name ADD CONSTRAINT $constraint_name FOREIGN KEY ($cols)";
undef @columns;
$sql1 = "$copyright
SELECT
COLUMN_NAME
FROM DBA_CONS_COLUMNS
WHERE CONSTRAINT_NAME = '$r_constraint_name'
AND OWNER = '$schema'
ORDER BY POSITION
";
$cursor1 = $dbh->prepare($sql1) or print "$DBI::errstrn";
$cursor1->execute;
while (($column_name,$r_table_name) = $cursor1->fetchrow_array) {
push @columns, $column_name;
$table_name = $r_table_name;
}
$cursor1->finish;
if ($#columns > 0) {
$cols = join(",", @columns);
$cols =~ s/^,//;
} else {
$cols = $columns[0];
}
logit(" Constraint name: $constraint_name, table $schema.$table_name: Columns: $colsn");
$ddl .= " REFERENCES $schema.$object_name ($cols)";
undef $table_name;
if ($delete_rule eq "CASCADE") {
$ddl .= " ON DELETE CASCADE";
}
if ($status =~ /ENABLE/) {
$ddl .= " ENABLEn/nn";
} else {
$ddl .= " DISABLEn/nn";
}
print $ddl;
}
$cursor->finish;
# Add table comments
$sql = "$copyright
SELECT
COMMENTS
FROM DBA_TAB_COMMENTS
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
AND COMMENTS IS NOT NULL
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ($comments = $cursor->fetchrow_array) {
print "COMMENT ON TABLE $schema.$object_name IS '$comments';n";
}
$cursor->finish;
# Add column comments
$sql = "$copyright
SELECT
COLUMN_NAME,
COMMENTS
FROM DBA_COL_COMMENTS
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
AND COMMENTS IS NOT NULL
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($column_name,$comments) = $cursor->fetchrow_array) {
print "COMMENT ON COLUMN $schema.$object_name.$column_name IS '$comments';n";
}
$cursor->finish;
# Extract grant statements
$sql = "$copyright
SELECT
PRIVILEGE,
GRANTEE
FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($privilege,$grantee) = $cursor->fetchrow_array) {
print "GRANT $privilege on $schema.$object_name to $grantee;n";
}
$cursor->finish;
print "nn";
}
# Finish the HTML
# unless the user is downloading the sql.
unless ($headertype eq "octet") {
print <<"EOF";
</PRE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
EOF
}
logit("Exit subroutine tableDDL");
exit;
}
sub Describe {
logit("Enter subroutine Describe");
my ($sql,$moresql,$text,$link,$infotext);
my $object_name = shift;
# Get owner if one is specified
$_ = uc($object_name);
if (/./) {
($schema, $object_name) = split /./;
$moresql = "AND OWNER = UPPER('$schema')";
} else {
$moresql = "AND OWNER = UPPER('$schema')";
}
$object_name = uc($object_name);
$schema = uc($schema);
$sql = "$copyright
SELECT
COLUMN_NAME "Column name",
DECODE(
NULLABLE,
'N','Not Null',
'Y',''
) "Null?",
DATA_TYPE "Type",
DATA_LENGTH "Data length",
DATA_PRECISION "Precision"
FROM
DBA_TAB_COLUMNS
WHERE TABLE_NAME = '$object_name'
$moresql
ORDER BY COLUMN_ID
";
$text = "Description of $schema.$object_name";
$link = "";
$infotext = "Object to be described does not exist";
my $err = DisplayTable($sql,$text,$link,$infotext);
print "<BR><HR WIDTH="10%"><BR>n";
logit("Exit subroutine Describe");
}
sub objectSearch {
logit("Enter subroutine objectSearch");
my ($sql,$text,$link,$infotext,$moresql,$count);
my ($obj_name,$object_type,$owner,$object_id);
my ($object_found);
# Search for an object in the entire database
# Check for a null value
if ($object_name eq "") {
message("You must enter an object name!n");
Footer();
exit;
} else {
$object_name = uc($object_name);
}
# Check to see if it is a username
$sql = "$copyright
SELECT
USERNAME "Username"
FROM DBA_USERS
WHERE USERNAME = UPPER('$object_name')
";
$text = "A username matches your search.";
$link = "$scriptname?database=$database&schema=$object_name&object_type=USERINFO";
$infotext = "No usernames match your search keyword";
DisplayTable($sql,$text,$link,$infotext);
# Check to see if it is a tablespace
$sql = "$copyright
SELECT
TABLESPACE_NAME "Tablespace name"
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = UPPER('$object_name')
";
$text = "A tablespace name matches your search.";
$link = "$scriptname?database=$database&schema=$object_name&object_type=TSINFO";
$infotext = "No tablespace names match your search keyword";
DisplayTable($sql,$text,$link,$infotext);
# Get owner if one is specified
$_ = $object_name;
if (/./) {
($schema, $object_name) = split /./;
$moresql = "AND OWNER = UPPER('$schema')";
}
$sql = "$copyright
SELECT
OBJECT_NAME "Object name",
OBJECT_TYPE "Object type",
OWNER "Owner",
OBJECT_ID "Object ID"
FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE UPPER('%$object_name%')
AND OBJECT_TYPE NOT LIKE '%PARTITION'
$moresql
ORDER BY 1,2,3
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$object_found = $cursor->fetchrow_array;
$cursor->finish;
if ($object_found) {
text("The following 'LIKE' objects were found.");
# Print the heading
print <<"EOF";
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Show marked object dependencies">
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="dependencies">
<TABLE BORDER =0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Mark</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Object name</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Object type</T
H>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Owner</TH>
EOF
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($obj_name,$object_type,$owner,$object_id) = $cursor->fetchrow) {
$_ = $object_type;
s/ /+/g;
# Object ID's are sometimes not returned because of database link naming conventions...
if ($object_id) {
print <<"EOF";
<TR>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><INPUT TYPE=CHECKBOX NAME=dependency~$object_id></TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A href=$scriptname?database=$database&arg=$obj_name&object_type=$_&schema=$owner>$obj_name</A></TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$object_type</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$owner</TD>
</TR>
EOF
}
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</FORM>
</TABLE>
EOF
} else {
message("No objects "LIKE" $object_name were found.");
}
# If $object_name is a number, search by object_id as well.
$_ = $object_name;
if ( ! /D/ ) {
$sql = "$copyright
SELECT
OBJECT_NAME "Object name",
OBJECT_TYPE "Object type",
OWNER "Owner"
FROM DBA_OBJECTS
WHERE OBJECT_ID = '$object_name'
";
$text = "The following object was found with object_id $object_name";
$link = "";
$infotext = "No objects were found with object_id $object_name";
ObjectTable($sql,$text,$infotext);
}
if ($count && ! $ENV{'LIMIT_SEARCH'}) {
$sql = "$copyright
SELECT
A.USERNAME "User accessing",
A.OSUSER "OS Username",
A.PROCESS "Process ID",
A.PROGRAM "Program",
B.SID "SID",
A.SERIAL# "Serial#",
B.OBJECT "Object name",
B.OWNER "Owner",
B.TYPE "Object type"
FROM V$SESSION A, V$ACCESS B
WHERE B.OBJECT IN
(SELECT OBJECT_NAME
FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE UPPER('%$object_name%') $moresql
AND A.SID = B.SID AND A.STATUS = 'ACTIVE')
";
$text = "Objects currently being accessed that match your search";
$link = "";
$infotext = "No objects that match your search are currently being accessed";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine objectSearch");
}
}
sub showConstraint {
logit("Enter subroutine showConstraint");
my ($sql,$text,$link);
# Constraint info
$sql = "$copyright
SELECT * FROM
(SELECT
TABLE_NAME "Table_name",
CONSTRAINT_NAME "Constraint name"
FROM DBA_CONSTRAINTS
WHERE OWNER = '$schema'
AND CONSTRAINT_NAME = '$object_name'),
(SELECT
TABLE_NAME "Parent table",
CONSTRAINT_NAME "Parent constraint",
OWNER "Parent owner"
FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_NAME =
(SELECT R_CONSTRAINT_NAME
FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_NAME = '$object_name'
AND OWNER = '$schema'))
";
$text = "General info: Constraint $object_name";
$link = "";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showConstraint");
}
sub showView() {
logit("Enter subroutine showView");
my ($sql,$cursor,$status,$text,$infotext,$link,$foo);
print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 cellpadding=5 cellspacing=1>
<TR>
<TD BGCOLOR=$headingcolor>
<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0>
<TR>
<TD ALIGN=CENTER>
<FORM METHOD="GET" ACTION="$scriptname">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="TABLEROWS">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="$object_name">
<INPUT TYPE="SUBMIT" NAME="tablerows" VALUE="Display $rowdisplay rows of this view">
</TD>
</TR>
<TR>
<TD ALIGN=CENTER><FONT COLOR=$fontcolor SIZE=$fontsize><B><I>where</I></B></FONT></TD>
</TR>
<TR>
<TD ALIGN=CENTER>
<INPUT TYPE="TEXT" SIZE=30 NAME="whereclause">
</TD>
</FORM>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
EOF
showGrantButton();
# General info
$sql = "$copyright
SELECT
TO_CHAR(CREATED,'Month DD, YYYY - HH24:MI') "Date created",
TO_CHAR(LAST_DDL_TIME,'Month DD, YYYY - HH24:MI') "Last compiled",
STATUS "Status"
FROM DBA_OBJECTS
WHERE OBJECT_NAME = '$object_name'
AND OBJECT_TYPE = '$object_type'
AND OWNER = '$schema'
";
$text = "General info: $object_type $object_name";
DisplayTable($sql,$text);
checkValidity();
$object_type = lc $object_type;
# View structure