Home » RDBMS Server » Server Administration » how to find tablespace space information?
how to find tablespace space information? [message #183833] Mon, 24 July 2006 01:58 Go to next message
anandhi
Messages: 31
Registered: April 2006
Location: Chennai
Member
hi

I have to find the used space, free space and total space information for all the tablespaces (including temporary tablespace). I created a script to find out the tablespace information but that doesnt include the TEMP tablespace.

I have attached the script

select fs.tablespace_name, sum(fs.bytes/1024/1024) "Free Space",
sum(df.bytes/1024/1024) "Total Space",
sum(df.bytes/1024/1024) - sum(fs.bytes/1024/1024) "Used Space"
from dba_free_space fs, dba_data_files df, where fs.tablespace_name=df.tablespace_name
group by fs.tablespace_name

Please let me know how to include the information in the above mentioned script to find the TEMP tablespace information also

please some one help.

Thanks
anandhi
Re: how to find tablespace space information? [message #183860 is a reply to message #183833] Mon, 24 July 2006 03:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to use dba_temp_files as well as dba_data_files.

Note that the dba_free_space view doesn't give info on temp tablespaces, because the concept of 'free space' is rather meaningless there.

Also, do try not to use double quoted column names with spaces in them - it makes the queries look nice to select from, but they're a pain to do anything with.

select df.tablespace_name, sum(fs.bytes/1024/1024) Free_Space,
sum(df.bytes/1024/1024) Total_Space,
sum(df.bytes/1024/1024) - sum(fs.bytes/1024/1024) Used_Space
from dba_free_space fs
    ,(select * from dba_data_files
     union all
      select * from dba_temp_files) df
where fs.tablespace_name(+)=df.tablespace_name
group by df.tablespace_name;
Re: how to find tablespace space information? [message #183876 is a reply to message #183860] Mon, 24 July 2006 03:57 Go to previous messageGo to next message
anandhi
Messages: 31
Registered: April 2006
Location: Chennai
Member
thanks...

thanks
anandhi

Re: how to find tablespace space information? [message #183883 is a reply to message #183876] Mon, 24 July 2006 04:20 Go to previous messageGo to next message
anandhi
Messages: 31
Registered: April 2006
Location: Chennai
Member
hi

i tried this script, it does gives the total space used but it doesnt give the free space and used space.

Please help

thanks
Anandhi

TABLESPACE_NAME FREE_SPACE TOTAL_SPACE USED_SPACE
------------------------------ ---------- ----------- ----------
SYSTEM 402.882813 3650 3247.11719
TEMP 2001
TEMP_TS1 2025
Re: how to find tablespace space information? [message #183899 is a reply to message #183883] Mon, 24 July 2006 05:20 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you go back and study my reply again, only reading for comprehension this time, rather than just looking for an quick answer, you should come across this line:
Quote:

Note that the dba_free_space view doesn't give info on temp tablespaces, because the concept of 'free space' is rather meaningless there.



Now, I intended that sentence to convey the information that the concept of free space in a temporary tablespace is meaningless, and that this is why the DBA_FREE_SPACE view does not provide such information for temporary tablespaces.

You have, it seems, taken some other meaning from it - do feel free to share with me what that meaning was, so I may refine my use of the English language and prevent further miscommunication (and also so I can have a cheap laugh)

Think about it - space in TEMP is assigned as needed, and reused when finished with. Unlike permenant tablespaces, there is no fixed amount of it in use, with the rest awaiting allocation. The amount of TMP space in use will almost certainly have changed significantly between the start and the end of you query.

In short: No the information you're looking for isn't there.
Previous Topic: oracle server 9i question
Next Topic: Insertion Taking a Large time
Goto Forum:
  


Current Time: Sun Dec 11 00:17:23 CST 2016

Total time taken to generate the page: 0.07749 seconds