This discussion is archived
5 Replies Latest reply: Nov 23, 2012 6:49 AM by 939412 RSS

Query to monitor storage space (Oracle xe 10g)

939412 Newbie
Currently Being Moderated
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)
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points