| how to find tablespace space information? [message #183833] |
Mon, 24 July 2006 01:58  |
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  |
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 #183883 is a reply to message #183876] |
Mon, 24 July 2006 04:20 |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
