tag:blogger.com,1999:blog-1113949853993421596.post2317438777198680199..comments2024-02-13T16:22:47.962+05:30Comments on Ingress IT Solutions: How to Find Over-all Database Size?Anonymoushttp://www.blogger.com/profile/16195763811758156489noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-1113949853993421596.post-31774918809525026082011-06-03T11:14:15.981+05:302011-06-03T11:14:15.981+05:30This is not pretty intesting. I need to get the si...This is not pretty intesting. I need to get the size of the datbaseVenkathttp://www.blogger.com/profile/16550667877917753188noreply@blogger.comtag:blogger.com,1999:blog-1113949853993421596.post-19948848717816739602008-09-24T10:32:00.000+05:302008-09-24T10:32:00.000+05:30your command wil not give full database size....Yo...your command wil not give full database size....<br><br>You can use this query<br><br>How to find the size of database? <br>In general the size of the database is defined as total size of the physical datafiles. <br>The following query will help you for the space management of your database. <br>Code: <br><br>select t.tablespace_name, round(a.bytes,2) tbsize, <br>nvl(round(c.bytes,2),'0') tbfree, <br>nvl(round(b.bytes,2),'0') tbused, <br>to_char(round(100 * (nvl(b.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbusedpct, <br>to_char(round(100 * (nvl(c.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbfreepct <br>from dba_tablespaces t, <br>(select tablespace_name, round(sum(bytes)/1024/1024,2) bytes <br>from dba_data_files <br>group by tablespace_name <br>union <br>select tablespace_name, round(sum(bytes)/1024/1024,2) bytes <br>from dba_temp_files <br>group by tablespace_name ) a, <br>(select e.tablespace_name, round(sum(e.bytes)/1024/1024,2) bytes <br>from dba_segments e <br>group by e.tablespace_name <br>union <br>select tablespace_name, sum(max_size) bytes <br>from v$sort_segment <br>group by tablespace_name) b, <br>(select f.tablespace_name, round(sum(f.bytes)/1024/1024,2) bytes <br>from dba_free_space f <br>group by f.tablespace_name <br>union <br>select tmp.tablespace_name, (sum(bytes/1024/1024) - sum(max_size)) bytes <br>from dba_temp_files tmp, v$sort_segment sort <br>where tmp.tablespace_name = sort.tablespace_name <br>group by tmp.tablespace_name) c <br>where <br>t.tablespace_name = a.tablespace_name (+) <br>and t.tablespace_name = b.tablespace_name (+) <br>and t.tablespace_name = c.tablespace_name (+) <br>order by t.tablespace_name <br><br><br><br><br><br>Obed Khan <br>OCP 9i<br>Mobile No +919860157699aparnahttp://www.blogger.com/profile/09752683872511190632noreply@blogger.com