Oracle查看控制文件的配置

来源：互联网发布：赌博游戏算法编辑：程序博客网时间：2024/06/05 02:12

作者：雨竹清风

要想得到控制文件的全部信息可以使用v$controlfile_record_section数据字典。命令如下：

SQL> desc v$controlfile_record_section

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

TYPE VARCHAR2(28)

RECORD_SIZE NUMBER

RECORDS_TOTAL NUMBER

RECORDS_USED NUMBER

FIRST_INDEX NUMBER

LAST_INDEX NUMBER

LAST_RECID NUMBER

SQL> select TYPE, RECORD_SIZE, RECORDS_TOTAL,RECORDS_USED from v$controlfile_record_section;

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED

---------------------------- ----------- ------------- ------------

DATABASE 316 1 1

CKPT PROGRESS 8180 11 0

REDO THREAD 256 8 1

REDO LOG 72 16 3

DATAFILE 428 100 5

FILENAME 524 2298 9

TABLESPACE 68 100 6

TEMPORARY FILENAME 56 100 1

RMAN CONFIGURATION 1108 50 0

LOG HISTORY 56 292 9

OFFLINE RANGE 200 163 0

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED

---------------------------- ----------- ------------- ------------

ARCHIVED LOG 584 28 0

BACKUP SET 40 409 0

BACKUP PIECE 736 200 0

BACKUP DATAFILE 116 282 0

BACKUP REDOLOG 76 215 0

DATAFILE COPY 660 223 1

BACKUP CORRUPTION 44 371 0

COPY CORRUPTION 40 409 0

DELETED OBJECT 20 818 1

PROXY COPY 852 249 0

BACKUP SPFILE 36 454 0

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED

---------------------------- ----------- ------------- ------------

DATABASE INCARNATION 56 292 2

FLASHBACK LOG 84 2048 0

RECOVERY DESTINATION 180 1 1

INSTANCE SPACE RESERVATION 28 1055 1

REMOVABLE RECOVERY FILES 32 1000 0

RMAN STATUS 116 141 0

THREAD INSTANCE NAME MAPPING 80 8 8

MTTR 100 8 1

DATAFILE HISTORY 568 57 0

STANDBY DATABASE MATRIX 400 10 10

GUARANTEED RESTORE POINT 212 2048 0

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED

---------------------------- ----------- ------------- ------------

RESTORE POINT 212 2083 0

已选择34行。

也可以通过where语句进行条件限制。如下所示：

SQL> select TYPE, RECORD_SIZE, RECORDS_TOTAL,RECORDS_USED from v$controlfile_record_section where type in('TABLESPACE','DATAFILE','REDO LOG');

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED

---------------------------- ----------- ------------- ------------

REDO LOG 72 16 3

DATAFILE 428 100 5

TABLESPACE 68 100 6

初始化参数文件的路径可以通过两种方式得到：

方式一：使用v$parameter数据字典。

SQL> desc v$parameter

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

NUM NUMBER

NAME VARCHAR2(80)

TYPE NUMBER

VALUE VARCHAR2(512)

DISPLAY_VALUE VARCHAR2(512)

ISDEFAULT VARCHAR2(9)

ISSES_MODIFIABLE VARCHAR2(5)

ISSYS_MODIFIABLE VARCHAR2(9)

ISINSTANCE_MODIFIABLE VARCHAR2(5)

ISMODIFIED VARCHAR2(10)

ISADJUSTED VARCHAR2(5)

ISDEPRECATED VARCHAR2(5)

DESCRIPTION VARCHAR2(255)

UPDATE_COMMENT VARCHAR2(255)

HASH NUMBER

SQL> select value from v$parameter where name='control_files';

VALUE

--------------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL, D:\ORACLE\PRODUCT\10.2.0\OR

ADATA\ORCL\CONTROL02.CTL, D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL

方式二：使用v$controlfile数据字典。

SQL> desc v$controlfile

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

STATUS VARCHAR2(7)

NAME VARCHAR2(513)

IS_RECOVERY_DEST_FILE VARCHAR2(3)

BLOCK_SIZE NUMBER

FILE_SIZE_BLKS NUMBER

SQL> select NAME from v$controlfile;

NAME

--------------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL

0 0