Oracle Tablespace monitoring using Python scripting

Dear Readers,

I am planning to convert most of the scripts I currently use for Oracle DB monitoring, into python in near future. But why am I fascinated with Python?? Its very easy to understand. Its open source & most important its platform independent.

So here comes python code to check tablespace usage details in Oracle DB.

Note: This code is ready to use. But you can definitely refer it & modify wherever as per your need.


  • python binaries with version 3.
  • cx_Oracle module

What this program does:

This program needs following inputs from user side:

  • SYSTEM password
  • TNS entry to connect to DB.

At the end of execution, it provides following details about each tablespace in database.

  • Tablespace Name
  • Free Space in MB
  • Free%

Note: This code considers autoexensible clause & maxbytes, while calculating tablespace usage. Any tablespace having space less than 20% will be highlighted in RED color, while others will be in GREEN color.

Actual code:

# Script Name:
# Description: Tablespace usage details in an Oracle DB
# Usage: python
# Author: Adityanath Dewoolkar
# Version: 1

# importing required modules
import cx_Oracle
import getpass
import os

# For conditional coloring
class style():
RED = '\033[31m'
GREEN = '\033[32m'
RESET = '\033[0m'

# Prompt the user for a password without echoing
password = getpass.getpass(prompt = 'Enter SYSTEM password: ', stream=None)
# Enter TNS string to connect
db_name = input("Enter DB name: ")

# Code for getting Tablespace usage details in an Oracle DB
con = cx_Oracle.connect("SYSTEM",password,db_name)
head = ("TbsName","FreeSpaceMB","Free%")
for result in cursor.execute("WITH new_tbs AS (SELECT /*+parallel(4)*/file_id, tablespace_name, file_name, DECODE (autoextensible,'YES', GREATEST (BYTES, maxbytes),BYTES) mysize,DECODE (autoextensible,'YES', CASE WHEN (maxbytes > BYTES) THEN (maxbytes - BYTES) ELSE 0 END, 0) growth FROM dba_data_files) select /*+parallel(4)*/ * from (SELECT new_tbs.tablespace_name tablespace ,ROUND ((SUM (NVL (freebytes, 0)) + SUM (growth)) / (1024 * 1024)) free_mb,ROUND ( (SUM (NVL (freebytes, 0)) + SUM (growth))/ SUM (new_tbs.mysize)* 100) pct_free FROM new_tbs, (SELECT file_id, SUM (BYTES) freebytes FROM dba_free_space GROUP BY file_id ) dfs WHERE new_tbs.file_id = dfs.file_id(+) AND upper(new_tbs.tablespace_name) NOT LIKE '%UNDO%' GROUP BY new_tbs.tablespace_name) my_tbs ORDER BY 3 "):
pieces = result.split()
pieces = pieces[2]
free_percent = float(pieces[:-1])
if free_percent < 20:
print(style.RED + result.format(head) + style.RESET )
print(style.GREEN + result.format(head) + style.RESET)

except cx_Oracle.DatabaseError as err:
print("Script execution failed: ", err)

How to execute:


Here comes the result:

I am re-running code after fixing SYSTEM tablespace. Here comes the new result:


You can download sample files from below location:

Hope u will find this post very useful.

Stay tuned for script to monitor FRA usage details in an Oracle DB



9 replies

  1. Thanks for blog post. i am also working as oracle dba since 7 years have worked in shell scripting for database aspects. However, i would like to open myself in different language like python.
    Can you guide me here , which books/link should i go first to know about it and do some scripting in this language too ?

  2. Hi, I check your new stuff like every week. Your humoristic style is awesome, keep doing what you’re doing!

  3. I want to to thank you for this great read!!
    I definitely loved every little bit of it. I’ve
    got you bookmarked to look at new stuff you post…

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s