|
|
|
|
| Re: Size of database [message #60244 is a reply to message #60238] |
Tue, 27 January 2004 04:47   |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Sachin,
dba_segments doesnt give you the size of all .dbf files. sum(bytes) from dba_segments give you the sum of the size of all segments.
sum(bytes) from dba_data_files & dba_temp_files give you the size of all data/temp files.
See here DB Size
-Thiru
[Updated on: Fri, 18 February 2005 23:32] Report message to a moderator |
|
|
|
| Re: Size of database [message #60254 is a reply to message #60244] |
Tue, 27 January 2004 11:19 |
Jadie
Messages: 64
Registered: January 2002
|
Member |
|
|
|
sum(bytes) of dba_segments is the size of the real data (don't include the free space in the tablespace), sum(bytes) of dba_data_files is the DB size allocated (including the free space).
|
|
|
|
|
|
| Re: Size of database [message #60256 is a reply to message #60255] |
Tue, 27 January 2004 14:27 |
Jadie
Messages: 64
Registered: January 2002
|
Member |
|
|
|
Because Sanjay Maral is looking for the DB size of actually data. I think Sachin's answer is correct. And also I want to get the confirm from you. :)
|
|
|
|
| Re: Size of database [message #60266 is a reply to message #60256] |
Wed, 28 January 2004 02:51 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Jadie,
Sachin said " select sum(bytes) from dba_segments;
gives u the actual size of all .dbf files. "
and that is INCORRECT.
As I said earlier , sum(bytes) from dba_segments gives you the sum of the size of all segments(tables,indexes etc) in the database and not the size of all .dbf files as Sachin says.
sum(bytes) from dba_data_files gives you the sum of the size of all datafiles and sum(bytes) from dba_temp_files gives you the sum the size of all temp files in a database.
DAta is contained in the segments which is contained in the tablespaces that is made of datafiles.
If somebody asks you the overall size of the data, tell them that you are going to give them an approximate allocated size and give them the sum(bytes) from dba_segments where segment_type not in ( 'ROLLBACK','TYPE2 UNDO','CACHE'); and this includes indexes too (which may not be perceived as 'data' in some requirements).
For eg,
thiru@9.2.0:SQL>select segment_type,sum(bytes) from dba_Segments group by segment_type order by
2 sum(bytes) desc;
SEGMENT_TYPE SUM(BYTES)
------------------ ----------
TABLE 381485056
INDEX 164102144
TABLE PARTITION 74776576
LOBSEGMENT 34734080
LOBINDEX 23986176
INDEX PARTITION 22282240
CLUSTER 12189696
ROLLBACK 2490368
TYPE2 UNDO 1269760
NESTED TABLE 262144
CACHE 65536
11 rows selected.
But this is not the SAME as the 'ACTUAL' used space. This is the allocated space for those segments.
For eg,
thiru@9.2.0:SQL>create table big_empty storage(initial 5m next 5m) as select * from scott.emp
2 where 1=2;
Table created.
thiru@9.2.0:SQL>select bytes from user_Segments where segment_name='BIG_EMPTY';
BYTES
----------
5242880
-- The size of the segment is 5M , but does that mean the table is 'actually used up' upto 5M ? Not necessarily . The table has Zero rows
thiru@9.2.0:SQL>select count(*) from big_empty;
COUNT(*)
----------
0
-- so dba/user/all_segments show you the allocated space for the segments.
-- and this is totally different from sum(bytes) from dba_data_files which gives you the sum of the size of all datafiles belonging to this database.
We are not even talking about freespace here. Where is the confusion ?
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
