1、查看数据库中数据文件结构信息
SQL> desc v$datafile; Name Null? Type ----------------------------------------- -------- ---------------------------- FILE# NUMBER CREATION_CHANGE# NUMBER CREATION_TIME DATE TS# NUMBER RFILE# NUMBER STATUS VARCHAR2(7) ENABLED VARCHAR2(10) CHECKPOINT_CHANGE# NUMBER CHECKPOINT_TIME DATE UNRECOVERABLE_CHANGE# NUMBER UNRECOVERABLE_TIME DATE LAST_CHANGE# NUMBER LAST_TIME DATE OFFLINE_CHANGE# NUMBER ONLINE_CHANGE# NUMBER ONLINE_TIME DATE BYTES NUMBER BLOCKS NUMBER CREATE_BYTES NUMBER BLOCK_SIZE NUMBER NAME VARCHAR2(513) PLUGGED_IN NUMBER BLOCK1_OFFSET NUMBER AUX_NAME VARCHAR2(513) FIRST_NONLOGGED_SCN NUMBER FIRST_NONLOGGED_TIME DATE FOREIGN_DBID NUMBER FOREIGN_CREATION_CHANGE# NUMBER FOREIGN_CREATION_TIME DATE PLUGGED_READONLY VARCHAR2(3) PLUGIN_CHANGE# NUMBER PLUGIN_RESETLOGS_CHANGE# NUMBER PLUGIN_RESETLOGS_TIME DATE
2、查看数据库中数据文件信息
SQL> select file#,name from v$datafile; FILE# NAME ---------- ---------------------------------------- 1 /u01/oradata/poli/system01.dbf 2 /u01/oradata/poli/sysaux01.dbf 3 /u01/oradata/poli/undotbs01.dbf 4 /u01/oradata/poli/users01.dbf 5 /u01/oradata/poli/example01.dbf 6 /u01/oradata/expimp01.dbf 6 rows selected.
3、查询数据文件与表空间信息对应关系
SQL> select file#,ts#,name from v$datafile; FILE# TS# NAME ---------- ---------- -------------------------------------------------- 1 0 /u01/oradata/md/system01.dbf 2 1 /u01/oradata/md/sysaux01.dbf 3 2 /u01/oradata/md/undotbs01.dbf 4 4 /u01/oradata/md/users01.dbf 5 6 /u01/oradata/md/example01.dbf 6 8 /u01/oradata/md/app1_01.dbf 6 rows selected.
4、查看数据库中数据文件、控制文件、日志文件信息
SQL> select name from v$datafile 2 union 3 select name from v$controlfile 4 union 5 select member from v$logfile; NAME -------------------------------------------------------------------------------- +DATA/phydb/controlfile/cont.ctl +DATA/phydb/datafile/example.257.819931453 +DATA/phydb/datafile/sysaux.259.819931453 +DATA/phydb/datafile/system.261.819931453 +DATA/phydb/datafile/undotbs1.258.819931453 +DATA/phydb/datafile/undotbs2.260.819931453 +DATA/phydb/datafile/users.262.819931457 +DATA/phydb/onlinelog/group_1.261.819703681 +DATA/phydb/onlinelog/group_2.262.819703685 +DATA/phydb/onlinelog/group_3.266.819704181 +DATA/phydb/onlinelog/group_4.267.819704185 NAME -------------------------------------------------------------------------------- +FLASH/devdb/onlinelog/group_1.257.819703683 +FLASH/devdb/onlinelog/group_2.258.819703687 +FLASH/devdb/onlinelog/group_3.259.819704183 +FLASH/devdb/onlinelog/group_4.260.819704189 15 rows selected.