Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Oracle Hash

by ccallahanwise (Initiate)
on Feb 24, 2004 at 19:19 UTC ( #331490=snippet: print w/replies, xml ) Need Help??
Description: 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
$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";
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

if (!$tnam)
    #table doesnt exist
    #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";

Replies are listed 'Best First'.
Re: Oracle Hash
by rdfield (Priest) on Feb 25, 2004 at 10:17 UTC
    Just a few points:
    • 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');
    • 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?


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: snippet [id://331490]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (3)
As of 2021-05-14 17:56 GMT
Find Nodes?
    Voting Booth?
    Perl 7 will be out ...

    Results (150 votes). Check out past polls.