Home » RDBMS Server » Backup & Recovery » Datafile Free Size
Datafile Free Size [message #194630] Sat, 23 September 2006 02:34 Go to next message
thiyagaraj
Messages: 41
Registered: August 2006
Member
Hi
Is there anyway to perfectly calculate the free space available in datafiles?
Can i consider the bytes that shows in dba_free_space and also dba_free_space_coalesce views as free space avilable to my datafile?

If i issue this below query:
SQL> select TABLESPACE_NAME,FILE_ID,BYTES from dba_free_space;
the output is:
TABLESPACE_NAME FILE_ID BYTES
----------------- - --------- ----------
XXX 11 655360
XXX 11 2031616

But i have only one tablespace name called XXX and have onlyone datafile in that named YYY. Then why this query fetches two rows that has same file-id with different bytes?
can i consider that 655360 as freespace available in my datafile?then what is 2031616 ? please clear my doubt.


If i issue this query:
SQL> select FILE_NAME,BYTES,AUTOEXTENSIBLE,MAXBYTES,INCREMENT_BY from dba_data_files where TABLESPACE_NAME='XXX';
output is:
FILE_NAME BYTES AUT MAXBYTES INCREMENT_BY
------------------------------------------------------------------------------------------------------------
/u01/oradata/AAA/YYY.dbf 454033408 YES 3.4360E+10 256

What is this increment_by will do?but, i know the bytes shows the total bytes allocated to datafile. Then what is that MAXBYTES values says.
Please let me know.

Thanks
Raaj
Re: Datafile Free Size [message #194643 is a reply to message #194630] Sat, 23 September 2006 05:56 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Is there anyway to perfectly calculate the free space available in datafiles?
Please search the forums before posting.
These are answered many times.
One such is here
http://www.orafaq.com/forum/m/171667/0/?srch=space+usage#msg_171667

For rest of all the questions, please refer documentation. Everything explained there.
Re: Datafile Free Size [message #194644 is a reply to message #194630] Sat, 23 September 2006 06:02 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>But i have only one tablespace name called XXX and have onlyone datafile in that named YYY. Then why this query fetches two rows that has same file-id with different bytes?

Because
< quoting docs >
Information about the free extents (extents not allocated to any segment) in a database is stored in the DBA_FREE_SPACE data dictionary view.
< end quoting docs >
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2170

[Updated on: Sat, 23 September 2006 06:02]

Report message to a moderator

Previous Topic: Recover part of database on a remote site
Next Topic: ORA-01171: datafile # going offline
Goto Forum:
  


Current Time: Mon Dec 05 12:52:30 CST 2016

Total time taken to generate the page: 0.26822 seconds