This content has been marked as final. Show 5 replies
should give you what you're after.
SELECT sum(bytes)/1024/1024/1024 gb_used FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM')
936409 wrote:Isn't there some sort of storage summary available via "homepage" of XE.
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.
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).
added thought on data files.
Edited by: orafad on Nov 13, 2012 12:06 PM
for db level you can use -
decode(d.autoext, NULL, 'NO','YES') ae
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
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
For remote login to database home page, check out
and to enable remote login
Do this with sqlPlus and you should be able to access remote database homepage from local computer.
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