| howto get the tablespace size and free space? [message #281059] |
Thu, 15 November 2007 09:58  |
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 #281068 is a reply to message #281065] |
Thu, 15 November 2007 10:49  |
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´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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
