Oracle查询数据库的各种文件

查询数据库的各种文件
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
/
备注:在数据文件没有空闲空间的时候,数据文件在
dba_free_space中将查询不到对象的对象.
查询Oracle是数据文件的状态
select distinct status from v$datafile
/
一般情况下为system和online状态。



查询包括数据文件的目录(linux系统)
select	distinct substr(name, 1, instr(name, '/', -1)) DIR
from	v$datafile
order by 1
/

查询扩展的数据文件
select	file_name
from	dba_data_files
where	autoextensible = 'YES'
/
生成关闭数据文件自动扩展的语句
select 'alter database datafile ''' || file_name || ''' autoextend off;'
from dba_data_files
/

不同磁盘之间数据文件的移动
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


查看控制文件
select	name
from	v$controlfile
/

创建一个操作系统命令
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
	)
/


查找重复的文件
select 	count(substr(name, instr(name, '/', -1) + 1, 999)) "total"
,	count(distinct substr(name, instr(name, '/', -1) + 1, 999)) "distinct" 
from 	v$datafile
/

热备份模式下的数据文件列表
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#
/