1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

To find the data files associates with a file system ?

Discussion in 'General' started by sidharthmellam, Jun 17, 2010.

  1. sidharthmellam

    sidharthmellam Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hi

    How ,or by which command or query , to find the data files associates with a file system

    /ORACLE/RP2/sapdata2 for this file system

    ie all the data files associated with ORACLE/RP2/sapdata2

    The purpose is to find out the growth of this sapdata2 file system during last 4 months

    how to analyze the growth ,

    Please suggest

    Regards

    :)
     
  2. sidharthmellam

    sidharthmellam Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80

    Hi Guys,

    Did i post it in wrong Group

    Since me being SAP Guy, who also needs to interact with Database often,

    I was anticipating some suggestions in this forum and group

    please suggest

    Regards
    sidharth
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    DBA_DATA_FILES would be the view you'd want to query; using the INSTR() function will allow you to find only those data files on that file system. Of course you'll also want to know of any temp tablespace files which may also be on that filesystem and DBA_TEMP_FILES would be your source of information:

    Code (SQL):
     
    SELECT SUM(bytes)/1024/1024 ttl_mb
    FROM dba_data_files
    WHERE instr(file_name, '/ORACLE/RP2/sapdata2') > 0;
     
    You'd use a similar query for DBA_TEMP_FILES.
     
  4. sidharthmellam

    sidharthmellam Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hi David,

    I tried

    SELECT sum(bytes)/1024/1024 ttl_mb FROM dba_data_files WHERE instr(file_name, '/ORACLE/RP2/sapdata2') > 0;

    but am getting reply

    SQL>Badly placed (.

    Am doing any mistakes in syntax


    Regards
    sidharth
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What you've posted is not what you're running as I have no issues with the posted statement:

    Code (SQL):
     
    SQL>  SELECT SUM(bytes)/1024/1024 ttl_mb FROM dba_data_files WHERE instr(file_name, '/uuuu/vvvvvvv') > 0

        TTL_MB
    ----------
    947.09
     
    Check your script again as there is at least one typographical error in it.