Tuesday, October 30, 2012

SQL to list the number of database files in Oracle Database

Hi there,


Found this interesting query which will list you all the DB files holding your database:


select    name, bytes

from    (select    name, bytes

    from    v$datafile

    union    all

    select    name, bytes

    from     v$tempfile

    union     all

    select     lf.member "name", l.bytes

    from    v$logfile lf

    ,    v$log l

    where    lf.group# = l.group#) used

,    (select sum(bytes) as poo

    from dba_free_space) free



Hope it helps,




No comments: