Oracle database all file info

@all_file.sql
@dba_all_file.sql
All file in database

 

 

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
/

SQL> /

Tablespace Name / File Class  Filename                                                               File Size Auto            Next             Max
----------------------------- ---------------------------------------------------------------- --------------- ---- --------------- ---------------
SYSAUX                        +DATA/vihaan/datafile/sysaux.257.776974139                           241,172,480 YES       10,485,760  34,359,721,984
SYSTEM                        +DATA/vihaan/datafile/system.256.776974135                           503,316,480 YES       10,485,760  34,359,721,984
TEMP                          +DATA/vihaan/tempfile/temp.264.776974399                              20,971,520 YES          655,360  34,359,721,984
UNDOTBS1                      +DATA/vihaan/datafile/undotbs1.258.776974143                          26,214,400 YES        5,242,880  34,359,721,984
USERS                         +DATA/vihaan/datafile/users.259.776974145                              5,242,880 YES        1,310,720  34,359,721,984
[ CONTROL FILE ]              +DATA/vihaan/controlfile/current.260.776974343
[ ONLINE REDO LOG ]           +DATA/vihaan/onlinelog/group_1.261.776974351                          52,428,800
[ ONLINE REDO LOG ]           +DATA/vihaan/onlinelog/group_2.262.776974357                          52,428,800
[ ONLINE REDO LOG ]           +DATA/vihaan/onlinelog/group_3.263.776974361                          52,428,800
                                                                                               ---------------
sum                                                                                                954,204,160

9 rows selected.