Topic: Database Administration >> Regarding Article "Script to display free space in tablespace" submitted by Mr.Sirinivasan
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: Regarding Article "Script to display free space in tablespace" submitted by Mr.Sirinivasan |
| gps |
Posted: Jul 12, 2007 05:34:17 AM |
Total Post: 38
Joined: May, 2006
|
I am new to DBA community. However i suppose the script "freespace.sql" published on 25-06-07 does not give correct picture of Total, free and used space in tablespace.
I tried following script to get the desired result.
select a.tablespace_name, total_space/1024/1024 total_space , free_space/1024/1024 free_space,
(total_space-free_space)/1024/1024 used_space
from
(select tablespace_name,sum(bytes) total_space from dba_data_files
group by tablespace_name) a,
(select tablespace_name,sum(bytes) free_space from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;
Kindly give suggesions
Gurwinder
(gurwinder_75@mail.com)
|
|
|
 Ratnaker |
| Posted: Jul 13, 2007 07:37:48 AM |  |
|
Total Post: 136
Joined: Apr, 2007
|
Hi Gurvinder,
You can use given below script :
column "Tablespace" format a13
column "Used MB" format 99,999,999
column "Free MB" format 99,999,999
column "Total MB" format 99,999,999
select
fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name;
K P Ratnaker
|
|
|
|
|
| KS |
| Posted: Jul 13, 2007 09:23:43 AM | |
|
Total Post: 60
Joined: Nov, 2006
|
Hi gps,
I tried the SQL statement you mentioned and its working fine for me,
Can you post the output you r getting?
Kindly tell us how u r executing the script?
SQL> conn / as sysdba
Connected.
SQL> select a.tablespace_name, total_space/1024/1024 total_space , free_space/1024/1024 free_space,
2 (total_space-free_space)/1024/1024 used_space
3 from
4 (select tablespace_name,sum(bytes) total_space from dba_data_files
5 group by tablespace_name) a,
6 (select tablespace_name,sum(bytes) free_space from dba_free_space
7 group by tablespace_name) b
8 where a.tablespace_name=b.tablespace_name;
TABLESPACE_NAME TOTAL_SPACE FREE_SPACE USED_SPACE
------------------------------ ----------- ---------- ----------
UNDOTBS1 130 118.6875 11.3125
SYSAUX 320 30.25 289.75
USERS 5 4 1
SYSTEM 490 10 480
SRI 100 99.8125 .1875
-- Sri
|
|
|
|
|
| gps |
| Posted: Jul 14, 2007 12:00:29 AM | |
|
Total Post: 38
Joined: May, 2006
|
I am getting following output on running similar script posted by Mr.KP
Tablespace Used MB Free MB Total MB Pct. Free
------------- ----------- ----------- ----------- ---------
FINANCE 415 565 980 58
FIN_IDX 0 200 200 100
H_RESOURCE 5 495 500 99
MAINTENANCE 94 6 100 6
MARKETING 402 598 1,000 60
MATERIAL01 611 189 800 24
MAT_IDX 4 496 500 99
MKT_IDX 0 100 100 100
OEM_REPOSITORY 7 13 20 65
PAY 198 302 500 60
PF 218 82 300 27
RBS 0 100 100 100
RBS8I 300 0 300 0
SYSTEM 438 62 500 12
TEMP 0 100 100 100
TEST 78 322 400 81
TIMEOFFICE 197 63 260 24
TOOLS 9 41 50 82
USERS 223 827 1,050 79
WAGE 335 67 402 17
Gurwinder
|
|
|
|
|
| KS |
| Posted: Jul 16, 2007 08:40:30 AM | |
|
Total Post: 60
Joined: Nov, 2006
|
Can you post the output you ae getting while executing Srinivasan's Script and also , can you let us know the desired format you are expecting ???
- Sri
|
|
|
|
|
| KS |
| Posted: Aug 20, 2007 02:47:10 AM | |
|
Total Post: 60
Joined: Nov, 2006
|
Please try the following query, which will give u the desired output,
select d.tablespace_name ,sum(d.bytes)/1024/1024/1024 Total_Space ,
s.Free_Space, (sum(d.bytes)/1024/1024/1024 - s.free_space) Actual_size
from dba_data_files d, (select tablespace_name,sum(bytes)/1024/1024/1024 Free_Space from
dba_free_space group by tablespace_name ) s
where d.tablespace_name=s.tablespace_name
group by d.tablespace_name,s.Free_Space
order by d.tablespace_name;
- Sri
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 814 |
| Mohammed Taj | 694 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| positive fanatic | 361 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 239 |
| neeraj sharma | 228 |
| Ramesh Jois | 226 |
| snehalatha p | 169 |
|
|
