DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: no information of a datafile in dba_free_space

Hybrid View

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    no information of a datafile in dba_free_space

    I inquired dba_free_space at one of our customer sites but didn't find free space information about three datafiles out of 54 listed in dba_data_files. May I know what could be the possible reason for it? Could it be that there is no free space in these datafiles so they are not listed in dba_free_space?
    lucky

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    evidence?

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Are missing datafiles related to a Temporary tablespace?
    dba_free_space only reports Permanent tablespace free space.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    As PAVB said, if they are not temporary tablespace data files, then for sure there is no free space left in those data files.

    However, if any tablespace has multiple data files then few of the data files occupied 100%. Better user group by tablespace_name if your intention is to check the free space in any tablespace.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  5. #5
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    VNKTUMMALA, I also think the same. There is tablespace named DMS_BLOB that has 9 datafiles and all of them are nearly fully packed up. These are datafiles of permanent tablespace.

    Just for evidence to Dave:

    SQL> select file_id,tablespace_name,file_name,bytes/1024/1024/1024 from dba_data_files
    2 where file_id not in (select file_id from dba_free_space)
    3 /

    FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024/1024
    ---------- ------------------------------ ---------------------------------------- --------------------
    2 QUARKDMS_BLOB F:\DATABASE\QDMS\DMB4QDMS.ORA 3.90710449
    39 QUARKDMS_BLOB F:\DATABASE\QDMS\DMB5QDMS.ORA 3.90637207
    37 SYSTEM F:\DATABASE\QDMS\SYS2QDMS.ORA .195312
    lucky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width