-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2011 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : dba_files_all.sql |
-- | CLASS : Database Administration |
-- | PURPOSE : Reports on all data files, online redo log files, and control |
-- | files within the database. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET LINESIZE 147
SET PAGESIZE 9999
SET VERIFY OFF
COLUMN tablespace FORMAT a29 HEADING 'Tablespace Name / File Class'
COLUMN filename FORMAT a64 HEADING 'Filename'
COLUMN filesize FORMAT 99,999,999,999 HEADING 'File Size'
COLUMN autoextensible FORMAT a4 HEADING 'Auto'
COLUMN increment_by FORMAT 99,999,999,999 HEADING 'Next'
COLUMN maxbytes FORMAT 99,999,999,999 HEADING 'Max'
BREAK ON report
COMPUTE SUM OF filesize ON report
SELECT /*+ ordered */
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes 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
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM
sys.dba_temp_files d
, (SELECT value
FROM v$parameter
WHERE name = 'db_block_size') e
UNION
SELECT
'[ ONLINE REDO LOG ]'
, a.member
, b.bytes
, null
, TO_NUMBER(null)
, TO_NUMBER(null)
FROM
v$logfile a
, v$log b
WHERE
a.group# = b.group#
UNION
SELECT
'[ CONTROL FILE ]'
, a.name
, TO_NUMBER(null)
, null
, TO_NUMBER(null)
, TO_NUMBER(null)
FROM
v$controlfile a
ORDER BY 1,2
/
Sample Output:
Code: Select all
Tablespace Name / File Class Filename File Size Auto Next Max
----------------------------- ---------------------------------------------------------------- --------------- ---- --------------- ---------------
EXAMPLE +VOL1/orcla10/datafile/example.269.771580081 104,857,600 YES 655,360 34,359,721,984
SYSAUX +VOL1/orcla10/datafile/sysaux.257.771579811 293,601,280 YES 10,485,760 34,359,721,984
SYSTEM +VOL1/orcla10/datafile/system.256.771579811 471,859,200 YES 10,485,760 34,359,721,984
TEMP +VOL1/orcla10/tempfile/temp.268.771580069 20,971,520 YES 655,360 34,359,721,984
UNDOTBS1 +VOL1/orcla10/datafile/undotbs1.258.771579813 73,400,320 YES 5,242,880 34,359,721,984
USERS +VOL1/orcla10/datafile/users.259.771579813 5,242,880 YES 1,310,720 34,359,721,984
[ CONTROL FILE ] +VOL1/orcla10/controlfile/current.260.771579965
[ CONTROL FILE ] +VOL1/orcla10/controlfile/current.261.771579965
[ ONLINE REDO LOG ] +VOL1/manila/onlinelog/group_1.262.772453817 52,428,800
[ ONLINE REDO LOG ] +VOL1/manila/onlinelog/group_1.263.772453805 52,428,800
[ ONLINE REDO LOG ] +VOL1/manila/onlinelog/group_2.265.772453839 52,428,800
[ ONLINE REDO LOG ] +VOL1/manila/onlinelog/group_2.292.772453847 52,428,800
[ ONLINE REDO LOG ] +VOL1/manila/onlinelog/group_3.266.772453867 52,428,800
[ ONLINE REDO LOG ] +VOL1/manila/onlinelog/group_3.282.772453877 52,428,800
---------------
sum 1,284,505,600