View original (French)

Size of an Oracle tablespace

darksquall57 Posted messages 33 Status Member -  
 HaddMoh -
Hello,

I would like to know how to retrieve the remaining space and the used space in a tablespace, in Oracle...

I have already searched on Google... and right now it's not my best friend lol...

5 answers

  1. Sans_pseudo
     
    Hello,

    There are plenty of solutions

    One of them:

    SELECT A.tablespace_Name, A.Alloue, B.Occupe, C.Libre
    2 FROM (select tablespace_name, sum(bytes)/1024/1024 AS ALLOUE from dba_data_files group by tablespace_name) a,
    3 (select tablespace_name, Sum(bytes)/1024/1024 AS OCCUPE from dba_segments group by tablespace_name) b,
    4 (select tablespace_name, Sum(bytes)/1024/1024 AS LIBRE from dba_free_space group by tablespace_name) c
    5 WHERE B.tablespace_Name = A.tablespace_Name
    6 AND C.Tablespace_Name = B.Tablespace_Name;

    Google does provide a lot: try using the search criteria "know the size of a tablespace"
    20
    1. paprika
       
      To improve the query so that it also displays tablespaces with allocated AND UNALLOCATED space:

      SELECT A.tablespace_Name, A.Alloue, B.Occupe, C.Libre
      FROM (select tablespace_name, sum(bytes)/1024/1024 AS ALLOUE from dba_data_files group by tablespace_name) A
      LEFT JOIN (select tablespace_name, Sum(bytes)/1024/1024 AS OCCUPE from dba_segments group by tablespace_name) B ON A.tablespace_Name = B.tablespace_Name
      LEFT JOIN (select tablespace_name, Sum(bytes)/1024/1024 AS LIBRE from dba_free_space group by tablespace_name) C ON A.tablespace_Name = C.tablespace_Name
      ORDER BY A.TABLESPACE_NAME;
      0