Space Consumption Oracle SQL Query By Tablespace, Datafiles, Schema, DB Users

(Please Note my other blog address: svgulhane wordpress IT updates)

 Default tablespace

SELECT PROPERTY_VALUE

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE property_name like 'DEFAULT%TABLESPACE';

Finding Free space in detail 

Query 1

SET LINESIZE 300

SET PAGESIZE 225

column File_path format a60

column AUTOEXTENSIBLE  format a3

column Allocated format 999,999,9999

column Free format 999,999,9999

column Maxsize format 9999999,9999

column Yet_Not_alloc_MB format 9999999,9999

select

a.tablespace_name,

sum(a.BYTES/(1024*1024))Allocated,

(b.free_space) Free,

sum(a.maxbytes/(1024*1024))Maxsize ,

abs(sum((a.maxbytes/(1024*1024))-(a.BYTES/(1024*1024)))) Yet_Not_alloc_MB

from dba_data_files a,

(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space

from dba_free_space group by tablespace_name) b

where a.tablespace_name like('%&TS_Name%') and a.tablespace_name=b.tablespace_name

group by a.tablespace_name,free_space

order by a.tablespace_name ;

Query 2

select 

a.tablespace_name,
sum(a.BYTES/(1024*1024))used_MB,
sum(a.maxbytes/(1024*1024))maxsize_MB ,
abs(sum((a.maxbytes/(1024*1024))-(a.BYTES/(1024*1024)))) free_MB
from dba_data_files a
where a.tablespace_name like('%&TS_Name%')
group by a.tablespace_name
order by a.tablespace_name ;

Monitoring system tablespace Memory by Users

set linesize 300

set pagesize 300

SELECT tablespace_name, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB

FROM (

SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,

0 TOTAL_MB, 0 MAX_MB

FROM dba_free_space

GROUP BY tablespace_name

UNION

SELECT tablespace_name, 0 CURRENT_MB,

SUM(bytes)/1024/1024 TOTAL_MB,

SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB

FROM dba_data_files

GROUP BY tablespace_name) where tablespace_name in ('SYSTEM','FC12ITSUP1')

GROUP BY tablespace_name ;

2)Following is ok , script 1 will show system tablespace in –ve.

SELECT df.tablespace_name name,
fs.freespace free
FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) TotalSpace
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) FreeSpace
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
and df.tablespace_name IN ('SYSTEM','FC12ITSUP1') ;

Size(total,used,free) of DB Instance.(MB)

SET LINESIZE 100

SET PAGESIZE 200

column total_mb  format 999,999

column used format 999,999

column free format 999,999

SELECT --df.tablespace_name name,

       SUM(df.totalspace) total_mb,

       SUM(df.totalspace - fs.freespace) used,

       SUM(fs.freespace)free

  FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) TotalSpace

          FROM dba_data_files

         GROUP BY tablespace_name) df,

       (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) FreeSpace

          FROM dba_free_space

         GROUP BY tablespace_name) fs

 WHERE df.tablespace_name = fs.tablespace_name(+)  ;

Note IN RAC DB, Finding the size of the db group by the disk group

Space Utilization Group by DB File Names

SELECT  ROUND(SUM(bytes) / 1048576) TotalSpace , substr(file_name,2,10)

          FROM dba_data_files

         GROUP BY substr(file_name,2,10) ;

Finding file Location, Space Utilized, Max Space Allocated, Autoextend Y/N

SET LINESIZE 300

SET PAGESIZE 225

column File_path format a60

column AUTOEXTENSIBLE  format a3

column used_G format 999,999

column maxsize_G format 999,999

column user_bytes format 9999999

select

a.file_name File_path,

a.BYTES/(1024*1024*1024)used_G,

a.maxbytes/(1024*1024*1024)maxsize_G ,

a.AUTOEXTENSIBLE

 , b.creation_time

, a.user_bytes/(1024*1024) user_bytes

-- , a.INCREMENT_BY

from dba_data_files a,v$datafile b

where a.file_id =b.file#

and a.tablespace_name like('%&TS_Name%')

and a.file_name like('%&File_Name%')

order by a.file_name ,b.creation_time

--==--

select sum(bytes)/(1024*1024),sum(maxbytes)/(1024*1024),sum(maxbytes)/(1024*1024)-sum(bytes)/(1024*1024)"FreeMB", tablespace_name from dba_data_files group by tablespace_name;

--==--

select sum(bytes)/(1024*1024),sum(maxbytes)/(1024*1024),sum(maxbytes)/(1024*1024)-sum(bytes)/(1024*1024)"FreeMB", tablespace_name from dba_data_files where tablespace_name='FCUBS114' group by tablespace_name;

--==--

set linesize 800

set pagesize 100

set col file_id a20

set col file_name a20

SELECT file_id, file_name

FROM dba_data_files

WHERE tablespace_name = 'SYSTEM';

SELECT ROUND(SUM(bytes) / 1048576) TotalSpace_MB

          FROM dba_data_files WHERE tablespace_name = 'AXASKIT'

SELECT file_id,substr(file_name,1,100), ROUND(SUM(bytes) / 1048576) TotalSpace_MB

          FROM dba_data_files WHERE tablespace_name = 'SYSTEM' group by file_id,file_name order by file_id ;

SELECT file_id,substr(file_name,1,100), ROUND(SUM(bytes) / 1048576) TotalSpace_MB

          FROM dba_data_files group by file_id,file_name order by file_id ;

Finding the Tablespace Names and its space consumed

SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) TotalSpace_MB

          FROM dba_data_files group by tablespace_name order by tablespace_name ;

Finding Username and the its Space Consumed

select owner,round(sum(bytes)/1024/1024,2) "MB USED" from dba_segments where owner not in ('ANONYMOUS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','HR','IX','MDDATA','MDSYS','MGMT_VIEW','OE','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PM','SCOTT','SH','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','WMSYS','XDB','XS$NULL') group by owner;

Finding Total Space/Used Space/Free Space/PctFree in Datafiles group by Tablespace

 SELECT fs.tablespace_name name,

       df.totalspace total_mb,

       (df.totalspace - fs.freespace) used,

       fs.freespace free,

       100 * (fs.freespace / df.totalspace) pct_free

  FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) TotalSpace

          FROM dba_data_files

         GROUP BY tablespace_name) df,

       (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) FreeSpace

          FROM dba_free_space

         GROUP BY tablespace_name) fs

 WHERE df.tablespace_name = fs.tablespace_name(+);

Finding Total Space/Used Space/Free Space/PctFree in Datafiles group by tablespace (Display Name even if the tablespace having no free space)

SET LINESIZE 100

SET PAGESIZE 200

column name format a30

column total_mb  format 999,999

column used_mb format 999,999

column free_mb format 999,999

column pct_free format 99.99

SELECT df.tablespace_name name,

       df.totalspace total_mb,

       (df.totalspace - fs.freespace) used_mb,

       fs.freespace free_mb,

       100 * (fs.freespace / df.totalspace) pct_free

  FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) TotalSpace

          FROM dba_data_files

         GROUP BY tablespace_name) df,

       (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) FreeSpace

          FROM dba_free_space

         GROUP BY tablespace_name) fs

 WHERE df.tablespace_name = fs.tablespace_name(+)

and df.tablespace_name like '%&Tabspace_Name' order by total_mb ;

set linesize 300

set pagesize 300

SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB,

ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,

ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL

FROM (

SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,

0 TOTAL_MB, 0 MAX_MB

FROM dba_free_space

GROUP BY tablespace_name

UNION

SELECT tablespace_name, 0 CURRENT_MB,

SUM(bytes)/1024/1024 TOTAL_MB,

SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB

FROM dba_data_files

GROUP BY tablespace_name)

GROUP BY tablespace_name order by PCT_FULL;

SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) FreeSpace  FROM dba_free_space

Where tablespace_name = 'FBME53IC' GROUP BY tablespace_name

Used Bytes from Tablespace

column used format 999999999999999999

SELECT

(df.bytes  -  free.free_kb) used

FROM

   dba_data_files df,

   (SELECT file_id, SUM(bytes) free_kb

    FROM dba_free_space GROUP BY file_id) free

WHERE

   df.file_id=free.file_id(+) and tablespace_name like (‘&TABLESPACE')

ORDER BY

   Tablespace_name;

Allocated space, free space, USED SPACE and percentage of free space for each Tablespace Datafile. (KB)

SET LINESIZE 200

SET PAGESIZE 500

column tablespace_name format a20

column file_name_KB format a55

column Allocated format 999,999,999

column free  format 999,999,999

column NotAlloc format 99999

column Percent_Free format 999

SELECT

   df.tablespace_name,

   df.file_name file_name_KB,

   df.bytes/1024 Allocated,

   free.free_kb freespace,

df.bytes/1024  -  free.free_kb used,

df.maxbytes/(1024) max,

df.maxbytes/(1024) - ROUND(df.bytes/(1024)- free.free_kb) NotAlloc,

   Round(free.free_kb/(df.bytes/1024)*100) PCFree

FROM

   dba_data_files df,

   (SELECT file_id, SUM(bytes)/1024 free_kb

    FROM dba_free_space GROUP BY file_id) free

WHERE

   df.file_id=free.file_id(+) and tablespace_name like ('%&TabSpc')

ORDER BY

   Tablespace_name,PcFree;

Allocated space, free space, USED SPACE and percentage of free space for each Tablespace Datafile. (MB)

SET LINESIZE 200

SET PAGESIZE 500

column tablespace_name format a20

column file_name_MB format a55

column Alloc format 99999

column Free format 99999

column NotAlloc format 99999

column Percent_Free format 999

SELECT

   df.tablespace_name,

   df.file_name file_name_MB,

   df.bytes/(1024*1024) Alloc,

   free.free_mb Free,

ROUND(df.bytes/(1024*1024)- free.free_mb) USED,

df.maxbytes/(1024*1024) max,

df.maxbytes/(1024*1024) - ROUND(df.bytes/(1024*1024)- free.free_mb) NotAlloc,

   (free.free_mb/(df.bytes/(1024*1024))*100) PCFree

FROM

   dba_data_files df,

   (SELECT file_id, SUM(bytes)/(1024*1024) free_mb

    FROM dba_free_space GROUP BY file_id) free

WHERE

   df.file_id=free.file_id(+) and tablespace_name like ('%&TabSpc')

ORDER BY

   Tablespace_name,PcFree;

Allocated space, free space and percentage of free space for each Tablespace Datafiles. (GB)

SET LINESIZE 300

SET PAGESIZE 200

column tablespace_name format a25

column file_name format a75

column Allocated_gb format 999,999,999

column free_gb format 999,999,999

column Percent_Free format 999

SELECT

   df.tablespace_name,

   df.file_name,

   df.bytes/(1024*1024*1024) Allocated_gb,

   free.free_gb free_gb,

   Round(free.free_gb/(df.bytes/(1024*1024*1024))*100) Percent_Free

FROM

   dba_data_files df,

   (SELECT file_id, SUM(bytes)/(1024*1024*1024) free_gb

    FROM dba_free_space GROUP BY file_id) free

WHERE

   df.file_id=free.file_id(+)

--and df.tablespace_name like ('&BDBFFT')

ORDER BY    Percent_Free;

Creating a Bigfile Tablespace

CREATE BIGFILE TABLESPACE bigfiletbs

DATAFILE '/u02/oracle/data/bigfiletbs01.dbf' SIZE 200G

kilobytes (K)

megabytes (M), 

gigabytes (G), 

terabytes (T).

You can specify default tablespace type to BIGFILE at database creation, here need not specify the keyword BIGFILE in the CREATE TABLESPACE statement and it will accept only one database file. Every time by default bigfile tablespace will get created.

For Small file type of tablespace then specify a CREATE SMALLFILE TABLESPACE statement to exclude the default tablespace option for the tablespace.

Else if  you not mention Smallfile you will get error ORA-32774: more than one file was specified for bigfile

Database Views contain a BIGFILE column that provides a tablespace as a bigfile tablespace:

• DBA_TABLESPACES
• USER_TABLESPACES
• V$TABLESPACE

Tablespace Quota

SET LINESIZE 300

SET PAGESIZE 200

column tablespace_name format a25

column username format a30

column freemb_tablesp format 999,999,999

column max_alloc_quota_mb  format 999,999,999

select TABLESPACE_NAME,USERNAME,BYTES/(1024*1024) usedmb_tablesp,MAX_BYTES/(1024*1024) max_alloc_quota_mb  from dba_ts_quotas ;

select * from dba_ts_quotas where USERNAME='&User_name';

Alter user APACK2114 quota unlimited on APACK2114;

TEMPORARY TABLESPACE

 CREATE TEMPORARY TABLESPACE temp

 TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M

 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Col FILE_NAME

desc dba_temp_files;

select * from dba_temp_files;

Fiding the space used by each temp files in a temp tablespace

set linesize 900
column file_name  format a70
column used  format 9999999999
column maxbytes  format 99999999

select file_name,bytes/(1024*1024*1024) used, maxbytes/(1024*1024*1024) maxbytes,autoextensible from dba_temp_files;

By default temporary files are auto extendable up to 32GB

SELECT * from DBA_TEMP_FREE_SPACE;

Thanks And Regards

 Santoshkumar Gulhane

(Please Note my other blog address: svgulhane wordpress IT updates)