oracletool.pl

用perl写的Oracle管理工具

   TO_CHAR(A.INITIAL_EXTENT,'999,999,999,999')		\"Initial extent\", 
   TO_CHAR(A.NEXT_EXTENT,'999,999,999,999')		\"Next extent\", 
   TO_CHAR(A.MAX_EXTENTS,'999,999,999,999')		\"Max extents\",
   TO_CHAR(A.BYTES,'999,999,999,999')			\"Bytes\",
   A.PCT_INCREASE					\"% increase\",
   DECODE(C.CACHE,
		'    Y','Yes',
                '    N','No')				\"Cache?\"
FROM DBA_SEGMENTS A, DBA_OBJECTS B, DBA_TABLES C
   WHERE A.SEGMENT_NAME = '$object_name' 
   AND A.SEGMENT_TYPE = 'TABLE' 
   AND A.OWNER = '$schema'
   AND B.OBJECT_NAME = '$object_name'
   AND B.OBJECT_TYPE = 'TABLE'
   AND B.OWNER = '$schema'
   AND C.TABLE_NAME = '$object_name'
   AND C.OWNER = '$schema'
";

      $object_type = lc $object_type;
      $text = "General info: $object_type $object_name";
      $link = "$scriptname?database=$database&object_type=TSINFO";
      DisplayTable($sql,$text,$link);

   }

# Check to see if table is partitioned, if Oracle8

   if ($partitioned) {
      
      $sql = "$copyright
SELECT
   PARTITIONED
FROM DBA_TABLES
   WHERE TABLE_NAME = '$object_name'
   AND OWNER = '$schema'
";

      $cursor=$dbh->prepare($sql);
      $cursor->execute;
      $foo = $cursor->fetchrow_array;
      $cursor->finish;
      if ($foo eq "YES") {
         $partitioned = "Yep";
      }
   }

# If partitioned, show some additional info.

   if ($partitioned) {
      $sql = "$copyright
SELECT
   PARTITION_NAME                               \"Partition name\",
   TABLESPACE_NAME                              \"Tablespace\",
   PARTITION_POSITION                           \"Position\",
   TO_CHAR(INITIAL_EXTENT,'999,999,999,999')    \"Initial\",
   TO_CHAR(NEXT_EXTENT,'999,999,999,999')       \"Next\",
   TO_CHAR(MAX_EXTENT,'999,999,999,999')        \"Max extents\",
   PCT_INCREASE                                 \"Pct increase\",
   HIGH_VALUE                                   \"High value\",
   HIGH_VALUE_LENGTH                            \"High value length\",
   LOGGING                                      \"Logging\"
FROM DBA_TAB_PARTITIONS
   WHERE TABLE_NAME = '$object_name'
   AND TABLE_OWNER = '$schema'
ORDER BY PARTITION_POSITION
";

      $text = "Partitions contained in this table";
      $link = "$scriptname?database=$database&schema=$schema&object_type=TABLE+PARTITION&table_name=$object_name";
      DisplayTable($sql,$text,$link);
   }

# Show primary key (if)

   $sql = "$copyright
SELECT 
   CONSTRAINT_NAME				\"Constraint name\",
   STATUS					\"Status\"
FROM DBA_CONSTRAINTS 
   WHERE  CONSTRAINT_TYPE = 'P' 
AND TABLE_NAME = '$object_name' 
AND OWNER = '$schema'
";
   $cursor = $dbh->prepare($sql);
   $cursor->execute;
   (($constraint_name,$status) = $cursor->fetchrow);
   $cursor->finish;
   if ($constraint_name) {
      $sql = "$copyright
SELECT COLUMN_NAME
   FROM DBA_CONS_COLUMNS
WHERE CONSTRAINT_NAME = '$constraint_name'
AND OWNER = '$schema'
   ORDER BY POSITION
";
      $cursor = $dbh->prepare($sql);
      $cursor->execute;
      while ($column_name = $cursor->fetchrow_array) {
         push @columns, $column_name;
      }
      $cursor->finish;
      if ($#columns > 0) {
         $cols = join(",", @columns);
         $cols =~ s/^,//;
      } else {
         $cols = $columns[0];
      }
      text("Primary key");
   print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
  <TR>
    <TD WIDTH=100%>
      <TABLE BORDER=0 cellpadding=2 cellspacing=1>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Constraint name</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Status</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Column(s)</TH>
        <TR ALIGN=LEFT>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A href=$scriptname?database=$database&schema=$schema&object_type=INDEX&arg=$constraint_name>$constraint_name</A></TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$status</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$cols</TD>
        </TR>
      </TABLE>
    </TD>
  </TR>
</TABLE>
EOF
   } else {
      message("This table has no primary key.");
   }

# Count indexes

   $sql = "$copyright
SELECT COUNT(*) 
   FROM DBA_INDEXES
WHERE TABLE_NAME = '$object_name'
   AND OWNER = '$schema'
";
   $cursor = $dbh->prepare($sql);
   $cursor->execute;
   while ($index_name = $cursor->fetchrow_array) {
      $indexes++;
   }
   $cursor->finish;
   
   if ($indexes) {

      text("Indexes");

      if ($oracle7) {
         print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
  <TR>
    <TD WIDTH=100%>
      <TABLE BORDER=0 cellpadding=2 cellspacing=1>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Index name</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Status</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Column(s)</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Tablespace name</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Unique?</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Initial</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Next</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Max</TH>
EOF
         $sql = "$copyright
SELECT 
   INDEX_NAME					\"Index name\",
   STATUS					\"Status\",
   TABLESPACE_NAME				\"Tablespace name\",
   DECODE(UNIQUENESS,
      'UNIQUE','Yes',
      'NONUNIQUE','No')				\"Unique?\",
   TO_CHAR(INITIAL_EXTENT,'999,999,999,999')	\"Initial extent\",
   TO_CHAR(NEXT_EXTENT,'999,999,999,999')	\"Next extent\",
   TO_CHAR(MAX_EXTENTS,'999,999,999,999')	\"Max extents\"
   FROM DBA_INDEXES
WHERE TABLE_NAME = '$object_name'
   AND OWNER = '$schema'
";
         $cursor = $dbh->prepare($sql);
         $cursor->execute;
         while (($index_name,$status,$tablespace_name,$uniqueness,$initial_extent,$next_extent,$max_extents) = $cursor->fetchrow) {
            undef @columns;
            $indexes++;
            $sql1 = "$copyright
SELECT COLUMN_NAME
   FROM DBA_IND_COLUMNS
WHERE INDEX_NAME = '$index_name'
AND INDEX_OWNER = '$schema'
   ORDER BY COLUMN_POSITION
";
            $cursor1 = $dbh->prepare($sql1);
            $cursor1->execute;
            while ($column_name = $cursor1->fetchrow_array) {
               push @columns, $column_name;
            }
            $cursor1->finish;
            if ($#columns > 0) {
               $cols = join(",", @columns);
               $cols =~ s/^,//;
            } else {
               $cols = $columns[0];
            }
            print <<"EOF";
        <TR ALIGN=LEFT>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A href=$scriptname?database=$database&schema=$schema&object_type=INDEX&arg=$index_name>$index_name</A></TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$status</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$cols</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$tablespace_name</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$uniqueness</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$initial_extent</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$next_extent</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$max_extents</TD>
        </TR>
EOF
         }
         print <<"EOF";
      </TABLE>
    </TD>
  </TR>
</TABLE>
EOF
      }

      if ($oracle8) {
         print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
  <TR>
    <TD WIDTH=100%>
      <TABLE BORDER=0 cellpadding=2 cellspacing=1>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Index name</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Index type</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Status</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Column(s)</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Tablespace name</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Unique?</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Initial</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Next</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Max</TH>
EOF
         $sql = "$copyright
SELECT 
   INDEX_NAME					\"Index name\",
   INDEX_TYPE					\"Index type\",
   STATUS					\"Status\",
   TABLESPACE_NAME				\"Tablespace name\",
   DECODE(UNIQUENESS,
      'UNIQUE','Yes',
      'NONUNIQUE','No')				\"Unique?\",
   TO_CHAR(INITIAL_EXTENT,'999,999,999,999')	\"Initial extent\",
   TO_CHAR(NEXT_EXTENT,'999,999,999,999')	\"Next extent\",
   TO_CHAR(MAX_EXTENTS,'999,999,999,999')	\"Max extents\"
   FROM DBA_INDEXES
WHERE TABLE_NAME = '$object_name'
   AND OWNER = '$schema'
";
         $cursor = $dbh->prepare($sql);
         $cursor->execute;
         while (($index_name,$index_type,$status,$tablespace_name,$uniqueness,$initial_extent,$next_extent,$max_extents) = $cursor->fetchrow) {
            undef @columns;
            $indexes++;
            $sql1 = "$copyright
SELECT COLUMN_NAME
   FROM DBA_IND_COLUMNS
WHERE INDEX_NAME = '$index_name'
AND INDEX_OWNER = '$schema'
   ORDER BY COLUMN_POSITION
";
            $cursor1 = $dbh->prepare($sql1);
            $cursor1->execute;
            while ($column_name = $cursor1->fetchrow_array) {
               push @columns, $column_name;
            }
            $cursor1->finish;
            if ($#columns > 0) {
               $cols = join(",", @columns);
               $cols =~ s/^,//;
            } else {
               $cols = $columns[0];
            }
            print <<"EOF";
        <TR ALIGN=LEFT>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'><A href=$scriptname?database=$database&schema=$schema&object_type=INDEX&arg=$index_name>$index_name</A></TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$index_type</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$status</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$cols</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$tablespace_name</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$uniqueness</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$initial_extent</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$next_extent</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$max_extents</TD>
        </TR>
EOF
         }
         print <<"EOF";
      </TABLE>
    </TD>
  </TR>
</TABLE>
EOF
      }
   } else {
      message("This table has no indexes");
   }

# Show column constraints (if)

   $sql = "$copyright
SELECT 
   CONSTRAINT_NAME				\"Constraint name\", 
   SEARCH_CONDITION				\"Search condition\", 
   STATUS					\"Status\" 
FROM DBA_CONSTRAINTS 
   WHERE CONSTRAINT_TYPE NOT IN ('P','R')
AND TABLE_NAME = '$object_name' 
AND OWNER = '$schema'
   ORDER BY TABLE_NAME, CONSTRAINT_NAME
";
   $text = "Column constraints";
   $link = "";
   $infotext = "This table has no column constraints.";
   DisplayTable($sql,$text,$link,$infotext);

# Show foreign key constraints (if)

   $count = "";

   $sql = "$copyright
SELECT 
   COUNT(*)
FROM DBA_CONSTRAINTS
   WHERE CONSTRAINT_TYPE = 'R'
   AND OWNER = '$schema'
   AND TABLE_NAME = '$object_name'
";
   $cursor = $dbh->prepare($sql);
   $cursor->execute;
   $count = $cursor->fetchrow_array;
   $cursor->finish;

   if ($count) {
      text("Foreign key constraints");
      print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
  <TR>
    <TD WIDTH=100%>
      <TABLE BORDER=0 cellpadding=2 cellspacing=1>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Constraint name</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Status</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Column(s)</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Ref owner</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Ref table</TH>
        <TH BGCOLOR='$headingcolor'><FONT COLOR='$headingfontcolor' SIZE='$fontsize' FACE='$headingfont'>Ref constraint</TH>
EOF
      $sql = "$copyright
SELECT
   CONSTRAINT_NAME,
   STATUS,
   R_OWNER,
   R_CONSTRAINT_NAME
FROM DBA_CONSTRAINTS
   WHERE CONSTRAINT_TYPE = 'R'
   AND OWNER = '$schema'
   AND TABLE_NAME = '$object_name'
";
      $cursor = $dbh->prepare($sql);
      $cursor->execute;
      while (($constraint_name,$status,$r_owner,$r_constraint_name) = $cursor->fetchrow_array) {
# Add the columns
         $sql1 = "$copyright
   SELECT
COLUMN_NAME
   FROM DBA_CONS_COLUMNS
WHERE OWNER = '$schema'
AND CONSTRAINT_NAME = '$constraint_name'
";
         $cursor1 = $dbh->prepare($sql1);
         $cursor1->execute;
         undef @columns;
         while ($column_name = $cursor1->fetchrow_array) {
            push @columns, $column_name;
         }
         $cursor1->finish;
         if ($#columns > 0) {
            $cols = join(",", @columns);
            $cols =~ s/^,//;
         } else {
            $cols = $columns[0];
         }
# Get the referenced table name 
         $sql1 = "$copyright
SELECT
   TABLE_NAME
FROM DBA_CONSTRAINTS
   WHERE OWNER = '$r_owner'
   AND CONSTRAINT_NAME = '$r_constraint_name'
";
         $cursor1 = $dbh->prepare($sql1);
         $cursor1->execute;
         $r_table_name = $cursor1->fetchrow_array;
         $cursor1->finish;
         print <<"EOF";
        <TR ALIGN=LEFT>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$constraint_name</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$status</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$cols</TD>
          <TD VALIGN=TOP BGCOLOR='$cellcolor'><FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>$r_owner</TD