Oracle数据库

开发平台：

Perl

oracletool.pl：源码内容

$sql = "$copyright
SELECT
COLUMN_NAME "Column name",
DATA_TYPE "Type",
DATA_LENGTH "Length"
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME = '$object_name'
AND OWNER = '$schema'
ORDER BY COLUMN_ID
";
$object_type = lc $object_type;
$text = "Structure of $object_type $object_name";
$link = "";
DisplayTable($sql,$text,$link);
$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 view.";
$link = "";
$infotext = "There are no synonyms pointing to this view.";
DisplayTable($sql,$text,$link,$infotext);
# View source
$sql = "$copyright
SELECT
TEXT "Text"
FROM DBA_VIEWS
WHERE VIEW_NAME = '$object_name'
AND OWNER = '$schema'";
$text = "Text: $object_type $object_name";
DisplayPiecedData($sql,$text);
logit("Exit subroutine showView");
}
sub checkValidity {
logit("Enter subroutine checkValidity");
my ($sql,$cursor,$status,$text);
# Check for validity. If invalid, show additional info.
$object_type = uc($object_type);
$sql = "$copyright
SELECT
STATUS
FROM DBA_OBJECTS
WHERE OBJECT_NAME = '$object_name'
AND OBJECT_TYPE = '$object_type'
AND OWNER = '$schema'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$status = $cursor->fetchrow_array;
$cursor->finish;
if ($status eq "INVALID") {
$sql = "$copyright
SELECT
LINE "Line",
POSITION "Position",
TEXT "Text"
FROM DBA_ERRORS
WHERE NAME = '$object_name'
AND TYPE = '$object_type'
AND OWNER = '$schema'
ORDER BY SEQUENCE
";
$text = "Errors";
DisplayTable($sql,$text);
}
logit("Enter subroutine checkValidity");
}
sub showTrigger {
logit("Enter subroutine showTrigger");
my ($sql,$text,$link);
# General info
$sql = "$copyright
SELECT
TRIGGER_NAME "Trigger name",
TRIGGER_TYPE "Trigger type",
TRIGGERING_EVENT "Triggering event",
REFERENCING_NAMES "Referencing names",
WHEN_CLAUSE "When clause",
STATUS "Status"
FROM DBA_TRIGGERS
WHERE TRIGGER_NAME = '$object_name'
AND OWNER = '$schema'
";
$text = "Trigger: $object_name";
DisplayTable($sql,$text,$link);
checkValidity();
# Source
$sql = "$copyright
SELECT
TRIGGER_BODY "Trigger body"
FROM DBA_TRIGGERS
WHERE TRIGGER_NAME = '$object_name'
AND OWNER = '$schema'
";
$text = "Trigger body";
DisplayPiecedData($sql,$text);
logit("Exit subroutine showTrigger");
}
sub showDBlink() {
logit("Enter subroutine showDBlink");
my ($sql,$text,$link);
# General info
$sql = "$copyright
SELECT
DB_LINK "Link name",
USERNAME "Username",
HOST "Host",
CREATED "Created"
FROM DBA_DB_LINKS
WHERE DB_LINK = '$object_name'
AND OWNER = '$schema'"
;
$text = "Database link: $object_name";
$link = "";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showDBlink");
}
sub showSource() {
logit("Enter subroutine showSource");
my ($sql,$cursor,$status,$text,$infotext,$link);
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();
$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 object.";
$link = "";
$infotext = "There are no synonyms pointing to this object.";
DisplayTable($sql,$text,$link,$infotext);
# Source of object (package, procedure, etc.)
$sql = "$copyright
SELECT
TEXT
FROM
DBA_SOURCE
WHERE TYPE = '$object_type'
AND OWNER = '$schema'
AND NAME = '$object_name'
ORDER BY LINE
";
$text = "Text: $object_type $object_name";
$link = "";
DisplayPiecedData($sql,$text,$link);
logit("Exit subroutine showSource");
}
sub showSequence() {
my ($sql,$text,$link);
logit("Enter subroutine showSequence");
showGrantButton();
# General info
$sql = "$copyright
SELECT
MIN_VALUE "Min value",
MAX_VALUE "Max value",
INCREMENT_BY "Increment by",
CYCLE_FLAG "Cycle flag",
ORDER_FLAG "Order flag",
CACHE_SIZE "Cache size",
LAST_NUMBER "Last number"
FROM DBA_SEQUENCES
WHERE SEQUENCE_NAME = '$object_name'
AND SEQUENCE_OWNER = '$schema'
";
$text = "$object_type $object_name";
$link = "";
DisplayTable($sql,$text,$link);
logit("Enter subroutine showSequence");
}
sub showGrantsto() {
my ($sql,$text,$link,$infotext);
logit("Enter subroutine showGrantsto");
# System privileges
$sql = "$copyright
SELECT
PRIVILEGE "Privilege",
ADMIN_OPTION "Admin option"
FROM DBA_SYS_PRIVS
WHERE GRANTEE = '$schema'
";
$text = "System privileges granted to $schema";
$link = "";
$infotext = "There are no system privileges granted to $schema.";
DisplayTable($sql,$text,$link,$infotext);
# Granted roles
$sql = "$copyright
SELECT
GRANTED_ROLE "Granted role",
ADMIN_OPTION "Admin option",
DEFAULT_ROLE "Default role"
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = '$schema'
";
$text = "Roles granted to $schema";
$link = "$scriptname?database=$database&schema=$schema&object_type=ROLES";
$infotext = "There are no roles granted to $schema.";
DisplayTable($sql,$text,$link,$infotext);
# Granted object privileges (explicit)
$sql = "$copyright
SELECT
PRIVILEGE "Privilege",
TABLE_NAME "Table name",
GRANTOR "Grantor",
GRANTABLE "Grantable"
FROM DBA_TAB_PRIVS
WHERE GRANTEE = '$schema'
ORDER BY GRANTOR, TABLE_NAME
";
$text = "Explicit grants to $schema";
$link = "";
$infotext = "There are no explicit grants to $schema.";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showGrantsto");
}
sub showRoles {
logit("Enter subroutine showRoles");
my ($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
GRANTEE "Granted user"
FROM DBA_ROLE_PRIVS
WHERE GRANTED_ROLE = '$object_name'
AND GRANTEE IN (
SELECT USERNAME
FROM DBA_USERS
)
";
$text = "Users which are granted this role.";
$link = "$scriptname?database=$database&object_type=USERINFO";
$infotext = "No users are granted this role.";
DisplayColTable($sql,$text,$link,$infotext,$schema_cols);
$sql = "$copyright
SELECT
GRANTEE "Granted user"
FROM DBA_ROLE_PRIVS
WHERE GRANTED_ROLE = '$object_name'
AND GRANTEE IN (
SELECT ROLE
FROM DBA_ROLES
)
";
$text = "Roles which are granted this role.";
$link = "";
$infotext = "No roles are granted this role.";
DisplayTable($sql,$text,$link,$infotext);
# Roles granted to this role
$sql = "$copyright
SELECT
GRANTED_ROLE "Granted role",
ADMIN_OPTION "Admin option",
DEFAULT_ROLE "Default role"
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = '$object_name'
";
$text = "Roles granted to role $object_name";
$link = "$scriptname?database=$database&object_type=ROLES";
$infotext = "There are no roles granted to this role.";
DisplayTable($sql,$text,$link,$infotext);
# System privileges granted to this role
$sql = "$copyright
SELECT
PRIVILEGE "Privilege",
ADMIN_OPTION "Admin option"
FROM DBA_SYS_PRIVS
WHERE GRANTEE = '$object_name'
ORDER BY PRIVILEGE
";
$text = "System privileges granted to role $object_name";
$link = "";
$infotext = "There are no system privileges granted to this role.";
DisplayTable($sql,$text,$link,$infotext);
# Object privileges granted to this role
$sql = "$copyright
SELECT
PRIVILEGE "Privilege",
TABLE_NAME "Table name",
GRANTOR "Grantor",
GRANTABLE "Grantable?"
FROM DBA_TAB_PRIVS
WHERE GRANTEE = '$object_name'
ORDER BY GRANTOR, TABLE_NAME
";
$text = "Object privileges granted to role $object_name";
$link = "";
$infotext = "There are no object privileges granted to this role.";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showRoles");
}
sub showGrantsfrom {
logit("Enter subroutine showGrantsfrom");
my ($sql,$text,$link,$infotext);
# Object privileges granted from this user
$sql = "$copyright
SELECT
GRANTEE,
PRIVILEGE,
TABLE_NAME,
GRANTABLE
FROM DBA_TAB_PRIVS
WHERE GRANTOR = '$schema'
ORDER BY GRANTEE, TABLE_NAME
";
$text = "Object privileges granted from user $schema";
$link = "";
$infotext = "$schema has not granted any privileges to other users. $schema is a stingy user.";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showGrantsfrom");
}
sub OPSpage {
logit("Enter subroutine OPSpage");
my ($sql,$cursor,$text,$link,$infotext);
my ($instance_name,$instance_number,$thread,$hostname,$startup_time);
# Local instance info
$sql = "$copyright
SELECT
INSTANCE_NAME "Instance name",
INSTANCE_NUMBER "Instance #",
THREAD# "Thread",
HOST_NAME "Hostname",
TO_CHAR(STARTUP_TIME,'Day, Month DD YYYY - HH24:MI:SS') "Startup time"
FROM V$INSTANCE
";
$text = "Info about this instance.";
$link = "";
$infotext = "";
DisplayTable($sql,$text,$link,$infotext);
# All instance info
# The instance name wil be a hyperlink to connect to that database.
text("Info about all instances");
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'>Instance name</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Instance number</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Thread#</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Hostname</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Startup time</TH>
EOF
$sql = "$copyright
SELECT INSTANCE_NAME "Instance name",
INSTANCE_NUMBER "Instance #",
THREAD# "Thread",
HOST_NAME "Hostname",
TO_CHAR(STARTUP_TIME,'Day, Month DD YYYY - HH24:MI:SS') "Startup time"
FROM GV$INSTANCE
ORDER BY INSTANCE_NAME
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($instance_name,$instance_number,$thread,$hostname,$startup_time) = $cursor->fetchrow_array) {
print " <TR ALIGN=LEFT><TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A href=$scriptname?database=$instance_name&object_type=FRAMEPAGE TARGET=_top>$instance_name</A></TD>n";
print <<"EOF";
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$instance_number</A></TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$thread</A></TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$hostname</A></TD>
<TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$startup_time</A></TD></TR>
EOF
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</TABLE>
EOF
# Locked objects
$sql = "$copyright
SELECT
DO.OBJECT_NAME "Object name",
DO.OBJECT_TYPE "Object type",
DO.OWNER "Owner",
VLO.INST_ID "Instance ID",
VLO.SESSION_ID "SID",
VLO.ORACLE_USERNAME "Ora user",
VLO.OS_USER_NAME "OS user",
VLO.PROCESS "Process",
VLO.LOCKED_MODE "Mode"
FROM GV$LOCKED_OBJECT VLO, DBA_OBJECTS DO
WHERE VLO.OBJECT_ID = DO.OBJECT_ID
";
$text = "Objects which currently have locks.";
$infotext = "There are currently no locked objects.";
ObjectTable($sql,$text,$infotext);
# Session list
$sql = "$copyright
SELECT
GVS.INST_ID "Instance",
GVS.USERNAME "Ora user",
GVS.OSUSER "OS user",
GVS.SID "SID",
GVS.SERIAL# "Serial#",
GVS.STATUS "Status",
GVS.PROCESS "Process",
GVS.PROGRAM "Program",
TO_CHAR(GVS.LOGON_TIME,'Day MM/DD/YY HH24:MI') "Logon time",
GVST.SQL_TEXT "SQL text"
FROM GV$SESSION GVS, GV$SQLTEXT GVST
WHERE GVS.USERNAME IS NOT NULL
AND GVST.ADDRESS = GVS.SQL_ADDRESS
AND GVST.INST_ID = GVS.INST_ID
AND GVST.PIECE = 0
ORDER BY GVS.USERNAME, GVS.INST_ID, GVS.STATUS
";
$text = "Global session summary.";
$link = "";
$infotext = "";
DisplayTable($sql,$text,$link,$infotext);
# Lock (IDLM) information
$sql = "$copyright
SELECT
A.INSTANCE_NAME "Instance Name",
B.FROM_VAL "From",
B.TO_VAL "To",
B.ACTION_VAL "Action",
TO_CHAR(B.COUNTER,'999,999,999,999') "Counter"
FROM GV$INSTANCE A, GV$LOCK_ACTIVITY B
WHERE B.INST_ID = A.INST_ID
ORDER BY A.INSTANCE_NAME, B.COUNTER DESC
";
$text = "Lock conversions by instance.";
$link = "";
$infotext = "No lock conversions";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine OPSpage");
}
sub showUsers {
logit("Enter subroutine showUsers");
my ($sql,$user,@dbausers,@connectedusers,$dba,$connected,$text,$cursor);
my (@lockedusers,$locked,$skip,$counter,$row,$usercount,$i);
my $highlight = "#FFFFC6";
my $redlight = "#DEBDDE";
# Show database connection info
logit(" Showing connection information");
print <<"EOF";
Connected to database : $database<BR>
$banner
<P>
EOF
logit(" Done showing connection information");
logit(" Getting list of users with DBA role");
# Get all users who have the DBA role granted to them
$sql = "$copyright
SELECT GRANTEE
FROM DBA_ROLE_PRIVS
WHERE GRANTED_ROLE='DBA'
";
$cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr");
$cursor->execute or ErrorPage ("$DBI::errstr");
while ($user = $cursor->fetchrow_array) {
push (@dbausers,$user);
}
$cursor->finish;
logit(" Done getting list of users with DBA role");
# Get all users whose account status is not "OPEN", if Oracle8
if ($oracle8) {
logit(" We are > oracle7: Getting users with non-open accounts");
$sql = "$copyright
SELECT USERNAME
FROM DBA_USERS
WHERE ACCOUNT_STATUS <> 'OPEN'
";
$cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr");
$cursor->execute or ErrorPage ("$DBI::errstr");
while ($user = $cursor->fetchrow_array) {
push (@lockedusers,$user);
}
$cursor->finish;
logit(" Done getting users with non-open accounts");
}
# Get all users that are currently connected.
logit(" Getting list of currently connected users");
$sql = "$copyright
SELECT DISTINCT USERNAME
FROM V$SESSION
";
$cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr");
$cursor->execute or ErrorPage ("$DBI::errstr");
while ($user = $cursor->fetchrow_array) {
push (@connectedusers,$user);
}
$cursor->finish;
logit(" Done getting list of currently connected users");
# Display a count of all users.
logit(" Getting count of all users");
$sql = "$copyright
SELECT COUNT(*)
FROM DBA_USERS
";
$cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr");
$cursor->execute or ErrorPage ("$DBI::errstr");
$usercount = $cursor->fetchrow_array;
$cursor->finish;
logit(" Done getting count of all users");
# Get all usernames
$sql = "$copyright
SELECT
USERNAME
FROM DBA_USERS
ORDER BY USERNAME
";
if ($oracle8) {
logit(" We are > Oracle7, so check for account status");
$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.";
} else {
logit(" We are < Oracle8, so don't check for account status");
$text = "Select a schema by clicking on it.<BR>Yellow background indicates user is connected.<BR>Bold text in parenthesis indicates DBA authority.";
}
$cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr");
$cursor->execute or ErrorPage ("$DBI::errstr");
$counter=0;
logit(" Generating HTML");
print "<B>$text</B><P>n";
print "<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>n";
print " <TR>n";
print " <TD WIDTH=100%>n";
print " <TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>n";
print " <TH COLSPAN=$schema_cols BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'><B>Total # users: $usercount</B></TH>n";
while ($row = $cursor->fetchrow_array) {
undef $dba;
foreach $user (@dbausers) {
if ( $row eq $user ) {
$dba = "yes";
last;
}
}
undef $connected;
foreach $user (@connectedusers) {
if ( $row eq $user ) {
$connected = "yes";
last;
}
}
undef $locked;
foreach $user (@lockedusers) {
if ( $row eq $user ) {
$locked = "yes";
last;
}
}
print "<TR ALIGN=CENTER>" if $counter == 0;
if (($connected) && ($dba)) {
print "<TD BGCOLOR="$highlight"><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><STRONG><A href=$scriptname?database=$database&schema=$row&object_type=USERINFO>(${row})</A></STRONG></TD>n";
} elsif ($connected) {
print "<TD BGCOLOR="$highlight"><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A href=$scriptname?database=$database&schema=$row&object_type=USERINFO>$row</A></TD>n";
} elsif ($locked) {
print "<TD BGCOLOR="$redlight"><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A href=$scriptname?database=$database&schema=$row&object_type=USERINFO>$row</A></TD>n";
} elsif ($dba) {
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><STRONG><A href=$scriptname?database=$database&schema=$row&object_type=USERINFO>(${row})</A></STRONG></TD>n";
} else {
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A href=$scriptname?database=$database&schema=$row&object_type=USERINFO>$row</A></TD>n";
}
$counter++;
print "</TR>" if $counter == 0;
$skip = "";
if ($counter == $schema_cols) {
$counter = 0;
$skip = "Y";
}
}
if ((! $skip) && ($counter < $schema_cols)) {
for ($i = $counter; $i < $schema_cols; $i++) {
print "<TD BGCOLOR='$cellcolor'> </TD>n";
}
}
print " </TR>n";
print " </TABLE>n";
print " </TD>n";
print " </TR>n";
print "</TABLE>nn";
$cursor->finish or ErrorPage ("$DBI::errstr");
logit("Exit subroutine showUsers");
}
sub showAuditTrail {
logit("Enter subroutine showAuditTrail");
my ($sql,$text,$link,$infotext,$command);
$command = $query->param('command');
logit(" Command: $command");
logit(" Object name: $object_name");
if ($command eq "statement") {
$sql = "$copyright
SELECT
OS_USERNAME "OS user",
USERNAME "Username",
USERHOST "Host",
TERMINAL "Terminal",
TO_CHAR(TIMESTAMP,'Day, Month DD YYYY - HH24:MI:SS') "Timestamp",
OWNER "Owner",
OBJ_NAME "Object name",
ACTION_NAME "Action",
PRIV_USED "Priv used"
FROM DBA_AUDIT_TRAIL
WHERE PRIV_USED = '$object_name'
ORDER BY TIMESTAMP DESC, OWNER
";
$text = "Audit records: $object_name.";
$link = "";
$infotext = "No audit records for $object_name.";
DisplayTable($sql,$text,$link,$infotext);
}
if ($command eq "object") {
$sql = "$copyright
SELECT
OS_USERNAME "OS user",
USERNAME "Username",
USERHOST "Host",
TERMINAL "Terminal",
TO_CHAR(TIMESTAMP,'Day, Month DD YYYY - HH24:MI:SS') "Timestamp",
OWNER "Owner",
OBJ_NAME "Object name",
ACTION_NAME "Action",
PRIV_USED "Priv used"
FROM DBA_AUDIT_TRAIL
WHERE OBJ_NAME = '$object_name'
ORDER BY TIMESTAMP DESC, OWNER
";
$text = "Audit records: $object_name.";
$link = "";
$infotext = "No audit records for $object_name.";
DisplayTable($sql,$text,$link,$infotext);
}
logit("Exit subroutine showAuditTrail");
}
sub showAllAuditing {
logit("Enter subroutine showAllAuditing");
my ($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
AUDIT_OPTION "Audit option",
USER_NAME "Username",
SUCCESS "Success",
FAILURE "Failure"
FROM DBA_STMT_AUDIT_OPTS
ORDER BY USER_NAME
";
$text = "Statements / system privileges which are being audited.";
$link = "$scriptname?database=$database&object_type=SHOWAUDITTRAIL&command=statement";
$infotext = "No SQL statement / system privileges are being audited.";
DisplayTable($sql,$text,$link,$infotext);
# Oracle7 SQL
$sql = "$copyright
SELECT
OBJECT_NAME "Object name",
OBJECT_TYPE "Object type",
OWNER "Owner",
ALT "Alter",
AUD "Audit",
COM "Comment",
DEL "Delete",
GRA "Grant",
IND "Index",
INS "Insert",
LOC "Lock",
REN "Rename",
SEL "Select",
UPD "Update",
REF "References",
EXE "Execute"
FROM DBA_OBJ_AUDIT_OPTS
WHERE ALT != '-/-'
OR AUD != '-/-'
OR COM != '-/-'
OR DEL != '-/-'
OR GRA != '-/-'
OR IND != '-/-'
OR INS != '-/-'
OR LOC != '-/-'
OR REN != '-/-'
OR SEL != '-/-'
OR UPD != '-/-'
OR REF != '-/-'
OR EXE != '-/-'
ORDER BY OWNER
";
$sql = "$copyright
SELECT
OBJECT_NAME "Object name",
OBJECT_TYPE "Object type",
OWNER "Owner",
ALT "Alter",
AUD "Audit",
COM "Comment",
DEL "Delete",
GRA "Grant",
IND "Index",
INS "Insert",
LOC "Lock",
REN "Rename",
SEL "Select",
UPD "Update",
EXE "Execute",
CRE "Create",
REA "Read",
WRI "Write"
FROM DBA_OBJ_AUDIT_OPTS
WHERE ALT != '-/-'
OR AUD != '-/-'
OR COM != '-/-'
OR DEL != '-/-'
OR GRA != '-/-'
OR IND != '-/-'
OR INS != '-/-'
OR LOC != '-/-'
OR REN != '-/-'
OR SEL != '-/-'
OR UPD != '-/-'
OR EXE != '-/-'
OR CRE != '-/-'
OR REA != '-/-'
OR WRI != '-/-'
ORDER BY OWNER
" if ($oracle8);
$text = "Auditing options pertaining to individual objects.";
$link = "$scriptname?database=$database&object_type=SHOWAUDITTRAIL&command=object";
$infotext = "No schema objects are being audited.";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showAllAuditing");
}
sub Auditing {
logit("Enter subroutine Auditing");
my ($sql,$cursor,$value);
$sql = "$copyright
SELECT
VALUE FROM V$PARAMETER
WHERE NAME = 'audit_trail'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$value = $cursor->fetchrow_array;
if ( (uc($value) ne "FALSE") && (uc($value) ne "NONE")) {
return(1);
} else {
return(0);
}
logit("Exit subroutine Auditing");
}
sub showSecurity {
logit("Enter subroutine showSecurity");
my ($sql,$cursor,$value,$text,$link,$infotext,$cols);
if ( Auditing() ) {
print <<"EOF";
<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0>
<TR>
<TD ALIGMN=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="AUDITING">
<INPUT TYPE="SUBMIT" NAME="auditing" VALUE="Auditing information">
</FORM>
</TD>
</TR>
</TABLE>
EOF
} else {
message("Database auditing is not enabled.");
}
$sql = "$copyright
SELECT
ROLE
FROM DBA_ROLES
ORDER BY ROLE
";
$text = "All roles for this database";
$link = "$scriptname?database=$database&object_type=ROLES";
$infotext = "There are no roles in this database";
DisplayColTable($sql,$text,$link,$infotext,$schema_cols);
$sql = "$copyright
SELECT DISTINCT
PROFILE
FROM DBA_PROFILES
ORDER BY PROFILE
";
$text = "All profiles for this database";
$link = "$scriptname?database=$database&object_type=PROFILE";
$infotext = "There are no profiles in this database";
DisplayColTable($sql,$text,$link,$infotext,$schema_cols);
$sql = "$copyright
SELECT GRANTEE
FROM DBA_ROLE_PRIVS
WHERE GRANTED_ROLE='DBA'
AND GRANTEE IN
(SELECT USERNAME
FROM DBA_USERS)
";
$text = "All users with the "DBA" role granted to them";
$link = "$scriptname?database=$database&object_type=USERINFO";
$infotext = "There are no users with the "DBA" role in this database";
DisplayColTable($sql,$text,$link,$infotext,$schema_cols);
$sql = "$copyright
SELECT GRANTEE
FROM DBA_ROLE_PRIVS
WHERE GRANTED_ROLE='DBA'
AND GRANTEE IN
(SELECT ROLE
FROM DBA_ROLES)
";
$text = "All roles with the "DBA" role granted to them";
$link = "$scriptname?database=$database&object_type=ROLES";
$infotext = "There are no roles with the "DBA" role in this database";
DisplayColTable($sql,$text,$link,$infotext,$schema_cols);
logit("Exit subroutine showSecurity");
}
sub showProfile {
logit("Enter subroutine showProfile");
my ($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
RESOURCE_NAME "Resource name",
LIMIT "Limit"
FROM DBA_PROFILES
WHERE PROFILE = '$object_name'
ORDER BY RESOURCE_NAME
" if ($oracle7);
$sql = "$copyright
SELECT
RESOURCE_NAME "Resource name",
RESOURCE_TYPE "Resource type",
LIMIT "Limit"
FROM DBA_PROFILES
WHERE PROFILE = '$object_name'
ORDER BY RESOURCE_NAME
" if ($oracle8);
$text = "Profile $object_name";
$link = "";
$infotext = "";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showProfile");
}
sub userInfo {
logit("Enter subroutine userInfo");
# User info
# Get the data from the database
my ($sql,$cursor,$count,$text,$link,$infotext,$cols);
my ($uname,$defts,$tmpts,$created,$profile,$objcount);
my ($status);
$schema = $object_name unless $schema;
# General user info
$sql = "$copyright
SELECT
USERNAME,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE,
TO_CHAR(CREATED,'Month DD, YYYY - HH24:MI'),
PROFILE
FROM DBA_USERS
WHERE USERNAME = '$schema'
";
if ($oracle8) {
$sql = "$copyright
SELECT
USERNAME,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE,
TO_CHAR(CREATED,'Month DD, YYYY - HH24:MI'),
PROFILE,
ACCOUNT_STATUS
FROM DBA_USERS
WHERE USERNAME = '$schema'
";
}
$status = "";
$cursor=$dbh->prepare($sql);
$cursor->execute;
($uname,$defts,$tmpts,$created,$profile,$status) = $cursor->fetchrow;
$cursor->finish;
if (! $oracle8) {
print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TR>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>User name</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Default tablespace</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Temp tablespace</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>User creation date</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Profile</TH>
</TR>
<TR>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=USERDDL&schema=$schema>$uname</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=TSINFO&schema=$schema&arg=$defts>$defts</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=TSINFO&schema=$schema&arg=$tmpts>$tmpts</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$created</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=PROFILE&arg=$profile>$profile</A></TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
EOF
} else {
print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TR>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>User name</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Default tablespace</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Temp tablespace</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>User creation date</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Account status</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Profile</TH>
</TR>
<TR>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=USERDDL&schema=$schema>$uname</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=TSINFO&schema=$schema&arg=$defts>$defts</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=TSINFO&schema=$schema&arg=$tmpts>$tmpts</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$created</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$status</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=PROFILE&arg=$profile>$profile</A></TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
EOF
}
# Tablespace quotas
$sql = "$copyright
SELECT
TABLESPACE_NAME "Tablespace",
TO_CHAR(BYTES,'999,999,999,999') "Bytes used",
DECODE
(
MAX_BYTES,
'-1','Unlimited', TO_CHAR(MAX_BYTES,'999,999,999,999')
) "Quota"
FROM DBA_TS_QUOTAS
WHERE USERNAME = '$schema'
";
$text = "Tablespace quotas";
$link = "$scriptname?database=$database&object_type=TSINFO&schema=$schema";
$infotext = "$schema has no individual tablespace quotas.";
DisplayTable($sql,$text,$link,$infotext);
# Buttons for displaying grants / session info
print <<"EOF";
<P>
<TABLE BORDER=0>
<TR ALIGN=CENTER>
<TD>
<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="object_type" VALUE="GRANTSTO">
<INPUT TYPE="SUBMIT" NAME="togrants" VALUE="Display grants to $schema">
</FORM>
</TD>
<TD>
<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="object_type" VALUE="GRANTSFROM">
<INPUT TYPE="SUBMIT" NAME="fromgrants" VALUE="Display grants from $schema">
</FORM>
</TD>
</TR>
EOF
# Display a button if the user currently has sessions in this instance.
$sql = "$copyright
SELECT COUNT(*)
FROM V$SESSION
WHERE USERNAME = '$schema'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$count = $cursor->fetchrow_array;
$cursor->finish;
if ($count > 0) {
print <<"EOF";
<TR>
<TD COLSPAN=2 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="user" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="SESSIONS">
<INPUT TYPE="SUBMIT" NAME="sessions" VALUE="Display $schema session info">
</FORM>
</TD>
</TR>
</TABLE>
EOF
} else {
print <<"EOF";
</TABLE>
EOF
message("$schema has no sessions in this instance.");
}
# Check to see if there are any public synonyms pointing
# to objects owned by the schema selected.
$sql = "$copyright
SELECT
COUNT(*)
FROM DBA_SYNONYMS
WHERE TABLE_OWNER = '$schema'
AND OWNER = 'PUBLIC'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$objcount = $cursor->fetchrow_array || "";
$cursor->finish;
# Get object types owned by user
if ($objcount) {
$sql = "$copyright
SELECT DISTINCT
OBJECT_TYPE "Object type"
FROM DBA_OBJECTS
WHERE OWNER = '$schema'
AND OBJECT_TYPE != 'UNDEFINED'
UNION
SELECT
DECODE(DUMMY,'X','PUBLIC SYNONYMS')
FROM DUAL
";
$text = "Object types owned by $schema, + public synonyms.<BR>Click an object type for a list.";
} else {
$sql = "$copyright
SELECT DISTINCT
OBJECT_TYPE "Object type"
FROM DBA_OBJECTS
WHERE OWNER = '$schema'
AND OBJECT_TYPE != 'UNDEFINED'
";
$text = "Object types owned by $schema.<BR>Click an object type for a list.";
}
$link = "$scriptname?database=$database&schema=$schema&object_type=LISTOBJECTS";
$infotext = "There are no objects owned by $schema in this database.";
DisplayColTable($sql,$text,$link,$infotext,$schema_cols);
$sql = "$copyright
SELECT COUNT(*) FROM DBA_OBJECTS WHERE OWNER = '$schema'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$objcount = $cursor->fetchrow_array;
$cursor->finish;
if ($objcount) {
$sql = "$copyright
SELECT
COUNT(DECODE(TYPE, 2, OBJ#, '')) "Table",
COUNT(DECODE(TYPE, 1, OBJ#, '')) "Index",
COUNT(DECODE(TYPE, 5, OBJ#, '')) "Synonym",
COUNT(DECODE(TYPE, 4, OBJ#, '')) "View`",
COUNT(DECODE(TYPE, 6, OBJ#, '')) "Sequence",
COUNT(DECODE(TYPE, 7, OBJ#, '')) "Procedure",
COUNT(DECODE(TYPE, 8, OBJ#, '')) "Function",
COUNT(DECODE(TYPE, 9, OBJ#, '')) "Package",
COUNT(DECODE(TYPE,12, OBJ#, '')) "Trigger",
COUNT(DECODE(TYPE,10, OBJ#, '')) "Dependency"
FROM SYS.OBJ$
WHERE OWNER# =
(
SELECT USER_ID
FROM DBA_USERS
WHERE USERNAME = '$schema'
)
" if $oracle7;
$sql = "$copyright
SELECT
COUNT(DECODE(TYPE#, 2, OBJ#, '')) "Table",
COUNT(DECODE(TYPE#, 1, OBJ#, '')) "Index",
COUNT(DECODE(TYPE#, 5, OBJ#, '')) "Synonym",
COUNT(DECODE(TYPE#, 4, OBJ#, '')) "View",
COUNT(DECODE(TYPE#, 6, OBJ#, '')) "Sequence",
COUNT(DECODE(TYPE#, 7, OBJ#, '')) "Procedure",
COUNT(DECODE(TYPE#, 8, OBJ#, '')) "Function",
COUNT(DECODE(TYPE#, 9, OBJ#, '')) "Package",
COUNT(DECODE(TYPE#,12, OBJ#, '')) "Trigger",
COUNT(DECODE(TYPE#,10, OBJ#, '')) "Dependency"
FROM SYS.OBJ$
WHERE OWNER# =
(
SELECT USER_ID
FROM DBA_USERS
WHERE USERNAME = '$schema'
)
" if $oracle8;
$text = "Object count";
$link = "";
DisplayTable($sql,$text,$link);
}
$sql = "$copyright
SELECT
COUNT(*)
FROM DBA_OBJECTS
WHERE OWNER = '$schema'
AND OBJECT_TYPE IN ('TABLE','INDEX','CLUSTER')
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$count = $cursor->fetchrow_array;
if ($count > 0) {
print <<"EOF";
</FONT>
<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="object_type" VALUE="OBJECTREPORT">
<INPUT TYPE="SUBMIT" NAME="objectreport" VALUE="Object report by tablespace.">
</FORM>
EOF
}
if ($objcount) {
$sql = "$copyright
SELECT COUNT(*)
FROM DBA_OBJECTS
WHERE OWNER = '$schema'
AND STATUS = 'INVALID'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$count = $cursor->fetchrow_array;
$cursor->finish;
if ($count > 0) {
if (checkPriv("ALTER ANY PROCEDURE")) {
print <<"EOF";
</FONT>
<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="object_type" VALUE="SHOWINVALIDOBJECTS">
<INPUT TYPE="SUBMIT" NAME="objectreport" VALUE="Display $count invalid objects.">
</FORM>
EOF
} else {
message("There are $count invalid objects in this schema.n");
}
} else {
message("There are no invalid objects in this schema.n");
}
}
logit("Exit subroutine userInfo");
}
sub showInvalidObjects {
invalidObjectList($schema);
}
sub enterExtentReport {
logit("Enter subroutine enterExtentReport");
text("Enter a value below. A report will be generated showing all objects with a number of extents greater than or equal to the number you have entered. (Objects with unusually large number of extents)");
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="object_type" VALUE="EXTENTREPORT">
<INPUT TYPE=TEXT MAXLENGTH=10 SIZE=10 NAME=extents1 VALUE=1000>
EOF
text("Enter a value below. A report will be generated showing all objects with a number of extents greater than MAX_EXTENTS minus this number. (Objects nearing max extents)");
print " <INPUT TYPE=TEXT MAXLENGTH=10 SIZE=10 NAME=extents2 VALUE=25><P>n";
print <<"EOF";
<INPUT TYPE="SUBMIT" NAME="foo" VALUE="Generate report">
</FORM>
EOF
logit("Exit subroutine enterExtentReport");
}
sub extentReport {
logit("Enter subroutine extentReport");
my ($sql,$cursor,$tablespace_name,$link,$text,$infotext,$extents1,$extents2);
my (@tablespaces);
$extents1 = $query->param('extents1');
$extents2 = $query->param('extents2');
logit(" Looking for objects with extents > $extents1");
$sql = "$copyright
SELECT
SEGMENT_NAME "Object name",
SEGMENT_TYPE "Object type",
OWNER "Owner",
TO_CHAR(COUNT(*),'999,999,999,999') "Extents",
TO_CHAR(SUM(BYTES),'999,999,999,999') "Bytes",
TABLESPACE_NAME "Tablespace name"
FROM DBA_EXTENTS
GROUP BY SEGMENT_TYPE, SEGMENT_NAME, TABLESPACE_NAME, OWNER
HAVING COUNT(*) >= $extents1
ORDER BY 4 DESC
";
$text = "Objects with a number of extents >= $extents1.";
$infotext = "There are no objects with extents >= $extents1.";
ObjectTable($sql,$text,$infotext);
logit(" SQL = n$sql");
logit(" Error: $DBI::errstr") if $DBI::errstr;
print "<HR WIDTH='75%'>n";
$sql = "$copyright
SELECT
SEGMENT_NAME "Object name",
SEGMENT_TYPE "Object type",
OWNER "Owner",
TABLESPACE_NAME "Tablespace name",
TO_CHAR(EXTENTS,'999,999,999,999') "Extents",
TO_CHAR(MAX_EXTENTS,'999,999,999,999') "Max extents"
FROM DBA_SEGMENTS
WHERE EXTENTS > (MAX_EXTENTS-$extents2)
AND SEGMENT_TYPE != 'CACHE'
";
$text = "Objects that are approaching their max_extents limit.";
$infotext = "No objects in the database have extents > ( max_extents - $extents2 ).";
ObjectTable($sql,$text,$infotext);
print "<HR WIDTH='75%'>n";
text("Checking all tablespaces for objects which cannot allocate a next extent due to lack of space.");
$sql = "$copyright
SELECT
TABLESPACE_NAME
FROM DBA_TABLESPACES
ORDER BY TABLESPACE_NAME
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ($tablespace_name = $cursor->fetchrow_array) {
push @tablespaces, $tablespace_name;
}
$cursor->finish;
foreach $tablespace_name(@tablespaces) {
$sql = "$copyright
SELECT
SEGMENT_NAME "Object name",
SEGMENT_TYPE "Object type",
OWNER "Owner",
TO_CHAR(NEXT_EXTENT,'999,999,999,999') "Next extent"
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = '$tablespace_name'
AND NEXT_EXTENT > (SELECT NVL(MAX(BYTES),'0') FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = '$tablespace_name')
";
$text = "Objects in tablespace $tablespace_name which cannot allocate a next extent.";
$link = "";
$infotext = "Tablespace $tablespace_name OK.";
ObjectTable($sql,$text,$infotext);
}
text("Done.");
logit("Exit subroutine extentReport");
}
sub userSpaceReport {
logit("Enter subroutine userSpaceReport");
my ($sql,$link,$text,$sortfield,$owner,$bytes,$highlight,$color,$count);
$sortfield = $query->param('sortfield') || "3";
$highlight = "#FFFFC6";
text("Click on a column name to change sort order.");
print << "EOF";
<TABLE BORDER =0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
EOF
if ($sortfield eq "1") {
$color = $highlight;
} else {
$color = $headingcolor;
}
print " <TH BGCOLOR='$color' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=1>Owner</A></TH>n";
if ($sortfield eq "2") {
$sortfield = "2 DESC";
$color = $highlight;
} else {
$color = $headingcolor;
}
print " <TH BGCOLOR='$color' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=2>Object count</A></TH>n";
if ($sortfield eq "3") {
$sortfield = "3 DESC";
$color = $highlight;
} else {
$color = $headingcolor;
}
print " <TH BGCOLOR='$color' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=3>Bytes</A></TH>n";
$sql = "$copyright
SELECT
OWNER,
TO_CHAR(COUNT(*),'999,999,999,999'),
TO_CHAR(SUM(BYTES),'999,999,999,999')
FROM DBA_SEGMENTS
GROUP BY OWNER
ORDER by $sortfield
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($owner,$count,$bytes) = $cursor->fetchrow_array) {
print "<TR><TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=OBJECTREPORT&arg=$owner>$owner</A></TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$count</TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$bytes</TD></TR>n";
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</TABLE>
EOF
logit("Exit subroutine userSpaceReport");
}
sub fileFragReport {
logit("Enter subroutine fileFragReport");
my ($sql,$link,$text,$sortfield,$file_name,$bytes,$largest,$smallest,$frags);
my ($highlight,$color,$count,$tablespace_name,@needs_coalescing);
my $maxfrags = 1000;
$sortfield = $query->param('sortfield') || "4";
$highlight = "#FFFFC6";
text("Click on a column name to change sort order.");
print << "EOF";
<TABLE BORDER =0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
EOF
if ($sortfield eq "1") {
$color = $highlight;
} else {
$color = $headingcolor;
}
print " <TH BGCOLOR='$color' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=1>File name</A></TH>n";
if ($sortfield eq "2") {
$sortfield = "2 DESC";
$color = $highlight;
} else {
$color = $headingcolor;
}
print " <TH BGCOLOR='$color' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=2>Bytes</A></TH>n";
if ($sortfield eq "3") {
$color = $highlight;
} else {
$color = $headingcolor;
}
print " <TH BGCOLOR='$color' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=3>Tablespace name</A></TH>n";
if ($sortfield eq "4") {
$sortfield = "4 DESC";
$color = $highlight;
} else {
$color = $headingcolor;
}
print " <TH BGCOLOR='$color' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=4>Fragments</A></TH>n";
if ($sortfield eq "5") {
$sortfield = "5 DESC";
$color = $highlight;
} else {
$color = $headingcolor;
}
print " <TH BGCOLOR='$color' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=5>Largest free chunk</A></TH>n";
if ($sortfield eq "6") {
$color = $highlight;
} else {
$color = $headingcolor;
}
print " <TH BGCOLOR='$color' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=6>Smallest free chunk</A></TH>n";
$sql = "$copyright
SELECT
A.FILE_NAME "File name",
TO_CHAR(A.BYTES,'999,999,999,999') "Bytes",
A.TABLESPACE_NAME "Tablespace name",
COUNT(*) "Pieces",
TO_CHAR(NVL(MAX(B.BYTES),'0'),'999,999,999,999') "Largest free chunk",
TO_CHAR(NVL(MIN(B.BYTES),'0'),'999,999,999,999') "Smallest free chunk"
FROM DBA_DATA_FILES A, DBA_FREE_SPACE B
WHERE A.FILE_ID = B.FILE_ID(+)
GROUP BY A.FILE_NAME, A.BYTES, A.TABLESPACE_NAME
ORDER BY $sortfield
";
logit(" SQL = $sql");
$cursor = $dbh->prepare($sql);
logit(" Error: $DBI::errstr") if $DBI::errstr;
$cursor->execute;
while (($file_name,$bytes,$tablespace_name,$frags,$largest,$smallest) = $cursor->fetchrow_array) {
# Push tablespace_name into an array if the tablespace needs coalescing.
if ($frags >= $maxfrags) {
push @needs_coalescing, "'$file_name'";
logit(" File $file_name needs coalescing.");
}
print "<TR><TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=DATAFILE&arg=$file_name>$file_name</A></TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$bytes</TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=TSINFO&arg=$tablespace_name>$tablespace_name</A></TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$frags</TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$largest</TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$smallest</TD></TR>n";
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</TABLE>
EOF
# Print a list of "ALTER TABLESAPCE tablespace name COALESCE statements
if (@needs_coalescing) {
my $file_names = join(",",@needs_coalescing);
logit(" Files to be coalesced: $file_names");
text("Coalesce statements for tablespaces containing datafiles which have at least $maxfrags fragments.");
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>
/*
Database $database
DDL generated by Oracletool v$VERSION
for any tablespace containing at least
one datafile with more than $maxfrags
fragments.
*/
EOF
$sql = "$copyright
SELECT DISTINCT TABLESPACE_NAME
FROM DBA_DATA_FILES
WHERE FILE_NAME IN ($file_names)
";
$cursor = $dbh->prepare($sql) or print("$DBI::errstrn");
$cursor->execute;
while ($tablespace_name = $cursor->fetchrow_array) {
logit(" Writing statement for tablespace $tablespace_name");
print "ALTER TABLESPACE $tablespace_name COALESCE;n"
}
$cursor->finish;
print <<"EOF";
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
EOF
}
logit("Exit subroutine fileFragReport");
}
sub tsSpaceReport {
logit("Enter subroutine tsSpaceReport");
my ($sql,$link,$text,$sortfield,$owner,$tablespace_name,$bytes);
my ($highlight,$color,$count);
$sortfield = $query->param('sortfield') || "4";
$highlight = "#FFFFC6";
text("Click on a column name to change sort order.");
print << "EOF";
<TABLE BORDER =0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
EOF
if ($sortfield eq "1") {
$color = $highlight;
} else {
$color = $headingcolor;
}
print " <TH BGCOLOR='$color' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=1>Owner</A></TH>n";
if ($sortfield eq "2") {
$color = $highlight;
} else {
$color = $headingcolor;
}
print " <TH BGCOLOR='$color' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=2>Tablespace name</A></TH>n";
if ($sortfield eq "3") {
$sortfield = "3 DESC";
$color = $highlight;
} else {
$color = $headingcolor;
}
print " <TH BGCOLOR='$color' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=3>Object count</A></TH>n";
if ($sortfield eq "4") {
$sortfield = "4 DESC";
$color = $highlight;
} else {
$color = $headingcolor;
}
print " <TH BGCOLOR='$color' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'><A HREF=$scriptname?database=$database&object_type=$object_type&arg=$object_name&sortfield=4>Bytes used</A></TH>n";
$sql = "$copyright
SELECT
OWNER,
TABLESPACE_NAME,
TO_CHAR(COUNT(*),'999,999,999,999'),
TO_CHAR(SUM(BYTES),'999,999,999,999')
FROM DBA_SEGMENTS
GROUP BY OWNER, TABLESPACE_NAME
ORDER BY $sortfield
";
logit(" SQL = $sql");
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($owner,$tablespace_name,$count,$bytes) = $cursor->fetchrow_array) {
print "<TR><TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=OBJECTREPORT&arg=$owner>$owner</A></TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=TSINFO&arg=$tablespace_name>$tablespace_name</TD></A>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$count</TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$bytes</TD></TR>n";
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</TABLE>
EOF
logit("Exit subroutine tsSpaceReport");
}
sub objectReport {
logit("Enter subroutine objectReport");
my ($sql1,$sql2,$count,$counter,$cursor1,$cursor2);
my ($tablespace_name,$object_type,@tablespaces,$text,$link);
my ($segment_name,$segment_type,$created,$last_ddl_time,$bytes,$extents);
# Show all objects for a particular user ordered by tablespace.
# This can be helpful for examining which tablespaces are expected
# to exist should you need to import this user's schema into a
# different database.
$schema = $object_name unless ($schema);
text("Object report for schema $schema ordered by tablespace.");
$sql1 = "$copyright
SELECT DISTINCT
TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE OWNER = '$schema'
AND SEGMENT_TYPE NOT IN ('CACHE',
'ROLLBACK',
'TEMPORARY')
ORDER BY TABLESPACE_NAME
";
$count=0;
$cursor1 = $dbh->prepare($sql1);
$cursor1->execute;
while ( $tablespace_name = $cursor1->fetchrow_array ) {
push @tablespaces, $tablespace_name;
$count++;
}
$cursor1->finish;
# Exit if user has no objects anywhere.
if ($count == 0) {
print "<BR>$schema has no objects in this database.<BR>n";
Footer();
exit;
} else {
print "<BR>Objects of type CACHE or TEMPORARY not shown.<BR>n";
print "$schema has objects in $count tablespace(s).<P></CENTER>n";
print "<B>Summary report:</B><P><CENTER>n";
}
# Print a summary report with object types and counts for each tablespace.
print "<TABLE>n";
foreach $tablespace_name (@tablespaces) {
print " <TR VALIGN=TOP>n" if $counter == 0;
print <<"EOF";
<TD>
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 cellpadding=2 cellspacing=1>
<TR>
<TD COLSPAN=2 ALIGN=CENTER BGCOLOR='$headingcolor'>
<FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$font'>$tablespace_name
</TD>
</TR>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Object type</TH>
<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Count</TH>
EOF
$sql1 = "$copyright
SELECT DISTINCT SEGMENT_TYPE
FROM DBA_SEGMENTS
WHERE OWNER = '$schema'
AND TABLESPACE_NAME = '$tablespace_name'
AND SEGMENT_TYPE NOT IN ('CACHE',
'ROLLBACK',
'TEMPORARY')
";
$cursor1=$dbh->prepare($sql1);
$cursor1->execute;
while ($object_type = $cursor1->fetchrow_array) {
print " <TR>n";
print " <TD BGCOLOR='$cellcolor'>n";
print " <FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$object_typen";
print " </TD>n";
$sql2 = "$copyright
SELECT COUNT(*)
FROM DBA_SEGMENTS
WHERE OWNER = '$schema'
AND SEGMENT_TYPE = '$object_type'
AND TABLESPACE_NAME = '$tablespace_name'
";
$cursor2=$dbh->prepare($sql2);
$cursor2->execute;
$count = $cursor2->fetchrow_array;
$cursor2->finish;
print " <TD BGCOLOR='$cellcolor'>n";
print " <FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$countn";
print " </TD>n";
print " </TR>n";
}
$cursor1->finish;
print " </TABLE>n";
$counter++;
print " </TD>n";
print " </TR>n";
print " </TABLE>n";
print " </TR>n" if $counter == 0;
if ( $counter == 6 ) { $counter = 0 };
}
print " </TR>n";
print "</TABLE>n";
print "</CENTER><P><HR WIDTH=100%><P><B>Detailed report:</B><P>n";
foreach $tablespace_name (@tablespaces) {
$sql2 = "$copyright
SELECT
A.SEGMENT_NAME "Object name",
A.SEGMENT_TYPE "Object type",
TO_CHAR(B.CREATED,'Month DD, YYYY - HH24:MI') "Created",
TO_CHAR(B.LAST_DDL_TIME,'Month DD, YYYY - HH24:MI') "Last DDL time",
TO_CHAR(A.BYTES,'999,999,999,999') "Bytes",
TO_CHAR(A.EXTENTS,'999,999,999,999') "Extents"
FROM DBA_SEGMENTS A, DBA_OBJECTS B
WHERE A.TABLESPACE_NAME = '$tablespace_name'
AND A.OWNER = '$schema'
AND B.OWNER = '$schema'
AND A.SEGMENT_NAME = B.OBJECT_NAME
ORDER BY A.SEGMENT_TYPE, A.SEGMENT_NAME
";
$text = "Tablespace $tablespace_name";
$link = "";
DisplayTable($sql2,$text,$link);
}
# Show text based report
print "<P>n";
format STDOUT =
@<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<< @>>>>>>>>>>>>>>>>>>> @>>>>>>>>>>>>>>
$segment_name,$segment_type,$created,$bytes,$extents
.
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
print "Object usage for user $schema database $databasenn";
foreach $tablespace_name (@tablespaces) {
print "Tablespace $tablespace_namen";
print "===========================nn";
print "Object name Object type Date created # Bytes # Extentsn";
print "=========== =========== ============ ======= =========n";
$sql = "$copyright
SELECT
A.SEGMENT_NAME "Object name",
A.SEGMENT_TYPE "Object type",
TO_CHAR(B.CREATED,'Mon DD, YYYY - HH24:MI') "Created",
TO_CHAR(B.LAST_DDL_TIME,'Mon DD, YYYY - HH24:MI') "Last DDL time",
TO_CHAR(A.BYTES,'999,999,999,999') "Bytes",
TO_CHAR(A.EXTENTS,'999,999,999,999') "Extents"
FROM DBA_SEGMENTS A, DBA_OBJECTS B
WHERE A.TABLESPACE_NAME = '$tablespace_name'
AND A.OWNER = '$schema'
AND B.OWNER = '$schema'
AND A.SEGMENT_NAME = B.OBJECT_NAME
ORDER BY A.SEGMENT_TYPE, A.SEGMENT_NAME
";
$cursor = $dbh->prepare($sql) or print "$DBI::errstrn";
$cursor->execute;
while (($segment_name,$segment_type,$created,$last_ddl_time,$bytes,$extents) = $cursor->fetchrow_array) {
$segment_name =~ s/ //g;
$segment_type =~ s/ //g;
$bytes =~ s/ //g;
$extents =~ s/ //g;
# print "$segment_name,$segment_type,$created,$last_ddl_time,$bytes,$extentsn";
write;
}
$cursor->finish;
print "n";
}
print<<"EOF";
</PRE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
EOF
logit("Exit subroutine objectReport");
}
sub showObjects {
logit("Enter subroutine showObjects");
my ($sql,$text,$link,$infotext,$nulltext,$count,$table_name);
my ($cursor);
logit(" Object is a $object_name");
# Object types with spaces are not +'d at this point.
$object_type = $object_name;
# Check for the different types of tables.
# If Oracle8, the table name will not show up in DBA_SEGMENTS if
# the table is partitioned, so we will select from DBA_PART_TABLES.
if ($object_type eq "TABLE") {
if ($oracle8) {
$sql = "$copyright
SELECT
TABLE_NAME "Table name",
DEF_TABLESPACE_NAME "Def. tablespace name",
PARTITIONING_TYPE "Partitioning type",
PARTITION_COUNT "Partition count"
FROM DBA_PART_TABLES
WHERE OWNER = '$schema'
";
$infotext = "No partitioned tables in this schema.";
$text = "Partitioned tables.";
$link = "$scriptname?database=$database&schema=$schema&object_type=$object_type";
DisplayTable($sql,$text,$link,$infotext);
# Check for Index Organized Tables, because they won't show up in DBA_SEGMENTS
$sql = "$copyright
SELECT
TABLE_NAME "Table name"
FROM DBA_TABLES
WHERE OWNER = '$schema'
AND IOT_TYPE = 'IOT'
";
$infotext = "No Index Organized Tables in this schema.";
$text = "Index Organized Tables.";
$link = "$scriptname?database=$database&schema=$schema&object_type=$object_name";
DisplayTable($sql,$text,$link,$infotext);
}
# Now, show the normal tables.
logit(" Start standard tables");
$sql = "$copyright
SELECT
SEGMENT_NAME "Object name",
TABLESPACE_NAME "Tablespace name",
TO_CHAR(BYTES,'999,999,999,999') "Bytes"
FROM DBA_SEGMENTS
WHERE OWNER = '$schema'
AND SEGMENT_TYPE = '$object_type'
ORDER BY SEGMENT_NAME
";
$text = "Standard tables.";
$infotext = "No standard tables in this schema.";
$link = "$scriptname?database=$database&schema=$schema&object_type=$object_name";
DisplayTable($sql,$text,$link,$infotext);
exit;
}
# If object is an index, show the space used
if ($object_type eq "INDEX") {
if ($oracle8) {
# Show partitioned indexes.
$sql = "$copyright
SELECT
INDEX_NAME "Index name",
DEF_TABLESPACE_NAME "Def. tablespace name",
PARTITIONING_TYPE "Partitioning type",
PARTITION_COUNT "Partition count"
FROM DBA_PART_INDEXES
WHERE OWNER = '$schema'
";
$infotext = "No partitioned indexes in this schema.";
$text = "Partitioned indexes.";
$link = "$scriptname?database=$database&schema=$schema&object_type=$object_type";
DisplayTable($sql,$text,$link,$infotext);
# Show bitmapped indexes
$sql = "$copyright
SELECT
INDEX_NAME "Index name",
TABLESPACE_NAME "Tablespace name"
FROM DBA_INDEXES
WHERE OWNER = '$schema'
AND INDEX_TYPE = 'BITMAP'
ORDER BY INDEX_NAME
";
$text = "Bitmapped indexes.";
$infotext = "No bitmapped indexes in this schema.";
$link = "$scriptname?database=$database&schema=$schema&object_type=$object_type";
DisplayTable($sql,$text,$link,$infotext);
# Show IOT indexes
$sql = "$copyright
SELECT
INDEX_NAME "Index name",
TABLESPACE_NAME "Tablespace name"
FROM DBA_INDEXES
WHERE OWNER = '$schema'
AND INDEX_TYPE LIKE '%IOT%'
ORDER BY INDEX_NAME
";
$text = "Index Organized Table indexes.";
$infotext = "No Index Organized Table indexes in this schema.";
$link = "$scriptname?database=$database&schema=$schema&object_type=$object_type";
DisplayTable($sql,$text,$link,$infotext);
# Show standard indexes
$sql = "$copyright
SELECT
INDEX_NAME "Object name",
TABLESPACE_NAME "Tablespace name"
FROM DBA_INDEXES
WHERE OWNER = '$schema'
AND INDEX_TYPE != 'BITMAP'
AND INDEX_TYPE NOT LIKE '%IOT%'
";
$text = "Standard indexes.";
$infotext = "No standard indexes in this schema.";
$link = "$scriptname?database=$database&schema=$schema&object_type=$object_type";
DisplayTable($sql,$text,$link,$infotext);
exit;
} else {
# We are Oracle7
$sql = "$copyright
SELECT
INDEX_NAME "Object name",
TABLESPACE_NAME "Tablespace name"
FROM DBA_INDEXES
WHERE OWNER = '$schema'
";
$text = "Select an index for more info.";
$infotext = "No standard indexes in this schema.";
$link = "$scriptname?database=$database&schema=$schema&object_type=$object_type";
DisplayTable($sql,$text,$link,$infotext);
exit;
}
}
# If object is of type partitioned, then
# show the subobject as well.
if ($object_type eq "TABLE PARTITION") {
$sql = "$copyright
SELECT
TABLE_NAME "Table name",
PARTITION_NAME "Partition 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 TABLE_OWNER = '$schema'
ORDER BY TABLE_NAME
";
$text = "Select a partition for info about the parent table.";
$link = "$scriptname?database=$database&schema=$schema&object_type=TABLE";
DisplayTable($sql,$text,$link,$infotext);
logit(" Link = $link");
exit;
}
if ($object_type eq "INDEX PARTITION") {
$sql = "$copyright
SELECT
INDEX_NAME "Index name",
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_OWNER = '$schema'
ORDER BY INDEX_NAME
";
$text = "Select a partition for info about the parent index.";
$link = "$scriptname?database=$database&schema=$schema&object_type=INDEX";
DisplayTable($sql,$text,$link,$infotext);
logit(" Link = $link");
exit;
}
# Show all for sequences
if ($object_type eq "SEQUENCE") {
$sql = "$copyright
SELECT
SEQUENCE_NAME "Sequence name",
MIN_VALUE "Min value",
MAX_VALUE "Max value",
INCREMENT_BY "Increment by",
CYCLE_FLAG "Cycle flag",
ORDER_FLAG "Order flag",
CACHE_SIZE "Cache size",
LAST_NUMBER "Last number"
FROM DBA_SEQUENCES
WHERE SEQUENCE_OWNER = '$schema'
";
$text = "Sequences owned by $schema..";
DisplayTable($sql,$text);
exit;
}
# Show public synonym info
if ($object_type eq "PUBLIC SYNONYMS") {
$sql = "$copyright
SELECT
SYNONYM_NAME "Synonym name",
TABLE_NAME "Object name",
TABLE_OWNER "Object owner",
DB_LINK "DB link"
FROM DBA_SYNONYMS
WHERE OWNER = 'PUBLIC'
AND TABLE_OWNER = '$schema'
";
$text = "All public synonyms pointing to $schema objects.";
$link = "";
$infotext = "No public synonyms are pointing to $schema objects.";
DisplayTable($sql,$text,$link,$infotext);
}
# Default sql
$sql = "$copyright
SELECT
OBJECT_NAME "Object name"
FROM DBA_OBJECTS
WHERE OWNER = '$schema'
AND OBJECT_TYPE = '$object_type'
";
$link = "$scriptname?database=$database&schema=$schema&object_type=$object_name";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showObjects");
}
sub showSynonym {
logit("Enter subroutine showSynonym");
my ($sql,$text,$link);
# General synonym info
$sql = "$copyright
SELECT
SYNONYM_NAME "Synonym name",
TABLE_NAME "Object name",
TABLE_OWNER "Table owner",
DB_LINK "Database link"
FROM DBA_SYNONYMS
WHERE SYNONYM_NAME = '$object_name'
AND OWNER = '$schema'
";
$text = "";
$link = "";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showSynonym");
}
sub showTablespaces {
logit("Enter subroutine showTablespaces");
my ($sql,$text,$link);
# Tablespace graph button
print <<"EOF";
<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="TSGRAPH">
<INPUT TYPE="SUBMIT" NAME="tsgraph" VALUE="Tablespace allocation graph">
</FORM>
</TD>
</TR>
</TABLE>
EOF
$sql = "$copyright
SELECT
TO_CHAR(SUM(BYTES),'999,999,999,999,999') "Total allocated space"
FROM DBA_DATA_FILES
";
$text = "";
$link = "";
DisplayTable($sql,$text,$link);
# General tablespace information
$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",
PCT_INCREASE "% increase",
STATUS "Status",
CONTENTS "Contents"
FROM DBA_TABLESPACES
ORDER BY TABLESPACE_NAME
";
$sql = "$copyright
SELECT
DTS.TABLESPACE_NAME "Tablespace name",
TO_CHAR(DTS.INITIAL_EXTENT,'999,999,999,999') "Initial extent",
TO_CHAR(DTS.NEXT_EXTENT,'999,999,999,999') "Next extent",
TO_CHAR(DTS.MAX_EXTENTS,'999,999,999,999') "Max extents",
TO_CHAR(TSD.DFLMINLEN*$db_block_size,'999,999,999,999') "Minimum extent",
DTS.PCT_INCREASE "% increase",
DTS.STATUS "Status",
DTS.CONTENTS "Contents",
DTS.LOGGING "Logging?"
FROM DBA_TABLESPACES DTS, SYS.TS$ TSD
WHERE DTS.TABLESPACE_NAME = TSD.NAME
ORDER BY TABLESPACE_NAME
" if ($oracle8);
$sql = "$copyright
SELECT
DTS.TABLESPACE_NAME "Tablespace name",
TO_CHAR(DTS.INITIAL_EXTENT,'999,999,999,999') "Initial extent",
TO_CHAR(DTS.NEXT_EXTENT,'999,999,999,999') "Next extent",
TO_CHAR(DTS.MAX_EXTENTS,'999,999,999,999') "Max extents",
TO_CHAR(TSD.DFLMINLEN*$db_block_size,'999,999,999,999') "Minimum extent",
DTS.PCT_INCREASE "% increase",
DTS.STATUS "Status",
DTS.CONTENTS "Contents",
DTS.LOGGING "Logging?",
DTS.EXTENT_MANAGEMENT "Ext. Mgmt",
DTS.ALLOCATION_TYPE "Alloc type",
DTS.PLUGGED_IN "Plugged?"
FROM DBA_TABLESPACES DTS, SYS.TS$ TSD
WHERE DTS.TABLESPACE_NAME = TSD.NAME
ORDER BY TABLESPACE_NAME
" if ($oracle8i);
$text = "Tablespace information: Database $database";
$link = "$scriptname?database=$database&object_type=TSINFO";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showTablespaces");
}
sub showTSinfo {
logit("Enter subroutine showTSinfo");
my ($sql,$cursor,$foo,$text,$link,$infotext);
refreshButton();
# Display a button for a screen with a datafile fragmentation map.
print <<"EOF";
<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="object_type" VALUE="FRAGMAP">
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="$object_name">
<INPUT TYPE="HIDDEN" NAME="whereclause" VALUE="tablespace">
<INPUT TYPE="SUBMIT" NAME="fragmap" VALUE="Fragmentation map">
</FORM>
</TD>
</TR>
</TABLE>
EOF
# Tablespace information
$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",
PCT_INCREASE "% increase",
STATUS "Status",
CONTENTS "Contents"
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = '$object_name'
";
$sql = "$copyright
SELECT
DTS.TABLESPACE_NAME "Tablespace name",
TO_CHAR(DTS.INITIAL_EXTENT,'999,999,999,999') "Initial extent",
TO_CHAR(DTS.NEXT_EXTENT,'999,999,999,999') "Next extent",
TO_CHAR(DTS.MAX_EXTENTS,'999,999,999,999') "Max extents",
TO_CHAR(TSD.DFLMINLEN*$db_block_size,'999,999,999,999') "Minimum extent",
DTS.PCT_INCREASE "% increase",
DTS.STATUS "Status",
DTS.CONTENTS "Contents",
DTS.LOGGING "Logging?"
FROM DBA_TABLESPACES DTS, SYS.TS$ TSD
WHERE DTS.TABLESPACE_NAME = '$object_name'
AND TSD.NAME = '$object_name'
" if ($oracle8);
$sql = "$copyright
SELECT
DTS.TABLESPACE_NAME "Tablespace name",
TO_CHAR(DTS.INITIAL_EXTENT,'999,999,999,999') "Initial extent",
TO_CHAR(DTS.NEXT_EXTENT,'999,999,999,999') "Next extent",
TO_CHAR(DTS.MAX_EXTENTS,'999,999,999,999') "Max extents",
TO_CHAR(TSD.DFLMINLEN*$db_block_size,'999,999,999,999') "Minimum extent",
DTS.PCT_INCREASE "% increase",
DTS.STATUS "Status",
DTS.CONTENTS "Contents",
DTS.LOGGING "Logging?",
DTS.EXTENT_MANAGEMENT "Ext. Mgmt",
DTS.ALLOCATION_TYPE "Alloc type",
DTS.PLUGGED_IN "Plugged?"
FROM DBA_TABLESPACES DTS, SYS.TS$ TSD
WHERE DTS.TABLESPACE_NAME = '$object_name'
AND TSD.NAME = '$object_name'
" if ($oracle8i);
$text = "General information: Tablespace $object_name";
$link = "$scriptname?database=$database&object_type=TSDDL";
DisplayTable($sql,$text,$link);
# Space allocation
$sql = "$copyright
SELECT
DF.TABLESPACE_NAME "Tablespace name",
TO_CHAR(DF.BYTES,'999,999,999,999') "Bytes allocated",
NVL(TO_CHAR(DF.BYTES-SUM(FS.BYTES),'999,999,999,999'),
TO_CHAR(DF.BYTES,'999,999,999,999')) "Bytes used",
NVL(TO_CHAR(SUM(FS.BYTES),'999,999,999,999'),0) "Bytes free",
NVL(ROUND((DF.BYTES-SUM(FS.BYTES))*100/DF.BYTES),100)||'%' "Percent used",
NVL(ROUND(SUM(FS.BYTES)*100/DF.BYTES),0)||'%' "Percent free"
FROM DBA_FREE_SPACE FS,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY
TABLESPACE_NAME ) DF
WHERE FS.TABLESPACE_NAME (+) = DF.TABLESPACE_NAME
AND DF.TABLESPACE_NAME = '$object_name'
GROUP BY DF.TABLESPACE_NAME, DF.BYTES
ORDER BY "Percent free"
";
$text = "Space allocation";
$link = "";
DisplayTable($sql,$text,$link);
# Fragmentation / general info
if ($oracle8) {
$sql = "$copyright
SELECT
A.FILE_NAME "File name",
B.FILE_ID "File #",
TO_CHAR(A.BYTES,'999,999,999,999') "Bytes",
TO_CHAR(MAX(B.BYTES),'999,999,999,999') "Largest free chunk",
TO_CHAR(MIN(B.BYTES),'999,999,999,999') "Smallest free chunk",
COUNT(*) "Pieces",
DECODE(A.AUTOEXTENSIBLE,
'YES','Yes',
'NO','No') "Xtend?",
TO_CHAR(A.MAXBYTES,'999,999,999,999') "Max bytes",
TO_CHAR(A.INCREMENT_BY*$db_block_size,'999,999,999,999') "Increment"
FROM DBA_DATA_FILES A, DBA_FREE_SPACE B
WHERE A.FILE_ID = B.FILE_ID
AND B.TABLESPACE_NAME = '$object_name'
GROUP BY A.FILE_NAME, B.FILE_ID, A.BYTES,A.AUTOEXTENSIBLE,A.MAXBYTES,A.INCREMENT_BY
";
} else {
$sql = "$copyright
SELECT
A.FILE_NAME "File name",
B.FILE_ID "File #",
TO_CHAR(A.BYTES,'999,999,999,999') "Bytes",
TO_CHAR(MAX(B.BYTES),'999,999,999,999') "Largest free chunk",
TO_CHAR(MIN(B.BYTES),'999,999,999,999') "Smallest free chunk",
COUNT(*) "Pieces"
FROM DBA_DATA_FILES A, DBA_FREE_SPACE B
WHERE A.FILE_ID = B.FILE_ID
AND B.TABLESPACE_NAME = '$object_name'
GROUP BY A.FILE_NAME, B.FILE_ID, A.BYTES
";
}
$text = "Tablespace (datafile) fragmentation";
$link = "$scriptname?database=$database&object_type=DATAFILE";
DisplayTable($sql,$text,$link);
# Tablespace graph button
print <<"EOF";
<BR>
<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0>
<TR>
<TD ALIGN=CENTER>
<FORM METHOD="GET" ACTION="$scriptname">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="TSFILEGRAPH">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$object_name">
<INPUT TYPE="SUBMIT" NAME="tsfilegraph" VALUE="Datafile information graphs">
</FORM>
</TD>
</TR>
</TABLE
EOF
$sql = "$copyright
SELECT DISTINCT
TO_CHAR(BYTES,'999,999,999,999') "Extent size",
TO_CHAR(COUNT(*),'999,999,999,999') "# extents"
FROM DBA_EXTENTS
WHERE TABLESPACE_NAME = '$object_name'
GROUP BY BYTES
ORDER BY 1 DESC
";
$text = "Extent sizes / counts contained in this tablespace.";
$foo = DisplayTable($sql,$text);
# Added this to check for DBA_FREE_SPACE
# returning a null value if there is no
# free space
$sql = "$copyright
SELECT MAX(BYTES)
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = '$object_name'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$foo = $cursor->fetchrow_array;
if ($foo) {
# Objects in the tablespace with next extent sizes larger than the largest
# free extent in the tablespace. Allocating a next extent for these objects
# will fail.
$sql = "$copyright
SELECT
SEGMENT_NAME "Object name",
OWNER "Owner",
TO_CHAR(NEXT_EXTENT,'999,999,999,999') "Next extent"
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = '$object_name'
AND NEXT_EXTENT > (SELECT MAX(BYTES) FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = '$object_name')
";
$text = "Objects that will fail to allocate a next extent";
$link = "";
$infotext = "No objects in $object_name will fail to allocate a next extent.";
DisplayTable($sql,$text,$link,$infotext);
} else {
message("Warning: No objects can allocate an extent. Add a datafile.");
}
undef $foo;
# Objects in the tablespace that are approaching their max_extents limit.
$sql = "$copyright
SELECT
SEGMENT_NAME "Object name",
OWNER "Owner",
SEGMENT_TYPE "Object type",
TO_CHAR(EXTENTS,'999,999,999,999') "Extents",
TO_CHAR(MAX_EXTENTS,'999,999,999,999') "Max extents"
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = '$object_name'
AND EXTENTS > (MAX_EXTENTS-25)
AND SEGMENT_TYPE != 'CACHE'
";
$text = "Objects that are approaching their max_extents limit";
$link = "";
$infotext = "No objects in $object_name have extents > ( max_extents - 25 )";
DisplayTable($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
A.SEGMENT_NAME "Object name",
A.SEGMENT_TYPE "Object type",
A.OWNER "Owner",
TO_CHAR(B.CREATED,'Month DD, YYYY - HH24:MI') "Created",
TO_CHAR(B.LAST_DDL_TIME,'Month DD, YYYY - HH24:MI') "Last DDL time",
TO_CHAR(A.BYTES,'999,999,999,999') "Bytes",
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.EXTENTS,'999,999,999,999') "Extents"
FROM DBA_SEGMENTS A, DBA_OBJECTS B
WHERE A.TABLESPACE_NAME = '$object_name'
AND A.SEGMENT_NAME = B.OBJECT_NAME
AND A.SEGMENT_TYPE = B.OBJECT_TYPE
AND A.OWNER = B.OWNER
ORDER BY A.OWNER, A.SEGMENT_TYPE, A.SEGMENT_NAME
";
$text = "Object list";
$link = "";
$infotext = "Tablespace $object_name has no objects.";
ObjectTable($sql,$text,$infotext);
logit("Exit subroutine showTSinfo");
}
sub showFile {
logit("Enter subroutine showFile");
my ($sql,$text,$link,$infotext);
# Display a button for a screen with a datafile fragmentation map.
print <<"EOF";
<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="FRAGMAP">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="$object_name">
<INPUT TYPE="HIDDEN" NAME="whereclause" VALUE="datafile">
<INPUT TYPE="SUBMIT" NAME="fragmap" VALUE="Fragmentation map">
</FORM>
</TD>
</TR>
</TABLE>
EOF
# Specific datafile information
$sql = "$copyright
SELECT
B.FILE_NAME "File name",
TO_CHAR(A.CREATE_BYTES,'999,999,999,999') "Creation size",
TO_CHAR(A.BYTES,'999,999,999,999') "Current size",
TO_CHAR(B.BLOCKS,'999,999,999,999') "Blocks",
B.TABLESPACE_NAME "Tablespace_name",
B.STATUS "Status"
FROM V$DATAFILE A, DBA_DATA_FILES B
WHERE B.FILE_NAME = '$object_name'
AND A.FILE# = B.FILE_ID
";
$text = "General information";
$link = "";
DisplayTable($sql,$text,$link);
$sql = "$copyright
SELECT
TO_CHAR(A.PHYRDS,'999,999,999,999') "Physical reads#",
TO_CHAR(A.PHYWRTS,'999,999,999,999') "Physical writes#",
TO_CHAR(A.PHYBLKRD*$db_block_size,'999,999,999,999') "Bytes read",
TO_CHAR(A.PHYBLKWRT*$db_block_size,'999,999,999,999') "Bytes written"
FROM V$FILESTAT A, DBA_DATA_FILES B
WHERE B.FILE_NAME = '$object_name'
AND A.FILE# = B.FILE_ID
";
$text = "I/O stats since database startup";
$link = "";
DisplayTable($sql,$text,$link);
# $text = "Historical I/O stats.";
# DisplayGraph("dbfile",$object_name,$text);
$sql = "$copyright
SELECT
TO_CHAR(NEXT_EXTENT,'999,999,999,999') "Next extent"
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME =
(SELECT
TABLESPACE_NAME FROM DBA_DATA_FILES
WHERE FILE_NAME = '$object_name')
";
$text = "Next extent size of tablespace";
$link = "";
DisplayTable($sql,$text,$link);
$sql = "$copyright
SELECT
TO_CHAR(BYTES,'999,999,999,999') "Chunk size (bytes)",
TO_CHAR(BLOCKS,'999,999,999,999') "Blocks"
FROM DBA_FREE_SPACE
WHERE FILE_ID =
(SELECT
FILE_ID FROM DBA_DATA_FILES
WHERE FILE_NAME = '$object_name')
AND ROWNUM <= 10
ORDER BY BYTES DESC
";
$text = "Free space (Top ten)";
$link = "";
$infotext = "There are no free chunks of data in this datafile.";
DisplayTable($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT DISTINCT
SEGMENT_NAME "Segment name",
SEGMENT_TYPE "Segment type",
OWNER "Owner",
TABLESPACE_NAME "Tablespace name"
FROM DBA_EXTENTS
WHERE FILE_ID = (
SELECT FILE_ID FROM DBA_DATA_FILES
WHERE FILE_NAME = '$object_name')
";
$text = "Objects which have extents in this datafile.";
$infotext = "There are no objects with extents in this datafile.";
ObjectTable($sql,$text,$infotext);
logit("Exit subroutine showFile");
}
sub runSQL {
logit("Enter subroutine runSQL");
my ($sql,$cursor,$rows,$text,$link,$infotext,@statements,$statement,$error);
$_ = shift;
$_ = $object_name unless $_;
# Get rid of trailing whitespace
s/s+$//;
@statements = split /;/;
foreach $_ (@statements) {
# Get rid of leading whitespace and newlines
s/^s+//;
# If the command is "DESCRIBE"
if (/^desc/i) {
@_ = split;
Describe("$_[1]");
next;
}
loginfo(" SQL:n$_");
print <<"EOF";
<TABLE BGCOLOR='$bordercolor' WIDTH="100" CELLPADDING="1" CELLSPACING="0" BORDER="0" ALIGN="CENTER">
<TR>
<TD VALIGN="TOP">
<TABLE BGCOLOR="$headingcolor" WIDTH="100%" CELLPADDING="2" CELLSPACING="1" BORDER="0">
<TR ALIGN="LEFT">
<TD><B><FONT SIZE="2">
<PRE>
$_</PRE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
EOF
# Check to see if it is a select statement.
if (/^SELECT/i) {
$text = "";
$link="";
$infotext = "No rows to display.";
$error = DisplayTable($_,$text,$link,$infotext);
logit(" DisplayTable returned $error") if $error;
unless ($error =~ /^d+$/) {
message("Select statement failed: $errorn");
}
print "<BR>n";
# If not a select, then "do" the statement
} else {
$cursor=$dbh->do($_);
if ( ! $cursor) {
$_ = $DBI::errstr;
# Get rid of unneccessary DBD::Oracle message
s/DBD: //;
s/(DBD.*)//;
message("SQL error:<BR>$_n");
} else {
message("SQL statement executed successfully.n");
$rows = $dbh->rows;
if ($rows > 0) {
message("$rows rows affected.n");
}
Footer();
}
}
print "<HR WIDTH="10%">n";
}
logit("Exit subroutine runSQL");
}
sub runExplainPlan {
logit("Enter subroutine runExplainPlan");
my ($sql,$cursor,$count,$text,$link,$infotext,@row,$title,$heading,$explainsql,$rc);
my ($utlxplan733,$utlxplan8,$operation,$options,$objname,$cost,$table_color,$foo);
my ($objtype,$objowner,$card,$bytes,$other,$maxlength,$line,$numspaces,$i,$linelength);
my ($optimizer);
$utlxplan733 = "
create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(30),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns numeric,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
other long)
";
$utlxplan8 = "
create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(30),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long)
";
# Connect to database using ID of the owner of the SQL passed. This will
# actually mean that the Oracletool that created the menu is (was) connected
# as the user with "SELECT ANY TABLE" privileges while the connection in
# the body does not need that level of access. Therefore, any clicks on the
# menu buttons will reconnect as the main user, not the SQL owner. Yeah, whatever.
# Have I mentioned that CGI bites?
my $data_source = "dbi:Oracle:$database";
my $dbh = DBI->connect($data_source,$explainschema,$password,{PrintError=>0});
if (! $dbh) {
$object_name =~ s/"/"/g;
$object_name =~ s/>/>/g;
$object_name =~ s/</</g;
if ($explainschema) {
ErrorPage("Could not connect as $explainschema.<BR>$DBI::errstr");
exit;
} else {
ErrorPage("Please enter a username and password.");
exit;
}
}
# OK, we're connected, let's display the header.
Header($title,$heading,$font,$fontsize,$fontcolor,$bgcolor);
# Now, check for a plan table under this schema.
$sql = "$copyright
SELECT COUNT(*)
FROM USER_TABLES
WHERE TABLE_NAME = 'PLAN_TABLE'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$count = $cursor->fetchrow_array;
$cursor->finish;
if (! $count) {
# No plan table exists. Let's create one.
if (! $oracle8) {
$sql = $utlxplan733;
} else {
$sql = $utlxplan8;
}
$cursor = $dbh->do($sql);
if (! $cursor) {
message("Could not create PLAN_TABLE.<BR>$DBI::errstrn");
$dbh->disconnect;
exit;
}
message("$explainschema did not have a PLAN_TABLE required for explain plan. Oracletool has created this table for you.");
}
# Got a connection, and a PLAN_TABLE exists.
# Set the statement_id to a unique identifier.
$statement_id = "Oracletool.$$";
$explainsql = $object_name;
# Get rid of ;, if one exists
$explainsql =~ s/;$//;
$explainsql = "EXPLAIN PLAN SET STATEMENT_ID = '$statement_id' INTO PLAN_TABLE FOR $explainsql";
$cursor=$dbh->do($explainsql);
if ( ! $cursor) {
message("Could not execute explain plan.<BR>$DBI::errstrn");
} else {
text("Explain plan executed successfully.n");
}
$sql = "$copyright
SELECT
DISTINCT OPTIMIZER
FROM PLAN_TABLE
WHERE STATEMENT_ID = '$statement_id'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$optimizer = $cursor->fetchrow_array;
$cursor->finish;
text("Optimizer mode is $optimizer.");
$sql = "$copyright
SELECT
LPAD(' ',2*LEVEL-1)||OPERATION ,
OPTIONS,
OBJECT_NAME,
OBJECT_TYPE,
OBJECT_OWNER,
COST,
CARDINALITY,
BYTES,
OTHER_TAG
FROM PLAN_TABLE
START WITH ID=0
AND STATEMENT_ID = '$statement_id'
CONNECT BY PRIOR ID = PARENT_ID
AND STATEMENT_ID = '$statement_id'
ORDER BY ID
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$maxlength = 0;
while (($operation,$options,$objname,$objtype,$objowner,$cost,$card,$bytes,$other) = $cursor->fetchrow_array) {
$line = "$operation $options $objname";
if ($cost) {
$line .= "Cost: $cost ";
}
if (length($line) > $maxlength) {
$maxlength = length($line);
logit(" Explain plan max line length is $maxlength");
}
}
$cursor->finish;
print "<TABLE BORDER=0 BGCOLOR=$table_color>n";
$cursor=$dbh->prepare($sql);
$cursor->execute;
while (($operation,$options,$objname,$objtype,$objowner,$cost,$card,$bytes,$other) = $cursor->fetchrow_array) {
logit(" Explain: $operation,$options,$objname,$objtype,$objowner,$cost,$card,$bytes,$other");
if ($foo) {
$table_color = "#C6EFF7";
$foo--;
} else {
$table_color = "#DEBDDE";
$foo++
}
$linelength = length("$operation $options $other $objname");
print "<TR BGCOLOR=$table_color><TD><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><PRE><B>$operation $options $other ";
if ($objowner) {
print "$objowner.$objname";
} else {
print "$objname";
}
if ($cost) {
$numspaces = $maxlength-$linelength+5;
for($i = 0; $i <= $numspaces; $i++) {
print " ";
}
print "Cardinality: $card " if $card;
print "Cost: $cost " if $cost;
}
print "</TD></TR>n";
$cost = 0;
}
$cursor->finish;
print "</TABLE>n";
$sql = "$copyright
DELETE FROM PLAN_TABLE
WHERE STATEMENT_ID = '$statement_id'
";
$rc = $dbh->do($sql);
unless ($rc) {
$dbh->disconnect;
ErrorPage("Could not delete records from PLAN_TABLE");
}
$dbh->disconnect;
Footer();
logit("Exit subroutine runExplainPlan");
exit;
}
sub enterWorksheet {
logit("Enter subroutine enterWorksheet");
my ($sql);
message("Connected to $database as $schema.");
text("Enter or paste the SQL you wish to execute.<BR>Terminate statements with a <B>';'</B> if entering multiple statements.");
print <<"EOF";
<FORM>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE=HIDDEN NAME=object_type VALUE=RUNSQL>
<INPUT TYPE=HIDDEN NAME=database VALUE=$database>
<INPUT TYPE=HIDDEN NAME=schema VALUE=$schema>
<TEXTAREA NAME=arg ROWS=$textarea_h COLS=$textarea_w></TEXTAREA>
<P>
</B>
<INPUT TYPE=SUBMIT VALUE="Execute">
</FORM>
EOF
logit("Exit subroutine enterWorksheet");
}
sub sqlAreaList {
logit("Enter subroutine sqlAreaList");
my ($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
DU.USERNAME "Username",
COUNT(SQL_TEXT) "# Entries"
FROM DBA_USERS DU, V$SQL VSA
WHERE DU.USER_ID = VSA.PARSING_SCHEMA_ID
GROUP BY USERNAME,PARSING_SCHEMA_ID
ORDER BY 2 DESC
";
$text = "The following users have SQL in the shared SQL area. Choose a user to display the parsed SQL.";
$link = "$scriptname?database=$database&object_type=SQLAREALISTBYUSER";
$infotext = "There are no entries in the shared SQL area";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine sqlAreaList");
}
sub sqlAreaListByUser {
logit("Enter subroutine sqlAreaListByUser");
my ($sql,$cursor,$text,$link,$infotext);
$sql = "$copyright
SELECT
TO_CHAR(EXECUTIONS,'999,999,999,999') "Executions",
TO_CHAR(ROWS_PROCESSED,'999,999,999,999') "Rows processed",
TO_CHAR(DISK_READS,'999,999,999,999') "Disk reads",
TO_CHAR(SORTS,'999,999,999,999') "Sorts",
OPTIMIZER_MODE "Optimizer mode",
SQL_TEXT "SQL text"
FROM V$SQL
WHERE PARSING_SCHEMA_ID =
(SELECT USER_ID FROM DBA_USERS
WHERE USERNAME = '$object_name')
ORDER BY 1 DESC, 2 DESC, 3 DESC, 4 DESC
";
logit($sql);
$text = " SQL for user $object_name in the shared SQL area.";
$link = "";
$infotext = "There are no entries in the shared SQL area for user $object_name";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine sqlAreaListByUser");
}
sub enterExplainPlan {
logit("Enter subroutine enterExplainPlan");
my ($sql,$cursor,$count,$text,$link,$infotext);
my ($owner,$table_name,$title,$heading);
Header($title,$heading,$font,$fontsize,$fontcolor,$bgcolor);
if ($explainschema) {
print <<"EOF";
SQL belongs to user $explainschema<BR>
Edit SQL and proceed
<FORM METHOD="POST" ACTION="$scriptname">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE=HIDDEN NAME=object_type VALUE=RUNEXPLAINPLAN>
<INPUT TYPE=HIDDEN NAME=database VALUE=$database>
<INPUT TYPE=HIDDEN NAME=explainschema VALUE=$explainschema>
<TEXTAREA NAME=arg ROWS=$textarea_h COLS=$textarea_w WRAP=SOFT>$object_name</TEXTAREA>
<P>Enter password for user $explainschema<P>
<INPUT TYPE=PASSWORD NAME=password SIZE=20>
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Run explain plan">
</FORM>
EOF
} else {
print <<"EOF";
Enter SQL below
<FORM METHOD="POST" ACTION="$scriptname">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE=HIDDEN NAME=object_type VALUE=RUNEXPLAINPLAN>
<INPUT TYPE=HIDDEN NAME=database VALUE=$database>
<FONT FACE="$font" SIZE="$fontsize">
<TEXTAREA NAME=arg ROWS=$textarea_h COLS=$textarea_w WRAP=SOFT>$object_name</TEXTAREA>
<TABLE BORDER=0>
<TR>
<TD ALIGN=CENTER>
<FONT FACE="$font" SIZE="$fontsize" COLOR="$fontcolor">
Enter user to run SQL<BR>
<INPUT TYPE=TEXT NAME=explainschema SIZE=20 MAXLENGTH=40>
</TD>
<TD ALIGN=CENTER>
<FONT FACE="$font" SIZE="$fontsize" COLOR="$fontcolor">
Enter password for user<BR>
<INPUT TYPE=PASSWORD NAME=password SIZE=20>
</TD>
</TR>
</TABLE>
<BR>
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Run explain plan">
</FORM>
EOF
}
logit("Exit subroutine enterExplainPlan");
exit;
}
sub showDBfiles {
logit("Enter subroutine showDBfiles");
my ($sql,$text,$link,$infotext);
print <<"EOF";
<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0>
<TR>
<TD ALIGMN=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="FILEGRAPH">
<INPUT TYPE="SUBMIT" NAME="filegraph" VALUE="Datafile graph by file I/O">
</FORM>
</TD>
</TR>
</TABLE>
EOF
# Show any files that need media recovery.
$sql = "$copyright
SELECT
A.FILE_NAME "File name",
A.TABLESPACE_NAME "Tablespace name",
A.FILE_ID "File#",
A.STATUS "Status",
B.ERROR "Error",
B.CHANGE# "Start SCN",
TO_CHAR(B.TIME,'Month DD, YYYY - HH24:MI:SS') "Recover from.."
FROM DBA_DATA_FILES A,
V$RECOVER_FILE B
WHERE A.FILE_ID = B.FILE#
";
$text = "Datafiles need recovery!!";
$link = "$scriptname?database=$database&object_type=DATAFILE";
$infotext = "No datafiles are needing media recovery :-)";
DisplayTable($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
TO_CHAR(SUM(BYTES),'999,999,999,999,999') "Total allocated space"
FROM DBA_DATA_FILES
";
$text = "";
$link = "";
DisplayTable($sql,$text,$link);
# General datafile information, all datafiles
if ($oracle8) {
$sql = "$copyright
SELECT
A.FILE_NAME "File name",
TO_CHAR(A.BYTES,'999,999,999,999') "Bytes",
A.TABLESPACE_NAME "Tablespace_name",
TO_CHAR(B.PHYBLKRD,'999,999,999,999') "Physical block reads",
TO_CHAR(B.PHYBLKWRT,'999,999,999,999') "Physical block writes",
A.STATUS "Status",
DECODE(A.AUTOEXTENSIBLE,
'YES','Yes',
'NO','No') "Xtend?",
TO_CHAR(A.MAXBYTES,'999,999,999,999') "Max bytes",
TO_CHAR(A.INCREMENT_BY*$db_block_size,'999,999,999,999') "Increment"
FROM DBA_DATA_FILES A, V$FILESTAT B
WHERE A.FILE_ID = B.FILE#
ORDER BY A.FILE_NAME, A.TABLESPACE_NAME
";
} else {
$sql = "$copyright
SELECT
A.FILE_NAME "File name",
TO_CHAR(A.BYTES,'999,999,999,999') "Bytes",
A.TABLESPACE_NAME "Tablespace_name",
TO_CHAR(B.PHYBLKRD,'999,999,999,999') "Physical block reads",
TO_CHAR(B.PHYBLKWRT,'999,999,999,999') "Physical block writes",
A.STATUS "Status"
FROM DBA_DATA_FILES A, V$FILESTAT B
WHERE A.FILE_ID = B.FILE#
ORDER BY A.FILE_NAME, A.TABLESPACE_NAME
";
}
$text = "Datafile information: Database $database";
$link = "$scriptname?database=$database&object_type=DATAFILE";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showDBfiles");
}
sub EnterPasswd {
logit("Enter subroutine EnterPasswd");
# Usage: EnterPasswd($database);
if ($object_type ne "MENU") {
my $database = shift;
my ($title,$heading);
$title = "Add database $database";
$heading = "Please enter a username and password for database $database.<BR>This user needs to have CREATE SESSION and SELECT ANY TABLE privileges.";
Header($title,$heading,$font,$fontsize,$fontcolor,$bgcolor);
print <<"EOF";
</CENTER>
<P>
<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="CENTER">
<TD ALIGN=LEFT>
<FORM METHOD="POST" ACTION="$scriptname">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<BR><STRONG>   Enter the username</STRONG>
<P>
<INPUT TYPE="TEXT" SIZE="20" NAME="username" MAXLENGTH="20">
<P>
<STRONG>   Enter the password</STRONG>
<P>
<INPUT TYPE="PASSWORD" SIZE="20" NAME="password" MAXLENGTH="20">
<P>
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="ADDPASSWORD">
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
   <INPUT TYPE="SUBMIT" VALUE="Submit">
</FORM>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
EOF
logit("Exit subroutine EnterPasswd");
exit;
} else {
Header($title,$heading,$font,$fontsize,$fontcolor,$bgcolor);
}
logit("Exit subroutine EnterPasswd");
}
sub GetPasswd {
logit("Enter subroutine GetPasswd");
# Usage: $info = GetPasswd($database);
my $database = shift;
my ($sessionid,$username,$password,$usercookie,$passcookie,$info,$message,$duration,$url,$path);
my ($sessioncookie,$bgline);
# if ($encryption_string) {
# logit(" Encryption string = $encryption_string");
# } else {
# logit(" Encryption string is not set");
# }
if ( defined $expire ) {
logit(" Expiring password cookie");
$path = dirname($scriptname);
$sessionid = "undefined";
$sessioncookie = cookie(-name=>"$database.sessionid",-value=>"$sessionid",-expires=>"-1y",-path=>"$path");
print header(-cookie=>$sessioncookie);
$message = "Password cookie for database $database has been expired.";
$duration = "1";
$url = "$scriptname";
$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>Notice!</TITLE>
<META HTTP-EQUIV="Refresh" Content="$duration;URL=$url">
</HEAD>
$bgline
<FONT FACE="$font" SIZE="$fontsize" COLOR="$fontcolor">
<CENTER>
$message
</CENTER
</BODY
</HTML>
EOF
logit("Exit subroutine GetPasswd");
exit;
}
$sessionid = cookie("$database.sessionid");
($username,$password) = decodeSessionid($sessionid);
$info = "$username $password";
logit("Exit subroutine GetPasswd");
return($info);
}
sub encryptionEnabled {
logit("Enter sub encryptionEnabled");
my $digest_found = (eval "require MD5");
my $crypt_found = (eval "require Crypt::CBC");
my $idea_found = (eval "require Crypt::IDEA");
my $mime_found = (eval "require MIME::Base64");
# We need Digest::MD5 for any type of encryption, so check for
# this first.
if ($digest_found) {
logit(" Digest::MD5 is installed. Good");
# Check for both Crypt::CBC and Crypt::IDEA for encryption level 2.
if ($crypt_found && $idea_found) {
logit(" Crypt::CBC is installed. Good.");
logit(" Crypt::IDEA is installed. Good.");
$encryption_enabled = 2;
} else {
logit(" Crypt::CBC is not installed!");
logit(" -- or -- ");
logit(" Crypt::IDEA is not installed!");
logit(" Checking for MIME::Base64 instead.");
if ($mime_found) {
logit(" MIME::Base64 is installed. Good.");
$encryption_enabled = 1;
}
}
} else {
logit(" Digest::MD5 is not installed. Encryption disabled!");
}
logit(" Value for encryption_enabled is $encryption_enabled");
if ($encryption_enabled) {
if ($encryption_string eq "changeme") {
logit(" Encryption key is set to the default! Change it!");
} else {
logit(" Encryption key is non-default. Good.");
}
}
logit("Exit sub encryptionEnabled");
}
sub buildSessionid {
logit("Enter sub buildSessionid");
my ($username,$password) = @_;
my $sessionid;
# $encryption enabled will be 0 if no encryption modules are installed.
# $encryption enabled will be 1 if Digest::MD5 is installed,
# but Crypt::CBC _and_ CRYPT::IDEA are not.
# $encryption enabled will be 3 if all three modules are installed.
if ($encryption_enabled) {
logit(" Encryption enabled, attempting to encrypt username / password.");
if ($encryption_enabled == 1) {
logit(" Digest::MD5 encryption only.");
$sessionid = encodeLevel1($username,$password);
}
if ($encryption_enabled == 2) {
logit(" Full IDEA encryption.");
$sessionid = encodeLevel2($username,$password);
}
if ($sessionid) {
logit(" Encrypt was successful.");
} else {
logit(" Encrypt was not successful!");
}
} else {
logit(" Encryption not enabled, attempting to build unencrypted sessionid.");
($sessionid) = "$username~$password";
if ($sessionid) {
logit(" Build was successful.");
} else {
logit(" Build was not successful!");
}
}
logit("Exit sub buildSessionid");
return($sessionid);
}
sub encodeLevel1 {
logit("Enter sub encodeLevel1");
my ($username,$password) = @_;
my ($sessionid,$context,$checksum);
$context = new MD5;
$context->add($username,$password,$encryption_string);
$checksum = $context->digest();
$sessionid=join("-",
map { MIME::Base64::encode($_) }
($username,$password,$checksum));
$sessionid=~s|n||g;
return($sessionid);
logit("Exit sub encodeLevel1");
}
sub encodeLevel2 {
logit("Enter sub encodeLevel2");
my ($username,$password) = @_;
my $sessionid;
$sessionid = Crypt::CBC->new($encryption_string,'IDEA')->
encrypt_hex(join("",($username,$password)));
logit("Exit sub encodeLevel2");
return($sessionid);
}
sub decodeLevel1 {
logit("Enter sub decodeLevel1");
my $sessionid = shift;
my ($username,$password,$context,$checksum,$new_checksum);
($username,$password,$checksum) =
map { MIME::Base64::decode($_) } split(/-/,$sessionid);
$context = new MD5;
$context->add($username,$password,$encryption_string);
$new_checksum = $context->digest();
if ( $checksum ne $new_checksum ) {
logit(" WARNING: The encryption string has been tampered with or changed.");
$username = "$new_checksum";
$password = "$new_checksum";
}
logit("Exit sub decodeLevel1");
return ($username,$password);
}
sub decodeLevel2 {
logit("Enter sub decodeLevel2");
my $sessionid = shift;
my ($username,$password);
($username,$password) = split(//, Crypt::CBC->new($encryption_string,'IDEA')->
decrypt_hex($sessionid));
return($username,$password);
logit("Exit sub decodeLevel2");
}
sub decodeSessionid {
logit("Enter sub decodeSessionid");
my($sessionid) = shift;
my ($username,$password);
if ($encryption_enabled) {
if ($encryption_enabled == 1) {
logit(" Encryption enabled (1), attempting to decrypt username / password.");
($username,$password) = decodeLevel1($sessionid);
}
if ($encryption_enabled == 2) {
logit(" Encryption enabled(2), attempting to decrypt username / password.");
($username,$password) = decodeLevel2($sessionid);
}
if ($username && $password) {
logit(" Decrypt was successful.");
} else {
logit(" Decrypt was not successful!");
}
} else {
logit(" Encryption not enabled, attempting to split username / password.");
($username,$password) = split(/~/, $sessionid);
if ($username && $password) {
logit(" Split was successful.");
} else {
logit(" Split was not successful!");
}
}
logit("Exit sub decodeSessionid");
return ($username,$password);
}
sub addPasswd {
logit("Enter subroutine addPasswd");
my $database = shift;
my $username = shift;
my $password = shift;
my ($sql,$cursor,$count,$message,$duration,$url,$usercookie,$passcookie);
my ($role,@allroles,$foo,$dbstatus,$sessionid,$sessioncookie,$bgline);
# Connect to the database.
$dbh = dbConnect($database,$username,$password);
# Determine the database status. (OPEN,MOUNTED etc.)
$dbstatus = dbStatus();
if ($dbstatus eq "OPEN") {
# First, check to be sure this user has "SELECT ANY TABLE" privilege.
# If not, send them packing.
$count = checkPriv("SELECT ANY TABLE");
if ($count < 1) {
ErrorPage("The username you have specified does not have the appropriate permissions to use this tool. Please specify a username with SELECT ANY TABLE privileges.");
}
$dbh->disconnect;
}