oracletool.pl

用perl写的Oracle管理工具

   PARTITION_NAME                               \"Partition name\",
   TABLESPACE_NAME                              \"Tablespace\",
   PARTITION_POSITION                           \"Position\",
   TO_CHAR(INITIAL_EXTENT,'999,999,999,999')    \"Initial\",
   TO_CHAR(NEXT_EXTENT,'999,999,999,999')       \"Next\",
   TO_CHAR(MAX_EXTENT,'999,999,999,999')        \"Max extents\",
   PCT_INCREASE                                 \"Pct increase\",
   HIGH_VALUE                                   \"High value\",
   HIGH_VALUE_LENGTH                            \"High value length\",
   LOGGING                                      \"Logging\"
FROM DBA_IND_PARTITIONS
   WHERE INDEX_NAME = '$object_name'
   AND INDEX_OWNER = '$schema'
ORDER BY PARTITION_POSITION
";

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

   if (checkPriv("ANALYZE ANY")) {
      print <<"EOF";
<FORM METHOD="GET" ACTION="$scriptname">
  <FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
  <INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
  <INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
  <INPUT TYPE="HIDDEN" NAME="arg" VALUE="$object_name">
  <INPUT TYPE="HIDDEN" NAME="object_type" VALUE="VALIDATEINDEX">
  <INPUT TYPE="SUBMIT" NAME="foo" VALUE="Validate index for detailed statistics.">
</FORM>
EOF
   }

   logit("Exit subroutine showIndex");

}

sub showIndexPart {

   logit("Enter subroutine showIndexPart");

   my ($sql,$cursor,$isanalyzed,$text,$link,$infotext,$index_name);

   $index_name = $query->param('index_name');

   $sql = "$copyright
SELECT
   PARTITION_NAME                               \"Partition name\",
   INDEX_NAME                                   \"Index name\",
   INDEX_OWNER                                  \"Owner\",
   TABLESPACE_NAME                              \"Tablespace\",
   PARTITION_POSITION                           \"Position\",
   TO_CHAR(INITIAL_EXTENT,'999,999,999,999')    \"Initial\",
   TO_CHAR(NEXT_EXTENT,'999,999,999,999')       \"Next\",
   TO_CHAR(MAX_EXTENT,'999,999,999,999')        \"Max extents\",
   PCT_INCREASE                                 \"Pct increase\",
   HIGH_VALUE                                   \"High value\",
   HIGH_VALUE_LENGTH                            \"High value length\",
   LOGGING                                      \"Logging\"
FROM DBA_IND_PARTITIONS
   WHERE PARTITION_NAME = '$object_name'
   AND INDEX_NAME = '$index_name'
   AND INDEX_OWNER = '$schema'
";

   $object_type = lc $object_type;
   $text = "General info: $object_type $object_name";
   $link = "";
   DisplayTable($sql,$text,$link);

   logit("Exit subroutine showIndexPart");

}

sub showTablePart {

   logit("Enter subroutine showTablePart");

   my ($sql,$cursor,$isanalyzed,$text,$link,$infotext,$table_name);

   $table_name = $query->param('table_name');

# General info

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

   $object_type = lc $object_type;
   $text = "General info: $object_type $object_name";
   $link = "";
   DisplayTable($sql,$text,$link);

# Check to see if partition has been analyzed.

   $sql = "$copyright
SELECT 
   DISTINCT LAST_ANALYZED
FROM DBA_TAB_PARTITIONS
   WHERE PARTITION_NAME = '$object_name'
   AND TABLE_OWNER = '$schema'
";

   $cursor=$dbh->prepare($sql);
   logit("   Error: $DBI::errstr") if $DBI::errstr;
   $cursor->execute;
   $isanalyzed = $cursor->fetchrow_array;
   logit("   Isanalyzed for partition $schema.$object_name is $isanalyzed");
   $cursor->finish;

   if ($isanalyzed) {

      $sql = "$copyright
SELECT
   TO_CHAR((BLOCKS / (EMPTY_BLOCKS+BLOCKS)) *100,'999.99')||'%'      \"Percent used\",
   TO_CHAR(NUM_ROWS,'999,999,999,999')                          \"Row count\",
   TO_CHAR(BLOCKS,'999,999,999,999')                            \"Blocks\",
   TO_CHAR(EMPTY_BLOCKS,'999,999,999,999')                      \"Empty blocks\",
   TO_CHAR(AVG_SPACE,'999,999,999,999')                         \"Average space\",
   TO_CHAR(AVG_ROW_LEN,'999,999,999,999')                       \"Average row length\",
   TO_CHAR(CHAIN_CNT,'999,999,999,999')                         \"Chain count\",
   TO_CHAR(LAST_ANALYZED,'Month DD, YYYY')			\"Last analyzed\"
FROM DBA_TAB_PARTITIONS
   WHERE PARTITION_NAME = '$object_name'
   AND TABLE_OWNER = '$schema'
";

      $text = "Analyzation info: $object_type $object_name";
      $link = "";
      DisplayTable($sql,$text,$link);
   } else {
      message("Partition has never been analyzed. Extended info will not be shown.");
   }

   logit("Exit subroutine showTablePart");

}

sub showCluster {

   logit("Enter subroutine showCluster");

   my ($sql,$text,$link,$infotext);

   $sql = "$copyright
SELECT
   TABLESPACE_NAME					\"Tablespace name\",
   TO_CHAR(INITIAL_EXTENT,'999,999,999,999')		\"Initial extent\",
   TO_CHAR(NEXT_EXTENT,'999,999,999,999')		\"Next extent\",
   TO_CHAR(MAX_EXTENTS,'999,999,999,999')		\"Max extents\",
   CLUSTER_TYPE						\"Cluster type\",
   FUNCTION						\"Function\",
   INSTANCES						\"Instances\",
   SINGLE_TABLE						\"Single table\"
FROM DBA_CLUSTERS 
   WHERE CLUSTER_NAME = '$object_name'
   AND OWNER = '$schema'
";

   logit ("   $sql");

   $text = "General info: Cluster $object_name";
   $link = "";
   DisplayTable($sql,$text,$link);

   $sql = "$copyright
SELECT
   TABLE_NAME						\"Table_name\",
   TO_CHAR(INITIAL_EXTENT,'999,999,999,999')		\"Initial extent\",
   TO_CHAR(NEXT_EXTENT,'999,999,999,999')		\"Next extent\",
   TO_CHAR(MAX_EXTENTS,'999,999,999,999')		\"Max extents\"
FROM DBA_TABLES
   WHERE CLUSTER_NAME = '$object_name'
   AND OWNER = '$schema'
";

   $text = "Tables belonging to cluster $object_name";
   $infotext = "No tables belong to cluster $object_name";
   $link = "$scriptname?database=$database&schema=$schema&object_type=TABLE";
   DisplayTable($sql,$text,$link,$infotext);
    
   logit("Exit subroutine showCluster");

}
   

sub showTable {

   logit("Enter subroutine showTable");

   my ($sql,$text,$link,$infotext,$cursor,$isanalyzed,$partitioned,$grantcount,$foo);
   my (@columns,$cols,$constraint_name,$column_name,$status,$index_name,$tablespace_name);
   my ($indexes,$cursor1,$sql1,$uniqueness,$initial_extent,$next_extent,$max_extents);
   my ($r_owner,$r_constraint_name,$count,$r_table_name,$iot_type,$temporary,$index_type);

   print <<"EOF";
<TABLE BORDER=0 BGCOLOR='$bordercolor' CELLPADDING=1 CELLSPACING=0>
  <TR>
    <TD WIDTH=100%>
      <TABLE BORDER=0 cellpadding=5 cellspacing=1>
        <TR>
          <TD BGCOLOR=$headingcolor>
            <TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0>
              <TR>
                <TD ALIGN=CENTER>
                  <FORM METHOD="GET" ACTION="$scriptname">
                    <FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
                    <INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
                    <INPUT TYPE="HIDDEN" NAME="object_type" VALUE="TABLEROWS">
                    <INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
                    <INPUT TYPE="HIDDEN" NAME="arg" VALUE="$object_name">
                    <INPUT TYPE="SUBMIT" NAME="tablerows" VALUE="Display $rowdisplay rows of this table">
                </TD>
              </TR>
              <TR>
                <TD ALIGN=CENTER><FONT COLOR=$fontcolor SIZE=$fontsize><B><I>where</I></B></FONT></TD>
              </TR>
              <TR>
                <TD ALIGN=CENTER>
                    <INPUT TYPE="TEXT" SIZE=30 NAME="whereclause">
                </TD> 
                  </FORM>
              </TR>
            </TABLE>
          </TD>
        </TR>
      </TABLE>
    </TD>
  </TR>
</TABLE>
EOF

$grantcount = showGrantButton();

unless ($grantcount) {
   print "<BR>";
}

print <<"EOF";
<TABLE BORDER=0 CELLPADDING=5 CELLSPACING=1>
  <TR>
    <TD ALIGN=CENTER>
      <FORM METHOD=POST ACTION=$scriptname>
        <FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
        <INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
        <INPUT TYPE="HIDDEN" NAME="object_type" VALUE="TABLEDDL">
        <INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
        <INPUT TYPE="HIDDEN" NAME="arg" VALUE="$schema.$object_name">
        <INPUT TYPE="SUBMIT" NAME="foo" VALUE="Generate DDL">
      </FORM>
    </TD>
    <TD ALIGN=CENTER>
      <FORM METHOD=POST ACTION=$scriptname>
        <FONT COLOR='$fontcolor' SIZE='$fontsize' FACE='$font'>
        <INPUT TYPE="HIDDEN" NAME="database" VALUE="$database">
        <INPUT TYPE="HIDDEN" NAME="object_type" VALUE="OBJECTFRAGMAP">
        <INPUT TYPE="HIDDEN" NAME="schema" VALUE="$schema">
        <INPUT TYPE="HIDDEN" NAME="arg" VALUE="$object_name">
        <INPUT TYPE="SUBMIT" NAME="foo" VALUE="Extent mapping">
      </FORM>
    </TD>
  </TR>
</TABLE>
EOF

# Table structure

   $sql = "$copyright
SELECT
   A.COLUMN_NAME                                 \"Column name\",
   A.DATA_TYPE                                   \"Type\",
   A.DATA_LENGTH                                 \"Length\",
   A.DATA_PRECISION                              \"Precision\",
   B.DATA_DEFAULT                                \"Default\",
   TO_CHAR(A.LAST_ANALYZED,'Month DD, YYYY')     \"Last analyzed\"
   FROM
          (SELECT
             TABLE_NAME, 
             OWNER,
             COLUMN_ID, 
             COLUMN_NAME,
             DATA_TYPE, 
             DATA_LENGTH, 
             DATA_PRECISION,
             LAST_ANALYZED
             FROM DBA_TAB_COLUMNS
          WHERE (TABLE_NAME = '$object_name') 
          AND (OWNER = '$schema') 
          AND (DATA_TYPE <> 'NUMBER')
           UNION
           SELECT
             TABLE_NAME, 
             OWNER,
             COLUMN_ID, 
             COLUMN_NAME,
             DATA_TYPE, 
             DATA_PRECISION, 
             DATA_SCALE,
             LAST_ANALYZED
             FROM DBA_TAB_COLUMNS
           WHERE (TABLE_NAME = '$object_name') 
           AND (OWNER = '$schema') 
           AND (DATA_TYPE = 'NUMBER')
          ) A,
        DBA_TAB_COLUMNS B
   WHERE (A.TABLE_NAME = B.TABLE_NAME) 
   AND (A.OWNER = B.OWNER) 
   AND (A.COLUMN_ID = B.COLUMN_ID)
ORDER BY b.COLUMN_ID
";

   $object_type = lc $object_type;
   $text = "Structure of $object_type $object_name";
   $link = "";
   DisplayTable($sql,$text,$link);

# Check to see if table has been analyzed.

   $sql = "$copyright
SELECT 
   DISTINCT LAST_ANALYZED
FROM DBA_TAB_COLUMNS
   WHERE TABLE_NAME = '$object_name'
   AND OWNER = '$schema'
";

   $cursor=$dbh->prepare($sql);
   $cursor->execute;
   $isanalyzed = $cursor->fetchrow_array;
   $cursor->finish;

# If the table has been analyzed, show some additional information

   if ($isanalyzed) {

      $sql = "$copyright
SELECT 
   TO_CHAR((BLOCKS / (EMPTY_BLOCKS+BLOCKS)) *100,'999.99')||'%'	\"Percent used\",
   TO_CHAR(NUM_ROWS,'999,999,999,999')				\"Row count\",
   TO_CHAR(BLOCKS,'999,999,999,999')				\"Blocks\",
   TO_CHAR(EMPTY_BLOCKS,'999,999,999,999')			\"Empty blocks\",
   TO_CHAR(AVG_SPACE,'999,999,999,999')				\"Average space\",
   TO_CHAR(AVG_ROW_LEN,'999,999,999,999')			\"Average row length\",
   TO_CHAR(CHAIN_CNT,'999,999,999,999')				\"Chain count\"
FROM DBA_TABLES 
   WHERE TABLE_NAME = '$object_name'
   AND OWNER = '$schema'
";

      $text = "Analyzation info: $object_type $object_name";
      $link = "";
      DisplayTable($sql,$text,$link);
   } else {
      message("Table has never been analyzed. Extended info will not be shown.");
   }

# Gather some info for later queries
# These can be used to determine what 
# type of table we are dealing with.
# PARTITIONED: YES/NO
# IOT_TYPE: IOT/NULL
# TEMPORARY: Y/N

   if ($oracle8) {
      $sql = "$copyright
SELECT
   PARTITIONED,
   IOT_TYPE,
   TEMPORARY
FROM DBA_TABLES
   WHERE TABLE_NAME = '$object_name'
   AND OWNER = '$schema'
";
      $cursor = $dbh->prepare($sql);
      $cursor->execute;
      ($partitioned,$iot_type,$temporary) = $cursor->fetchrow_array;
      $cursor->finish;
   }

   if ($iot_type eq "IOT") {

      message("Table $object_name is an Index Organized Table.");

   } else {

# General info

      $sql = "$copyright
SELECT 
   A.TABLESPACE_NAME					\"Tablespace\", 
   TO_CHAR(B.CREATED,'Month DD, YYYY - HH24:MI')	\"Date created\",
   TO_CHAR(B.LAST_DDL_TIME,'Month DD, YYYY - HH24:MI')	\"Last DDL time\",
   TO_CHAR(A.EXTENTS,'999,999,999,999')			\"Extents\",