« Black Eyes Peas-黑眼豆豆北京演唱会记行 | Blog首页 | Oracle10gR2 ORA-3136 错误解决 »
Oracle10g v$database视图SCN增强
作者：eygle |【转载时请务必以超链接形式标明文章原始出处和作者信息及本声明】链接：http://www.eygle.com/archives/2006/07/oracle10g_v_database_scn.html
在Oracle10g中,Oracle对v$database视图做出增强,增加了很多字段,其中一个重要字段是:CURRENT_SCN,代表数据库当前的SCN:
|
SQL> select * from v$version; BANNER SQL> desc v$database |
这个字段来自底层基础表x$kccdi ,其中的字段为:DICUR_SCN , DI代表Database Information ,cur_scn 代表 current SCN:
| SQL> desc x$kccdi Name Null? Type ----------------------------------------- -------- ----------------- ADDR RAW(4) INDX NUMBER ............... DIPLID NUMBER DIPLN VARCHAR2(101) DICUR_SCN VARCHAR2(16) DIDBUN VARCHAR2(30) DIFSTS NUMBER DIFOPR NUMBER DIFTHS NUMBER DIFTGT VARCHAR2(30) DIFOBS VARCHAR2(512) |
这个SCN值和9i中引入的dbms_flashback.get_system_change_number获得的值相同:
|
SQL> select A B |
v$database在Oracle10g中构建的语句如下,引用供参考:
| SELECT di.inst_id, di.didbi, di.didbn, TO_DATE (di.dicts, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), TO_NUMBER (di.dirls), TO_DATE (di.dirlc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), TO_NUMBER (di.diprs), TO_DATE (di.diprc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), DECODE (di.dimla, 0, 'NOARCHIVELOG', 1, 'ARCHIVELOG', 'MANUAL'), TO_NUMBER (di.discn), TO_NUMBER (di.difas), DECODE (BITAND (di.diflg, 256), 256, 'CREATED', DECODE (BITAND (di.diflg, 1024), 1024, 'STANDBY', DECODE (BITAND (di.diflg, 32768), 32768, 'CLONE', DECODE (BITAND (di.diflg, 4096), 4096, 'BACKUP', 'CURRENT' ) ) ) ), TO_DATE (di.dicct, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), di.dicsq, TO_NUMBER (di.dickp_scn), TO_DATE (di.dickp_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), DECODE (BITAND (di.diflg, 4), 4, 'REQUIRED', DECODE (di.diirs, 0, 'NOT ALLOWED', 'ALLOWED') ), TO_DATE (di.divts, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), DECODE (di.didor, 0, 'MOUNTED', DECODE (di.didor, 1, 'READ WRITE', 'READ ONLY') ), DECODE (BITAND (di.diflg, 65536), 65536, 'MAXIMUM PROTECTION', DECODE (BITAND (di.diflg, 128), 128, 'MAXIMUM AVAILABILITY', DECODE (BITAND (di.diflg, 134217728), 134217728, 'RESYNCHRONIZATION', DECODE (BITAND (di.diflg, 8), 8, 'UNPROTECTED', 'MAXIMUM PERFORMANCE' ) ) ) ), DECODE (di.diprt, 1, 'MAXIMUM PROTECTION', 2, 'MAXIMUM AVAILABILITY', 3, 'RESYNCHRONIZATION', 4, 'MAXIMUM PERFORMANCE', 5, 'UNPROTECTED', 'UNKNOWN' ), DECODE (di.dirae, 0, 'DISABLED', 1, 'SEND', 2, 'RECEIVE', 3, 'ENABLED', 'UNKNOWN' ), TO_NUMBER (di.diacid), TO_NUMBER (di.diacid), DECODE (BITAND (di.diflg, 33554432), 33554432, 'LOGICAL STANDBY', DECODE (BITAND (di.diflg, 1024), 1024, 'PHYSICAL STANDBY', 'PRIMARY' ) ), TO_NUMBER (di.diars), DECODE (BITAND (difl2, 1), 1, 'ENABLED', 'DISABLED'), DECODE (di.disos, 0, 'IMPOSSIBLE', 1, 'NOT ALLOWED', 2, 'SWITCHOVER LATENT', 3, 'SWITCHOVER PENDING', 4, 'TO PRIMARY', 5, 'TO STANDBY', 6, 'RECOVERY NEEDED', 7, 'SESSIONS ACTIVE', 8, 'PREPARING SWITCHOVER', 9, 'PREPARING DICTIONARY', 10, 'TO LOGICAL STANDBY', 'UNKNOWN' ), DECODE (di.didgd, 0, 'DISABLED', 'ENABLED'), DECODE (BITAND (di.diflg, 1048576), 1048576, 'ALL', DECODE (BITAND (di.diflg, 2097152), 2097152, 'STANDBY', 'NONE' ) ), DECODE (BITAND (diflg, 1073741824), 1073741824, 'YES', DECODE (BITAND (diflg, 131072 + 262144 + 524288), 0, DECODE (BITAND (difl2, 2), 0, 'NO', 'IMPLICIT'), 'IMPLICIT' ) ), DECODE (BITAND (di.diflg, 131072), 131072, 'YES', 'NO'), DECODE (BITAND (di.diflg, 262144), 262144, 'YES', 'NO'), DECODE (BITAND (di.diflg, 268435456), 268435456, 'YES', 'NO'), di.diplid, di.dipln, di2.di2rdi, di2.di2inc, TO_NUMBER (di.dicur_scn), DECODE (BITAND (di2.di2flag, 1), 1, 'YES', DECODE (di2.di2rsp_oldest, 0, 'NO', 'RESTORE POINT ONLY') ), DECODE (BITAND (diflg, 524288), 524288, 'YES', 'NO'), DECODE (BITAND (difl2, 2), 2, 'YES', 'NO'), di.didbun, TO_NUMBER (di2.di2actiscn), DECODE (di.difsts, 0, 'DISABLED', 1, 'BYSTANDER', 2, 'SYNCHRONIZED', 3, 'UNSYNCHRONIZED', 4, 'SUSPENDED', 5, 'STALLED', 6, 'LOADING DICTIONARY', 7, 'PRIMARY UNOBSERVED', 8, 'REINSTATE REQUIRED', 9, 'REINSTATEIN PROGRESS', 10, 'REINSTATE FAILED', '' ), di.diftgt, di.difths, DECODE (di.difopr, 1, 'YES', 2, 'NO', 3, 'UNKNOWN', ''), di.difobs FROM x$kccdi di, x$kccdi2 di2 |
此前获取SCN可以通过如下方法:
http://www.eygle.com/faq/How.To.Get.Current.SCN.of.Database.htm
By eygle on 2006-07-19 10:34 | Comments (0) | Posted to Oracle10g/11g | Edit |Pageviews:
