Oracle10g v$database视图SCN增强

2006-11-15 作者:网管整理来源:bitsCN.com 点评 投稿 收藏

在Oracle10g中,Oracle对v$database视图做出增强,增加了很多字段,其中一个重要字段是:CURRENT_SCN,代表数据库当前的SCN: 网管联盟bitsCN_com

SQL> select * from v$version; 网管bitscn_com

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

网管下载dl.bitscn.com

SQL> desc v$database
Name Null? Type
----------------------------------------- -------- ----------------------------
DBID NUMBER
NAME VARCHAR2(9)
...................
CURRENT_SCN NUMBER
FLASHBACK_ON VARCHAR2(18)
SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3)
DB_UNIQUE_NAME VARCHAR2(30)
STANDBY_BECAME_PRIMARY_SCN NUMBER
FS_FAILOVER_STATUS VARCHAR2(21)
FS_FAILOVER_CURRENT_TARGET VARCHAR2(30)
FS_FAILOVER_THRESHOLD NUMBER
FS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7)
FS_FAILOVER_OBSERVER_HOST VARCHAR2(512)
网管u家u.bitscn@com

这个字段来自底层基础表x$kccdi ,其中的字段为:DICUR_SCN , DI代表Database Information ,cur_scn 代表 current SCN: 中国网管联盟bitsCN.com

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获得的值相同:

网管网www.bitscn.com

SQL> select
2 (select dicur_scn from x$kccdi ) a,
3 (select dbms_flashback.get_system_change_number a from dual) b
4 from dual; 网管网www.bitscn.com

A B
---------------- ------------------
8905603606859 8905603606859 网管联盟bitsCN_com

v$database在Oracle10g中构建的语句如下,引用供参考: 中国网管论坛bbs.bitsCN.com

网管网www.bitscn.com

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'), 网管bitscn_com
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),

网管u家u.bitscn@com

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'),

中国网管联盟bitsCN.com

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可以通过如下方法:

网管网www.bitscn.com

http://www.eygle.com/faq/How.To.Get.Current.SCN.of.Database.htm

中国网管联盟bitsCN.com

顶一下

TAGs：增强 DECODE BITAND di.diflg VARCHAR2 TO_NUMBER NUMBER

上一篇：在sqlplus中操作blob和clob 下一篇：Oracle 8i中回滚段使用和ORA-1555

Oracle10g v$database视图SCN增强

2006-11-15 作者:网管整理 来源:bitsCN.com 点评 投稿 收藏

2006-11-15 作者:网管整理来源:bitsCN.com 点评投稿收藏