修復identity 型別欄位資料的跳躍（轉）
修復identity 型別欄位資料的跳躍（譯）
內容提要
1、引言
2、dbcc object_atts
3、下載'sp_identity' 儲存過程
4、使用sp_identity顯示使用identity 型別欄位的表
5 使用sp_identity顯示當前表的 identity 數字
6、使用sp_identity 修改表的 identity數字
1引言
在sybase 中，有一個使用未公開的dbcc 命令 ，即 dbcc object_atts 快速修復identity 跳躍的方法，因為 dbcc
object_atts 比較難於使用,而儲存過程 sp_identity 更容易完成你的問題
警告：本文的方法沒有出現在sybase 釋出的文件中，並且沒有被 sybase 支援，使用與否取決定你自己，如果出現問題，不能從
sybase 技術支援那裡得到幫助
2、dbcc object_atts
dbcc object_atts 命令提供了 一個訪問 OAM 頁的後門，在 OMA 中，儲存了表的 identity 計數器。
不幸的是，這個命令在文件中幾乎沒有任何說明，並且很難使用，其語法如下：
dbcc object_atts (table_name, 0, subcommand, [ new_value ] )
第一個引數 ：表名，第二個引數 0，
第三個引數： get ---顯示 儲存在 OMA 中的 identity 值
或put ---設定 OMA 中的 identity 值，設定的具體資料為 第四個引數資料
第四個引數 ：新設定的數
主要的問題是 dbcc object_atts 顯示的資料為 16位元組的 16進位制字元，你必須自己解釋為10進位制，並且，如何解釋依賴於
identity 列的長度，同樣，設定它也是。在實際環境中，很少作為首選。
幸運的是，有一個儲存過程 sp_identity 能夠處理編碼與解碼， 並且給出所有含 identity 列的表的情況
下載 'sp_identity'
sp_identity 只支援 11。0 以後版本，下載檔案中包含兩個版本，一個用於 ASE 11.9/11.5/11.0 的叫
,sp_identity.119.sql，另外一個用於ASE 12.0及以後版本(sp_identity.120.sql)
4、使用sp_identity顯示使用identity 型別欄位的表
sp_identity 有幾個不同的功能，不帶任何引數執行，只顯示當前資料庫中 包含 identity 列的表，
以及最大可能的 identity 跳躍
1> sp_identity
2> go
Tables with an identity column in database '':
Owner.Table.Column datatype Maximum Identity Gap
------------------------------- -------------------------------
dbo.invoices.inv_nr numeric(18) 500000000000000 (burn)
dbo.small_gap_tab.a numeric(30) 10 (identity_gap)
dbo.my_table.n numeric(6) 500 (burn)
(burn) 表示 identity列的 一次取數由系統引數"identity burning set factor" 決定，而
(identity_gap) 表示 該表有自定義的identity_gap 屬性
5 使用sp_identity顯示當前表的 identity 數字
確定當前特定表的 OAM 頁的identity 值，必須按照以下三步過程：
首先 開啟跟蹤標誌 3604 (執行 dbcc traceon(3604) )
執行 指定特定表名執行 sp_identity
1> sp_identity invoices
2> go
Table = invoices (id=1804533462)
Identity column = a numeric(18)
Max. Identity Gap = 500000000000000
("identity burning set factor" = 5000 = 0.05%)
Reading identity value from OAM page...
object_atts:get:return value=1
0207E20C: 0001c6bf 52634001 00000000 00000000 ....Rc@.........
0207E21C: .
DBCC execution completed. If DBCC printed error messages,
contact a user with System Administrator (SA) role.
這個 16位元組的 16進位制字串是 OAM 中儲存的 identity 值 （0001c6bf 52634001 00000000 00000000）
再次執行 sp_identity ,並且 指定這個字串引數
1> sp_identity invoices, "0001c6bf 52634001 00000000 0000000"
2> go
Table = invoices (id=1804533462)
Identity column = a numeric(18)
Max. Identity Gap = 500000000000000
("identity burning set factor" = 5000 = 0.05%)
Decoded identity value on OAM page = 500000000000001
(hex=0001c6bf52634001)
現在，我們得到並解碼了 OAM 中的 identity 值，但它意味著什麼呢？
首先，如果你用 "with nowait" 停止伺服器，這個 identity 列將跳躍多少。而不是表中資料的下一個。
而是儲存在 OAM 中的資料加上 identity 種子設頂數(在 ASE 12.0, 為identity_gap 選項)
另外，當你執行 ASE 12.0, 可以透過執行 dbcc listoam看到這個資料
6、使用sp_identity 修改表的 identity數字
使用 sp_identity ， OAM 中identity 的 數字可以被設定為任意數. 這個需要如下步驟：
1、確認你要設定的資料（檢視錶中identity 的資料應該到多少,比如 10033 ）
2、正常停止伺服器
3 重新啟動伺服器，以單使用者模式，或者不讓使用者訪問
4、 執行 sp_identity table_name, null, new_value ;
1> sp_identity invoices, null, 10032
2> go
Table = invoices (id=1804533462)
Identity column = a numeric(18)
Max. Identity Gap = 500000000000000
("identity burning set factor" = 5000 = 0.05%)
object_atts:put:return value=1
DBCC execution completed. If DBCC printed error messages,
contact a user with System Administrator (SA) role.
Identity value on OAM page has been set to 10032
(hex=0x00000000000027300000000000000000)
You should now do a 'shutdown with nowait' immediately.
After restarting the server, the value assigned to the
next row inserted into 'invoices' will be 10033.
5 然後立即執行 'shutdown with nowait' ，這樣 表的下一個 identity 數字是 10033
6 重新正常啟動sybase 服務
說明：如果你應用了 identity 資料型別，並且要求identity 列保持連續，並且資料量很大，那麼，用這個方法需要的時間可能比較短
否則不推薦使用。
下面是SP_IDENTITY.120.SQL，用於 12.0 以後版本
/*
* SP_IDENTITY.120.SQL - version for ASE 12.0 or later
* (when running ASE 11.9/11.5/11.0, install SP_IDENTITY.119.SQL instead)
*
* Description
* ===========
* This file contains the stored procedure 'sp_identity', which performs some
* useful functions w.r.t. identity columns:
*
* - displays all tables with an identity column in the current database
* - displays the identity value stored on a table's OAM page
* - resets the identity value stored on a table's OAM page
*
* For details and background, see .
*
*
* Installation
* ============
* Execute this script using "isql", using a login having both "sa_role"
* and "sso_role".
* The stored procedure will be created in the sybsystemprocs database.
*
*
* Notes
* =====
* - Traceflag 3604 should be enabled before running sp_identity for a
* specific table ("dbcc traceon(3604)"); if omitted, you won't see
* any output.
*
* - To run sp_identity for a specific table, sa_role and sybase_ts_role
* are required.
*
* - sp_identity was successfully tested on ASE versions 11.0.3, 11.5,
* 11.9.2 and 12.0 on various platforms.
*
* - sp_identity will not work on ASE 11.0 because this version
* does not support the 'case' expression.
*
* - sp_identity was not tested on a 64-bit ASE version.
*
*
* Revision History
* ================
* Version 1.0 17-Dec-2000 First version
* Version 1.1 Oct-2002 Handle identity_gap=NULL (thanks to Alan Cooper),
* and some small improvements for installation
* Version 1.2 Apr-2003 Improved installation
*
*
* Copyright Note & Disclaimer :
* =============================
* This software is provided "as is"; there is no warranty of any kind.
* While this software is believed to work accurately, it may not work
* correctly and/or reliably in a production environment. In no event shall
* Rob Verschoor and/or Sypron B.V. be liable for any damages resulting
* from the use of this software.
* You are allowed to use this software free of charge for your own
* professional, non-commercial purposes.
* You are not allowed to sell or bundle this software or use it for any
* other commercial purpose without prior written permission from
* Rob Verschoor/Sypron B.V.
* You may (re)distribute only unaltered copies of this software, which
* must include this copyright note, as well as the copyright note in
* the header of each stored procedure.
*
* Note: All trademarks are acknowledged.
*
* Please send any comments, bugs, suggestions etc. to the below email
* address.
*
* Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V.
* P.O.Box 10695
* 2501 HR Den Haag
* The Netherlands
*
* Email:
* WWW :
*----------------------------------------------------------------------------
*/
set nocount on
go
set flushmessage on
go
use sybsystemprocs
go
-- we need to be at ASE 12.0 or later; if not, abort this script
if isnull(object_id("master.dbo.sysqueryplans"),99) >= 99
begin
print ""
print ""
print "********************************************"
print "********************************************"
print " This script is for ASE 12.0 or later."
print " Please install SP_IDENTITY.119.SQL instead."
print "********************************************"
print "********************************************"
print " "
print " "
print ""
set background on -- terminate this script now
end
go
print ""
print "Installing 'sp_identity'..."
print ""
go
if object_id("sp_identity_help") <> NULL
begin
drop proc sp_identity_help
end
go
create proc sp_identity_help
/* Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V. */
as
begin
print " "
print " Usage: sp_identity table_name, ""hex-string-from-OAM-page"", new-identity-value "
print " Notes: "
print " - specifying only parameter 1 retrieves the current identity value for"
print " that table from the OAM page as a hexadecimal string."
print " - specifying this hexadecimal string as the second parameter (in quotes)"
print " will decode the hex value to a numeric value."
print " - specifying a numeric value for the third parameter will set that"
print " value as the new identity value for this table."
print " - parameters 2 and 3 cannot be specified together: one of them must be NULL."
print " - ""dbcc traceon(3604)"" must be run before using option 2 or 3"
print " "
print " Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V."
print " See for background information & updates."
print " "
end
go
grant execute on sp_identity_help to public
dump tran sybsystemprocs with truncate_only
go
if object_id("sp_identity") <> NULL
begin
drop proc sp_identity
end
go
create proc sp_identity
/* Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V. */
@p0 varchar(50) = NULL, -- table name
@p1 varchar(50) = NULL, -- hex value to decode
@p2 numeric(38) = NULL -- new value to set
as
begin
set nocount on
declare @n numeric(38), @j numeric(38), @n256 numeric(3), @n10 numeric(38)
declare @i int, @max int, @idlen int, @idlenb int, @ib int, @len int
declare @p0_id int, @b int, @b1 binary(1), @lsb int
declare @idgap int, @idburn int, @maxidgap numeric(38), @max1 int, @max2 int
declare @idburnpct numeric(5,2), @v int, @ntab int
declare @c2 char(2), @doampg int, @indid int, @dbname varchar(32)
declare @vc50 varchar(50), @colname varchar(32), @vb16 varbinary(16)
select @dbname = db_name()
-- check version
select @v = 0
if exists (select * from sysobjects
where name = "sysqueryplans" and type = "S")
begin
select @v = 12
end
if @p0 = '?'
begin
exec sp_identity_help
return 0
end
-- numeric values
select @n256 = 256
select @n10 = 10
-- id burning set factor
select @idburn = value from master.dbo.syscurconfigs where config=141
select @idburnpct = convert(numeric(5,2), @idburn * 0.00001)
-- figure out msb/lsb
select @lsb = 0
if substring(convert(binary(4), 1), 1 ,1) = 0x01 select @lsb = 1
if substring(convert(binary(4), 1), 2 ,1) = 0x01 select @lsb = 2
if substring(convert(binary(4), 1), 3 ,1) = 0x01 select @lsb = 3
if substring(convert(binary(4), 1), 4 ,1) = 0x01 select @lsb = 4
-- get all tables containing identity columns in this database
select id = so.id, owner = su.name, uid = so.uid, so.name, si.doampg, si.indid, idgap =
isnull(si.identitygap,0),
colname = sc.name, sc.prec, maxgap = convert(numeric(38), ceiling((@idburn * 0.0000001) *
power(@n10,prec)))
into #id
from sysindexes si, syscolumns sc, sysobjects so, sysusers su
where si.indid < 2
and so.type = "U"
and sc.status & 128 = 128
and so.id = sc.id
and so.id = si.id
and so.uid = su.uid
select @ntab = @@rowcount
set arithabort numeric_truncation off
if @p0 = null
begin
-- display all tables with identity columns in the current DB
if @ntab = 0
begin
print " There are no tables with an identity column in database '%1!'", @dbname
return 0
end
print " Tables with an identity column in database '%1!':", @dbname
print " "
select @max1 = max(char_length(owner + "." + name + "." + colname)) + 12 from #id
select @max2 = max(char_length(convert(varchar(38), maxgap))) + 7 from #id
if @max1 <= 52 and @max2 <= 24
begin
select convert(varchar(52), owner + "." + name + "." + colname + " numeric(" +
convert(varchar(2),prec) + ")") "Owner.Table.Column datatype",
convert(varchar(24), case when idgap = 0 then convert(varchar(38),maxgap) + "
(burn)"
else convert(varchar, idgap) + " (identity_gap)"
end) "Maximum Identity Gap"
from #id
order by name
end
else
if @max1 <= 32 and @max2 <= 44
begin
select convert(varchar(32), owner + "." + name + "." + colname + " numeric(" +
convert(varchar(2),prec) + ")") "Owner.Table.Column datatype",
convert(varchar(44), case when idgap = 0 then convert(varchar(38),maxgap) + "
(burn)"
else convert(varchar, idgap) + " (identity_gap)"
end) "Maximum Identity Gap"
from #id
order by name
end
else
begin
select convert(varchar(78), owner + "." + name + "." + colname + " numeric(" +
convert(varchar(2),prec) + ")") "Owner.Table.Column datatype",
convert(varchar(50), case when idgap = 0 then convert(varchar(38),maxgap) + "
(burn)"
else convert(varchar, idgap) + " (identity_gap)"
end) "Maximum Identity Gap"
from #id
order by name
end
print " "
print " Legend:"
print " (burn) : gap size is determined by ""identity burning set factor"" "
if @v = 12
begin
print " (identity_gap) : gap size is determined by the ""identity_gap"" setting"
end
print " "
print " Current value for ""identity burning set factor"" = %1! (=%2!%%)", @idburn, @idburnpct
return 0
end
if charindex("sa_role", show_role()) = 0
begin
print "You must have 'sa_role' to run this procedure."
return -1
end
if charindex("sybase_ts_role", show_role()) = 0
begin
print "You must have 'sybase_ts_role' to run this procedure."
return -1
end
-- get some info on the object
select @p0_id = object_id(@p0)
if @p0_id = NULL
begin
print " Error: '%1!' is not a user table.", @p0
return -1
end
if @p0 not like "%.%"
begin
select @ntab = count(*) from sysobjects where name = @p0 and type = "U"
if @ntab > 1
begin
print " %1! tables named '%2!' exist in this database:", @ntab, @p0
print " "
select owner + "." + name "owner.table_name"
from #id where name = @p0 order by uid
print " "
print " Specify 'owner.table_name' to identify the table."
return -1
end
end
select @doampg = doampg, @indid = indid, @idgap = idgap
from #id where id = @p0_id
if not exists (select 1 from syscolumns where id = @p0_id and status & 128 = 128)
begin
print " Error: Table '%1!' does not have an identity column", @p0
return -1
end
-- retrieve the length of the numeric identity column as declared when the
-- table was created
select @len = prec, @colname = name
from syscolumns where id = @p0_id and status & 128 = 128
if @@rowcount = 0
begin
print " Error: Could not retrieve length of identity column for table '%1!'", @p0
return -1
end
select @idlen = @len -- column precision (positions)
select @idlenb = (@idlen+1-((@idlen+5)/12)-(@idlen/12))/2 -- column length (bytes)
print " "
print " Table = %1! (id=%2!)", @p0, @p0_id
print " Identity column = %1! numeric(%2!)", @colname, @len
--print " OAM page = %1!", @doampg
--print " Bytes on OAM page = %1!", @idlenb
if @idgap > 0
begin
print " Max. Identity Gap = %1! (""identity_gap"" = %2!)", @idgap, @idgap
end
else
begin
set arithabort numeric_truncation off
select @maxidgap = ceiling((convert(numeric(38),@idburn) * 0.0000001) * power(@n10,@len))
print " Max. Identity Gap = %1!", @maxidgap
print " (""identity burning set factor"" = %1! = %2!%%)", @idburn, @idburnpct
if @v = 12
begin
print " (Note: ""identity_gap"" has not been set for this table !)"
end
end
print " "
if @p1 = NULL and @p2 = NULL -- retrieve current ID value
begin
print " "
print " Reading identity value from OAM page... (traceflag 3604 should be enabled !)"
print " "
dbcc object_atts (@p0, 0, get)
print " "
print " To decode this hexadecimal value, re-run 'sp_identity' with the hexadecimal"
print " string as a quoted 2nd parameter (you can leave the spaces in). "
print " Example: sp_identity %1!, ""the-hex-string-from-the-above-output"" ", @p0
print " "
--
-- only for 12.0+, dbcc listoam will display the values in the OAM and the DES
--
--dbcc listoam(@dbname, @p0_id, @indid)
--
return 0
end
if upper(@p1) like "[ 0-9A-F]%" -- entered a hex string, decode it
begin
if @p2 != NULL
begin
print " Error: When decoding a hex value from the OAM page, parameter 3"
print " (the new identity value) must be NULL."
exec sp_identity_help
return -1
end
select @ib = @idlenb -- counts bytes
select @i = char_length(@p1) -- counts string
select @n = 0 -- holds result
while 1 = 1
begin
select @c2 = substring(@p1, (char_length(@p1)-@i)+1, 2)
--print "Step %1!, [%2!]", @ib, @c2
if upper(substring(@c2,1,1)) = " " -- ignore single space pasted in by user
begin
select @i = @i - 1
continue
end
if (upper(@c2) not like "[0-9A-F][0-9A-F]")
begin
print " Error: Invalid characters (%1!) in hex string", @c2
break
end
-- add this byte to the result
select @vc50 = @vc50 + @c2
select @j = power(@n256, (@ib-1))
select @n = @n + @j * hextoint("0x" + @c2)
--print "Step %1!, exp= %2!, result = %3!", @ib, @j, @n
-- next byte
select @ib = @ib - 1
if @ib = 0 break -- ready
-- next 2 chars
select @i = @i - 2
if @i = 0
begin
-- we shouldn't have got here, error
print " Error: ID column length and specified hex string do not match !"
break
end
end
if @ib = 0
begin
print " Decoded identity value on OAM page = %1!", @n
print " (hex= %1!)", @vc50
print " "
return 0
end
else
begin
print " Error: Aborted due to error."
return -1
end
end
if @p2 != NULL -- entered a new ID value, set it
begin
if @p1 != NULL
begin
print " Error: When setting a new identity value, parameter 2"
print " (the hex string from the OAM page) must be NULL."
exec sp_identity_help
return -1
end
-- set the new ID value
if @p2 <= 0
begin
print " Error: The new identity value must be >= 0."
print " "
exec sp_identity_help
return -1
end
if char_length(convert(varchar, @p2)) > @idlen
begin
select @vc50 = replicate("9", @idlen)
print " Error: New identity value (%1!) is too large", @p2
print " for this identity column. The maximum possible value", @p2
print " is %1! (%2! positions).", @vc50, @idlen
return -1
end
if @lsb = 0
begin
print " "
print "Internal error in 'sp_identity':"
print "Cannot figure out byte order on this platform."
print "Please send a note to with the text of this"
print "error and your @@version string."
return -1
end
-- convert the numeric value to hex
select @ib = @idlenb -- counts bytes
select @i = 15
select @vc50 = NULL -- holds result
select @vb16 = NULL -- holds result
select @n256 = 256
select @n = @p2
set arithabort numeric_truncation off
while 1 = 1
begin
select @j = power(@n256, @i)
select @b = @n / @j
select @n = @n - (@b * @j)
select @c2 = right(inttohex(@b),2)
select @b1 = substring(convert(binary(4), @b), @lsb,1)
select @vb16 = @vb16 + @b1
--print "Step %1!, @b=%2!, hex(c2)=%3!, hex(b1)=%5!, result=%4!", @i, @b, @c2, @vb16, @b1
select @i = @i - 1
if @i < 0 break -- ready
end
if @i < 0
begin
select @vb16 = substring(@vb16, 16 - (@idlenb) + 1, (@idlenb) ) + substring(@vb16, 1, 16 -
(@idlenb) )
-- set the new ID value
dbcc object_atts(@p0, 0, "put", @vb16)
print " "
print " Identity value on OAM page has been set to %1!", @p2
print " (hex= %1!)", @vb16
print " "
select @p2 = @p2 + 1
print " You should now do a 'shutdown with nowait' immediately."
print " After restarting the server, the value assigned to the next row "
print " inserted into '%1!' will be %2!.", @p0, @p2
print " "
return 0
end
else
begin
print " Error: Aborted due to error."
return -1
end
end
-- we should never get here
print "Internal error in 'sp_identity': Reached invalid end of procedure."
return -1
end
go
grant execute on sp_identity to public
dump tran sybsystemprocs with truncate_only
go
print "Ready. For usage information, run 'sp_identity ""?"" '."
print ""
go
/*
** end
*/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/66009/viewspace-788224/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 主流資料庫欄位型別轉.Net型別的方法資料庫型別
- 【轉】修改表的欄位資料型別的方法資料型別
- 資料欄位型別匹配型別
- 轉載：Oracle常用的資料庫欄位型別Oracle資料庫型別
- 保留兩位小數：資料庫欄位型別NUMBER，Java欄位型別Double型別資料庫型別Java
- 修改欄位資料型別的方法資料型別
- 欄位的資料型別隱式轉換有關係資料型別
- PHP 操作 mysql blob 資料型別的欄位PHPMySql資料型別
- MSSQL資料庫的欄位型別總結SQL資料庫型別
- Java資料型別與資料庫欄位型別對應關係Java資料型別資料庫
- 【mongo】mongo 欄位型別互轉Go型別
- [轉]MySQL 欄位型別參考MySql型別
- 資料庫中欄位資料型別以及約束資料庫資料型別
- oracle的欄位型別Oracle型別
- WHRER條件裡的資料型別必須和欄位資料型別一致資料型別
- Oracle-不刪表資料，修改欄位型別Oracle型別
- 修改表的欄位型別型別
- MongoDB更改欄位型別MongoDB型別
- MongoDB中的欄位型別IdMongoDB型別
- oracle 修改欄位型別的方法Oracle型別
- 查詢mysql某張表中的所有資料（欄位）型別MySql型別
- 支援 enum 型別的欄位允許為空插入資料庫型別資料庫
- mssql sqlserver 可以儲存二進位制資料的欄位型別詳解SQLServer型別
- MySQL欄位新增註釋，但不改變欄位的型別MySql型別
- sql語句修改欄位型別和增加欄位SQL型別
- Field的getModifiers()方法返回int型別值表示該欄位的修飾符型別
- MySQL欄位型別最全解析MySql型別
- date、timestamp欄位型別型別
- MySQL欄位型別小記MySql型別
- sqlite sql 修改欄位型別SQLite型別
- SqlSugar code first 欄位為列舉型別，預設生成資料庫欄位為bigint如何設定為int型別SqlSugar型別資料庫
- [提問交流]建立模型，新增屬性，欄位型別如何設定2位小數的欄位型別模型型別
- lob欄位型別轉換ora-22858型別
- Oracle查詢資料表結構(欄位,型別,大小,備註)Oracle型別
- 資料型別,型別轉換資料型別
- 用Elasticsearch做大規模資料的多欄位、多型別索引檢索Elasticsearch多型型別索引
- andFilterWhere()函式找不出某個int型別欄位為0的資料Filter函式型別
- SQL SERVER 查詢表的欄位名、資料型別和最大長度SQLServer資料型別