5 Replies Latest reply: Nov 23, 2012 8:49 AM by 939412 RSS

    Query to monitor storage space (Oracle xe 10g)

    939412
      Hello,

      Can someone please tell me the query to find out the storage space used by a oracle XE 10g database. I want to find out how much storage space is left out of the 4GB limit.

      I cannot access the homepage of the database, since the remote server where the database is hosted is a Debian machine without a GUI.

      Thanks.
        • 1. Re: Query to monitor storage space (Oracle xe 10g)
          JustinCave
          SELECT sum(bytes)/1024/1024/1024 gb_used
            FROM dba_segments
           WHERE owner NOT IN ('SYS', 'SYSTEM')
          should give you what you're after.

          Justin
          • 2. Re: Query to monitor storage space (Oracle xe 10g)
            orafad
            936409 wrote:
            Can someone please tell me the query to find out the storage space used by a oracle XE 10g database. I want to find out how much storage space is left out of the 4GB limit.

            I cannot access the homepage of the database, since the remote server where the database is hosted is a Debian machine without a GUI.
            Isn't there some sort of storage summary available via "homepage" of XE.

            Even if the server machine itself has no "GUI" for remote control, why can't you simply use a browser and access homepage/Apex in the XE instance?

            Otherwise take a look at dba_tablespaces, dba_segments, dba_extents views. Maybe start with a sum(bytes) on group by owner.

            ...when thinking about it, maybe XE counts file size even if space is not yet allocated to segments. Cross check with dba_data_files (file_name, bytes, tablespace_name).


            Edit:
            added thought on data files.

            Edited by: orafad on Nov 13, 2012 12:06 PM
            • 3. Re: Query to monitor storage space (Oracle xe 10g)
              Veeresh.S
              for db level you can use -

              select c.tablespace_name,
              round(decode(d.autoext,NULL,decode(e.free,NULL,0,e.free)/(1024*1024),(f.maxsz+decode(g.nonauto_sz,NULL,0,g.nonauto_sz)-f.totsz+decode(e.free,NULL,0,e.free))/(1024*1024))) free_mb,
              round(f.totsz/(1024*1024)) Total_mb,
              round((f.maxsz+decode(g.nonauto_sz,NULL,0,g.nonauto_sz))/(1024*1024)) maxsize_mb,
              decode(d.autoext, NULL, 'NO','YES') ae
              ,round(decode(d.autoext,NULL,(f.totsz-decode(e.free,NULL,0,e.free))/f.totsz*100,(f.totsz-decode(e.free,NULL,0,e.free))/(f.maxsz+decode(g.nonauto_sz,NULL,0,g.nonauto_sz))*100)) Used_pct
              from dba_tablespaces c,
              (select a.tablespace_name, count(b.autoextensible)autoext
              from dba_tablespaces a, dba_data_files b
              where a.tablespace_name = b.tablespace_name(+)and
              a.contents not in ('TEMPORARY','UNDO') and
              b.autoextensible = 'YES'
              group by a.TABLESPACE_NAME
              having count(b.autoextensible) > 0) d,(select tablespace_name, sum(bytes) free
              from dba_free_space
              group by tablespace_name) e,
              (select df1.tablespace_name, sum(df1.bytes) totsz, sum(df1.maxbytes) maxsz
              from dba_data_files df1, dba_tablespaces ts1
              where ts1.contents not in ('TEMPORARY','UNDO') and
              ts1.tablespace_name = df1.tablespace_name
              group by df1.tablespace_name
              ) f, (select df2.tablespace_name, sum(df2.bytes) nonauto_sz
              from dba_data_files df2, dba_tablespaces ts2
              where ts2.contents not in ('TEMPORARY','UNDO') and
              ts2.tablespace_name = df2.tablespace_name and
              df2.autoextensible = 'NO'
              group by df2.tablespace_name
              ) g where c.tablespace_name = d.tablespace_name(+) and
              c.contents not in ('TEMPORARY','UNDO') and
              c.tablespace_name = e.tablespace_name(+) and
              c.tablespace_name = f.tablespace_name and
              c.tablespace_name = g.tablespace_name(+);

              select tablespace_name,FILE_NAME, file_id,BYTES/1024/1024 as used_mb, AUTOEXTENSIBLE, MAXBYTES/1024/1024 as max_mb from dba_data_files order by file_id; -- used_mb-max_mb gives free_mb left

              or for free_mb you can try

              select file_id,sum(BYTES/1024/1024) as free_mb from dba_free_space group by file_id order by file_id;

              for OS level below commands gives you some idea

              df -h /u01/app/oracle/oradata, du -sch /u01/app/oracle/oradata
              • 4. Re: Query to monitor storage space (Oracle xe 10g)
                AlexandarVu
                For remote login to database home page, check out
                http://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/getstart.htm#CHDCEAGE
                and to enable remote login
                http://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/network.htm#BHCBCFBA
                Do this with sqlPlus and you should be able to access remote database homepage from local computer.
                • 5. Re: Query to monitor storage space (Oracle xe 10g)
                  939412
                  The remote login worked, thank you for the idea "orafad". I can't believe how I didn't think of this before. What I did was to login to the VPN and just enter the IP of the database server e.g.: "IP:8080/apex" and I was able to access the database home page and monitor the storage. Thank you all for the replies.

                  Edited by: 936409 on 23.11.2012 06:49