Oracle查询数据库的各种文件

bjcpajkyii

查询数据库的各种文件
set lines 100 pages 999
col name format a50
selectname, bytes
from    (selectname, bytes
fromv$datafile
unionall
selectname, bytes
from v$tempfile
union all
select lf.member "name", l.bytes
fromv$logfile lf
,v$log l
wherelf.group# = l.group#
union all
selectname, 0
fromv$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系统)
selectdistinct substr(name, 1, instr(name, '/', -1)) DIR
fromv$datafile
order by 1
/
查询扩展的数据文件
selectfile_name
fromdba_data_files
whereautoextensible = '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

查看控制文件
selectname
fromv$controlfile
/
创建一个操作系统命令
select'rm ' || name
from(selectname
fromv$datafile
unionall
selectname
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
selectdf.name
,b.status
,to_char(time, 'hh24:mi:ss dd/mm/yyyy') time
fromv$datafiledf
,v$backupb
wheredf.file# = b.file#
andb.status = 'ACTIVE'
order by b.file#
/