科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网软件频道Oracle10g v$database视图SCN增强

Oracle10g v$database视图SCN增强

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

在Oracle10g中,Oracle对v$database视图做出增强,增加了很多字段,其中一个重要字段是:CURRENT_SCN,代表数据库当前的SCN: SQL> select * from v$version; BANNER ---------

作者:中国IT实验室 来源:中国IT实验室 2007年10月7日

关键字: ORACLE

  • 评论
  • 分享微博
  • 分享邮件

在本页阅读全文(共2页)

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

查看本文来源

    • 评论
    • 分享微博
    • 分享邮件