Use Groovy to Find Database Size

Last week I found a SQL statement to find the size of rows in a database. That was helpful but I wanted to be able to run this from the command line. Sounds like a good job for the Groovy database features.

import groovy.sql.Sql

//connect to the database
def sql = Sql.newInstance('jdbc:oracle:thin:user/pass@host:1521:database')

//define the statement to run
def sizeStatement = """select table_name, num_rows, avg_row_len,
ROUND((num_rows * avg_row_len / 1048576),2) "mb_used"
from user_tables
where num_rows > 0"""

//create a date to output (not needed for SQL, just for reporting purposes
def formattedDate = String.format('%tD %
//output the results
sql.eachRow(sizeStatement) { row ->
 println "${formattedDate},${row.table_name},${row.mb_used}"
}

A nice little script. The only catch is that my database is Oracle so I need to pass in the driver jar when I run the script from the command line like this

groovy -classpath OracleThinDrivers.jar CheckSize.groovy

Read more...

Getting the Size of a Database

I recently needed to get an estimated size of an Oracle database. It turns out to be not too hard.

--by table
select table_name, num_rows, avg_row_len,
 ROUND((num_rows * avg_row_len / 1048576),2) "MB Used"
from user_tables
where num_rows > 0

--total
select SUM(ROUND((num_rows * avg_row_len / 1048576),2)) "Total MB Used"
from user_tables

Read more...