Home » RDBMS Server » Server Administration » Free space of database (Oracle 11g, Linux)  () 1 Vote
Free space of database [message #618689] Mon, 14 July 2014 21:22 Go to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Hi ,

Is it possible to find free space of database? If so,can you tell me how to find that ?

Regards,
Srini
Re: Free space of database [message #618690 is a reply to message #618689] Mon, 14 July 2014 21:25 Go to previous messageGo to next message
BlackSwan
Messages: 23059
Registered: January 2009
Senior Member
Database admin wrote on Mon, 14 July 2014 19:22
Hi ,

Is it possible to find free space of database? If so,can you tell me how to find that ?

Regards,
Srini


SQL> desc dba_free_space
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                                    VARCHAR2(30)
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER

Re: Free space of database [message #618691 is a reply to message #618690] Mon, 14 July 2014 21:42 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Thank you Blackswan for your instant response Smile
Re: Free space of database [message #618692 is a reply to message #618691] Mon, 14 July 2014 21:43 Go to previous messageGo to next message
BlackSwan
Messages: 23059
Registered: January 2009
Senior Member
post the SQL you used to answer your question & results from it
Re: Free space of database [message #618693 is a reply to message #618692] Mon, 14 July 2014 22:34 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Blackswan,

Sorry to say that as of now i am waiting to get access to all database servers for the account i recently joined.
Once i get access to servers i will provide you the output you requested.Right now i am helping my colleague to get
resolution for issue we are facing.

Regards,
Srini

[Updated on: Mon, 14 July 2014 22:34]

Report message to a moderator

Re: Free space of database [message #618709 is a reply to message #618693] Tue, 15 July 2014 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59799
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can use Database Reference for any catalog view and post the query you will use, you don't need any database access to write a query.

Re: Free space of database [message #618821 is a reply to message #618693] Tue, 15 July 2014 22:43 Go to previous messageGo to next message
trantuananh24hg
Messages: 618
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Database admin wrote on Tue, 15 July 2014 03:34
Blackswan,

Sorry to say that as of now i am waiting to get access to all database servers for the account i recently joined.
Once i get access to servers i will provide you the output you requested.Right now i am helping my colleague to get
resolution for issue we are facing.

Regards,
Srini


DA!
I do not make a definition about concept of Database Space but just call as Datafile Space. A Database includes physical files, some of them do not grown normally as datafile. Exactly, I do not care about their space, such as control file, spfile, ...
So, I make that term "How does datafile or subset datafile grow?", it's required to many thing, sometime, it's called as sizing.

First time, if you would like to measure tbs or datafile, simply, write a script by your self by access to fix-viwed dba_free_space, dba_extends/segments, ... Or if you can not, stay with my script:

SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY   OFF

COLUMN tablespace  FORMAT a18             HEADING 'Tablespace Name'
COLUMN filename    FORMAT a50             HEADING 'Filename'
COLUMN filesize    FORMAT 99,999,999,999  HEADING 'File Size'
COLUMN used        FORMAT 99,999,999,999  HEADING 'Used (in bytes)'
COLUMN pct_used    FORMAT 999             HEADING 'Pct. Used'

BREAK ON report
COMPUTE SUM OF filesize  ON report
COMPUTE SUM OF used      ON report
COMPUTE AVG OF pct_used  ON report

SELECT /*+ ordered */
    d.tablespace_name                     tablespace
  , d.file_name                           filename
  , d.file_id                             file_id
  , d.bytes                               filesize
  , NVL((d.bytes - s.bytes), d.bytes)     used
  , TRUNC(((NVL((d.bytes - s.bytes) , d.bytes)) / d.bytes) * 100)  pct_used
FROM
    sys.dba_data_files d
  , v$datafile v
  , ( select file_id, SUM(bytes) bytes
      from sys.dba_free_space
      GROUP BY file_id) s
WHERE
      (s.file_id (+)= d.file_id)
  AND (d.file_name = v.name)
UNION
SELECT
    d.tablespace_name                       tablespace 
  , d.file_name                             filename
  , d.file_id                               file_id
  , d.bytes                                 filesize
  , NVL(t.bytes_cached, 0)                  used
  , TRUNC((t.bytes_cached / d.bytes) * 100) pct_used
FROM
    sys.dba_temp_files d
  , v$temp_extent_pool t
  , v$tempfile v
WHERE 
      (t.file_id (+)= d.file_id)
  AND (d.file_id = v.file#)
ORDER BY 1
/


But, remember, the total of datafile maybe 31gb, maybe 15gb within 30gb resized, and you should not resize from 30 to 15gb, depend something like "the last block contains data" ...etc. And, so good to read Michel's link!!!
Re: Free space of database [message #618827 is a reply to message #618821] Tue, 15 July 2014 23:56 Go to previous messageGo to next message
suhasdba
Messages: 141
Registered: April 2013
Location: INDIA
Senior Member
Hi trantuananh,

Your script looks good.Does output of your script produces sizes of used,free and data space of database ?
Sorry to post my question on others thread.Just curious to know the answer.So what posted my question here.

Regards,
Suhas

[Updated on: Wed, 16 July 2014 00:01]

Report message to a moderator

Re: Free space of database [message #618829 is a reply to message #618827] Wed, 16 July 2014 00:13 Go to previous message
Michel Cadot
Messages: 59799
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Does output of your script produces sizes of used,free and data space of database ?


Why don't you read the query or, simpler, execute it?

Previous Topic: Oracle error ora 07445
Next Topic: Default Limit of Resources
Goto Forum:
  


Current Time: Thu Nov 27 01:05:55 CST 2014

Total time taken to generate the page: 1.55949 seconds