Check tablespace usage percent in oracle

Thursday, May 25th, 2017 (0 Comment)

If you are database administrator, sometime you need check tablespace usage percent in oracle

In this article,  we have some script to check size of tablespace in oracle and crease size of tablespace when it full space.

1) Check tablespace usage base on view of system

Use view of system below, it will run faster.

Or use dba_free_space and dba_data_files of system

2) Customize script to check tablespace usage

Or more detail

  • PERC_ALLOC: percent base on each block of one data file
  • PERC_MAX: percent base on total data files (maximum 1 data file is 32gb) of one tablespace name
  • FREE_ALLOC: Size (MB) free base on each block of one data file
  • FREE_MAX: Size (MB) free base on totals data files of one tablespace name
  • TOL_ALLOC: Total size (MB) was allocated of all blocks of data files
  • MAX_SIZE: Total size (MB) of all data files of one tablespace name

If we found USED_PERCENT or Used % or PERC_MAX column that over 95%, we should crease size of tablespace.

We need to get file_name in dba_data_file with condition is tablespace_name and run script below with file_name

  • line 3: FILE_NAME: get in dba_data_file (ex: +DB/instance/datafile/user02.dbf), we need edit name user02.dbf if it has existed.

 

Tags: , ,

Related Posts

database
How to create function return table or cursor in oracle
database
Create group user class by using object oriented plsql
database
Functions encrypt decrypt and checksum oracle
database
Create stored procedure pl/sql in oracle

Related Posts

database
How to create function return table or cursor in oracle
database
Create group user class by using object oriented plsql
database
Functions encrypt decrypt and checksum oracle
database
Create stored procedure pl/sql in oracle

Leave a Reply

Your email address will not be published. Required fields are marked *