Checking Tablespace HWM and Adding Datafiles

 
One of the most common DBA tasks is to check for tablespace utilization and avoid application related failures due to a full tables.

Oracle 10g

As of 10g and future versions, there is a new database view named DBA_TABLESPACE_USAGE_METRICS which makes our lives easier. The USED_PERCENT columns shows the utilization % of the tablespace. However, the USED_SPACE and TABLESPACE_SIZE columns are showed in BLOCKSIZE so it can be hard to calculate how much space to add to the tablespace. You can use the alternate query below to convert the blocksize depending on the setup of the tablespace.

select *
from DBA_TABLESPACE_USAGE_METRICS
where tablespace_name = '&tablespace_name';

OR


set lines 200
select met.tablespace_name,
tblsp.block_size * met.used_space / 1024 / 1024 as "USED_SPACE_in_MB",
(met.tablespace_size - met.used_space) * tblsp.block_size / 1024 / 1024 as "FREE_SPACE_in_MB",
tblsp.block_size * met.tablespace_size / 1024 / 1024 as "TABLESPACE_SIZE_in_MB",
met.used_percent
from dba_tablespace_usage_metrics met,
dba_tablespaces tblsp
where tblsp.tablespace_name = met.tablespace_name
and met.tablespace_name = '&tablespace_name';

OR

*checking all tablespaces order by highest % used
set lines 200
select met.tablespace_name,
tblsp.block_size * met.used_space / 1024 / 1024 as "USED_SPACE_in_MB",
(met.tablespace_size - met.used_space) * tblsp.block_size / 1024 / 1024 as "FREE_SPACE_in_MB",
tblsp.block_size * met.tablespace_size / 1024 / 1024 as "TABLESPACE_SIZE_in_MB",
met.used_percent
from dba_tablespace_usage_metrics met,
dba_tablespaces tblsp
where tblsp.tablespace_name = met.tablespace_name
order by met.used_percent desc;

Oracle 9i

Oracle 9i and older versions of oracle makes it hard to check for tablespace, you need to make a query to join the dba_free_space and dba_data_files tables.

set lines 200
select tablespace.tablespace_name,
round(dbf.meg-free.meg,2) as "USED_SPACE_in_MB",
round(free.meg,2) as "FREE_SPACE_in_MB",
round(dbf.meg,2) as "TABLESPACE_SIZE_in_MB",
(dbf.meg-free.meg)/dbf.meg*100 as "USED_PERCENT"
FROM dba_tablespaces tablespace,
(select tablespace_name,
sum( bytes ) / 1024 / 1024 meg
from dba_data_files
group by tablespace_name ) dbf,
(select tablespace_name,
sum( bytes ) / 1024 / 1024 meg
from dba_free_space
group by tablespace_name ) free
where tablespace.tablespace_name = dbf.tablespace_name
and dbf.tablespace_name = free.tablespace_name
and tablespace.tablespace_name = '&tablespace_name';

OR

*checking all tablespaces order by highest % used
set lines 200
select tablespace.tablespace_name,
round(dbf.meg-free.meg,2) as "USED_SPACE_in_MB",
round(free.meg,2) as "FREE_SPACE_in_MB",
round(dbf.meg,2) as "TABLESPACE_SIZE_in_MB",
(dbf.meg-free.meg)/dbf.meg*100 as "USED_PERCENT"
from dba_tablespaces tablespace,
(select tablespace_name,
sum( bytes ) / 1024 / 1024 meg
from dba_data_files
group by tablespace_name ) dbf,
(select tablespace_name,
sum( bytes ) / 1024 / 1024 meg
from dba_free_space
group by tablespace_name ) free
where tablespace.tablespace_name = dbf.tablespace_name
and dbf.tablespace_name = free.tablespace_name
Order by USED_PERCENT desc;

Adding and Checking Datafiles

After determining the space needed for the tablespace, we need to check the datafiles if they are on ASM or using OMF (Oracle Managed Files) or Both or just the classic method.

set lines 130
set pages 100
col file_name for a60
col tablespace_name for a25
col mb_alloc for 999999
col status for a10
select file_name,
tablespace_name,
bytes/1024/1024 AS MB_ALLOC,
status
from dba_data_files
where tablespace_name = '&tablespace_name'
order by file_id asc;

set lines 130
set pages 100
col name for a30
col total_mb for 999999
col free_mb for 999999
col useable_file_mb 999999
select name,
total_mb,
free_mb,
usable_file_mb
from v$asm_diskgroup;

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart