查询数据库的各种文件

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#

/