这里将查看的是数据库系统8大文件的相关字典。8大文件包括:
参数文件、密码文件、控制文件、重做日志文件、归档日志文件、数据文件、alert告警文件和trace跟踪文件。
细说的当然还有临时文件、备份文件、概要文件等文件,这些对于数据库来说都很重要。
了解这8大文件的存放位置以及相关的内容,对管理数据库起到至关重要的作用。
----查看数据文件的相关字典:
---1查看参数文件:
sys@PROD>show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilePROD.ora
sys@PROD>show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilePROD.ora
---2密码文件:通常密码文件与两个参数文件的存放目录是一样的。
---3查看alert告警文件:
sys@PROD>show parameter dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /u01/app/oracle/diag/rdbms/pro
d/PROD/trace
core_dump_dest string /u01/app/oracle/diag/rdbms/pro
d/PROD/cdump
user_dump_dest string /u01/app/oracle/diag/rdbms/pro
d/PROD/trace
---4查看trace跟踪文件:
sys@PROD>show parameter dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /u01/app/oracle/diag/rdbms/pro
d/PROD/trace
core_dump_dest string /u01/app/oracle/diag/rdbms/pro
d/PROD/cdump
user_dump_dest string /u01/app/oracle/diag/rdbms/pro
d/PROD/trace
#trace文件与alert文件同在一个目录下面,所以还可以使用以下方法查看这两个文件存放的位置:
sys@PROD>select value from v$diag_info where name='Diag Alert';
VALUE
----------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/prod/PROD/alert
或者:
VALUE
----------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/prod/PROD/trace
---5查看控制文件:
sys@PROD>show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/PROD/c
ontrol01.ctl, /u01/app/oracle/
fast_recovery_area/PROD/contro
l02.ctl
或者:
Select * from v$controlfile;
sys@PROD>desc v$controlfile
Name Null? Type
----------------------------------------- -------- ----------------------------
STATUS VARCHAR2(7)
NAME VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
BLOCK_SIZE NUMBER
FILE_SIZE_BLKS NUMBER
---6查看数据文件:
Select * from v$datafile;
sys@PROD>desc v$datafile
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
... ...
或者:
--查看数据文件的信息:
Select * from dba_data_files;
sys@PROD>desc dba_data_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
---7查看重做日志文件(redo_log):
Select * from v$log;
sys@PROD>desc v$log;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
BLOCKSIZE NUMBER
MEMBERS NUMBER
... ...
Select * from v$logfile;
sys@PROD>desc v$logfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
---8查看归档日志文件:
select * from v$archived_log;
sys@PROD>desc v$archived_log;
Name Null? Type
----------------------------------------- -------- ----------------------------
RECID NUMBER
STAMP NUMBER
NAME VARCHAR2(513)
DEST_ID NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
... ...
---9查看归档日志的存放目录:
select * from v$archive_dest;
desc v$archive_dest;
sys@PROD>desc v$archive_dest;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEST_ID NUMBER
DEST_NAME VARCHAR2(256)
STATUS VARCHAR2(9)
BINDING VARCHAR2(9)
NAME_SPACE VARCHAR2(7)
TARGET VARCHAR2(7)
ARCHIVER VARCHAR2(10)
... ...
---相关数据文件:
---10查看临时文件信息:
Select * from v$tempfile;
SQL> desc v$tempfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
---11查看备份文件的信息:
Select * from v$backup_files;
sys@PROD>desc v$backup_files
Name Null? Type
----------------------------------------- -------- ----------------------------
PKEY NUMBER
BACKUP_TYPE VARCHAR2(32)
FILE_TYPE VARCHAR2(32)
KEEP VARCHAR2(3)
KEEP_UNTIL DATE
KEEP_OPTIONS VARCHAR2(13)
STATUS VARCHAR2(16)
FNAME VARCHAR2(1024)
TAG VARCHAR2(32)
MEDIA VARCHAR2(80)
RECID NUMBER
STAMP NUMBER
DEVICE_TYPE VARCHAR2(255)
... ...
---12查看数据文件头部:
Select * from v$datafile_header;
sys@PROD>desc v$datafile_header;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
STATUS VARCHAR2(7)
ERROR VARCHAR2(18)
FORMAT NUMBER
RECOVER VARCHAR2(3)
FUZZY VARCHAR2(3)
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TABLESPACE_NAME VARCHAR2(30)
TS# NUMBER
... ...
---13查看用户概要文件的信息:
Select * from dba_profiles;
sys@PROD>desc dba_profiles
Name Null? Type
----------------------------------------- -------- ----------------------------
PROFILE NOT NULL VARCHAR2(30)
RESOURCE_NAME NOT NULL VARCHAR2(32)
RESOURCE_TYPE VARCHAR2(8)
LIMIT VARCHAR2(40)
#用户概要文件,约束账户的一些行为的文件。--罗列查来这些主要是能够方便大家容易集中地获取到这些重要的字典信息。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2131228/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2131228/