Oracle数据库

开发平台：

Perl

oracletool.pl：源码内容

logit(" Updating password for database $database");
$foo = dirname($scriptname);
if ($encryption_enabled) {
$sessionid = buildSessionid($username,$password);
logit(" SessionID = $sessionid");
logit(" Encryption is enabled.");
} else {
$sessionid = "$username~$password";
logit(" Encryption is NOT enabled.");
}
$sessioncookie = cookie(-name=>"$database.sessionid",-value=>"$sessionid",-expires=>"$expiration",-path=>"$foo");
print header(-cookie=>$sessioncookie);
$message = "Password for database $database has been updated.";
$duration = "1";
$url = "$scriptname?database=$database&object_type=FRAMEPAGE";
$bgline = "<BODY BGCOLOR=$bgcolor>n";
if ($bgimage) {
if ((-e "$ENV{'DOCUMENT_ROOT'}/$bgimage") && (-r "$ENV{'DOCUMENT_ROOT'}/$bgimage")) {
logit(" Background image is $ENV{'DOCUMENT_ROOT'}/$bgimage and is readable");
$bgline = "<BODY BACKGROUND=$bgimage>n";
}
}
print <<"EOF";
<HTML>
<HEAD>
<TITLE>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 addPasswd");
exit;
}
sub recentEvents {
logit("Enter subroutine recentEvents");
my ($sql,$text,$link,$infotext,$cols);
$sql = "$copyright
SELECT
TO_CHAR(TO_DATE(D.VALUE,'J'),'Day, Month DD, YYYY')||' - '||
TO_CHAR(TO_DATE(S.VALUE,'sssss'),'HH24:MI:SS') "Instance startup time"
FROM V$INSTANCE D, V$INSTANCE S
WHERE D.KEY = 'STARTUP TIME - JULIAN'
AND S.key = 'STARTUP TIME - SECONDS'
" if ($oracle7);
$sql = "$copyright
SELECT
TO_CHAR(STARTUP_TIME,'Day, Month DD YYYY - HH24:MI:SS') "Instance startup time"
FROM V$INSTANCE
" if ($oracle8);
$text = "";
$link = "";
DisplayTable($sql,$text,$link);
$sql = "$copyright
SELECT
TO_CHAR(COUNT(*),'999,999,999,999') "Log switches"
FROM V$LOG_HISTORY
WHERE FIRST_TIME > SYSDATE-1
";
$text = "Number of redo log switches last 24 hours.";
$link = "";
DisplayTable($sql,$text,$link);
$sql = "$copyright
SELECT
NAME "Username"
FROM SYS.USER$
WHERE CTIME > SYSDATE-30
AND TYPE = 1
";
$sql = "$copyright
SELECT
NAME "Username"
FROM SYS.USER$
WHERE CTIME > SYSDATE-30
AND TYPE# = 1
" if ($oracle8);
$text = "Users added in the last 30 days.";
$link = "$scriptname?database=$database&object_type=USERINFO";
$infotext = "No users have been added in the last 30 days.";
DisplayColTable($sql,$text,$link,$infotext,$schema_cols);
$sql = "$copyright
SELECT
NAME "Role"
FROM SYS.USER$
WHERE CTIME > SYSDATE-30
AND TYPE = 0
";
$sql = "$copyright
SELECT
NAME "Role"
FROM SYS.USER$
WHERE CTIME > SYSDATE-30
AND TYPE# = 0
" if ($oracle8);
$text = "Roles added in the last 30 days.";
$link = "$scriptname?database=$database&object_type=ROLES";
$infotext = "No roles have been added in the last 30 days.";
DisplayColTable($sql,$text,$link,$infotext,$schema_cols);
if ($oracle8) {
$sql = "$copyright
SELECT
VDF.NAME "File name",
TO_CHAR(VDF.BYTES,'999,999,999,999') "Bytes",
TO_CHAR(VDF.CREATION_TIME,'Dy, Mon DD YYYY HH24:MI:SS') "Creation date",
TS.NAME "Tablespace name"
FROM V$DATAFILE VDF,
SYS.TS$ TS
WHERE VDF.CREATION_TIME > SYSDATE - 30
AND VDF.TS# = TS.TS#
ORDER BY VDF.CREATION_TIME DESC
";
$text = "Datafiles which have been added in the last 30 days.";
$infotext = "There have been no datafiles added in the last 30 days.";
$link = "";
DisplayTable($sql,$text,$link,$infotext);
}
$sql = "$copyright
SELECT
DS.SEGMENT_NAME "Object name",
DS.SEGMENT_TYPE "Object type",
DS.OWNER "Owner",
DO.STATUS "Status",
DS.TABLESPACE_NAME "Tablespace name",
TO_CHAR(DO.LAST_DDL_TIME,'Dy, Mon DD YYYY HH24:MI:SS') "Last DDL date",
TO_CHAR(DO.CREATED,'Dy, Mon DD YYYY HH24:MI:SS') "Creation date"
FROM DBA_SEGMENTS DS, DBA_OBJECTS DO
WHERE DS.SEGMENT_TYPE NOT LIKE '%PARTITION'
AND DS.SEGMENT_NAME = DO.OBJECT_NAME
AND DO.CREATED > SYSDATE-1
ORDER BY CREATED, SEGMENT_TYPE DESC
";
$text = "Objects which have been created in the last 24 hours.";
$infotext = "There have been no objects created in the last 24 hours.";
ObjectTable($sql,$text,$infotext);
$sql = "$copyright
SELECT
DS.SEGMENT_NAME "Object name",
DS.SEGMENT_TYPE "Object type",
DS.OWNER "Owner",
DO.STATUS "Status",
DS.TABLESPACE_NAME "Tablespace name",
TO_CHAR(DO.LAST_DDL_TIME,'Dy, Mon DD YYYY HH24:MI:SS') "Last DDL date",
TO_CHAR(DO.CREATED,'Dy, Mon DD YYYY HH24:MI:SS') "Creation date"
FROM DBA_SEGMENTS DS, DBA_OBJECTS DO
WHERE DS.SEGMENT_TYPE NOT LIKE '%PARTITION'
AND DS.SEGMENT_NAME = DO.OBJECT_NAME
AND DO.LAST_DDL_TIME > SYSDATE-1
ORDER BY LAST_DDL_TIME DESC
";
$text = "Objects which have been edited in the last 24 hours.";
$infotext = "There have been no objects edited in the last 24 hours.";
ObjectTable($sql,$text,$infotext);
logit("Exit subroutine recentEvents");
}
sub showPerformance {
logit("Enter subroutine showPerformance");
my ($sql,$cursor,$value,$text,$link,$infotext);
my ($username,$sid,$counter,$rows);
# Check to see if TIMED_STATISTICS is set to true.
$sql = "$copyright
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'timed_statistics'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$value = $cursor->fetchrow_array;
$cursor->finish;
# If timed_statistics is enabled, get some info about CPU usage.
if ($value eq "TRUE") {
$sql = "$copyright
SELECT
SS.USERNAME "Username",
SE.SID "SID",
TO_CHAR(VALUE,'999,999,999,999') "Value"
FROM V$SESSION SS,
V$SESSTAT SE,
V$STATNAME SN
WHERE SE.STATISTIC# = SN.STATISTIC#
AND NAME = 'CPU used by this session'
AND SE.SID = SS.SID
AND SS.USERNAME IS NOT NULL
ORDER BY VALUE DESC
";
$text = "Top ten CPU users (Via TIMED_STATISTICS)";
$infotext = "No CPU usage via Oracle at this time.";
$link = "";
$rows = 10;
DisplayTable($sql,$text,$link,$infotext,$rows);
} else {
message("TIMED_STATISTICS is set to FALSE.");
}
# Put SGA and memory info in a table together to save space..
print <<"EOF";
<TABLE BORDER=0 CELLPADDING=20>
<TR>
<TD VALIGN=TOP ALIGN=CENTER>
<FONT FACE="$font" SIZE="$fontsize" COLOR="$fontcolor">
EOF
# Instance SGA information
$sql = "$copyright
SELECT
NAME "Name",
TO_CHAR(VALUE,'999,999,999,999') "Value"
FROM V$SGA
";
$text = "Instance SGA info";
$link = "";
DisplayTable($sql,$text,$link);
print <<"EOF";
</TD>
<TD VALIGN=TOP ALIGN=CENTER>
<FONT FACE="$font" SIZE="$fontsize" COLOR="$fontcolor">
EOF
$sql = "$copyright
SELECT
NAME "Name",
TO_CHAR(BYTES,'999,999,999,999') "Bytes"
FROM V$SGASTAT
WHERE NAME IN ('free memory','db_block_buffers','log_buffer','dictionary cache','sql area','library cache')
";
$text = "Memory usage";
$link = "";
DisplayTable($sql,$text,$link);
print <<"EOF";
</TD>
</TR>
</TABLE>
EOF
# Data dictionary cache miss ratio
$sql = "$copyright
SELECT
TO_CHAR(SUM(gets),'999,999,999,999') "Gets",
TO_CHAR(SUM(getmisses),'999,999,999,999') "Misses",
TO_CHAR(SUM(getmisses) / SUM(gets) * 100,'999')||'%' "Percentage"
FROM V$ROWCACHE
";
$text = "Data dictionary cache miss ratio";
$link = "";
DisplayTable($sql,$text,$link);
# Sort area information. Thanks to Tommy Wareing.
$sql = "$copyright
SELECT
S.SID "SID",
S.SERIAL# "Serial#",
S.USERNAME "Ora user",
S.OSUSER "OS user",
U.TABLESPACE "Tablespace",
SUM(U.EXTENTS) "Extents",
SUM(U.BLOCKS) "Blocks",
SA.SQL_TEXT "SQL Text"
FROM V$SESSION S,
V$SORT_USAGE U,
V$SQLAREA SA
WHERE S.SADDR=U.SESSION_ADDR
AND U.CONTENTS='TEMPORARY'
AND S.SQL_ADDRESS=SA.ADDRESS(+)
GROUP BY S.SID, S.SERIAL#, S.USERNAME, S.OSUSER, U.TABLESPACE,
SA.SQL_TEXT
";
$text = "Sort Area Usage";
$link = "";
$infotext = "No sorts currently using disk.";
DisplayTable($sql,$text,$link,$infotext);
# Percentage of sorts that are taking place in memory,
# as opposed to in temporary segments on disk.
$sql = "$copyright
SELECT
ROUND((SUM(DECODE(NAME, 'sorts (memory)', VALUE, 0))
/ (SUM(DECODE(NAME, 'sorts (memory)', VALUE, 0))
+ SUM(DECODE(NAME, 'sorts (disk)', VALUE, 0))))
* 100,2) "Percentage"
FROM V$SYSSTAT
";
$text = "Percentage of sorts that are taking place in memory";
$link = "";
DisplayTable($sql,$text,$link);
# Information from v$librarycache
$sql = "$copyright
SELECT
NAMESPACE "Namespace",
TO_CHAR(GETS,'999,999,999,999') "Gets",
TO_CHAR(GETHITS,'999,999,999,999') "Gethits",
TO_CHAR(GETHITRATIO,'99.99') "GetHitRatio",
TO_CHAR(PINS,'999,999,999,999') "Pins",
TO_CHAR(PINHITS,'999,999,999,999') "PinHits",
TO_CHAR(PINHITRATIO,'99.99') "PinHitRatio",
TO_CHAR(RELOADS,'999,999,999,999') "Reloads",
TO_CHAR(INVALIDATIONS,'999,999,999,999') "Invalidations"
FROM V$LIBRARYCACHE
";
$sql = "$copyright
SELECT
NAMESPACE "Namespace",
TO_CHAR(GETS,'999,999,999,999') "Gets",
TO_CHAR(GETHITS,'999,999,999,999') "Get hits",
TO_CHAR(GETHITRATIO,'99.99') "Get hit ratio",
TO_CHAR(PINS,'999,999,999,999') "Pins",
TO_CHAR(PINHITS,'999,999,999,999') "Pin hits",
TO_CHAR(PINHITRATIO,'99.99') "Pin hit ratio",
TO_CHAR(RELOADS,'999,999,999,999') "Reloads",
TO_CHAR(INVALIDATIONS,'999,999,999,999') "Invalidations",
TO_CHAR(DLM_LOCK_REQUESTS,'999,999,999,999') "DLM lock requests",
TO_CHAR(DLM_PIN_REQUESTS,'999,999,999,999') "DLM pin requests",
TO_CHAR(DLM_PIN_RELEASES,'999,999,999,999') "DLM pin releases",
TO_CHAR(DLM_INVALIDATION_REQUESTS,'999,999,999,999') "DLM invalidation requests",
TO_CHAR(DLM_INVALIDATIONS,'999,999,999,999') "DLM invalidations"
FROM V$LIBRARYCACHE
" if ($oracle8 && parallel());
$text = "Library cache information";
$link = "";
DisplayTable($sql,$text,$link);
# Resource limit info for Oracle8 databases.
if ( $oracle8 ) {
$sql = "$copyright
SELECT
RESOURCE_NAME "Resource name",
INITIAL_ALLOCATION "Initial value",
CURRENT_UTILIZATION "Current utilization",
MAX_UTILIZATION "Max utilization",
LIMIT_VALUE "Upper limit"
FROM V$RESOURCE_LIMIT
";
$text = "Resource limits";
$link = "";
DisplayTable($sql,$text,$link);
}
# Parallel query slave stats
$sql = "$copyright
SELECT
SLAVE_NAME "Slave name",
STATUS "Status",
TO_CHAR(SESSIONS,'999,999,999,999') "Sessions",
TO_CHAR(IDLE_TIME_CUR,'999,999,999,999') "Idle time (cur)",
TO_CHAR(IDLE_TIME_TOTAL,'999,999,999,999') "Idle time (tot)",
TO_CHAR(BUSY_TIME_CUR,'999,999,999,999') "Busy time (cur)",
TO_CHAR(BUSY_TIME_TOTAL,'999,999,999,999') "Busy time (tot)",
TO_CHAR(CPU_SECS_CUR ,'999,999,999,999') "CPU seconds (cur)",
TO_CHAR(CPU_SECS_TOTAL,'999,999,999,999') "CPU seconds (tot)",
TO_CHAR(MSGS_SENT_CUR,'999,999,999,999') "Msgs sent (cur)",
TO_CHAR(MSGS_SENT_TOTAL,'999,999,999,999') "Msgs sent (tot)",
TO_CHAR(MSGS_RCVD_CUR,'999,999,999,999') "Msgs rcvd (cur)",
TO_CHAR(MSGS_RCVD_TOTAL,'999,999,999,999') "Msgs rcvd (tot)"
FROM V$PQ_SLAVE
";
$text = "Parallel query slave statistics";
$infotext = "No parallel query slaves are active";
$link = "";
DisplayTable($sql,$text,$link,$infotext);
# Parallel query server stats
$sql = "$copyright
SELECT
STATISTIC "Statistic",
TO_CHAR(VALUE,'999,999,999,999') "Value"
FROM V$PQ_SYSSTAT
";
$text = "Parallel query server status";
$link = "";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showPerformance");
}
sub showParameters {
logit("Enter subroutine showParameters");
my ($sql,$text,$link);
# Instance parameters
$sql = "$copyright
SELECT
NAME "Name",
DESCRIPTION "Description",
VALUE "Value",
ISDEFAULT "Default",
ISSES_MODIFIABLE "Session mod",
ISSYS_MODIFIABLE "System mod",
ISADJUSTED "Changed"
FROM V$PARAMETER
ORDER BY NAME
";
$text = "Instance parameter information";
$link = "";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showParameters");
}
sub showRows {
logit("Enter subroutine showRows");
my $numrows = shift;
my ($sql,$text,$link,$infotext,$error,$rowtext);
# Check to see if they want all rows..
# If so, set numrows to 1000000.
# Anyone wanting to see that many rows
# in a web browser is on drugs.
if ($numrows eq "all") {
$numrows = "1000000";
$rowtext = "All";
} else {
$rowtext = $numrows;
}
# If they entered a "where" clause, use it.
if ($whereclause) {
# Get rid of trailing semicolon, if present.
$whereclause =~ s/;$//;
$whereclause = " AND $whereclause";
}
# Show the first $numrows rows of a table
$sql = "$copyright
SELECT * FROM $schema.$object_name
WHERE ROWNUM <= $numrows
$whereclause
";
$text = "$rowtext rows of $object_name";
$link = "";
$infotext = "There are no rows to display.";
$error = DisplayTable($sql,$text,$link,$infotext);
if (!($error =~ /^d+$/)) {
message("Error in your "where" clause.<BR>Check the SQL and try again.<BR><BR>$error");
}
logit("Exit subroutine showRows");
}
sub showRollback {
logit("Enter subroutine showRollback");
refreshButton();
my ($sql,$text,$link,$infotext);
# Rollback segment information
$sql = "$copyright
SELECT
A.NAME "Rollback name",
TO_CHAR(C.INITIAL_EXTENT,'999,999,999,999') "Initial extent",
TO_CHAR(C.NEXT_EXTENT,'999,999,999,999') "Next extent",
TO_CHAR(C.MIN_EXTENTS,'999,999,999,999') "Min extents",
B.EXTENTS "Extents",
TO_CHAR(C.MAX_EXTENTS,'999,999,999,999') "Max extents",
TO_CHAR(D.BYTES,'999,999,999,999') "Size",
NVL(TO_CHAR(B.OPTSIZE,'999,999,999,999'),'Not set') "Optimal",
TO_CHAR(B.EXTENDS,'999,999,999') "Extends",
TO_CHAR(B.SHRINKS,'999,999,999') "Shrinks",
TO_CHAR(B.WRAPS,'999,999,999') "Wraps",
B.STATUS "Status"
FROM V$ROLLNAME A, V$ROLLSTAT B, DBA_ROLLBACK_SEGS C, DBA_SEGMENTS D
WHERE A.NAME = '$object_name'
AND C.SEGMENT_NAME = '$object_name'
AND A.USN = B.USN
AND D.SEGMENT_NAME = '$object_name'
AND D.SEGMENT_TYPE = 'ROLLBACK'
";
$text = "Rollback segment info";
$link = "$scriptname?database=$database&object_type=RBSDDL&arg=$object_name";
DisplayTable($sql,$text,$link);
# Active transactions occupying this rollback
$sql = "$copyright
SELECT
OSUSER "OS user",
USERNAME "Ora user",
SID "SID",
SERIAL# "Serial#",
SEGMENT_NAME "RBS",
SA.SQL_TEXT "SQL Text"
FROM V$SESSION S,
V$TRANSACTION T,
DBA_ROLLBACK_SEGS R,
V$SQLAREA SA
WHERE R.SEGMENT_NAME = '$object_name'
AND S.TADDR = T.ADDR
AND T.XIDUSN = R.SEGMENT_ID(+)
AND S.SQL_ADDRESS = SA.ADDRESS(+)
";
$text = "Transaction info";
$link = "";
$infotext = "No current transactions on this segment";
DisplayTable($sql,$text,$link,$infotext);
# Tablespace information for the tablespace this rollback belongs to.
# Good for monitoring the growth of a rollback with a long running
# transaction.
$sql = "$copyright
SELECT TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = '$object_name'
AND SEGMENT_TYPE = 'ROLLBACK'
";
my $cursor=$dbh->prepare($sql);
$cursor->execute;
$object_name = $cursor->fetchrow_array;
$cursor->finish;
$sql = "$copyright
SELECT * FROM
(SELECT TO_CHAR(SUM(BYTES),'999,999,999,999') "Bytes allocated"
FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = '$object_name'),
(SELECT TO_CHAR(SUM(BYTES),'999,999,999,999') "Bytes used"
FROM DBA_EXTENTS WHERE TABLESPACE_NAME = '$object_name'),
(SELECT TO_CHAR(SUM(BYTES),'999,999,999,999') "Bytes free"
FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = '$object_name'),
(SELECT TO_CHAR(MAX(BYTES),'999,999,999,999') "Largest free extent"
FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = '$object_name')
";
$text = "$object_name tablespace allocation";
$link = "";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showRollback");
}
sub showTransactions {
logit("Enter subroutine showTransactions");
my ($sql,$text,$link,$infotext);
# Display a refresh button
refreshButton();
# Check for transactions which are rolling back.
$sql = "$copyright
SELECT
S.USERNAME "Username",
S.SID "SID",
S.SERIAL# "Serial#",
TO_CHAR(T.USED_UBLK,'999,999,999,999,999') "Used undo blocks",
DRS.SEGMENT_NAME "RBS name"
FROM V$SESSION S,
V$TRANSACTION T,
DBA_ROLLBACK_SEGS DRS
WHERE T.ADDR IN
(SELECT
ADDR
FROM V$TRANSACTION
WHERE FLAG = 7811)
AND S.TADDR IN
(SELECT
ADDR
FROM V$TRANSACTION
WHERE FLAG = 7811)
AND T.ADDR = S.TADDR
AND T.XIDUSN = DRS.SEGMENT_ID
";
$text = "The following sessions appear to be in a rollback status.";
$infotext = "No transactions are in a rollback status.";
$link = "$scriptname?database=$database&object_type=SESSIONS";
DisplayTable($sql,$text,$link,$infotext);
# Active transactions occupying all rollbacks
$sql = "$copyright
SELECT
SEGMENT_NAME "RBS",
OSUSER "OS user",
USERNAME "Ora user",
SID "SID",
SERIAL# "Serial#",
SA.SQL_TEXT "SQL Text"
FROM V$SESSION S,
V$TRANSACTION T,
DBA_ROLLBACK_SEGS R,
V$SQLAREA SA
WHERE S.TADDR = T.ADDR
AND T.XIDUSN = R.SEGMENT_ID(+)
AND S.SQL_ADDRESS = SA.ADDRESS(+)
";
$text = "Transaction info";
$link = "$scriptname?database=$database&object_type=ROLLBACK";
$infotext = "No transactions active on any segments.";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showTransactions");
}
sub showRollbacks {
logit("Enter subroutine showRollbacks");
my ($sql,$cursor,$foo,$text,$link,$infotext);
# Display a refresh button
refreshButton();
# Rollback segment information
$sql = "$copyright
SELECT
A.SEGMENT_NAME "Name",
A.OWNER "Owner",
A.TABLESPACE_NAME "Tablespace",
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",
TO_CHAR(A.MAX_EXTENTS,'999,999,999,999') "Max Extents",
NVL(TO_CHAR(C.OPTSIZE,'999,999,999,999'),'Not set') "Optimal",
B.STATUS "Status",
TO_CHAR(C.WRITES,'999,999,999,999,999') "Writes",
C.WAITS "Waits",
C.XACTS "Active Xacts"
FROM DBA_SEGMENTS A, DBA_ROLLBACK_SEGS B, V$ROLLSTAT C
WHERE A.SEGMENT_TYPE = 'ROLLBACK'
AND A.SEGMENT_NAME = B.SEGMENT_NAME
AND B.SEGMENT_ID = C.USN
AND ( B.INSTANCE_NUM =
( SELECT VALUE FROM V$PARAMETER
WHERE NAME = 'instance_number' )
OR B.INSTANCE_NUM IS NULL )
ORDER BY A.SEGMENT_NAME, A.TABLESPACE_NAME
";
$text = "Online rollback segments";
$link = "$scriptname?database=$database&object_type=ROLLBACK";
DisplayTable($sql,$text,$link);
$sql = "$copyright
SELECT
SEGMENT_NAME "Name",
STATUS "Status",
OWNER "Owner",
TABLESPACE_NAME "Tablespace"
FROM DBA_ROLLBACK_SEGS
WHERE STATUS != 'ONLINE'
";
$text = "Warning: You have rollback(s) which are not online";
$link = "$scriptname?database=$database&object_type=ROLLBACK";
DisplayTable($sql,$text,$link);
# Show gets and waits percentages for performance
$sql = "$copyright
SELECT
SUM(VALUE)
FROM V$SYSSTAT
WHERE NAME IN
('db block gets','consistent gets')
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$foo = $cursor->fetchrow_array;
$cursor->finish;
$sql = "$copyright
SELECT
TO_CHAR(SUM(VALUE),'999,999,999,999') "Total gets"
FROM V$SYSSTAT
WHERE NAME IN
('db block gets','consistent gets')
";
$text = "Total gets";
$link = "";
DisplayTable($sql,$text,$link);
$sql = "$copyright
SELECT
CLASS "Class",
TO_CHAR(COUNT,'999,999,999,999') "Count",
TO_CHAR(COUNT/$foo,'99.99') "Wait %"
FROM V$WAITSTAT
WHERE CLASS IN
('system undo header','system undo block','undo header','undo block')
";
$text = "Wait statistics.<BR>If any wait% is greater than 1, you may need to add rollbacks.";
$link = "";
DisplayTable($sql,$text,$link);
$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 IN (
SELECT DISTINCT TABLESPACE_NAME
FROM DBA_ROLLBACK_SEGS )
GROUP BY DF.TABLESPACE_NAME, DF.BYTES
ORDER BY "Percent free"
";
$text = "Tablespaces containing rollback segments.";
$link = "";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showRollbacks");
}
sub showContention {
logit("Enter subroutine showContention");
my ($sql,$text,$link,$infotext);
# Locking contention information
$sql = "$copyright
SELECT DISTINCT
O.OBJECT_NAME "Object_name",
SH.USERNAME "Holding username",
SH.SID "Holder SID",
SW.USERNAME "Waiting username",
SW.SID "Waiter SID",
DECODE(LH.LMODE,
1, 'null',
2, 'row share',
3, 'row exclusive',
4, 'share',
5, 'share row exclusive',
6, 'exclusive') "Mode held"
FROM DBA_OBJECTS O,
V$SESSION SW,
V$LOCK LW,
V$SESSION SH,
V$LOCK LH
WHERE LH.ID1 = O.OBJECT_ID
AND LH.ID1 = LW.ID1
AND SH.SID = LH.SID
AND SW.SID = LW.SID
AND SH.LOCKWAIT IS NULL
AND SW.LOCKWAIT IS NOT NULL
AND LH.TYPE = 'TM'
AND LW.TYPE = 'TM'
";
$text = "Object lock contention info";
$link = "";
$infotext = "No object lock contention found.";
DisplayTable($sql,$text,$link,$infotext);
# Locked objects (Not neccessarily contending).
$sql = "$copyright
SELECT
A.OBJECT_NAME "Object_name",
A.OWNER "Owner",
B.OBJECT_ID "Object ID",
B.SESSION_ID "SID",
B.ORACLE_USERNAME "Oracle user",
B.PROCESS "OS process ID",
DECODE(B.LOCKED_MODE,
0,'None',
1,'Null',
2,'Row-S (SS)',
3,'Row-X (SX)',
4,'Share',
5,'S/Row-X (SSX)',
6,'Exclusive') "Locked mode"
FROM DBA_OBJECTS A, V$LOCKED_OBJECT B
WHERE A.OBJECT_ID = B.OBJECT_ID
ORDER BY A.OBJECT_NAME, A.OWNER
";
$text = "Object lock info";
$link = "";
$infotext = "No object locks found.";
DisplayTable($sql,$text,$link,$infotext);
# Session wait information
$sql = "$copyright
SELECT
SES.USERNAME "Username",
SW.SID "SID",
SW.EVENT "Event"
FROM V$SESSION SES,
V$SESSION_WAIT SW
WHERE SES.SID = SW.SID
AND SES.USERNAME IS NOT NULL
";
$text = "Session wait info";
$link = "";
$infotext = "No waits found.";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showContention");
}
sub showRefreshgroups {
logit("Enter subroutine showRefreshgroups");
my ($sql,$cursor,$text,$link,$infotext);
# $sql = "$copyright
#SELECT
# OWNER "Refresh owner",
# NAME "Refresh Name",
# TABLE_NAME "Table name",
# MASTER_VIEW "Master view",
# MASTER_OWNER "Master owner",
# MASTER "Master table",
# MASTER_LINK "DB link",
# CAN_USE_LOG "Log?",
# UPDATABLE "Updatable?",
# TO_CHAR(LAST_REFRESH,'Month DD, YYYY - HH24:MI') "Last refresh"
#FROM DBA_SNAPSHOTS
#";
# $text = "Parent snapshots";
# $link = "$scriptname?database=$database&object_type=SNAPINFO";
# $infotext = "";
# DisplayTable($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
REFGROUP "Group ID",
ROWNER "Group owner",
RNAME "Group name",
ROLLBACK_SEG "Rollback",
PUSH_DEFERRED_RPC "Push changes?",
JOB "Job ID",
TO_CHAR(NEXT_DATE,'Month DD, YYYY - HH24:MI') "Next date",
BROKEN "Broken?",
PURGE_OPTION "Purge option",
PARALLELISM "Parallelism"
FROM DBA_REFRESH
";
$text = "Refresh groups";
$link = "$scriptname?database=$database&object_type=REFRESHINFO";
$infotext = "No refresh groups";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showRefreshgroups");
}
sub showRefreshinfo {
logit("Enter subroutine showRefreshinfo");
my ($sql,$cursor,$text,$link,$infotext);
$sql = "$copyright
SELECT
OWNER "Owner",
NAME "Name",
TYPE "Type",
ROLLBACK_SEG "Rollback",
PUSH_DEFERRED_RPC "Push changes?",
JOB "Job ID",
TO_CHAR(NEXT_DATE,'Month DD, YYYY - HH24:MI') "Next date",
BROKEN "Broken?",
PURGE_OPTION "Purge option",
PARALLELISM "Parallelism"
FROM DBA_REFRESH_CHILDREN
WHERE REFGROUP = $object_name
";
$text = "Refresh group children";
$link = "";
$infotext = "No refresh group children";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showRefreshinfo");
}
sub showAdvRepGroup {
logit("Enter subroutine showAdvRepGroup");
my ($sql,$cursor,$text,$link,$infotext);
$sql = "$copyright
SELECT
DBLINK "DB link",
MASTERDEF "Masterdef",
SNAPMASTER "Snap link",
MASTER_COMMENT "Comment",
DECODE(MASTER,
'Y','YES',
'N','NO') "Master?"
FROM DBA_REPSITES
WHERE GNAME = '$object_name'
ORDER BY MASTERDEF DESC
";
$text = "Information for replicated group $object_name.";
$link = "";
$infotext = "";
DisplayTable($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
SNAME "Owner",
ONAME "Object name",
TYPE "Object type",
STATUS "Status",
GENERATION_STATUS "Generation status",
ID "ID",
OBJECT_COMMENT "Comment"
FROM DBA_REPOBJECT
WHERE GNAME = '$object_name'
ORDER BY TYPE DESC
";
$text = "";
$link = "";
$infotext = "";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showAdvRepGroup");
}
sub showAdvRepGroups {
logit("Enter subroutine showAdvRepGroups");
my ($sql,$cursor,$text,$link,$infotext);
$sql = "$copyright
SELECT
GNAME "Group name",
DECODE(MASTER,
'Y','YES',
'N','NO') "Master?",
STATUS "Status",
SCHEMA_COMMENT "Comment"
FROM DBA_REPGROUP
ORDER BY MASTER
";
$text = "Advanced replication group(s)";
$link = "$scriptname?database=$database&object_type=ADVREPGROUP";
$infotext = "";
DisplayTable($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
TO_CHAR(ID,'999,999,999,999') "ID",
SOURCE "Source",
USERID "User ID",
TO_CHAR(TIMESTAMP,'Month DD, YYYY - HH24:MI') "Timestamp",
ROLE "Role",
MASTER "Master",
SNAME "Remote schema",
REQUEST "Request",
ONAME "Object_name",
TYPE "Object type",
STATUS "Status",
MESSAGE "Message",
ERRNUM "Ora error",
GNAME "Group name"
FROM DBA_REPCATLOG
";
$text = "Repcatlog entries";
$link = "";
$infotext = "No entries in the Repcatlog table.";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showAdvRepGroups");
}
sub showRepmaster {
logit("Enter subroutine showRepmaster");
my ($sql,$cursor,$text,$link,$infotext);
$sql = "$copyright
SELECT
SNAPSHOT_ID "Snap ID",
NAME "Name",
OWNER "Owner",
SNAPSHOT_SITE "Snap site",
CAN_USE_LOG "Use log?",
UPDATABLE "Updatable?",
REFRESH_METHOD "Refresh method",
VERSION "Version"
FROM DBA_REGISTERED_SNAPSHOTS
";
$text = "Registered snapshots";
$link = "$scriptname?database=$database&object_type=SNAPINFO";
$infotext = "";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showRepmaster");
}
sub showSnapinfo {
logit("Enter subroutine showSnapinfo");
my ($sql,$cursor,$text,$link,$infotext);
$sql = "$copyright
SELECT
NAME "Name",
OWNER "Owner",
SNAPSHOT_SITE "Snap site"
FROM DBA_REGISTERED_SNAPSHOTS
WHERE SNAPSHOT_ID = $object_name
";
$text = "Detailed snapshot info";
$link = "";
$infotext = "";
DisplayTable($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
QUERY_TXT "Query text"
FROM DBA_REGISTERED_SNAPSHOTS
WHERE SNAPSHOT_ID = $object_name
";
$text = "";
DisplayPiecedData($sql,$text);
$sql = "$copyright
SELECT
LOG_TABLE "Log table",
MASTER "Master",
LOG_OWNER "Log owner",
ROWIDS "Rowids?",
PRIMARY_KEY "Primary key?",
FILTER_COLUMNS "Filter columns?",
TO_CHAR(CURRENT_SNAPSHOTS,'Month DD, YYYY - HH24:MI') "Current snapshot"
FROM DBA_SNAPSHOT_LOGS
WHERE SNAPSHOT_ID = $object_name
";
$text = "Snapshot log table";
$link = "";
$infotext = "No snapshot log table for this snapshot.";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showSnapinfo");
}
sub showControlfiles {
logit("Enter subroutine showControlfiles");
my ($sql,$cursor,$text,$link,$infotext);
my ($alloc,$used,$alloc_total,$used_total);
$sql = "$copyright
SELECT
NAME "Name",
DECODE (STATUS,'','OK') "Status"
FROM V$CONTROLFILE
";
$text = "Controlfile info";
$link = "";
$infotext = "There is something terribly wrong...";
DisplayTable($sql,$text,$link,$infotext);
if ($oracle8) {
$sql = "$copyright
SELECT
RECORDS_TOTAL*RECORD_SIZE,
RECORDS_USED*RECORD_SIZE
FROM V$CONTROLFILE_RECORD_SECTION
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
while (($alloc,$used) = $cursor->fetchrow_array) {
$alloc_total += $alloc;
$used_total += $used;
}
$cursor->finish;
$sql = "$copyright
SELECT
TO_CHAR($alloc_total,'999,999,999,999') "Total allocated",
TO_CHAR($used_total,'999,999,999,999') "Total used"
FROM DUAL
";
$text = "Controlfile record space usage";
$link = "";
$infotext = "There is something terribly wrong...";
DisplayTable($sql,$text,$link,$infotext);
$sql = "$copyright
SELECT
DECODE(TYPE,
'DATABASE','Database',
'CKPT PROGRESS','Checkpoint progress',
'REDO THREAD','Redo thread',
'REDO LOG','Redo log',
'DATAFILE','Datafile',
'FILENAME','Filename',
'TABLESPACE','Tablespace',
'LOG HISTORY','Log history',
'OFFLINE RANGE','Offline range',
'ARCHIVED LOG','Archived log',
'BACKUP SET','Backup set',
'BACKUP PIECE','Backup piece',
'BACKUP DATAFILE','Backup datafile',
'BACKUP REDOLOG','Backup redolog',
'DATAFILE COPY','Datafile copy',
'BACKUP CORRUPTION','Backup corruption',
'COPY CORRUPTION','Copy corruption',
'DELETED OBJECT','Deleted object','Reserved') "Record type",
TO_CHAR(RECORD_SIZE,'999,999,999,999') "Record size",
TO_CHAR(RECORDS_TOTAL,'999,999,999,999') "Records total",
TO_CHAR(RECORDS_USED,'999,999,999,999') "Records used",
TO_CHAR(RECORDS_TOTAL*RECORD_SIZE,'999,999,999,999') "Space allocated",
TO_CHAR(RECORDS_USED*RECORD_SIZE,'999,999,999,999') "Space used"
FROM V$CONTROLFILE_RECORD_SECTION
";
$text = "Controlfile record info";
$link = "";
$infotext = "There is something terribly wrong...";
DisplayTable($sql,$text,$link,$infotext);
}
logit("Exit subroutine showControlfiles");
}
sub showArchiving {
logit("Enter subroutine showArchiving");
my ($sql,$cursor,$value,$log_archive_dest,$text,$link,$infotext);
$sql = "$copyright
SELECT
VALUE FROM V$PARAMETER
WHERE NAME = 'log_archive_dest'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$value = $cursor->fetchrow_array;
$log_archive_dest .= $value;
$sql = "$copyright
SELECT
VALUE FROM V$PARAMETER
WHERE NAME = 'log_archive_format'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$value = $cursor->fetchrow_array;
$log_archive_dest .= $value;
$sql = "$copyright
SELECT '$log_archive_dest' "Archive log format" FROM DUAL
";
$text = "";
$link = "";
$infotext = "";
DisplayTable($sql,$text,$link,$infotext);
# print <<"EOF";
#<TABLE BORDER=1>
# <TH><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Archive log destination / format</TH>
# <TR>
# <TD><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$log_archive_dest</TD>
# </TR>
#</TABLE>
#EOF
$sql = "$copyright
SELECT
THREAD# "Thread#",
SEQUENCE# "Sequence#",
TIME "Time of first entry",
LOW_CHANGE# "Lowest SCN",
(HIGH_CHANGE#-1) "Highest SCN",
ARCHIVE_NAME "Archived log name"
FROM V$LOG_HISTORY
ORDER BY SEQUENCE# DESC
" if ($oracle7);
$sql = "$copyright
SELECT
THREAD# "Thread#",
SEQUENCE# "Sequence#",
TO_CHAR(FIRST_TIME,'Day, Month DD YYYY - HH24:MI:SS') "Time of first entry",
FIRST_CHANGE# "Lowest SCN",
NEXT_CHANGE# "Highest SCN",
RECID "Controlfile RecID",
STAMP "Controlfile stamp"
FROM V$LOG_HISTORY
ORDER BY SEQUENCE# DESC
" if ($oracle8);
$sql = "$copyright
SELECT
THREAD# "Thread#",
SEQUENCE# "Sequence#",
TO_CHAR(FIRST_TIME,'Day, Month DD YYYY - HH24:MI:SS') "Time of first entry",
FIRST_CHANGE# "Lowest SCN",
NEXT_CHANGE# "Highest SCN",
RECID "Controlfile RecID",
STAMP "Controlfile stamp"
FROM V$LOG_HISTORY
WHERE THREAD# = (
SELECT VALUE FROM V$PARAMETER
WHERE NAME = 'thread')
ORDER BY SEQUENCE# DESC
" if ( ($oracle8) && (parallel()) );
$text = "Archived redo log info";
$link = "";
$infotext = "There are no archived redologs to report on.";
DisplayTable($sql,$text,$link,$infotext);
logit("Exit subroutine showArchiving");
}
sub showRedo {
logit("Enter subroutine showRedo");
my ($sql,$cursor,$value,$text,$link);
$sql = "$copyright
SELECT
VALUE FROM V$PARAMETER
WHERE NAME = 'log_archive_start'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$value = $cursor->fetchrow_array;
if ( $value eq "TRUE" ) {
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="ARCHIVING">
<INPUT TYPE="SUBMIT" NAME="archiving" VALUE="Archiving information">
</FORM>
</TD>
</TR>
</TABLE>
EOF
} else {
message("Database archiving is not enabled.");
}
# Online redo log information
$sql = "$copyright
SELECT
A.MEMBER "Member",
B.GROUP# "Group#",
B.THREAD# "Thread#",
B.SEQUENCE# "Sequence#",
TO_CHAR(B.BYTES,'999,999,999,999') "Bytes",
B.MEMBERS "Members",
B.ARCHIVED "Archived",
B.STATUS "Status"
FROM V$LOGFILE A, V$LOG B
WHERE A.GROUP# = B.GROUP#
ORDER BY A.MEMBER
";
$text = "Online redo log info";
$link = "";
DisplayTable($sql,$text,$link);
$sql = "$copyright
SELECT
TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "Date",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "00",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "13",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "14",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "15",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "16",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "17",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "18",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "19",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "20",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "21",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "22",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "23"
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
" if $oracle8;
$sql = "$copyright
SELECT
SUBSTR(TIME,1,5) "Day",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'00',1,0)),'9999') "00",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'01',1,0)),'9999') "01",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'02',1,0)),'9999') "02",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'03',1,0)),'9999') "03",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'04',1,0)),'9999') "04",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'05',1,0)),'9999') "05",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'06',1,0)),'9999') "06",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'07',1,0)),'9999') "07",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'08',1,0)),'9999') "08",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'09',1,0)),'9999') "09",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'10',1,0)),'9999') "10",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'11',1,0)),'9999') "11",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'12',1,0)),'9999') "12",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'13',1,0)),'9999') "13",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'14',1,0)),'9999') "14",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'15',1,0)),'9999') "15",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'16',1,0)),'9999') "16",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'17',1,0)),'9999') "17",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'18',1,0)),'9999') "18",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'19',1,0)),'9999') "19",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'20',1,0)),'9999') "20",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'21',1,0)),'9999') "21",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'22',1,0)),'9999') "22",
TO_CHAR(SUM(DECODE(SUBSTR(TIME,10,2),'23',1,0)),'9999') "23"
FROM V$LOG_HISTORY
GROUP BY SUBSTR(TIME,1,5)
" if $oracle7;
$text = "Graph of log switch history by day and hour";
$link = "";
DisplayTable($sql,$text,$link);
logit("Exit subroutine showRedo");
}
sub checkPriv {
logit("Enter subroutine checkPriv");
my ($privilege,$yesno);
$privilege = shift;
logit(" Checking for privilege "$privilege"");
$sql = "$copyright
SELECT
COUNT(*)
FROM SESSION_PRIVS
WHERE PRIVILEGE = '$privilege'
";
$cursor=$dbh->prepare($sql) or ErrorPage("$DBI::errstr");
$cursor->execute;
$yesno = $cursor->fetchrow_array;
$cursor->finish;
logit(" Returning value of $yesno for privilege");
logit("Exit subroutine checkPriv");
return($yesno);
}
sub refreshButton {
logit("Enter subroutine refreshButton");
my $url = "$scriptname?database=$database&user=$user&schema=$schema&object_type=$object_type&arg=$object_name";
my $sid = $query->param('sid') || "";
my $serial = $query->param('serial') || "";
# Display refresh button
print <<"EOF";
<FORM METHOD="POST" ACTION="$scriptname">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
EOF
print "<INPUT TYPE=HIDDEN NAME=database VALUE=$database>n" if $database;
print "<INPUT TYPE=HIDDEN NAME=user VALUE=$user>n" if $user;
print "<INPUT TYPE=HIDDEN NAME=schema VALUE=$schema>n" if $schema;
print "<INPUT TYPE=HIDDEN NAME=object_type VALUE=$object_type>n" if $object_type;
print "<INPUT TYPE=HIDDEN NAME=arg VALUE=$object_name>n" if $object_name;
print "<INPUT TYPE=HIDDEN NAME=url VALUE=$url>n" if $url;
print "<INPUT TYPE=HIDDEN NAME=sid VALUE=$sid>n" if $sid;
print "<INPUT TYPE=HIDDEN NAME=serial VALUE=$serial>n" if $serial;
print "<INPUT TYPE=SUBMIT NAME=sessions VALUE=Refresh>n";
print "</FORM>n";
logit("Exit subroutine refreshButton");
}
sub topSessions {
logit("Enter subroutine topSessions");
my ($sql,$cursor,$sid,$serial,$username,$command,$osuser,$status,$process,$terminal,$program);
my ($sortfield,$refreshrate,$highlight,$color,$blockchanges,$sqltext);
$sortfield = $query->param('sortfield') || "orauser";
$refreshrate = $ENV{'AUTO_REFRESH'} || "10";
$highlight = "#FFFFC6";
logit(" Sort field = $sortfield");
print <<"EOF";
<FORM METHOD="POST" ACTION="$scriptname">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE=HIDDEN NAME=database VALUE=$database>
<INPUT TYPE=HIDDEN NAME=object_type VALUE=$object_type>
<INPUT TYPE=HIDDEN NAME=arg VALUE=$object_name>
<INPUT TYPE=HIDDEN NAME=refreshrate VALUE=$refreshrate>
<INPUT TYPE=HIDDEN NAME=sortfield VALUE=$sortfield>
<INPUT TYPE=SUBMIT NAME=foobar VALUE="AutoRefresh ($refreshrate)">
</FORM>
EOF
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 "sid") {
$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=sid>SID</A></TH>n";
if ($sortfield eq "serial") {
$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=serial>Serial#</A></TH>n";
if ($sortfield eq "orauser") {
$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=orauser>Ora user</A></TH>n";
if ($sortfield eq "osuser") {
$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=osuser>OSuser</A></TH>n";
if ($sortfield eq "command") {
$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=command>Commmand</A></TH>n";
if ($sortfield eq "blockchanges") {
$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=blockchanges>Block changes</A></TH>n";
if ($sortfield eq "status") {
$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=status>Status</A></TH>n";
if ($sortfield eq "process") {
$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=process>Process</A></TH>n";
if ($sortfield eq "sqltext") {
$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=sqltext>SQL text</A></TH>n";
if ($sortfield eq "program") {
$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=program>Program</A></TH>n";
if ($sortfield eq "terminal") {
$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=terminal>Terminal</A></TH>n";
$sortfield = "SID DESC" if ($sortfield eq "sid");
$sortfield = "SERIAL# DESC" if ($sortfield eq "serial");
$sortfield = "USERNAME" if ($sortfield eq "orauser");
$sortfield = "OSUSER" if ($sortfield eq "osuser");
$sortfield = "COMMAND DESC" if ($sortfield eq "command");
$sortfield = "BLOCK_CHANGES DESC" if ($sortfield eq "blockchanges");
$sortfield = "STATUS" if ($sortfield eq "status");
$sortfield = "PROCESS DESC" if ($sortfield eq "process");
$sortfield = "SQL_TEXT DESC" if ($sortfield eq "sqltext");
$sortfield = "TERMINAL" if ($sortfield eq "terminal");
$sortfield = "PROGRAM" if ($sortfield eq "program");
$sql = "$copyright
SELECT
VS.SID SID,
VS.SERIAL# SERIAL#,
VS.USERNAME USERNAME,
VS.OSUSER OSUSER,
DECODE(VS.COMMAND,
'0','None',
'1','Create table',
'2','Insert',
'3','Select',
'4','Create cluster',
'5','Alter cluster',
'6','Update',
'7','Delete',
'8','Drop cluster',
'9','Create index',
'10','Drop index',
'11','Alter index',
'12','Drop table',
'13','Create sequence',
'14','Alter sequence',
'15','Alter table',
'16','Drop sequence',
'17','Grant',
'18','Revoke',
'19','Create synonym',
'20','Drop synonym',
'21','Create view',
'22','Drop view',
'23','Validate index',
'24','Create procedure',
'25','Alter procedure',
'26','Lock table',
'27','No operation in progress',
'28','Rename',
'29','Comment',
'30','Audit',
'31','Noaudit',
'32','Create database link',
'33','Drop database link',
'34','Create database',
'35','Alter database',
'36','Create rollback segment',
'37','Alter rollback segment',
'38','Drop rollback segment',
'39','Create tablespace',
'40','Alter tablespace',
'41','Drop tablespace',
'42','Alter session',
'43','Alter user',
'44','Commit',
'45','Rollback',
'46','Savepoint',
'47','PL/SQL Execute',
'48','Set transaction',
'49','Alter system switch log',
'50','Explain',
'51','Create user',
'52','Create role',
'53','Drop user',
'54','Drop role',
'55','Set role',
'56','Create schema',
'57','Create control file',
'58','Alter tracing',
'59','Create trigger',
'60','Alter trigger',
'61','Drop trigger',
'62','Analyze table',
'63','Analyze index',
'64','Analyze cluster',
'65','Create profile',
'66','Drop profile',
'67','Alter profile',
'68','Drop procedure',
'69','Drop procedure',
'70','Alter resource cost',
'71','Create snapshot log',
'72','Alter snapshot log',
'73','Drop snapshot log',
'74','Create snapshot',
'75','Alter snapshot',
'76','Drop snapshot',
'79','Alter role',
'85','Truncate table',
'86','Truncate cluster',
'88','Alter view',
'91','Create function',
'92','Alter function',
'93','Drop function',
'94','Create package',
'95','Alter package',
'96','Drop package',
'97','Create package body',
'98','Alter package body',
'99','Drop package body') COMMAND,
TO_CHAR(VSI.BLOCK_CHANGES,'999,999,999,999') BLOCK_CHANGES,
VS.STATUS STATUS,
VS.PROCESS PROCESS,
VST.SQL_TEXT,
VS.TERMINAL TERMINAL,
VS.PROGRAM PROGRAM
FROM V$SESSION VS,
V$SESS_IO VSI,
V$SQLTEXT VST
WHERE VS.SID = VSI.SID
AND VS.SQL_ADDRESS = VST.ADDRESS
AND VST.PIECE = 0
AND VS.USERNAME IS NOT NULL
ORDER BY $sortfield
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($sid,$serial,$username,$osuser,$command,$blockchanges,$status,$process,$sqltext,$terminal,$program) = $cursor->fetchrow_array) {
$sid = "&nbsp" unless $sid;
$serial = "&nbsp" unless $serial;
$username = "&nbsp" unless $username;
$osuser = "&nbsp" unless $osuser;
$command = "&nbsp" unless $command;
$blockchanges = "&nbsp" unless $blockchanges;
$status = "&nbsp" unless $status;
$process = "&nbsp" unless $process;
$sqltext = "&nbsp" unless $sqltext;
$terminal = "&nbsp" unless $terminal;
$program = "&nbsp" unless $program;
print "<TR><TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=SESSIONS&user=$username&sid=$sid&serial=$serial>$sid</A></TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$serial</TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A HREF=$scriptname?database=$database&object_type=SESSIONS&user=$username>$username</A></TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$osuser</TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$command</TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$blockchanges</TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$status</TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$process</TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$sqltext</TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$program</TD>n";
print "<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$terminal</TD></TR>n";
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</TABLE>
EOF
logit("Exit subroutine topSessions");
}
sub showSessions {
logit("Enter subroutine showSessions");
# Instance session information
my ($sql,$text,$cursor,$numfields,@row,$counter1,$counter2,$count,$paddr);
my (@username,@osuser,@sid,@serial,@status,@process,@program,@command);
my (@address,@hash_value,@rows_processed,@logon_time,@sqltext,$altersystem);
my ($mysid,$sessions,$sid,$serial,$user,$moresql);
$user = shift;
$sid = $query->param('sid') || "";
$serial = $query->param('serial') || "";
# Find out if the connected user has the "ALTER SYSTEM" privilege.
# This has nothing to do with the above $user variable.
$altersystem = checkPriv("ALTER SYSTEM");
refreshButton();
print "</CENTER>n";
# If user is not "%", then count the number of sessions
# and show a message if there is none.
if ( $user ne "%" ) {
$sql = "$copyright
SELECT COUNT(*) FROM V$SESSION
WHERE USERNAME = '$user'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$count = $cursor->fetchrow_array;
$cursor->finish;
if ( $count == 0 ) {
message("$user has no sessions in this instance.");
Footer();
exit;
}
} else {
# DisplayGraph("sessions","","Active and inactive session history");
}
# If a sid and serial# is passed, create the SQL to select only that session.
if ($sid && $serial) {
$moresql = "AND S.SID = $sid AND S.SERIAL# = $serialn";
} else {
$moresql = "";
}
# Get my SID
$sql = "$copyright
SELECT DISTINCT SID FROM V$MYSTAT
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$mysid = $cursor->fetchrow_array;
$cursor->finish;
$sql = "$copyright
SELECT DISTINCT
S.USERNAME,
S.OSUSER,
S.SID,
S.SERIAL#,
S.STATUS,
S.PROCESS,
S.PROGRAM,
DECODE(S.COMMAND,
'0','No command in progress',
'1','Create table',
'2','Insert',
'3','Select',
'4','Create cluster',
'5','Alter cluster',
'6','Update',
'7','Delete',
'8','Drop cluster',
'9','Create index',
'10','Drop index',
'11','Alter index',
'12','Drop table',
'13','Create sequence',
'14','Alter sequence',
'15','Alter table',
'16','Drop sequence',
'17','Grant',
'18','Revoke',
'19','Create synonym',
'20','Drop synonym',
'21','Create view',
'22','Drop view',
'23','Validate index',
'24','Create procedure',
'25','Alter procedure',
'26','Lock table',
'27','No operation in progress',
'28','Rename',
'29','Comment',
'30','Audit',
'31','Noaudit',
'32','Create database link',
'33','Drop database link',
'34','Create database',
'35','Alter database',
'36','Create rollback segment',
'37','Alter rollback segment',
'38','Drop rollback segment',
'39','Create tablespace',
'40','Alter tablespace',
'41','Drop tablespace',
'42','Alter session',
'43','Alter user',
'44','Commit',
'45','Rollback',
'46','Savepoint',
'47','PL/SQL Execute',
'48','Set transaction',
'49','Alter system switch log',
'50','Explain',
'51','Create user',
'52','Create role',
'53','Drop user',
'54','Drop role',
'55','Set role',
'56','Create schema',
'57','Create control file',
'58','Alter tracing',
'59','Create trigger',
'60','Alter trigger',
'61','Drop trigger',
'62','Analyze table',
'63','Analyze index',
'64','Analyze cluster',
'65','Create profile',
'66','Drop profile',
'67','Alter profile',
'68','Drop procedure',
'69','Drop procedure',
'70','Alter resource cost',
'71','Create snapshot log',
'72','Alter snapshot log',
'73','Drop snapshot log',
'74','Create snapshot',
'75','Alter snapshot',
'76','Drop snapshot',
'79','Alter role',
'85','Truncate table',
'86','Truncate cluster',
'88','Alter view',
'91','Create function',
'92','Alter function',
'93','Drop function',
'94','Create package',
'95','Alter package',
'96','Drop package',
'97','Create package body',
'98','Alter package body',
'99','Drop package body'),
TO_CHAR(S.LOGON_TIME,'Day MM/DD/YY HH24:MI'),
T.ADDRESS,
T.HASH_VALUE,
MAX(Q.ROWS_PROCESSED)
FROM V$SESSION S, V$SQLTEXT T, V$SQL Q
WHERE S.USERNAME IS NOT NULL
AND S.USERNAME LIKE '$user'
AND S.SQL_ADDRESS = T.ADDRESS(+)
AND S.SQL_ADDRESS = Q.ADDRESS(+)
AND S.SQL_HASH_VALUE = T.HASH_VALUE(+)
AND S.SQL_HASH_VALUE = Q.HASH_VALUE(+)
AND S.SID != $mysid
$moresql
GROUP BY S.USERNAME, S.OSUSER, S.SID, S.SERIAL#, S.STATUS, S.PROCESS, S.PROGRAM, S.COMMAND, S.LOGON_TIME, T.ADDRESS, T.HASH_VALUE
ORDER BY S.STATUS
";
$cursor = $dbh->prepare($sql);
print $DBI::errstr unless ($cursor);
$cursor->execute;
$numfields = $cursor->{NUM_OF_FIELDS};
$counter1=0;
while (@row = $cursor->fetchrow_array) {
$sessions++;
$username[$counter1] = $row[0] or $username[$counter1] = "&nbspc;";
$osuser[$counter1] = $row[1] or $osuser[$counter1] = "Unknown";
$sid[$counter1] = $row[2] or $sid[$counter1] = " ";
$serial[$counter1] = $row[3] or $serial[$counter1] = " ";
$status[$counter1] = $row[4] or $status[$counter1] = " ";
$process[$counter1] = $row[5] or $process[$counter1] = "Unknown";
$program[$counter1] = $row[6] or $program[$counter1] = "Unknown";
$command[$counter1] = $row[7] or $command[$counter1] = "Unknown";
$logon_time[$counter1] = $row[8] or $logon_time[$counter1] = "Unknown";
$address[$counter1] = $row[9] or $address[$counter1] = " ";
$hash_value[$counter1] = $row[10] or $hash_value[$counter1] = " ";
$rows_processed[$counter1] = $row[11] or $rows_processed[$counter1] = "None";
$counter1++;
}
$cursor->finish;
$counter2 = 0;
foreach (@address) {
$sql = "$copyright
SELECT
SQL_TEXT,
PIECE
FROM V$SQLTEXT
WHERE ADDRESS = '$address[$counter2]'
ORDER BY PIECE
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (@row = $cursor->fetchrow_array) {
$sqltext[$counter2] = "$sqltext[$counter2]$row[0]";
}
$cursor->finish;
# Check for processes with a ':' in them. These would be remote connections.
# Resolve the bad process ID's
if ($process[$counter2] =~ /:/) {
$sql = "$copyright
SELECT PADDR FROM V$SESSION
WHERE SID = $sid[$counter2]
AND SERIAL# = $serial[$counter2]
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$paddr = $cursor->fetchrow_array;
$cursor->finish;
$sql = "$copyright
SELECT SPID FROM V$PROCESS
WHERE ADDR = '$paddr'
";
$cursor=$dbh->prepare($sql);
$cursor->execute;
$process[$counter2] = $cursor->fetchrow_array;
$cursor->finish;
}
# Fix the SQL so it displays in HTML format correctly
$sqltext[$counter2] =~ s/"/"/g;
$sqltext[$counter2] =~ s/>/>/g;
$sqltext[$counter2] =~ s/</</g;
print << "EOF";
<TABLE BORDER =0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TH BGCOLOR='$headingcolor' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Ora user</TH>
<TH BGCOLOR='$headingcolor' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>OS user</TH>
<TH BGCOLOR='$headingcolor' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>SID</TH>
<TH BGCOLOR='$headingcolor' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Serial#</TH>
<TH BGCOLOR='$headingcolor' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Status</TH>
<TH BGCOLOR='$headingcolor' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Process</TH>
<TH BGCOLOR='$headingcolor' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Program</TH>
<TH BGCOLOR='$headingcolor' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Command</TH>
<TH BGCOLOR='$headingcolor' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Rows</TH>
<TH BGCOLOR='$headingcolor' ALIGN=LEFT><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Logon time</TH>
<TR>
<TD BGCOLOR='$headingcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$username[$counter2]</TD>
<TD BGCOLOR='$headingcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$osuser[$counter2]</TD>
<TD BGCOLOR='$headingcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$sid[$counter2]</TD>
<TD BGCOLOR='$headingcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$serial[$counter2]</TD>
<TD BGCOLOR='$headingcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$status[$counter2]</TD>
<TD BGCOLOR='$headingcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$process[$counter2]</TD>
<TD BGCOLOR='$headingcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$program[$counter2]</TD>
<TD BGCOLOR='$headingcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$command[$counter2]</TD>
<TD BGCOLOR='$headingcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$rows_processed[$counter2]</TD>
<TD BGCOLOR='$headingcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$logon_time[$counter2]</TD>
</TR>
EOF
if ($sqltext[$counter2]) {
print <<"EOF";
<TR>
<TD BGCOLOR='$cellcolor' COLSPAN=10><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$sqltext[$counter2]<P>
<TABLE>
<TR>
EOF
} else {
print <<"EOF";
<TR>
<TD BGCOLOR='$cellcolor' COLSPAN=10><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>No SQL<P>
<TABLE>
<TR>
EOF
}
print <<"EOF";
<TD>
<FORM METHOD=POST ACTION="$scriptname">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE=HIDDEN NAME="object_type" VALUE="SESSIONSTATS">
<INPUT TYPE=HIDDEN NAME="database" VALUE="$database">
<INPUT TYPE=HIDDEN NAME="schema" VALUE="$sid[$counter2]">
<INPUT TYPE=SUBMIT VALUE="Session stats">
</FORM>
</TD>
EOF
if ($sqltext[$counter2]) {
print <<"EOF";
<TD>
<FORM METHOD=POST ACTION="$scriptname">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE=HIDDEN NAME="object_type" VALUE="EXPLAIN">
<INPUT TYPE=HIDDEN NAME="database" VALUE="$database">
<INPUT TYPE=HIDDEN NAME="schema" VALUE="$sid[$counter2]">
<INPUT TYPE=HIDDEN NAME="explainschema" VALUE="$username[$counter2]">
<INPUT TYPE=HIDDEN NAME="arg" VALUE="$sqltext[$counter2]">
<INPUT TYPE=SUBMIT VALUE="Explain plan">
</FORM>
</TD>
EOF
}
if (($altersystem) && ($status[$counter2] ne "KILLED")) {
print <<"EOF";
<TD>
<FORM METHOD=POST ACTION="$scriptname">
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
          
          
<INPUT TYPE=HIDDEN NAME="object_type" VALUE="KILLSESSION">
<INPUT TYPE=HIDDEN NAME="database" VALUE="$database">
<INPUT TYPE=HIDDEN NAME="arg" VALUE="$sid[$counter2]">
<INPUT TYPE=HIDDEN NAME="schema" VALUE="$serial[$counter2]">
<INPUT TYPE=SUBMIT VALUE="Kill session">
</FORM>
</TD>
EOF
}
print <<"EOF";
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
<HR WIDTH="5%" ALIGN=LEFT>
EOF
$counter2++;
}
print "<CENTER>n";
message("No sessions to display.") unless ($sessions);
refreshButton("10");
logit("Exit subroutine showSessions");
}
sub GetTNS {
logit("Enter subroutine GetTNS");
# Usage: @tns_entries = GetTNS();
# If you have ever wondered why Russia was first in space, check out the hacks below!
# Many thanks to Dima Dorofeev.
# Returns the database connection strings defined in the tnsnames.ora.
# Also removes duplicates and sorts alphabetically. Duplicates can show
# up because the DBI->data_sources checks both the tnsnames.ora file
# and the oratab file. There are several possibilities here, I'm waiting
# to see if there are any complaints before I remove the others.
# 1 my %hash = map { (split(':'))[-1] , undef } DBI->data_sources('Oracle');
# 2 my %hash = map { /:(w[w-]*)(?:.world){0,1}$/i , undef } DBI->data_sources('Oracle');
# delete($hash{""});
# 3 my %hash = map { /:([.w]+)(?:.world){0,1}$/i , undef } DBI->data_sources('Oracle');
my %hash = map { (split(/.world/i,(split(':'))[-1]))[0] , undef } DBI->data_sources('Oracle');
return sort keys %hash;
logit("Exit subroutine GetTNS");
}
sub ObjectTable {
logit("Enter subroutine ObjectTable");
# Usage: ObjectTable ($dbh,$sql,$text,$infotext);
# This sub is specifically for displaying a table with
# database object name, type, and owner.
# It will make each entry a hyperlink to obtain additional
# information about the object.
my $sql = shift;
my $text = shift;
my $infotext = shift;
my ($cursor,@row,$object_name,$object_type,$schema,$count,$numfields,$field,$name);
$infotext = "<FONT COLOR="$infocolor">$infotext</FONT>";
$cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr");
$cursor->execute or ErrorPage ("$DBI::errstr");
$count = 0;
while (@row = $cursor->fetchrow_array) {
$count++;
}
$cursor->finish or ErrorPage ("$DBI::errstr");
if ($count != 0) {
$cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr");
$cursor->execute or ErrorPage ("$DBI::errstr");
print "<P><B>$text</B></P>n" if defined $text;
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";
$cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr");
$cursor->execute or ErrorPage ("$DBI::errstr");
$numfields = $cursor->{NUM_OF_FIELDS};
for ($field=0; $field < $numfields; $field++) {
$name = $cursor->{NAME}->[$field];
print " <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>$name</TH>";
}
while (@row = $cursor->fetchrow_array) {
$object_name =$row[0];
$object_type =$row[1];
$schema =$row[2];
print " <TR ALIGN=LEFT>";
$_ = $row[0];
s/ /+/;
$object_name = $_;
$_ = $row[1];
s/ /+/;
$object_type = $_;
for ($field=0; $field < $numfields; $field++) {
print " <TD BGCOLOR='$cellcolor'";
print " ALIGN=RIGHT" if ($row[$field] =~ /^s*.?d/);
if ($field == 0) {
print "><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A href=$scriptname?database=$database&arg=$object_name&object_type=$object_type&schema=$schema>$row[$field]</A></TD>n";
} else {
print "><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$row[$field]</TD>n";
}
}
print " </TR>n";
}
print " </TABLE>n";
print " </TD>n";
print " </TR>n";
print "</TABLE>n";
$cursor->finish or ErrorPage ("$DBI::errstr");
} else {
print "<P><B>$infotext</B></P>n" if ( defined $infotext );
}
logit("Exit subroutine ObjectTable");
if ($DBI::errstr) {
return($DBI::errstr);
} else {
return($count);
}
}
sub DisplayColTable {
logit("Enter subroutine DisplayColTable");
# Usage: DisplayColTable ($sql,$text,$link,$infotext,$cols);
# This sub is for displaying a table of 'n' columns
# wide from a query that returns a single column of
# data.
# The first arg is the SQL you want to execute.
# The second arg (text) is for optionally putting a text description
# of the outputted data above the table.
# The third argument is to optionally make the table output
# a hyperlink. the hyperlink will use the data in the first column
# as an argument to whatever the link is pointing to.
# The fourth argument is the text that you want to display if
# no rows are returned from the query.
# The fifth argument is the number of columns wide you want the table
# to be.
# The sixth argument is if you want a checkbox beside each entry
# The seventh argument is the target (read by Director()) to go to.
# The eight (Geez!) argument is to set a value to the hidden param "command"
my $sql = shift;
my $text = shift;
my $link = shift;
my $infotext = shift;
my $cols = shift;
my $checkbox = shift;
my $target = shift;
my $submittext = shift;
my $command = shift;
my $counter = 0;
my ($cursor,$row,$i,@row,$count,$skip,$arg);
$dbh->{LongReadLen} = 2048;
$infotext = "<FONT COLOR="$infocolor">$infotext</FONT>";
$cursor = $dbh->prepare($sql);
$cursor->execute;
$count = 0;
while (@row = $cursor->fetchrow_array) {
$count++;
}
if ($count <= $cols) {
$cols = $count;
}
$cursor->finish;
if ($count != 0) {
print "<P><B>$text</B></P>n" if defined $text;
print "<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>n";
if ($checkbox) {
print <<"EOF";
<FORM METHOD=POST ACTION=$scriptname>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="$target">
<INPUT TYPE="HIDDEN" NAME="command" VALUE="$command">
EOF
}
print " <TR>n";
print " <TD WIDTH=100%>n";
print " <TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>n";
$cursor = $dbh->prepare($sql) or return($DBI::errstr);
$cursor->execute or return($DBI::errstr);
while ($row = $cursor->fetchrow_array) {
$arg = $row;
$arg =~ s/ /+/;
print " <TR ALIGN=CENTER>" if $counter == 0;
if ($link) {
print " <TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A href=$link&arg=$arg>$row</A></TD>n";
} else {
if ($checkbox) {
print " <TD ALIGN=LEFT BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><INPUT TYPE=CHECKBOX NAME=checked~$row> $row</TD>n";
} else {
print " <TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$row</TD>n";
}
}
$counter++;
print " </TR>" if $counter == 0;
$skip = "";
if ($counter == $cols) {
$counter = 0;
$skip = "Y";
}
}
if ((! $skip) && ($counter < $cols)) {
for ($i = $counter; $i < $cols; $i++) {
print " <TD BGCOLOR='$cellcolor'> </TD>n";
}
}
print " </TABLE>n";
print " </TD>n";
print " </TR>n";
if ($checkbox) {
print <<"EOF";
<INPUT TYPE=SUBMIT VALUE="$submittext">
</FORM>
EOF
}
print "</TABLE>n";
$cursor->finish;
} else {
print "<P><B>$infotext</B></P>n" if ( defined $infotext );
}
logit("Exit subroutine DisplayColTable");
if ($DBI::errstr) {
return($DBI::errstr);
} else {
return($count);
}
}
sub DisplayTable {
logit("Enter subroutine DisplayTable");
# Usage: DisplayTable ($sql,$text,$link,$infotext);
# This sub is for formatting the output of a SQL query. The
# output will have the column headings in bold with the data
# in a HTML table.
# The first arg is the SQL you want to execute.
# The second arg (text) is for optionally putting a text description
# of the outputted data above the table.
# The third argument is to optionally make the table output
# a hyperlink. the hyperlink will use the data in the first column
# as an argument to whatever the link is pointing to.
# The fourth argument is the text that you want to display if
# no rows are returned from the query.
# The fifth argument is for optionally specifying a set number of
# rows to return.
my $sql = shift || "";
my $text = shift || "";
my $link = shift || "";
my $infotext = shift || "";
my $rows = shift || "";
my $count = 0;
logit(" Link passed = $link") if $link;
# Change spaces to +'s on links passed.
$link =~ s/ /+/g if $link;
$dbh->{LongReadLen} = 2048;
$dbh->{LongTruncOk} = 1;
$infotext = "<FONT COLOR="$infocolor">$infotext</FONT>" if $infotext;
my ($cursor,@row,$numfields,$field,$name,$arg);
if ( $link eq "" ) {
undef $link;
} else {
$link =~ tr/ /+/;
}
$cursor = $dbh->prepare($sql) or return($DBI::errstr);
$cursor->execute or return($DBI::errstr);
while (@row = $cursor->fetchrow_array) {
$count++;
}
logit(" Rows returned: $count");
$cursor->finish or return("$DBI::errstr");
if ($count != 0) {
$count = 0;
print "<P><B>$text</B></P>n" if defined $text;
print "<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>n";
print "<TR><TD WIDTH=100%>n";
print "<TABLE BORDER=0 cellpadding=2 cellspacing=1>n";
$cursor = $dbh->prepare($sql) or return($DBI::errstr);
$cursor->execute or return($DBI::errstr);
$numfields = $cursor->{NUM_OF_FIELDS};
for ($field=0; $field < $numfields; $field++) {
$name = $cursor->{NAME}->[$field];
print "<TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>$name</TH>";
}
print "n";
while (@row = $cursor->fetchrow_array) {
$count++;
print "<TR ALIGN=LEFT>";
for (my $field=0; $field < $numfields; $field++) {
if ($field == 0) {
# Change spaces to +'s, and escape all unsafe characters
$_ = $row[$field];
s/ /+/g;
s/#/%23/g;
s/</%3C/g;
s/>/%3E/g;
s/{/%7B/g;
s/|/%7C/g;
s/}/%7D/g;
s/\/%5C/g;
s/^/%5E/g;
$arg = $_;
}
print "<TD VALIGN=TOP BGCOLOR='$cellcolor'";
if ($row[$field] ne "") {
print " ALIGN=RIGHT" if ($row[$field] =~ /^s*.?d/);
# Change spaces to real HTML spaces and fix HTML characters.
$row[$field] =~ s/s/ /g;
# $row[$field] =~s/&/&/g;
$row[$field] =~s/"/"/g;
$row[$field] =~s/>/>/g;
$row[$field] =~s/</</g;
if (($link) && ($field == 0)) {
print "><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A href=$link&arg=$arg>$row[$field]</A></TD>n";
} else {
print "><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$row[$field]</TD>n";
}
} else {
print "> </TD>n";
}
}
print "</TR>n";
if ($rows) {
last if ($count > $rows);
}
}
print "</TABLE></TD></TR>n";
print "</TABLE>n";
$cursor->finish or return("$DBI::errstr");
} else {
print "<P><B>$infotext</B></P>n" if ( defined $infotext );
}
logit("Exit subroutine DisplayTable");
return($count);
}
sub DisplayPiecedData {
logit("Enter subroutine DisplayPiecedData");
# Usage: DisplayPiecedData ($sql,$text,$link)
# This is for formatting the output of a SQL query. The
# output will have the column headings in bold with the data
# in a HTML table. This sub should be used for data retrieved
# from tables which have the data in pieces such as dba_source,
# and v$sql.
# The first arg is the SQL you want to execute.
# The second arg (text) is for optionally putting a text description
# of the outputted data above the table.
# The third argument is to optionally make the table output
# a hyperlink. the hyperlink will use the data in the first column
# as an argument to whatever the link is pointing to.
my $sql = shift || "";
my $text = shift || "";
my $link = shift || "";
my $numbers = shift || "";
my $count = 0;
my ($cursor,@row,$field,$name,$arg,$row,$data);
my (@lines,$line,$linecounter);
if ( $link eq "" ) {
undef $link;
} else {
$link =~ tr/ /+/;
}
$dbh->{LongReadLen} = 10240;
$dbh->{LongTruncOk} = 1;
$data= "";
$cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr");
$cursor->execute or ErrorPage ("$DBI::errstr");
while (@row = $cursor->fetchrow_array) {
$count++;
}
$cursor->finish or ErrorPage ("$DBI::errstr");
if ($count != 0) {
$cursor = $dbh->prepare($sql) or ErrorPage ("$DBI::errstr");
$cursor->execute or ErrorPage ("$DBI::errstr");
while ($_ = $cursor->fetchrow_array) {
s/</</g;
s/>/>/g;
$row = $_;
$data = "$data$row";
}
print "<P><B>$text</B></P>n" if defined $text;
print "<TABLE BORDER=0>n";
print " <TR>n";
print " <TD>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 BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Recreate text</TH>n";
print " <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Debug text</TH>n";
print " <TR>n";
print " <TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><PRE>$data</PRE></TD>" if ! defined $link;
print " <TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><PRE><A href=$link&arg=$arg>$data</A></PRE></TD>" if defined $link;
@lines = split /n/, $data;
$linecounter = 0;
print " <TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><PRE>nn";
foreach $line (@lines) {
$linecounter++;
print "<FONT COLOR=$linkcolor><$linecounter></FONT> $line<BR>" if ! defined $link;
print "<A href=$link&arg=$arg><FONT COLOR=$linkcolor><$linecounter></FONT> $line</A><BR>" if defined $link;
}
print " </PRE>n";
print " </TD>n";
print " </TR>";
print " </TABLE>n";
print " </TD>n";
print " </TR>n";
print " </TABLE>n";
print " </TD>n";
print " </TR>n";
print "</TABLE>n";
$cursor->finish or ErrorPage ("$DBI::errstr");
}
logit("Exit subroutine DisplayPiecedData");
}
sub Header {
# Usage: Header ($title,$heading,$font,$fontsize,$fontcolor,$bgcolor);
logit("Enter subroutine Header");
my ($title,$heading,$font,$fontsize,$fontcolor,$bgcolor,$headertype);
my ($refreshrate,$url,$arg,$sortfield,$bgline);
# Creates a HTML header with title
$title = shift || "";
$heading = shift || "";
$font = shift || "";
$fontsize = shift || "";
$fontcolor = shift || "";
$bgcolor = shift || "";
$headertype = $query->param('headertype') || "";
$refreshrate = $query->param('refreshrate') || "";
$sortfield = $query->param('sortfield') || "";
$url = $scriptname;
$url .= "?database=$database" if $database;
$url .= "&user=$user" if $user;
$url .= "&schema=$schema" if $schema;
$url .= "&object_type=$object_type" if $object_type;
$url .= "&arg=$arg" if $arg;
$url .= "&refreshrate=$refreshrate" if $refreshrate;
$url .= "&sortfield=$sortfield" if $sortfield;
if ($headertype eq "octet") {
binmode(STDOUT);
print header(-type => 'Oracletool');
# print header(-type => 'application/octet-stream');
} else {
print header(-type => 'text/html');
}
$bgline = "<BODY BGCOLOR=$bgcolor LINK=$linkcolor ALINK=$linkcolor VLINK=$linkcolor>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 LINK=$linkcolor ALINK=$linkcolor VLINK=$linkcolor>n";
}
}
unless ($headertype eq "octet") {
print << "EOF";
<HTML>
<HEAD>
EOF
# Set a refresh rate for the page if desired
if ($refreshrate) {
logit(" Refresh rate of $refreshrate set. URL = $url");
print "<META HTTP-EQUIV="Refresh" Content="$refreshrate;URL=$url">n";
}
print << "EOF";
<META HTTP-EQUIV="pragma" CONTENT="nocache">
<TITLE>$title</TITLE>
</HEAD>
$bgline
EOF
print << "EOF";
<FONT FACE="$font" SIZE="$fontsize" COLOR="$fontcolor">
<BR><BR>
<CENTER>
EOF
# The following was added to send a message to people I noticed were
# trying to break into my demo database.
# my (@forbidden,$forbidden_message,$forbidden_ip);
# @forbidden = ("");
# $forbidden_message = "Hello, $ENV{'REMOTE_ADDR'}. This demo has been placed here at the expense of the author to distribute useful software for free. It was not put here for you to exercise your cracking skills. Please go away.";
#
# foreach $forbidden_ip(@forbidden) {
# if ($ENV{'REMOTE_ADDR'} eq $forbidden_ip) {
# message($forbidden_message);
# exit;
# }
# }
# if ( $heading ne "" ) {
print "$heading";
# }
}
logit("Exit subroutine Header");
}
sub framePage {
logit("Enter subroutine framePage");
# Usage: framePage ($title,$heading,$font,$fontsize,$fontcolor,$bgcolor);
# Creates a HTML header with title
my $title = shift;
my $heading = shift;
my $font = shift;
my $fontsize = shift;
my $fontcolor = shift;
my $bgcolor = shift;
my $schema = uc($username);
print << "EOF";
Content-type: Text/htmlnn
<HTML>
<HEAD>
<TITLE>$database: Oracletool v$VERSION connected as $schema</TITLE>
</HEAD>
<FRAMESET COLS="150,*" BORDER="0">
<FRAME NAME="menu" SRC="$scriptname?database=$database&object_type=MENU">
<FRAME NAME="body" SRC="$scriptname?database=$database&schema=$schema&object_type=LISTUSERS">
</FRAMESET>
</HTML>
EOF
logit("Exit subroutine framePage");
exit;
}
sub statsPackMenu {
logit("Enter subroutine statsPackMenu");
my ($sql,$cursor,$snap_count,$min_snap,$max_snap,$db_bounces);
$sql = "
SELECT * FROM
(SELECT TO_CHAR(COUNT(SNAP_ID ),'999,999,999,999')
FROM PERFSTAT.STATS$SNAPSHOT),
(SELECT TO_CHAR(MIN(SNAP_TIME),'Day, Month DD YYYY @ HH24:MI:SS')
FROM PERFSTAT.STATS$SNAPSHOT),
(SELECT TO_CHAR(MAX(SNAP_TIME),'Day, Month DD YYYY @ HH24:MI:SS')
FROM PERFSTAT.STATS$SNAPSHOT),
(SELECT TO_CHAR(COUNT(DISTINCT(STARTUP_TIME)),'999,999,999,999')
FROM PERFSTAT.STATS$SNAPSHOT)
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
($snap_count,$min_snap,$max_snap,$db_bounces) = $cursor->fetchrow_array;
$cursor->finish;
logit(" #Snap records = $snap_count");
text("You have $snap_count snapshots available for analyzation spanning $db_bounces database startups.<BR>Oldest snapshot is $min_snap: Most recent is $max_snap.");
text("Oracle Statspack functions.");
Button("$scriptname?database=$database&object_type=STATSPACKADMIN&command=snapshot TARGET=body","Execute a snapshot","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=STATSPACKADMIN&command=statsgroups TARGET=body","Snapshot analyzation / admin","$headingcolor","CENTER","200");
logit("Exit subroutine statsPackMenu");
}
sub rollbackMenu {
logit("Enter subroutine rollbackMenu");
Button("$scriptname?database=$database&object_type=SHOWROLLBACKS&command=recentbackup TARGET=body","Rollback segment information","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=SHOWTRANSACTIONS&command=recentbackup TARGET=body","Transaction information","$headingcolor","CENTER","200");
logit("Exit subroutine rollbackMenu");
}
sub backupMenu {
logit("Enter subroutine backupMenu");
if (backupsFound() && rmanCatalogExists()) {
logit(" This database is backed up by RMAN and has a RMAN catalog.");
Button("$scriptname?database=$database&object_type=RMANBACKUPS&command=recentbackup TARGET=body","RMAN info via controlfiles","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=RMANCATALOGQUERY TARGET=body","RMAN info via catalog","$headingcolor","CENTER","200");
return(0);
} elsif (backupsFound()) {
logit(" This database is backed up by RMAN, but has no RMAN catalog(s).");
rmanBackups("menu");
} elsif (rmanCatalogExists()) {
logit(" This database is not backed up by RMAN, but has a RMAN catalog.");
rmanCatalogQuery();
} else {
logit(" This database is not backed up by RMAN, nor does it have any RMAN catalogs.");
message("This database is not backed up using RMAN.");
message("This database contains no RMAN backup catalogs.");
}
logit("Exit subroutine backupMenu");
}
sub perfMenu {
logit("Enter subroutine perfMenu");
print "</CENTER></FONT>n";
Button("$scriptname?database=$database&object_type=PERFORMANCE TARGET=body","Memory allocation & resources","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=SQLAREALIST TARGET=body","Shared SQL area","$headingcolor","CENTER","200");
logit("Exit subroutine perfMenu");
}
sub sessionMenu {
logit("Enter subroutine sessionMenu");
print "</CENTER></FONT>n";
Button("$scriptname?database=$database&object_type=SESSIONS TARGET=body","Detailed session listing","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=TOPSESSIONS TARGET=body","Top sessions","$headingcolor","CENTER","200");
logit("Exit subroutine sessionMenu");
}
sub auditMenu {
logit("Enter subroutine auditMenu");
print "</CENTER></FONT>n";
Button("$scriptname?database=$database&object_type=AUDITADMIN&command=schemaobjects TARGET=body","Schema object auditing","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=ENTERAUDITS&command=statementobjects TARGET=body","SQL statement auditing","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=ENTERAUDITS&command=systemobjects TARGET=body","System privilege auditing","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=AUDITLIST TARGET=body","Remove audits","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=AUDITING TARGET=body","Display auditing records","$headingcolor","CENTER","200");
logit("Exit subroutine auditMenu");
}
sub prefMenu {
logit("Enter subroutine prefMenu");
print "</FONT>n";
Button("$scriptname?database=$database&object_type=SHOWPROPS TARGET=body","Fonts etc.","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=SHOWTHEMES TARGET=body","Themes","$headingcolor","CENTER","200");
logit("Exit subroutine prefMenu");
}
sub taskMenu {
logit("Enter subroutine taskMenu");
my $count;
print "</FONT>n";
text("Database administration");
if (checkPriv("CREATE USER")) {
Button("$scriptname?database=$database&object_type=ENTERCREATEUSER TARGET=body","User administration","$headingcolor","CENTER","200");
} else {
Button("","User administration","$headingcolor","CENTER","200");
$count++;
}
if (checkPriv("ALTER SYSTEM")) {
Button("$scriptname?database=$database&object_type=SESSIONLIST TARGET=body","Session administration","$headingcolor","CENTER","200");
} else {
Button("","Session administration","$headingcolor","CENTER","200");
$count++;
}
if ( Auditing()) {
if (checkPriv("AUDIT ANY")) {
Button("$scriptname?database=$database&object_type=AUDITMENU TARGET=body","Auditing administration","$headingcolor","CENTER","200");
} else {
Button("","Auditing administration","$headingcolor","CENTER","200");
$count++;
}
}
if (checkPriv("ALTER ROLLBACK SEGMENT")) {
Button("$scriptname?database=$database&object_type=RBSLIST TARGET=body","Rollback segment administration","$headingcolor","CENTER","200");
} else {
Button("","Rollback segment administration","$headingcolor","CENTER","200");
$count++;
}
Button("$scriptname?database=$database&object_type=ENTERDDLTABLES TARGET=body","Generate table DDL","$headingcolor","CENTER","200");
# Button("$scriptname?database=$database&object_type=ENTERDEPENDENCYOBJECT TARGET=body","List dependencies for an object","$headingcolor","CENTER","200");
if (checkPriv("ALTER ANY PROCEDURE")) {
Button("$scriptname?database=$database&object_type=OBJECTADMIN TARGET=body","Invalid object administration","$headingcolor","CENTER","200");
} else {
Button("","Object administration","$headingcolor","CENTER","200");
$count++;
}
# if (checkPriv("CREATE TABLESPACE")) {
# Button("$scriptname?database=$database&object_type=CREATETABLESPACE TARGET=body","Create tablespace","$headingcolor","CENTER","200");
# }
text("Database reports");
Button("$scriptname?database=$database&object_type=USERSPACEREPORT TARGET=body","Space report by user","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=TSSPACEREPORT TARGET=body","Space report by tablespace / user","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=FILEFRAGREPORT TARGET=body","Datafile fragmentation report","$headingcolor","CENTER","200");
Button("$scriptname?database=$database&object_type=ENTEREXTENTREPORT TARGET=body","Object extent report","$headingcolor","CENTER","200");
$username = uc($username);
message("<CENTER>You ($username) do not have authority to enter one or more of the DBA areas.") if $count;
logit("Exit subroutine taskMenu");
}
sub objectAdmin {
logit("Enter subroutine objectAdmin");
invalidObjectList();
logit("Exit subroutine objectAdmin");
}
sub auditAdmin {
my $command = $query->param('command');
logit("Enter subroutine auditAdmin");
my ($sql,$text,$link,$infotext,$cols,$checkbox,$target,$submittext);
$sql = "SELECT USERNAME FROM DBA_USERS ORDER BY USERNAME";
$text = "Schema object auditing: Select one or more users to set auditing options.";
$link = "";
$infotext = "";
$cols = $schema_cols;
$checkbox = "Yep";
$target = "ENTERAUDITS";
$submittext = "Choose audit options";
$command = "$command";
DisplayColTable($sql,$text,$link,$infotext,$cols,$checkbox,$target,$submittext,$command);
logit("Exit subroutine auditAdmin");
}
sub enterAudits {
logit("Enter subroutine enterAudits");
my ($sql,$cursor,$privilege,@statement_options,@system_privs);
my ($foo,@users,@sqlusers,$username,@params,$param,$count);
my ($tables,$views,$sequences,$procedures,$functions,$packages);
my ($libraries,$directories,$owner,$object_name,$command);
my ($statement_option);
$command = $query->param('command');
# Get a list of the usernames passed
@params = $query->param;
if ($command eq "systemobjects") {
message("System privilege auditing.<BR>Multiple statements and users may be selected by holding down the <CTRL> key. The statement options you choose will be set for all users that you have highlighted.");
print <<"EOF";
</CENTER>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="dostatementaudits">
<B>AUDIT 
<SELECT SIZE=5 NAME=privilege MULTIPLE>
EOF
$sql = "$copyright
SELECT
DISTINCT PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE PRIVILEGE NOT LIKE '% ANY %'
ORDER BY PRIVILEGE
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ($privilege = $cursor->fetchrow_array) {
print "<OPTION>$privilegen";
}
$cursor->finish;
print "</SELECT>nBY <SELECT SIZE=5 NAME=users MULTIPLE>";
$sql = "$copyright
SELECT
USERNAME
FROM DBA_USERS
ORDER BY USERNAME
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ($username = $cursor->fetchrow_array) {
print "<OPTION>$usernamen";
}
$cursor->finish;
print <<"EOF";
</SELECT>
<BR>
BY 
<INPUT TYPE="RADIO" NAME="by" VALUE="SESSION" CHECKED>session
<INPUT TYPE="RADIO" NAME="by" VALUE="ACCESS">access
<BR>
WHENEVER 
<INPUT TYPE="CHECKBOX" NAME="whenever~SUCCESSFUL" CHECKED>successful
<INPUT TYPE="CHECKBOX" NAME="whenever~NOTSUCCESSFUL" CHECKED>not successful
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Submit change">
</FORM>
EOF
}
if ($command eq "statementobjects") {
@statement_options = (
"CLUSTER",
"DATABASE LINK",
"DIRECTORY",
"INDEX",
"NOT EXISTS",
"PROCEDURE",
"PROFILE",
"PUBLIC DATABASE LINK",
"PUBLIC SYNONYM",
"ROLE",
"ROLLBACK SEGMENT",
"SEQUENCE",
"SESSION",
"SYNONYM",
"SYSTEM AUDIT",
"SYSTEM GRANT",
"TABLE",
"TABLESPACE",
"TRIGGER",
"USER",
"VIEW",
"ALTER SEQUENCE",
"ALTER TABLE",
"COMMENT TABLE",
"DELETE TABLE",
"EXECUTE PROCEDURE",
"GRANT DIRECTORY",
"GRANT PROCEDURE",
"GRANT SEQUENCE",
"GRANT TABLE",
"INSERT TABLE",
"LOCK TABLE",
"SELECT SEQUENCE",
"SELECT TABLE",
"UPDATE TABLE"
);
message("SQL statement auditing.<BR>Multiple statements and users may be selected by holding down the <CTRL> key. The statement options you choose will be set for all users that you have highlighted.");
print <<"EOF";
</CENTER>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="dostatementaudits">
<B>AUDIT 
<SELECT SIZE=5 NAME=privilege MULTIPLE>
EOF
foreach $statement_option (@statement_options) {
print "<OPTION>$statement_optionn";
}
print "</SELECT>nBY <SELECT SIZE=5 NAME=users MULTIPLE>";
$sql = "$copyright
SELECT
USERNAME FROM DBA_USERS
ORDER BY USERNAME
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ($username = $cursor->fetchrow_array) {
print "<OPTION>$usernamen";
}
$cursor->finish;
print <<"EOF";
</SELECT>
<BR>
BY 
<INPUT TYPE="RADIO" NAME="by" VALUE="SESSION" CHECKED>session
<INPUT TYPE="RADIO" NAME="by" VALUE="ACCESS">access
<BR>
WHENEVER 
<INPUT TYPE="CHECKBOX" NAME="whenever~SUCCESSFUL" CHECKED>successful
<INPUT TYPE="CHECKBOX" NAME="whenever~NOTSUCCESSFUL" CHECKED>not successful
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Submit change">
</FORM>
EOF
}
if ($command eq "schemaobjects") {
foreach $param(@params) {
if ($param =~ /checked~/) {
$count++;
($foo,$username) = split("~", $param);
push @users, $username;
push @sqlusers, "'$username'";
logit(" Username = $username");
}
}
logit(" Number of users passed: $count");
unless ($count) {
message("You must select at least one user!");
footer();
}
# Join the usernames to be suitable for a "IN"
# clause.
@sqlusers = join(",", @sqlusers);
logit(" Users = @users");
logit(" SQL Users = @sqlusers");
message("Schema object auditing.<BR>Multiple object names may be selected by holding down the <CTRL> key. The auditing options you choose will be set for all objects that you have highlighted.");
# Find out how many of each auditable object
# We have to work with. Snapshots are not
# included here, as they don't seem to have
# an object type# associated with them.
$sql = "$copyright
SELECT
COUNT(DECODE(TYPE, 2, OBJ#, '')) "Table",
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, 22, OBJ#, '')) "Library",
COUNT(DECODE(TYPE, 23, OBJ#, '')) "Directory"
FROM SYS.OBJ$
WHERE OWNER# IN
(
SELECT USER_ID
FROM DBA_USERS
WHERE USERNAME IN (@sqlusers)
)
" if $oracle7;
$sql = "$copyright
SELECT
COUNT(DECODE(TYPE#, 2, OBJ#, '')) "Table",
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#, 22, OBJ#, '')) "Library",
COUNT(DECODE(TYPE#, 23, OBJ#, '')) "Directory"
FROM SYS.OBJ$
WHERE OWNER# IN
(
SELECT USER_ID
FROM DBA_USERS
WHERE USERNAME IN (@sqlusers)
)
" if $oracle8;
$cursor=$dbh->prepare($sql);
$cursor->execute;
($tables,$views,$sequences,$procedures,$functions,$packages,$libraries,$directories) = $cursor->fetchrow_array;
$cursor->finish;
logit(" Tables - $tables: Views - $views: Sequences - $sequences: Procedures - $procedures: Functions - $functions: Packages - $packages: Libraries - $libraries: Directories - $directories");
# Start cycling through the object types, displaying them
# if any exist for any of the selected schemas.
if ($tables) {
text("Audit table objects.");
$sql = "$copyright
SELECT
OWNER,
TABLE_NAME
FROM DBA_TABLES
WHERE OWNER IN (@sqlusers)
ORDER BY OWNER, TABLE_NAME
";
print <<"EOF";
</CENTER>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="doschemaaudits">
<B>AUDIT 
<INPUT TYPE="CHECKBOX" NAME="audit~ALL">All  --
<INPUT TYPE="CHECKBOX" NAME="audit~ALTER">Alter,
<INPUT TYPE="CHECKBOX" NAME="audit~AUDIT">Audit,
<INPUT TYPE="CHECKBOX" NAME="audit~COMMENT">Comment,
<INPUT TYPE="CHECKBOX" NAME="audit~DELETE">Delete,
<INPUT TYPE="CHECKBOX" NAME="audit~GRANT">Grant,
<INPUT TYPE="CHECKBOX" NAME="audit~INDEX">Index,
<INPUT TYPE="CHECKBOX" NAME="audit~INSERT">Insert,
<INPUT TYPE="CHECKBOX" NAME="audit~LOCK">Lock,
<INPUT TYPE="CHECKBOX" NAME="audit~RENAME">Rename,
<INPUT TYPE="CHECKBOX" NAME="audit~SELECT">Select,
<INPUT TYPE="CHECKBOX" NAME="audit~UPDATE">Update
<BR>
ON&nbsp
<SELECT SIZE=5 NAME=object MULTIPLE>
EOF
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($owner,$object_name) = $cursor->fetchrow_array) {
print "<OPTION>$owner.$object_namen";
}
print <<"EOF";
</SELECT>
<BR>
BY 
<INPUT TYPE="RADIO" NAME="by" VALUE="SESSION" CHECKED>session
<INPUT TYPE="RADIO" NAME="by" VALUE="ACCESS">access
<BR>
WHENEVER 
<INPUT TYPE="CHECKBOX" NAME="whenever~SUCCESSFUL" CHECKED>successful
<INPUT TYPE="CHECKBOX" NAME="whenever~NOTSUCCESSFUL" CHECKED>not successful
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Submit change">
</FORM>
<CENTER>
<P><HR WIDTH=90%><P>
EOF
}
if ($views) {
text("Audit view objects.");
$sql = "$copyright
SELECT
OWNER,
VIEW_NAME
FROM DBA_VIEWS
WHERE OWNER IN (@sqlusers)
ORDER BY OWNER, VIEW_NAME
";
print <<"EOF";
</CENTER>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="doschemaaudits">
<B>AUDIT 
<INPUT TYPE="CHECKBOX" NAME="audit~ALL">All  --
<INPUT TYPE="CHECKBOX" NAME="audit~AUDIT">Audit,
<INPUT TYPE="CHECKBOX" NAME="audit~COMMENT">Comment,
<INPUT TYPE="CHECKBOX" NAME="audit~DELETE">Delete,
<INPUT TYPE="CHECKBOX" NAME="audit~GRANT">Grant,
<INPUT TYPE="CHECKBOX" NAME="audit~INSERT">Insert,
<INPUT TYPE="CHECKBOX" NAME="audit~LOCK">Lock,
<INPUT TYPE="CHECKBOX" NAME="audit~RENAME">Rename,
<INPUT TYPE="CHECKBOX" NAME="audit~SELECT">Select,
<INPUT TYPE="CHECKBOX" NAME="audit~UPDATE">Update
<BR>
ON&nbsp
<SELECT SIZE=5 NAME=object MULTIPLE>
EOF
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($owner,$object_name) = $cursor->fetchrow_array) {
print "<OPTION>$owner.$object_namen";
}
print <<"EOF";
</SELECT>
<BR>
BY 
<INPUT TYPE="RADIO" NAME="by" VALUE="SESSION" CHECKED>session
<INPUT TYPE="RADIO" NAME="by" VALUE="ACCESS">access
<BR>
WHENEVER 
<INPUT TYPE="CHECKBOX" NAME="whenever~SUCCESSFUL" CHECKED>successful
<INPUT TYPE="CHECKBOX" NAME="whenever~NOTSUCCESSFUL" CHECKED>not successful
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Submit change">
</FORM>
<CENTER>
<P><HR WIDTH=90%><P>
EOF
}
if ($sequences) {
text("Audit sequence objects.");
$sql = "$copyright
SELECT
SEQUENCE_OWNER,
SEQUENCE_NAME
FROM DBA_SEQUENCES
WHERE SEQUENCE_OWNER IN (@sqlusers)
ORDER BY SEQUENCE_OWNER, SEQUENCE_NAME
";
print <<"EOF";
</CENTER>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="doschemaaudits">
<B>AUDIT 
<INPUT TYPE="CHECKBOX" NAME="audit~ALL">All  --
<INPUT TYPE="CHECKBOX" NAME="audit~ALTER">Alter,
<INPUT TYPE="CHECKBOX" NAME="audit~AUDIT">Audit,
<INPUT TYPE="CHECKBOX" NAME="audit~GRANT">Grant,
<INPUT TYPE="CHECKBOX" NAME="audit~RENAME">Rename
<BR>
ON&nbsp
<SELECT SIZE=5 NAME=object MULTIPLE>
EOF
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($owner,$object_name) = $cursor->fetchrow_array) {
print "<OPTION>$owner.$object_namen";
}
print <<"EOF";
</SELECT>
<BR>
BY 
<INPUT TYPE="RADIO" NAME="by" VALUE="SESSION" CHECKED>session
<INPUT TYPE="RADIO" NAME="by" VALUE="ACCESS">access
<BR>
WHENEVER 
<INPUT TYPE="CHECKBOX" NAME="whenever~SUCCESSFUL" CHECKED>successful
<INPUT TYPE="CHECKBOX" NAME="whenever~NOTSUCCESSFUL" CHECKED>not successful
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Submit change">
</FORM>
<CENTER>
<P><HR WIDTH=90%><P>
EOF
}
if ($procedures || $packages || $functions) {
text("Audit source objects.");
$sql = "$copyright
SELECT
OWNER,
OBJECT_NAME
FROM DBA_OBJECTS
WHERE OWNER IN (@sqlusers)
AND OBJECT_TYPE IN ('PACKAGE','PROCEDURE','FUNCTION')
ORDER BY OWNER, OBJECT_NAME
";
print <<"EOF";
</CENTER>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="doschemaaudits">
<B>AUDIT 
<INPUT TYPE="CHECKBOX" NAME="audit~ALL">All  --
<INPUT TYPE="CHECKBOX" NAME="audit~AUDIT">Audit,
<INPUT TYPE="CHECKBOX" NAME="audit~EXECUTE">Execute,
<INPUT TYPE="CHECKBOX" NAME="audit~GRANT">Grant,
<INPUT TYPE="CHECKBOX" NAME="audit~RENAME">Rename
<BR>
ON&nbsp
<SELECT SIZE=5 NAME=object MULTIPLE>
EOF
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($owner,$object_name) = $cursor->fetchrow_array) {
print "<OPTION>$owner.$object_namen";
}
print <<"EOF";
</SELECT>
<BR>
BY 
<INPUT TYPE="RADIO" NAME="by" VALUE="SESSION" CHECKED>session
<INPUT TYPE="RADIO" NAME="by" VALUE="ACCESS">access
<BR>
WHENEVER 
<INPUT TYPE="CHECKBOX" NAME="whenever~SUCCESSFUL" CHECKED>successful
<INPUT TYPE="CHECKBOX" NAME="whenever~NOTSUCCESSFUL" CHECKED>not successful
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Submit change">
</FORM>
<CENTER>
<P><HR WIDTH=90%><P>
EOF
}
if ($libraries) {
text("Audit library objects.");
$sql = "$copyright
SELECT
OWNER,
LIBRARY_NAME
FROM DBA_LIBRARIES
WHERE OWNER IN (@sqlusers)
ORDER BY OWNER, LIBRARY_NAME
";
print <<"EOF";
</CENTER>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="doschemaaudits">
<B>AUDIT 
<INPUT TYPE="CHECKBOX" NAME="audit~ALL">All  --
<INPUT TYPE="CHECKBOX" NAME="audit~EXECUTE">Execute,
<INPUT TYPE="CHECKBOX" NAME="audit~GRANT">Grant
<BR>
ON&nbsp
<SELECT SIZE=5 NAME=object MULTIPLE>
EOF
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($owner,$object_name) = $cursor->fetchrow_array) {
print "<OPTION>$owner.$object_namen";
}
print <<"EOF";
</SELECT>
<BR>
BY 
<INPUT TYPE="RADIO" NAME="by" VALUE="SESSION" CHECKED>session
<INPUT TYPE="RADIO" NAME="by" VALUE="ACCESS">access
<BR>
WHENEVER 
<INPUT TYPE="CHECKBOX" NAME="whenever~SUCCESSFUL" CHECKED>successful
<INPUT TYPE="CHECKBOX" NAME="whenever~NOTSUCCESSFUL" CHECKED>not successful
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Submit change">
</FORM>
<CENTER>
<P><HR WIDTH=90%><P>
EOF
}
if ($directories) {
text("Audit directory objects.");
$sql = "$copyright
SELECT
OWNER,
DIRECTORY_NAME
FROM DBA_DIRECTORIES
WHERE OWNER IN (@sqlusers)
ORDER BY OWNER, DIRECTORY_NAME
";
print <<"EOF";
</CENTER>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="doschemaaudits">
<B>AUDIT 
<INPUT TYPE="CHECKBOX" NAME="audit~ALL">All  --
<INPUT TYPE="CHECKBOX" NAME="audit~AUDIT">Audit,
<INPUT TYPE="CHECKBOX" NAME="audit~GRANT">Grant,
<INPUT TYPE="CHECKBOX" NAME="audit~READ">Read
<BR>
ON&nbsp
<SELECT SIZE=5 NAME=object MULTIPLE>
EOF
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($owner,$object_name) = $cursor->fetchrow_array) {
print "<OPTION>$owner.$object_namen";
}
print <<"EOF";
</SELECT>
<BR>
BY 
<INPUT TYPE="RADIO" NAME="by" VALUE="SESSION" CHECKED>session
<INPUT TYPE="RADIO" NAME="by" VALUE="ACCESS">access
<BR>
WHENEVER 
<INPUT TYPE="CHECKBOX" NAME="whenever~SUCCESSFUL" CHECKED>successful
<INPUT TYPE="CHECKBOX" NAME="whenever~NOTSUCCESSFUL" CHECKED>not successful
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Submit change">
</FORM>
<CENTER>
<P><HR WIDTH=90%><P>
EOF
}
}
logit("Exit subroutine enterAudits");
}
sub schemaTableDDL {
logit("Enter subroutine schemaTableDDL");
my ($username,$sql,$cursor,$table_name);
$username = $query->param('username');
text("Select the tables you would like to reverse engineer: Schema $username");
print <<"EOF";
<TABLE BORDER =0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="CHECKBOX" NAME="headertype" VALUE="octet"><B>Send DDL to your workstation<BR><BR>
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Generate DDL for marked tables"><BR><BR>
<P>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="TABLEDDL">
<INPUT TYPE="HIDDEN" NAME="username" VALUE="$username">
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Mark</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Table name                                                    </TH>
EOF
$sql = "$copyright
SELECT
TABLE_NAME
FROM DBA_TABLES
WHERE OWNER = '$username'
";
$cursor = $dbh->prepare($sql);
$cursor->execute;
while ($table_name = $cursor->fetchrow) {
print <<"EOF";
<TR>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><INPUT TYPE=CHECKBOX NAME=reverse~$table_name></TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$table_name</TD>
</TR>
EOF
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</FORM>
</TABLE>
EOF
logit("Exit subroutine schemaTableDDL");
}
sub invalidObjectList {
logit("Enter subroutine invalidObjectList");
my ($username,$sql,$cursor,$owner,$object_type,$object_name,$object_id);
my ($count);
$username = shift;
$sql = "$copyright
SELECT COUNT(*)
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
";
$sql = "$copyright
SELECT COUNT(*)
FROM DBA_OBJECTS
WHERE OWNER = '$username'
AND STATUS = 'INVALID'
" if ($username);
$cursor = $dbh->prepare($sql);
$cursor->execute;
$count = $cursor->fetchrow_array;
$cursor->finish;
unless ($count) {
message("There are no invalid objects to compile");
footer();
}
text("Select the objects you would like to compile.n");
$sql = "$copyright
SELECT
OWNER,
OBJECT_TYPE,
OBJECT_NAME,
A.OBJECT_ID
FROM
DBA_OBJECTS A,
SYS.ORDER_OBJECT_BY_DEPENDENCY B
WHERE
A.OBJECT_ID = B.OBJECT_ID(+) AND
";
$sql .= " OWNER = '$username' ANDn" if $username;
$sql .= "
STATUS = 'INVALID'
ORDER BY
DLEVEL DESC,
OBJECT_TYPE,
OBJECT_NAME
";
logit(" invalidObjectList SQL:n$sql");
# Print the heading
print <<"EOF";
<TABLE BORDER =0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
<FORM METHOD=POST ACTION=$scriptname>
<FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
<INPUT TYPE="SUBMIT" NAME="foobar" VALUE="Compile marked objects">
<P>
<INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
<INPUT TYPE="HIDDEN" NAME="object_type" VALUE="DBADMIN">
<INPUT TYPE="HIDDEN" NAME="arg" VALUE="compile">
<TR>
<TD WIDTH=100%>
<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=1>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Mark</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Owner</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Object type</TH>
<TH BGCOLOR='$headingcolor' ALIGN=CENTER><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Name</TH>
EOF
$cursor = $dbh->prepare($sql);
$cursor->execute;
while (($owner,$object_type,$object_name,$object_id) = $cursor->fetchrow) {
print <<"EOF";
<TR>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><INPUT TYPE=CHECKBOX NAME=compile_$object_id></TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$owner</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$object_type</TD>
<TD BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$object_name</TD>
</TR>
EOF
}
$cursor->finish;
print <<"EOF";
</TABLE>
</TD>
</TR>
</FORM>
</TABLE>
EOF
logit("Exit subroutine invalidObjectList");
}
sub rbsList {
logit("Enter subroutine rbsList");
my ($sql,$cursor,$sql1,$cursor1,$id);
my ($rbs,$owner,$tsname,$bytes,$init,$next,$extents,$max,$optimal,$status,$writes,$waits,$xacts);
text("Rollbacks will shrink to OPTIMAL unless other value is specified.");
$sql = "$copyright
SELECT
A.SEGMENT_NAME,
B.SEGMENT_ID,
A.OWNER,
A.TABLESPACE_NAME,
B.STATUS,
TO_CHAR(A.BYTES,'999,999,999,999'),
TO_CHAR(A.INITIAL_EXTENT,'999,999,999,999'),
TO_CHAR(A.NEXT_EXTENT,'999,999,999,999'),
TO_CHAR(A.EXTENTS,'999,999,999,999'),
TO_CHAR(A.MAX_EXTENTS,'999,999,999,999')
FROM DBA_SEGMENTS A, DBA_ROLLBACK_SEGS B
WHERE A.SEGMENT_TYPE = 'ROLLBACK'
AND A.SEGMENT_NAME = B.SEGMENT_NAME
AND ( B.INSTANCE_NUM =
( SELECT VALUE FROM V$PARAMETER
WHERE NAME = 'instance_number' )
OR B.INSTANCE_NUM IS NULL )
ORDER BY A.SEGMENT_NAME, A.TABLESPACE_NAME