作者:雨竹清风
要想得到控制文件的全部信息可以使用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