Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    36

    Unanswered: doubt in datafiles

    hello

    how can i know which datafile is active in a specifed tablespace

    that is let us assume i have created one tablespace called T1
    in which two datafiles d1 and d2 are created i want to know currently which datafile is active just similar to redolog file status as we could get the status which redolog is currently active is it possible with the datafile too? if so from where we can get the status

    i tried from v$datafiles,dba_data_files but i could not get the information what i require

    Thank U

  2. #2
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118

    dba_tablespaces, dba_data_files

    Hi vadlamanibujji

    A datafile can not be active or in-active:

    To see wich datafile belongs to which tablespae you can do:

    [SYSTEM@DB4.WORLD:NL-UTH-L057656] SELECT
    2 tablespace_name,
    3 file_name,
    4 bytes
    5 FROM
    6 dba_data_files
    7 ORDER BY
    8 tablespace_name
    9 /

    I use the following script to see some storage statistics for a datafile. (ie is the any data in the datafile)

    [SYSTEM@DB4.WORLD:NL-UTH-L057656] SELECT dba_data_files.tablespace_name
    2 , dba_data_files.file_id
    3 , dba_data_files.file_name "FILE_NAME"
    4 , dba_data_files.bytes / 1024 / 1024 "SIZE [M]"
    5 , DECODE( autoextensible, 'YES', '>' , '<') "X"
    6 , (dba_data_files.bytes - NVL( SUM( dba_free_space.bytes), 0)) / 1024 /1024 "USED [M]"
    7 , NVL( SUM( dba_free_space.bytes), 0) / 1024 /1024 "FREE [M]"
    8 , 100 * ( dba_data_files.bytes - NVL( SUM( dba_free_space.bytes), 0)) / dba_data_files.bytes "USED %"
    9 , (dba_data_files.increment_by * v.value) / 1024 / 1024 "NEXT [M]"
    10 , SUM( dba_data_files.maxbytes) / 1024 / 1024 "MAX SIZE [M]"
    11 FROM dba_free_space dba_free_space
    12 , dba_data_files dba_data_files
    13 , v$parameter v
    14 WHERE dba_data_files.file_id = dba_free_space.file_id(+)
    15 AND v.name = 'db_block_size'
    16 GROUP BY dba_data_files.tablespace_name
    17 , dba_data_files.file_id
    18 , dba_data_files.file_name
    19 , dba_data_files.bytes
    20 , dba_data_files.autoextensible
    21 , dba_data_files.increment_by
    22 , v.value
    23 ORDER BY DECODE( tablespace_name
    24 , 'SYSTEM', '1'
    25 , tablespace_name) ASC
    26 , file_id ASC
    27 /


    Good luck!
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

Posting Permissions

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