db_files.sql

-- ************************************************************************************************************
-- * Filename           : db_files.sql
-- * Author             : Saravanakumar K
-- * Description        : This script with show all database physical file info  
-- * Usage              : @db_files.sql
-- * Version            : v1
-- *************************************************************************************************************
SET TERMOUT OFF;
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
SET TERMOUT ON;
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 250 
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
SET TAB OFF
COLUMN ft_sort NOPRINT
COLUMN tablespace FORMAT a30 HEADING 'Tablespace Name / File Class'
COLUMN filename FORMAT a100 HEADING 'Filename'
COLUMN filesize FORMAT 9999999999999 HEADING 'File Size GB'
COLUMN autoextensible FORMAT a4 HEADING 'Auto'
COLUMN increment_by FORMAT 999999999999 HEADING 'Next in GB'
COLUMN maxbytes FORMAT 999999999999 HEADING 'Max Size GB'

BREAK ON REPORT ON ft DUPLICATES SKIP 1
COMPUTE SUM OF filesize ON REPORT
COMPUTE SUM OF maxbytes ON REPORT

SELECT /*+ ordered */
    1 ft_sort,
    d.tablespace_name   tablespace,
    d.file_name         filename,
    d.bytes / 1024 / 1024 / 1024 filesize,
    d.autoextensible    autoextensible,
    ( d.increment_by * e.value ) / 1024 / 1024 / 1024 increment_by,
    d.maxbytes / 1024 / 1024 / 1024 maxbytes
FROM
    sys.dba_data_files   d,
    v$datafile           v,
    (
        SELECT
            value
        FROM
            v$parameter
        WHERE
            name = 'db_block_size'
    ) e
WHERE
    ( d.file_name = v.name )
UNION
SELECT
    2 ft_sort,
    d.tablespace_name   tablespace,
    d.file_name         filename,
    d.bytes / 1024 / 1024/ 1024 filesize,
    d.autoextensible    autoextensible,
    ( d.increment_by * e.value ) / 1024 / 1024/ 1024increment_by,
    d.maxbytes / 1024 / 1024 / 1024 maxbytes
FROM
    sys.dba_temp_files d,
    (
        SELECT
            value
        FROM
            v$parameter
        WHERE
            name = 'db_block_size'
    ) e
UNION
SELECT
    3 ft_sort,
    '[ ONLINE REDO LOG  ]',
    a.member,
    b.bytes / 1024 / 1024 / 1024,
    NULL,
    to_number(NULL),
    to_number(NULL)
FROM
    v$logfile   a,
    v$log       b
WHERE
    a.group# = b.group#
UNION
SELECT
    4 ft_sort,
    '[ STANDBY REDO LOG ]',
    a.member,
    b.bytes / 1024 / 1024 / 1024,
    NULL,
    to_number(NULL),
    to_number(NULL)
FROM
    v$logfile       a,
    v$standby_log   b
WHERE
    a.group# = b.group#
UNION
SELECT
    5 ft_sort,
    '[ CONTROL FILE     ]',
    a.name,
    to_number(NULL),
    NULL,
    to_number(NULL),
    to_number(NULL)
FROM
    v$controlfile a
UNION
SELECT
    6 ft_sort,
    '[ SP FILE          ]',
    value "name",
    to_number(NULL),
    NULL,
    to_number(NULL),
    to_number(NULL)
FROM
    v$parameter
WHERE
    name = 'spfile'
ORDER BY
    1,
    2,
    3
/
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
SET LINES 175 PAGES 9999 TAB ON