Home » SQL & PL/SQL » SQL & PL/SQL » show % of tablespace correctly
show % of tablespace correctly [message #358241] Mon, 10 November 2008 05:16 Go to next message
guif
Messages: 17
Registered: November 2008
Junior Member
I have this query to know the tablespace more than 80%:

column tablespaces format A15
column maxfree format 9G999G999
column free format 9G999G999
column tot format 9G999G999
clear compute
clear breaks
break on report
compute sum of free on report
compute sum of tot on report

select C.tablespaces,
C.total Total,
(C.total-D.free) busy ,
D.free free,
round((100*(C.total-D.free)/C.total)) porc_busy
from ( select A.tablespaces ,
round (max(A.bytes)/1024) maxfree,
round (sum(A.bytes)/1024) free
from dba_free_space A
group by A.tablespaces ) D,
(select tablespaces,
round( sum(B.bytes)/1024) total
from dba_data_files B
group by B.tablespaces) C
where D.tablespaces = C.tablespaces and (round((100*(C.total-D.free)/C.total)) > 80);


exit;

But.... this query is not "good" because I have more tablespaces with the "autoextend" mode.
Is it possible to redefine this query to see the exactly space? or any solution?

thanks!
Re: show % of tablespace correctly [message #358243 is a reply to message #358241] Mon, 10 November 2008 05:21 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.

http://www.orafaq.com/forum/m/194630/94420/?srch=calculate+freespace#msg_194630

Regards

Raj
Re: show % of tablespace correctly [message #358250 is a reply to message #358243] Mon, 10 November 2008 05:52 Go to previous messageGo to next message
guif
Messages: 17
Registered: November 2008
Junior Member
thanks for your quickly answer.

Unfortunately, my lvel of oracle is very poor. My Select is "correctly" but not show the information correctly.
It's necesary that I modify my query?

[Updated on: Mon, 10 November 2008 05:52]

Report message to a moderator

Re: show % of tablespace correctly [message #358270 is a reply to message #358241] Mon, 10 November 2008 08:03 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
guif wrote on Mon, 10 November 2008 06:16

But.... this query is not "good" because I have more tablespaces with the "autoextend" mode.
Is it possible to redefine this query to see the exactly space? or any solution?



What do you mean? Please explain further with examples of what you see and what you want.
Re: show % of tablespace correctly [message #358272 is a reply to message #358270] Mon, 10 November 2008 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
joy_division wrote on Mon, 10 November 2008 15:03
guif wrote on Mon, 10 November 2008 06:16

But.... this query is not "good" because I have more tablespaces with the "autoextend" mode.
Is it possible to redefine this query to see the exactly space? or any solution?



What do you mean? Please explain further with examples of what you see and what you want.

And also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: show % of tablespace correctly [message #358279 is a reply to message #358241] Mon, 10 November 2008 08:40 Go to previous messageGo to next message
guif
Messages: 17
Registered: November 2008
Junior Member
when I insert this Select, It returns all my tablespaces more than 80%. It's correct, but in more cases the result is 100%.

This result aren't correct because the table space are in mode autoextend, and I will know the exactly space

(sorry... my english is very poor!)
Re: show % of tablespace correctly [message #358281 is a reply to message #358279] Mon, 10 November 2008 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: show % of tablespace correctly [message #358282 is a reply to message #358281] Mon, 10 November 2008 09:01 Go to previous messageGo to next message
guif
Messages: 17
Registered: November 2008
Junior Member
if you read my first post, the query results:



TABLESPACE_NAME TOTAL OCUPADO LIBRE PORC_OCUPADO
--------------- ---------- ---------- ---------- ------------
UNDOTBS 1658880 1657856 1024 100


100 Is not real!!!

[Updated on: Mon, 10 November 2008 09:01]

Report message to a moderator

Re: show % of tablespace correctly [message #358284 is a reply to message #358282] Mon, 10 November 2008 09:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you unable to read our posts and links we posted?
If you want help, follow the rules.

Regards
Michel
Re: show % of tablespace correctly [message #358323 is a reply to message #358282] Mon, 10 November 2008 12:31 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
1657856 / 1658880 is as close to 100 as you will get. It equals 99.93827. What is wrong with that?
Re: show % of tablespace correctly [message #358438 is a reply to message #358323] Tue, 11 November 2008 05:57 Go to previous messageGo to next message
guif
Messages: 17
Registered: November 2008
Junior Member
yes, but 99.93827 not's 100.
Re: show % of tablespace correctly [message #358442 is a reply to message #358438] Tue, 11 November 2008 06:24 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@guif,

guif wrote on Tue, 11 November 2008 17:27
yes, but 99.93827 not's 100.


If you don't need rounding off remove the ROUND Functions from your code wherever necessary.

Regards,
Jo
Re: show % of tablespace correctly [message #358443 is a reply to message #358438] Tue, 11 November 2008 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle overhead.
Read Database Concepts.

Regards
Michel
Re: show % of tablespace correctly [message #358493 is a reply to message #358241] Tue, 11 November 2008 10:28 Go to previous message
guif
Messages: 17
Registered: November 2008
Junior Member
ok thanks people!
Previous Topic: Merge TWO or more Result Rows in ONE
Next Topic: Deviation of monthly totals?
Goto Forum:
  


Current Time: Mon Dec 05 14:52:28 CST 2016

Total time taken to generate the page: 0.09543 seconds