Here in this article , we will checking how to check tablespace in Oracle database, tablespace free space,tablespace growth information,tablespace size ,associated datafiles with tablespace,checking highest allocated extent in the tablespace
how to check tablespace in oracle
To list the names and various other 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
To list the Datafiles and Associated Tablespace of a Database
To list the names, 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 usage
To produce statistics about free extents and coalescing activity for each tablespace in the database, enter the following query:
set echo off feedback off verify off pages 75Download sql
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
/
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)/(1024*1024) used_mb,
tablespace_size tablespace_blocks,
(tablespace_size*&dbblocks)/(1024*1024) tablespace_mb,
used_percent
from dba_tablespace_usage_metrics;
How to check highest allocated extent
column file_name format a50;
column tablespace_name format a15;
column highwater format 9999999999;
set pagesize 9999select 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 , largest free chunk and no of free chunk in tablespace.
set feedback offDownload sql
set echo off
set numwidth 15
set linesize 150
set pages 1000Accept 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');
How to check tablespace size in oracle
The below query will provide the tablespace size in MB
Download sqlSelect (sum(bytes)/1024/1024) Space_allocated
from dba_data_files
where tablespace_name=upper('&tname');
To check all tablespace information in the database
set echo off feedback off verify off pages 60col tablespace_name format a16 head 'Tablespace Name'Download sql
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 head 'Status'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
/
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 below script 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 last 7 days and predict based on last 7 days growth. I would recommend to change AWR retention to at least 35 days – this will also be more helpful in case of 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 )/(1024*1024),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;
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
ORA-01652
How to shrink the datafile in Oracle
Oracle database administration tutorial
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm
Leave a Reply