Interested to Buy Any Domain ? << Click Here >> for more details...
How can you find the size of a database?
- 5 Answers
- 11482 Views
- Baidu, HCL, I also Faced
- E-Mail Answers
Answer Posted / vani bhargavi
In general the size of the database is defined as total
size of the physical datafiles.
The following query will help you for the space management
of your database.
Code:
clear breaks
clear computes
clear columns
set pagesize 50
set linesize 120
set heading on
column tablespace_name heading 'Tablespace' justify left
format a20 truncated
column tbsize heading 'Size|(Mb) ' justify left format
9,999,999.99
column tbused heading 'Used|(Mb) ' justify right format
9,999,999.99
column tbfree heading 'Free|(Mb) ' justify right format
9,999,999.99
column tbusedpct heading 'Used % ' justify left format a8
column tbfreepct heading 'Free % ' justify left format a8
break on report
compute sum label 'Totals:' of tbsize tbused tbfree on
report
select t.tablespace_name, round(a.bytes,2) tbsize,
nvl(round(c.bytes,2),'0') tbfree,
nvl(round(b.bytes,2),'0') tbused,
to_char(round(100 * (nvl(b.bytes,0)/nvl(a.bytes,1)),2))
|| '%' tbusedpct,
to_char(round(100 * (nvl(c.bytes,0)/nvl(a.bytes,1)),2))
|| '%' tbfreepct
from dba_tablespaces t,
(select tablespace_name, round(sum(bytes)/1024/1024,2)
bytes
from dba_data_files
group by tablespace_name
union
select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_temp_files
group by tablespace_name ) a,
(select e.tablespace_name, round(sum(e.bytes)/1024/1024,2)
bytes
from dba_segments e
group by e.tablespace_name
union
select tablespace_name, sum(max_size) bytes
from v$sort_segment
group by tablespace_name) b,
(select f.tablespace_name, round(sum(f.bytes)/1024/1024,2)
bytes
from dba_free_space f
group by f.tablespace_name
union
select tmp.tablespace_name, (sum(bytes/1024/1024) - sum
(max_size)) bytes
from dba_temp_files tmp, v$sort_segment sort
where tmp.tablespace_name = sort.tablespace_name
group by tmp.tablespace_name) c
where
t.tablespace_name = a.tablespace_name (+)
and t.tablespace_name = b.tablespace_name (+)
and t.tablespace_name = c.tablespace_name (+)
order by t.tablespace_name
/
| Is This Answer Correct ? | 5 Yes | 9 No |
Post New Answer View All Answers
What is the function of Redo Log in Oracle?
What is an Integrity Constrains in Oracle?
Teradata: How can we know the indexed column in a table?
How to calculate the approximate size of the SGA based on the size of the block,shared_pool,redo_buffer_cache and db_buffer_cache?
What is an Oracle SYSTEM tablespace and when is it Created?
What are Oracle Schema Objects?
What is the use of Oracle Control File?
What is an oracle database schema?
What is an Oracle Data Segment?
Can a View based on another View in Oracle?
How to define Data Block size in Oracle?
What is an Oracle Rollback Segment?
What are the consideration at tehe migration of Oracle 9i to 10G.
What is an Oracle Data Block?
What is an Oracle Data Dictionary?
