Getting Schema - Datafile - Tablespace Relation with SQL

Last week i posted a single query how to identify the relation between datafiles, tablespaces and users. Because of the Twitter limitation of 140Chars i posted it as a screenshot. Here is now the whole query

with table_spaces as
  (select sum(bytes) / 1000000 mb_free, tablespace_name
  from dba_free_space group by tablespace_name),
     data_files as
  (select f.tablespace_name
         ,bytes / 1000000 file_size_mb
         ,maxbytes / 1000000 file_size_max_mb
         ,autoextensible
         ,file_name
   from dba_data_files f),
     schemas as
   (select distinct owner, tablespace_name from dba_segments)
select s.owner
      ,t.tablespace_name
      ,round((t.file_size_mb-d.mb_free),2) file_size_mb
      ,round(((t.file_size_mb-d.mb_free) / t.file_size_mb) * 100,2) percent_content_vs_filesize
      ,round(t.file_size_mb,2) file_size_mb
      ,round((t.file_size_mb / t.file_size_max_mb) * 100,2) perc_content_vs_filesize_max
      ,round(t.file_size_max_mb,2) file_size_max_mb
      ,autoextensible
      ,t.file_name
from data_files t
join table_spaces d on d.tablespace_name = t.tablespace_name
left join schemas s on s.tablespace_name = d.tablespace_name
order by s.owner, t.tablespace_name

 the result looks like this:

If you are only interested in the tablespace-datafile relation you can use this query:

with daten as
  (select sum(bytes) / 1000000 mb_free, tablespace_name
  from dba_free_space group by tablespace_name),
     data_files as
  (select f.tablespace_name
         ,bytes / 1000000 file_size_mb
         ,maxbytes / 1000000 file_size_max_mb
         ,autoextensible
   from dba_data_files f)
select t.tablespace_name
      ,round((t.file_size_mb-d.mb_free),2) file_size_mb
      ,round(((t.file_size_mb-d.mb_free) / t.file_size_mb) * 100,2) percent_content_vs_filesize
      ,round(t.file_size_mb,2) file_size_mb
      ,round((t.file_size_mb / t.file_size_max_mb) * 100,2) perc_content_vs_filesize_max
      ,round(t.file_size_max_mb,2) file_size_max_mb
      ,autoextensible
from data_files t
join daten d on d.tablespace_name = t.tablespace_name
order by t.tablespace_name

 This is the result:

The queries can help to identfiy how much space is left in a tablespace or datafile. Or you can identify how big the datafiles can be in maximum. And of course you can identfiy the default datafiles which are created by apex when you don't use any external script. In normal case these datafiles are named like: APEX_XXXXXXXXXX.

Inhalt des APEX UserGroup Düsseldorf Treffens am 14.07.2015

In knapp 2 Wochen findet das nächste Treffen der APEX UserGroup / DOAG Regionaltreffen-NRW statt. Inhalte sind:

• SVG in Interactive Reports (Vortrag) - Tobias Strohmeyer (Twitter)
• APEX 5 (Diskussion/Austausch) - Oliver Lemm (Twitter, XING)
• Single-Sign-On (Vortrag) - Niels de Bruijn (Twitter, XING)

Innerhalb der Diskussion werden erste Erfahrungen mit APEX 5 und mögliche Umstiegsszenarien angesprochen von Vorversionen angesprochen. Es wird wie üblich um kostenfreie Anmeldung gebeten, damit die Teilnehmerzahl im vorraus feststeht.
Wichtig: Der Termin findet diesmal in der Oracle Düsseldorf Geschäftsstelle statt.

Datum & Uhrzeit:	Di, 14.07.2015, 17-20Uhr
Ort:	Oracle Düsseldürf, Hamborner Str. 51, 40472 Düsseldorf
Anmeldung:	DOAG Regionaltreffen Anmeldelink (kostenfrei)
Ansprechpartner:	Oliver Lemm (Twitter, XING, XING-Gruppe)