Home » RDBMS Server » Server Administration » howto get the tablespace size and free space?
howto get the tablespace size and free space? [message #281059] Thu, 15 November 2007 09:58 Go to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

i was trying to determine how many space i have in each tablespace.
but i get with an error. maybe i am doing wrong the join.
col c1 heading 'Tablespace'			format a20
col c2 heading 'Free|space'			format 999999999
col c3 heading 'Total|tbs'			format 999999999
col c4 heading '% occuped'			format 999
select 
	f.tablespace_name c1,
	round(sum(f.bytes/1024/1024)) c2,
	round(sum(d.bytes/1024/1024)) c3, 
	100-(round(sum(f.bytes/1024/1024))/round(sum(d.bytes/1024/1024))*100) c4
from 
	dba_free_space f, 
	dba_data_files d
where
	f.tablespace_name=d.tablespace_name
having
	(round(sum(f.bytes/1024/1024))/round(sum(d.bytes/1024/1024))*100)<20
group by 
	f.tablespace_name;




does anyone know why?
maybe i must try to join dba_tablespaces- dba_data_files- dba_free_space but using tablespace_name maybe is no necesary or maybe i am wrong :S
Re: howto get the tablespace size and free space? [message #281065 is a reply to message #281059] Thu, 15 November 2007 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

but i get with an error.

Which error?
What is Oracle version? You should know now you have to post that.

Regards
Michel
Re: howto get the tablespace size and free space? [message #281068 is a reply to message #281065] Thu, 15 November 2007 10:49 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

sorry michael
is not an error, how to explain
ok the best way to explain it is showing (the result)

-------------------- ---------- ---------- ----------
AHORA_DAT                 11076    2468400        100
AHORA_IND                  1766     355100        100
BL_IDX                     6212      92950         93
BL_USR                     5054     110400         95
COMPE_DAT                   152        500         70
COMPE_IND                    94        250         62
EPS_DAT                      49         50          2
EPS_IND                      25         25          0
OINVP                      1884      88000         98
RBS                        2898    7635000        100
SEGURIDAD_DAT              1484       1500          1
SEGURIDAD_IND               500        500          0
SIP_DAT                     553       2000         72
SIP_IND                      22        150         85
SIP_LOG                     178       1000         82
SOPORDBA                    200       9800         98
SYSTEM                      342       7200         95
TOOLS                       100       1300         92
USERS                       259       3000         91
UTL                          42        300         86

the thing is that the database has not that tablespaces
ill show you
select 
       sum(bytes)/1024/1024, 
       tablespace_name 
from 
       dba_free_space 
group by 
       tablespace_name;

SUM(BYTES)/1024/1024 TABLESPACE_NAME
-------------------- ------------------------------
                1845 AHORA_DAT
            588.6875 AHORA_IND
           1242.4375 BL_IDX
            1010.875 BL_USR
            151.9375 COMPE_DAT
             93.9375 COMPE_IND
             49.0625 EPS_DAT
             24.6875 EPS_IND
             942.125 OINVP
          2898.43555 RBS
            1484.375 SEGURIDAD_DAT

SUM(BYTES)/1024/1024 TABLESPACE_NAME
-------------------- ------------------------------
          499.511719 SEGURIDAD_IND
            552.5625 SIP_DAT
             22.1875 SIP_IND
            178.1875 SIP_LOG
          199.998047 SOPORDBA
          341.642578 SYSTEM
             99.6875 TOOLS
            258.9375 USERS
             41.9375 UTL

if you see, the AHORA_DAT tbs the real free space is 1.8gb and not 11gb and the same with the sum of the datafiles
that tbs has 11.8gb and not 2Tb
that&acute;s the "error" sorry for my wrong expresion


i dont know if is a version /O.S. problem i was thinking that maybe i was doing a bad join like a crossjoin
Re: howto get the tablespace size and free space? [message #281069 is a reply to message #281068] Thu, 15 November 2007 11:00 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tablespaces have many files, you just can't join on tablespace name.
In addition, some tablespaces may have no free space, so an outer join is required.

Regards
Michel
Previous Topic: Space Management
Next Topic: Autoextend
Goto Forum:
  


Current Time: Thu Dec 08 00:17:34 CST 2016

Total time taken to generate the page: 0.11009 seconds