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
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.
# Script Name: tbs.py # Description: Tablespace usage details in an Oracle DB # Usage: python tbs.py # Author: Adityanath Dewoolkar # Version: 1 # importing required modules import cx_Oracle import getpass import os # For conditional coloring os.system("") 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 try: con = cx_Oracle.connect("SYSTEM",password,db_name) cursor=con.cursor() head = ("TbsName","FreeSpaceMB","Free%") print(head) 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 "): result=str(result) pieces = result.split() pieces = pieces free_percent = float(pieces[:-1]) if free_percent < 20: print(style.RED + result.format(head) + style.RESET ) else: 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:
ALL GREEN!!! HAPPY!!!!
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
Categories: 12c, 19c, Administration, automation, Monitoring, Oracle 18c, python, Scripts
Like!! Great article post.Really thank you! Really Cool.
I really like and appreciate your blog post.
A big thank you for your article.
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 ?
I learned basic python from course mentioned below:
Just wanted to let you know, this doesnt cover anything related to Oracle, but can help you to get an idea how python works.
Then you can explore further as per your interest.
Script execution failed: DPI-1047: Cannot locate a 64-bit Oracle Client library: “The specified module could not be found”. See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help
You need a oracle client installed on your machine.
Hi, I check your new stuff like every week. Your humoristic style is awesome, keep doing what you’re doing!
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…