Home » Oracle » Oracle Database » How to check Tablespace in Oracle -Size,Free space,datafiles ,growth

How to check Tablespace in Oracle -Size,Free space,datafiles ,growth

  • In Oracle, tablespaces are logical storage units that can contain various types of database objects like tables, indexes, and others. To check the details of tablespaces in an Oracle database, you can use SQL queries against the data dictionary views
  • Here in this article, we will check how to check tablespace  in Oracle database, tablespace free space, tablespace growth information, tablespace size, associated datafiles with tablespace, checking the highest allocated extent in the tablespace

How to check tablespace in Oracle

To list the names and various other parameters of all tablespace in a database, use the following query on the DBA_TABLESPACES view:

SELECT TABLESPACE_NAME "TABLESPACE", EXTENT_MANAGEMENT,FORCE_LOGGING,
BLOCK_SIZE,
SEGMENT_SPACE_MANAGEMENT
FROM DBA_TABLESPACES;
Download

How to check tablespace in Oracle

With CDB Databases, you need to connect to individual PDB and execute the query to check the tablespace. If you want to gather all the tablespace across all the PDBs and Root containers, then you need to fire the below query connected to the root container

select tablespace_name, con_id from cdb_tablespaces;

How to List the Datafiles and Associated Tablespace of a Database

To list the datafiles, sizes, and associated tablespace of a database, enter the following query on the DBA_DATA_FILES view

SELECT
FILE_NAME,
BLOCKS,
TABLESPACE_NAME
FROM DBA_DATA_FILES;
Download sql

How to check Oracle tablespace Utilization

To produce statistics about total space, used space, free space and % used for each tablespace in the database, enter the following query:

set echo off feedback off verify off pages 75
col tablespace_name format a20 head 'Tablespace Name'
col total format 999,999,999,999 head 'Total(KB)'
col used format 999,999,999,999 head 'Used(KB)'
col free format 999,999,999,999 head 'Free(KB)'
col pct format 999 head 'Percent|Used'
break on report
compute sum of total on report
compute sum of used on report
compute sum of free on report
select tbs.tablespace_name,
tot.bytes/1024 total,
tot.bytes/1024-sum(nvl(fre.bytes,0))/1024 used,
sum(nvl(fre.bytes,0))/1024 free,
(1-sum(nvl(fre.bytes,0))/tot.bytes)100 pct from dba_free_space fre, 
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) tot, 
dba_tablespaces tbs where tot.tablespace_name = tbs.tablespace_name and 
fre.tablespace_name(+) = tbs.tablespace_name 
group by tbs.tablespace_name, tot.bytes/1024, tot.bytes 
union 
select tsh.tablespace_name, dtf.bytes/1024 total, sum(nvl(tsh.bytes_used,0))/1024 used, 
sum(nvl(tsh.bytes_free,0))/1024 free, 
(1-sum(nvl(tsh.bytes_free,0))/dtf.bytes)100 pct
from v$temp_space_header tsh,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) dtf
where dtf.tablespace_name = tsh.tablespace_name(+)
group by tsh.tablespace_name, dtf.bytes/1024, dtf.bytes
order by 1
/
Download sql
See also  Solaris OS parameter required for R12.2 Upgrade

With CDB Databases, you need to connect to individual PDB and execute the query.

We have another view dba_tablespace_usage_metrics introduced from 11.2 which can be used to find Oracle tablespace utilization. The values reported are in blocks, not bytes, which can be easily computed using the database block size

column value new_value dbblock noprint
select value from v$parameter where name = 'db_block_size';
select tablespace_name,
used_space used_blocks,
(used_space&dbblocks)/(10241024) used_mb,
tablespace_size tablespace_blocks,
(tablespace_size&dbblocks)/(10241024) tablespace_mb,
used_percent
from dba_tablespace_usage_metrics;


How to check the highest allocated extent

The highest allocated extent is the high watermark of the data file. This information is helpful when reducing the size of the datafile

column file_name format a50;
column tablespace_name format a15;
column highwater format 9999999999;
set pagesize 9999

select a.tablespace_name
,a.file_name
,(b.maximum+c.blocks-1)*d.db_block_size highwater
from dba_data_files a
,(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b
,dba_extents c
,(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id = b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name;

To check the free space, the largest free chunk and number of free chunks in the tablespace.

We can use the below query to determine the total free space, largest free check size and number of free chunks in the tablespace. This queries the dictionary view DBA_FREES_SPACE to get these details

set feedback off
set echo off
set numwidth 15
set linesize 150
set pages 1000
Accept tname Prompt "Enter Tablespace Name : "
Select (Sum(bytes)/1024/1024) Free_space_MB,(max(bytes)/1024/1024) Largest_Free_chunck_MB,
count(*) No_of_free_chunk
from dba_free_space where tablespace_name=upper('&tname');
Download sql

How to check tablespace size in Oracle

The below query will provide the tablespace size in MB

Select (sum(bytes)/1024/1024) Space_allocated
from dba_data_files
where tablespace_name=upper('&tname');
Download sql

The below query will provide the tablespace size in GB

Select (sum(bytes)/1024/1024/1024) Space_allocated
from dba_data_files
where tablespace_name=upper('&tname');

If Autoextensible is “On” on the tablespace, Tablespace can grow without explicitly adding the space. we can use the below query to find the size of the tablespace to which, it can be extended automatically if required

For MB
Select (sum(maxbytes)/1024/1024) Space_allocated
from dba_data_files
where tablespace_name=upper('&tname');

For GB
Select (sum(maxbytes)/1024/1024/1024) Space_allocated
from dba_data_files
where tablespace_name=upper('&tname');

To check all tablespace information in the database

The below query can be used to get detailed information about all the tablespace in the database

set echo off feedback off verify off pages 60
col tablespace_name format a16 head 'Tablespace Name'
col initial_extent format 99,999,999 head 'Initial|Extent(K)'
col next_extent format 99,999,999 head 'Next|Extent(K)'
col max_extents format a4 head 'Max|Ext'
col pct_increase format 999 head 'Pct|Inc'
col extent_management format a10 head 'Extent|Management'
col allocation_type format a10 head 'Allocation|Type'
col status format a7
select tbs.tablespace_name
, tbs.initial_extent
, tbs.next_extent
, decode(tbs.max_extents,2147483645,'UL',tbs.max_extents) max_extents
, tbs.pct_increase
, tbs.extent_management
, tbs.allocation_type
, tbs.status
from dba_tablespaces tbs
order by 1
/
Download sql
See also  Adop(Ad online patching utility) explained R12.2

How to check Oracle tablespace growth history

Starting Oracle 10G, Oracle records tablespaces usage (allocated, used, etc.) in AWR which can be retrieved by querying the data dictionary view dba_hist_tbspc_space_usage. We can use the below script to view the history of tablespace(s) usage and predict the expected growth for the future.

This script is based on AWR. If your AWR retention period is 7 days, this script can only tell the growth history of the last 7 days and predict based on the last 7 day’s growth. I would recommend changing AWR retention to at least 35 days – this will also be more helpful in case of a performance tuning situation as you will have a longer window from the past to look into for performance comparisons.

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(10241024),2) ) cur_size_MB , max(round((tsu.tablespace_usedsize dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;

How to check tablespace used by schema in Oracle

select distinct tablespace_name from dba_segments where owner='&schema';

How to list datafiles in tablespace in Oracle

select file_name from dba_data_files where tablespace_name like '%&tablespace%';

How to list datafiles with size in tablespace in Oracle

In MB

select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like '%&tablespace%';

In GB

 select file_name,bytes/1024/1024/1024 from dba_data_files where tablespace_name like '%&tablespace%'; 

Frequently Asked Questions (FAQs) on Checking Tablespaces in Oracle

Q1: What is a tablespace in Oracle?
A1: In Oracle, a tablespace is a logical storage unit that contains database objects like tables and indexes. It’s used to manage and organize data within the database.

Q2: How can I view the list of tablespaces in my Oracle database?
A2: You can view the list of tablespaces by querying the DBA_TABLESPACES view. Use the SQL command: SELECT tablespace_name FROM dba_tablespaces;.

Q3: How can I check the size of a specific tablespace?
A3: To check the size of a specific tablespace, you can query the DBA_DATA_FILES view. For example: SELECT SUM(bytes) FROM dba_data_files WHERE tablespace_name = 'your_tablespace_name';.

Q4: Is there a way to find out how much free space is available in a tablespace?
A4: Yes, you can find the free space by querying the DBA_FREE_SPACE view. For example: SELECT tablespace_name, SUM(bytes) AS free_space FROM dba_free_space GROUP BY tablespace_name;.

Q5: What are the best practices for managing tablespaces in Oracle?
A5: Best practices include regular monitoring of space usage, understanding autoextend settings, ensuring proper sizing based on growth patterns, and maintaining consistent backup routines.

See also  Oracle database 12c: Container Database (CDB) and Pluggable Database (PDB)

Q6: Are there any tools provided by Oracle for tablespace management?
A6: Yes, Oracle provides tools like Oracle Enterprise Manager (OEM) which offers a graphical interface for managing tablespaces and other database components.

I hope you like the queries given in the post for Oracle tablespace. Please do let me know your feedback

Related Articles

Oracle Create Tablespace: This article on how to create tablespace in Oracle, the various characteristics associated with it and different create tablespace statements
ORA-01652:ORA-01652 error usually because when the tablespace does not have free space in Permanent and Temporary tablespace in Oracle database. Check out how to resolve it
Shrink datafile in Oracle: Check out how to shrink the datafile and reclaim space on the filesystem. How to resolve ORA-03297
Oracle database administration tutorial: This lists all the Oracle DBA-related stuff. Very helpful for the administration
How to change default tablespace in Oracle: Default tablespace is the tablespace where the objects are created when no tablespace name is specified by users. Find out how to check the default tablespace
How to check temp tablespace in Oracle: This article is about temp tablespace in Oracle, resize the tempfile, drop the temp file, find the temp usage by Session
alter tablespace add datafile: Check out this post on How to add a datafile to tablespace in Oracle, add tempfile to the temporary tablespace, how to add datafile in ASM
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm

Recommended  Courses

The following are some of the recommended courses you can buy if you want to get a step further

Given below are the links to some of the courses


Oracle DBA 11g/12c – Database Administration for Junior DBA : This course is good for the people who are starting as Junior DBA or aspire to be Oracle DBA. This will provide a good understanding of backup & recovery and General administration tasks
Oracle Database: Oracle 12C R2 RAC Administration : This course covers the installation, administration of Oracle RAC. A good course for Oracle DBA who want to upgrade his skills for Oracle RAC
Oracle Data Guard: Database Administration for Oracle 12C R2 : This course covers the installation, administration of Oracle Dataguard. A good course for Oracle DBA who want to upgrade his skills for Oracle Dataguard

4 thoughts on “How to check Tablespace in Oracle -Size,Free space,datafiles ,growth”

  1. Hi, I would like to know how to reduce de size of sysaux tbs (pdb), right now the %used is 3%, i need to resize the tablespace. Thxs. I will appreciate any help that you can give me.

    (I’m new working with pluggable db, and english isn’t my first language, excuse me).

    1. SYSAUX tablespace contains objects on AWH,ASH, AUD$ ,Advisor Optimizer statistics.
      You need to evaluate size of each of these and then purge them
      Below queries will help determining it
      SQL> SET LINES 120
      SQL> COL OCCUPANT_NAME FORMAT A30
      SQL> SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC;

      SQL> COL SEGMENT_NAME FORMAT A30
      SQL> COL OWNER FORMAT A10
      SQL> COL TABLESPACE_NAME FORMAT A10
      SQL> COL SEGMENT_TYPE FORMAT A15
      SQL> SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024 “SIZE(MB)”,SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME=’SYSAUX’ ORDER BY BYTES DESC) WHERE ROWNUM<=10;

Leave a Comment

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

Scroll to Top