dbfiles.sql

--
-- Title :       dbfiles.sql
-- Description : Show datafile details.
--
-- Usage/Notes:  Requires read access to the DBA dictionary views.
--
-- Copyright :   ABCdba.com 2014
--
 
SET LINESIZE 132
SET PAGESIZE 200
 
COL tablespace      HEA '    Tablespace     |       Name        ' FOR a18
COL file_name       HEA '                       File|                       Name'
COL file_name                                                     FOR a56
COL file_id         HEA 'File| No '                               FOR 9999
COL auto            HEA 'Auto| On '                               FOR a4
COL created         HEA '   Creation    |     Date      '         FOR a15
COL act_size        HEA ' Current |Size (MB)'                     FOR 99,999
COL shrink_size     HEA 'Possible |Size (MB)'                     FOR 99,999
COL diff_size       HEA ' Reclaim |Size (MB)'                     FOR 99,999
 
SELECT
   a.tablespace,
   a.file_name,
   a.file_id,
   a.autoextensible autO,
   TO_CHAR(b.creation_time,'DDMMYY HH24:MI:SS') created,
   TO_NUMBER(a.actual) act_size,
   TO_NUMBER(a.shrink_to) shrink_size,
   TO_NUMBER(a.actual)-TO_NUMBER(a.shrink_to) diff_size
FROM
   (
   SELECT
      c.tablespace_name tablespace,
      c.file_name,
      c.file_id,
      c.autoextensible,
      DECODE(
         c.actual_size,
         -1, 'n/a (offline)',
         c.actual_size
      ) actual,
      DECODE(
         c.shrink_to,
         -1, c.actual_size,
         NULL, c.actual_size,
         c.actual_size, c.actual_size,
         c.shrink_to
      ) shrink_to
   FROM
      (
      SELECT
         e.tablespace_name,
         e.file_id,
         e.file_name,
         e.bytes/1024/1024 actual_size,
         e.autoextensible,
         NVL(
            ( d.maxblock - 1 ) * f.blocksize / 1024 / 1024,
            ( 64 * 1024 + f.bitmapped * f.blocksize ) / 1024 / 1024 ) shrink_to
      FROM
         (
         SELECT
            g.fileid file_id,
            MAX(g.block+g.length) maxblock
         FROM
            sys.dba_lmt_used_extents g
         GROUP BY
            g.fileid
         ) d,
         sys.dba_data_files e,
         sys.ts$ f
      WHERE
         d.file_id(+) = e.relative_fno
      AND
         e.tablespace_name = f.name
      AND
         f.bitmapped > 0
      AND
         e.bytes IS NOT NULL
      ) c
   ORDER BY
      c.tablespace_name,
      c.file_name
   ) a,
   sys.v$datafile b
WHERE
   a.file_id = b.file#
ORDER BY
   a.tablespace,
   b.creation_time,
   a.file_name
/

© copyright 2001-2014 ABCdba.com | all rights reserved