Home » SQL & PL/SQL » SQL & PL/SQL » TABLESPACE PERCENT
TABLESPACE PERCENT [message #237697] Tue, 15 May 2007 08:40 Go to next message
aboodlardy
Messages: 80
Registered: November 2006
Location: India
Member
Hi
I want to write a query which gives me tablespace storage infm
such as
tablespace totalsize free used
---------- ---------- ----- ----



i have tried this
select t1.tablespace_name,t1.bytes,t2.bytes, t2.bytes/t1.bytes*100
from dba_data_files t1, dba_free_space t2
where t1.tablespace_name=t2.tablespace_name;

BUT IT REPEATS THE TABLESPACE NAME.
NOTE: SOME TABLESPACE CONTAINS MORE THAN ONE DATAFILE.
PLEASE HELP
Re: TABLESPACE PERCENT [message #237707 is a reply to message #237697] Tue, 15 May 2007 08:59 Go to previous messageGo to next message
anil_apps
Messages: 34
Registered: November 2005
Location: India
Member

Hi,
I dont know exact query by using following 3 statements we can find


SET ECHO OFF;

SET TERM OFF;

SET TIMING OFF;

SET HEAD OFF;

SET FEED OFF;



CREATE TABLE temp_ts (tablespace_name,total_bytes,free_bytes,max_chunk)

AS

SELECT tablespace_name, NVL(SUM(bytes), 1), 1, 1

FROM dba_data_files

GROUP BY tablespace_name;







UPDATE temp_ts a

SET a.free_bytes = (SELECT NVL(SUM(b.bytes), 1)

FROM dba_free_space b

WHERE b.tablespace_name = a.tablespace_name);

COMMIT;



UPDATE temp_ts a

SET a.max_chunk = (SELECT NVL(MAX(b.bytes), 1)

FROM dba_free_space b

WHERE b.tablespace_name = a.tablespace_name);

COMMIT;





REM ********************************************************************

REM Display tablespaces more than 95% full.

REM ********************************************************************







SELECT

tablespace_name ||

' is ' ||

TO_CHAR(ROUND(100-(free_bytes*100/total_bytes), 2)) ||

'% full.' T

FROM temp_ts

WHERE 95 < 100-(free_bytes*100/total_bytes)

ORDER BY tablespace_name;

Regards
Anil
Re: TABLESPACE PERCENT [message #237708 is a reply to message #237697] Tue, 15 May 2007 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well start with the basic.
If you want the total of space in your tablespaces how do you write?
If you want the total of free space in your tablespaces how do you write it?

When you have these both queries, join them.

Regards
Michel
Re: TABLESPACE PERCENT [message #237719 is a reply to message #237708] Tue, 15 May 2007 09:11 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Include in your query:
datafiles with fixed size vs. autoextensible datafiles

Re: TABLESPACE PERCENT [message #237744 is a reply to message #237697] Tue, 15 May 2007 10:07 Go to previous messageGo to next message
puneet.kakkar
Messages: 11
Registered: February 2007
Junior Member
select a.tablespace_name, (sum(a.bytes)+sum(b.bytes)) as total,sum(a.bytes)as used,sum(b.bytes) as free from dba_Data_files a,dba_free_space b
where a.tablespace_name=b.tablespace_name
group by a.tablespace_name

The size is in bytes...
Re: TABLESPACE PERCENT [message #237758 is a reply to message #237744] Tue, 15 May 2007 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dba_data_files contains the whole file space not only the used one.

Regards
Michel
Re: TABLESPACE PERCENT [message #237851 is a reply to message #237758] Wed, 16 May 2007 00:16 Go to previous messageGo to next message
aboodlardy
Messages: 80
Registered: November 2006
Location: India
Member
HI
thanks u all
i get it with your help
here is my query,


select a.tablespace_name,round(sum(a.bytes/1024/1024),2) "Total(mb)",
round(sum(b.bytes/1024/1024),2) "free(mb)",
round(100-sum(b.bytes/1024/1024)/sum(a.bytes/1024/1024)*100,2) "Used%"
from dba_data_files a,
dba_free_space b
where a.tablespace_name=b.tablespace_name
group by a.tablespace_name;
Re: TABLESPACE PERCENT [message #237863 is a reply to message #237851] Wed, 16 May 2007 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
tablespace_name is neither the (logical) primary key for dba_data_files nor dba_free_space, so you get many rows for the same tablespace (in the join, before the group by) and the result is wrong.

Try what I said previously.

Regards
Michel

[Updated on: Wed, 16 May 2007 00:55]

Report message to a moderator

Re: TABLESPACE PERCENT [message #237871 is a reply to message #237697] Wed, 16 May 2007 01:05 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Michel already suggested it.
Try

SELECT t.tablespace_name, t.bytes total, f.bytes free
FROM
  ( SELECT tablespace_name, sum(bytes) bytes FROM dba_data_files
    GROUP BY tablespace_name ) t,
  ( SELECT tablespace_name, sum(bytes) bytes FROM dba_free_space
    GROUP BY tablespace_name ) f
WHERE t.tablespace_name = f.tablespace_name


You still have to deal with AUTOEXTENSIBLE datafiles.

HTH.
Michael
Re: TABLESPACE PERCENT [message #237892 is a reply to message #237871] Wed, 16 May 2007 01:41 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well, I tried to make it find a solution and learn how to find it.
Too bad you gave it. ./fa/1601/0/

Regards
Michel
Previous Topic: help with queries
Next Topic: Merge statement - oracle errors
Goto Forum:
  


Current Time: Wed Dec 07 04:29:27 CST 2016

Total time taken to generate the page: 0.10526 seconds