- Administration
- Structure
- Performance
- Backup
- Miscellaneous
- SQL*Net
|
|
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
/
|
Quick datafile health check
You should only see online and system (maybe read-only too)
select distinct status from v$datafile
/
|
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#
/
|
|
