Oracle Hash

Loads a list of existing oracle tables and their block size to a hash. Ask user for a table, lookup block from the hash, convert block to mb.

#
use Win32::ODBC;
#
# Load a list of Existing Tables and their Block size to a Hash
#
#
$TableList = new Win32::ODBC("DSN=databasename; UID=yourlogonid; PWD=y
+ourpassword;");
$TableList->Sql("select TABLE_NAME, BLOCKS from USER_TABLES where tabl
+e_name like 'PS_%'");
while ( $TableList->FetchRow() )
{
    %hash= $TableList->DataHash();
    $tabnam = $hash{TABLE_NAME};
    $blocks = $hash{BLOCKS};
    
    $TabNam{$tabnam} = $tabnam;
    $Blocks{$tabnam} = $blocks;
    
    $errcd = $TableList->Error();
#    print "$tabnam", "\n", "$errcd";
};
$TableList->Close();
foreach $key (keys %TabNam) { print "given $key we get $Blocks{$key}\n
+"};
#
#
#
print "please enter table name\n";
$tabnam = <STDIN>;
chomp $tabnam;
#
#
$tnam        = $TabNam{$tabnam};                           #lookup _ta
+ble

if (!$tnam)
        {
    #table doesnt exist
    }
else    
    {
    #get table blocks
    $Blks = $Blocks{$tnam};
    $mbyte = (($Blks * 8192)/1048576);
    
    ($mb, $dummy) = split(/\./,(($mbyte)));
    if ($mb < 1) {$mb = 1;}
    
    print "TableName: $tnam  Blocks: $Blks  MB: $mb";
    }


#
#
[download]

Comment on Oracle Hash Download Code

Replies are listed 'Best First'.

Re: Oracle Hash
by rdfield (Priest) on Feb 25, 2004 at 10:17 UTC

What if my block size isn't 8K?
You do know that underscore is a single character wildcard in Oracle?
Why limit your self to tables with a name starting with "PS"?
Why not use DBI? Say I want to run this script on something other than Windows?
Why select all of the tables when the script only prints out the value for a single table?
Why build two hashes when a single one will do? (specifically $TabNam{$tabnam} = $tabname; is completely redundant)
use warnings?
use strict?

Would this be better written as an SQL script or stored procedure? After all, it's just:

select round(bytes/1048576,2)
from user_segments
where segment_name = upper('&1');
[download]

The script prints out the size of each table in block, but what if I have 200 tables? 300? Given the description of the script, was this intended behaviour?
Your output is to any number of decimal place, surely a sprintf would be beneficial?

rdfield

[reply]
[d/l]
[select]


Think about Loose Coupling
	PerlMonks