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.
|
1 |
select * from DBA_TABLESPACE_USAGE_METRICS |
Or use dba_free_space and dba_data_files of system
|
1 2 3 4 5 6 7 8 9 |
--check tablespace usage select t2.tablespace_name, t2.tbs_size SizeMb, t1.free_space FreeMb from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space from dba_free_space group by tablespace_name) t1, (select tablespace_name, sum(bytes)/1024/1024 as tbs_size from dba_data_files group by tablespace_name) t2 where t1.tablespace_name(+)=t2.tablespace_name; |
2) Customize script to check tablespace usage
|
1 2 3 4 5 6 7 8 |
--check tablespace size SELECT t1.tablespace_name, ROUND((t1.used_space * t2.block_size) / 1048576, 2) AS "Used space (MB)", ROUND((t1.tablespace_size * t2.block_size) / 1048576, 2) AS "Tablespace size (MB)", ROUND(t1.used_percent, 2) AS "Used %" FROM dba_tablespace_usage_metrics t1 JOIN dba_tablespaces t2 ON t1.tablespace_name = t2.tablespace_name |
Or more detail
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
--check more detail size tablespace SELECT m.tablespace_name, round(max(m.tablespace_size*t.block_size/1024/1024),2) TOL_METRICS, round(sum(d.maxbytes/1024/1024),2) MAX_SIZE, round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,2) TOL_ALLOC, round(max(m.used_space*t.block_size/1024/1024),2) USED, round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),2) PERC_ALLOC, round(max(m.used_percent),2) PERC_MAX, --PERC_METRICS round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,2) FREE_ALLOC, round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) FREE_MAX --FREE METRIC FROM dba_tablespace_usage_metrics m, dba_tablespaces t, dba_data_files d--, dba_thresholds tt WHERE m.tablespace_name=t.tablespace_name AND d.tablespace_name=t.tablespace_name --and tt.metrics_name='Tablespace Space Usage' --and tt.object_name is null GROUP BY m.tablespace_name order by 2 desc |
- 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
|
1 2 3 4 5 |
--create data file to crease size of tablespace ALTER TABLESPACE "USERS" ADD DATAFILE 'FILE_NAME' SIZE 1000M AUTOEXTEND ON next 100M maxsize unlimited; --or 10000M |
- 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: oracle, tablespace size, tablespace usage
Related Posts
 How to create function return table or cursor in oracle
|
Create group user class by using object oriented plsql
|
Functions encrypt decrypt and checksum oracle
|
Create stored procedure pl/sql in oracle
|
Related Posts
|
How to create function return table or cursor in oracle
|
Create group user class by using object oriented plsql
|
|
Functions encrypt decrypt and checksum oracle
|
Create stored procedure pl/sql in oracle
|
Import/Read excel file 2003 or 2007 with Spring MVC
Tutorial: how to install Jboss server 7.1 with eclipse