Structure - Files

Display all datafiles, tempfiles and logfiles (and their sizes)

set lines 100 pages 999
col name format a50
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#
 union all
 select name, 0
 from v$controlfile) used
, (select sum(bytes) as p
 from dba_free_space) free
/

Output:
NAME                                                         BYTES
-------------------------------------------------- ---------------
+ORQ/orq2/datafile/system01.dbf                         2147483648
+ORQ/orq2/datafile/system02.dbf                         2147483648
+ORQ/orq2/datafile/system03.dbf                         1073741824
+ORQ/orq2/datafile/system04.dbf                         1073741824
+ORQ/orq2/datafile/system05.dbf                         1073741824
+ORQ/orq2/datafile/ctxd01.dbf                            157286400
+ORQ/orq2/datafile/owad01.dbf                             10485760

Quick datafile health check

You should only see online and system (maybe read-only too)

select distinct status from v$datafile
/
Output:

SQL> select distinct status from v$datafile;

STATUS
-------
ONLINE
SYSTEM

2 rows selected.

Show directories that contain datafiles

select distinct substr(name, 1, instr(name, '/', -1)) DIR
from v$datafile
order by 1
/

List autoextensible datafiles

select file_name
from dba_data_files
where autoextensible = 'YES'
/

Turn autoextend off for all datafiles

select 'alter database datafile ''' || file_name || ''' autoextend off;'
from dba_data_files
/

Move files between disks

set trimspool on wrap off
set heading off
set verify off
set pages 1000 lines 100

spool rename.sql

select  'alter database rename file ''' || 
 name || ''' to ''' || 
 replace(name || ''';', '/u01', '/u02')
from  v$datafile
/

select  'alter database rename file ''' || 
 member || ''' to ''' || 
 replace(member || ''';', '/u01', '/u02')
from  v$logfile
/

spool off

List controlfiles

select name
from v$controlfile
/

Create an OS command for every file in the database

The example deletes all files

select 'rm ' || name
from (select name 
 from v$datafile
 union all
 select name
 from  v$tempfile
 union  all
 select  member
 from  v$logfile
  union   all
  select  name
  from    v$controlfile
 )
/

Find duplicate filenames

Searches for files with the same name in all directories

select  count(substr(name, instr(name, '/', -1) + 1, 999)) "total"
, count(distinct substr(name, instr(name, '/', -1) + 1, 999)) "distinct" 
from  v$datafile
/

List files that are in hot-backup mode

set lines 100 pages 999
col name format a60
select df.name
, b.status
, to_char(time, 'hh24:mi:ss dd/mm/yyyy') time
from v$datafile df
, v$backup b
where df.file# = b.file#
and b.status = 'ACTIVE'
order by b.file#
/