26、SCN_2
SCN号
查询系统SCN号：
select * from v$database;
或者：
SQL> select CHECKPOINT_CHANGE# from v$database;
CHECKPOINT_CHANGE#
------------------
1278460
查询控制文件里面文件的SCN号、终止SCN号（null）：
select * from v$datafile;
或者：
SQL> select CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
1278460
1278460
1278460
1278460
1278460
1278460
1278460
7 rows selected.
这里，文件的SCN号一定在最新的日志的first SCN号和最旧的日志的first SCN号之间
查询数据头部的SCN号：
select * from v$datafile_header;
或者：
SQL> select CHECKPOINT_CHANGE# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
1278460
1278460
1278460
1278460
1278460
1278460
1278460
7 rows selected.
查询redo log日志的SCN号：
select * from v$log;
或者：
SQL> select GROUP#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from v$log;
GROUP# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
1 1 1278457 2.8147E+14（无穷大，表示null）
2 0 0 0
3 0 0 0
日志切换：
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from v$log;
GROUP# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
1 1 1278457 1295372
2 2 1295372 2.8147E+14
3 0 0 0
SQL> select CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
1278460 -- 文件SCN号没变
1278460
1278460
1278460
1278460
1278460
1278460
7 rows selected.
再切一次，再查：
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from v$log;
GROUP# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
1 1 1278457 1295372
2 2 1295372 1295554
3 3 1295554 2.8147E+14
SQL> select CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
1278460 -- 文件SCN号还是没变
1278460
1278460
1278460
1278460
1278460
1278460
7 rows selected.
再切一次，再查：
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from v$log;
GROUP# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
1 4 1295995 2.8147E+14
2 2 1295372 1295554
3 3 1295554 1295995
SQL> select CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
1295372 -- 文件SCN号改变了
1295372
1295372
1295372
1295372
1295372
1295372
7 rows selected.
当切换日志，日志的SCN号即将要覆盖控制文件里的文件的SCN号的时候，控制文件里的文件的SCN号就会发生改变（改变的具体情况还跟日志的状态有关，等于最近的inactive状态的日志的next SCN号）
查询归档日志的SCN号：
select * from v$archived_log;
或者：
SQL> select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,STATUS from v$archived_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# S
---------- ------------- ------------ -
6 979488 985035 D
7 985035 986316 D
8 986316 986329 D
9 986329 991755 D
10 991755 991764 D
11 991764 991772 D
12 991772 992385 D
13 992385 992394 D
14 992394 992402 D
15 992402 992714 D
16 992714 992723 D
17 992723 992731 D
18 992731 993306 D
19 993306 993315 D
20 993315 993323 D
21 993323 994231 D
22 994231 994240 D
23 994240 994248 D
24 994248 994790 D
25 994790 994799 D
26 994799 994807 D
27 994807 995537 D
28 995537 995546 D
29 995546 995554 D
30 995554 995828 D
31 995828 995837 D
32 995837 995845 D
33 995845 996147 A
34 996147 996156 A
35 996156 996164 A
36 996164 1000469 A
37 1000469 1000478 A
38 1000478 1000486 A
39 1000486 1000816 A
40 1000816 1000825 A
41 1000825 1000834 A
42 1000834 1001141 A
43 1001141 1001150 A
44 1001150 1001158 A
45 1001158 1001393 A
46 1001393 1001402 A
47 1001402 1001410 A
48 1001410 1001658 A
49 1001658 1001667 A
50 1001667 1001676 A
51 1001676 1001934 A
52 1001934 1001943 A
53 1001943 1001951 A
54 1001951 1022709 A
55 1022709 1059579 A
56 1059579 1081843 A
57 1081843 1100718 A
58 1100718 1109232 A
59 1109232 1110819 A
60 1110819 1112688 A
61 1112688 1116660 A
62 1116660 1140019 A
63 1140019 1158942 A
64 1158942 1163815 A
65 1163815 1167980 A
66 1167980 1170143 A
67 1170143 1177352 A
68 1177352 1184755 A
69 1184755 1185981 A
70 1185981 1197888 A
71 1197888 1207833 A
72 1207833 1225426 A
73 1225426 1252415 A
74 1252415 1278456 A
69 rows selected.
SCN号与时间之间的转换：
select to_char(scn_to_timestamp(1295554),'YYYY-MM-DD HH24:MI:SS') from dual;
select timestamp_to_scn(to_date('2017-03-07 16:09:35','YYYY-MM-DD HH24:MI:SS')) from dual;
select dbms_flashback.get_system_change_number scn1, timestamp_to_scn(sysdate) scn2 from dual;
