{"id":1163,"date":"2015-09-30T14:05:43","date_gmt":"2015-09-30T05:05:43","guid":{"rendered":"http:\/\/www.climb.co.jp\/blog_dbmoto\/?p=1163"},"modified":"2015-09-30T14:05:51","modified_gmt":"2015-09-30T05:05:51","slug":"oracle%e9%a0%86%e5%ba%8f%e3%82%aa%e3%83%96%e3%82%b8%e3%82%a7%e3%82%af%e3%83%88%ef%bc%88%e3%82%b7%e3%83%bc%e3%82%b1%e3%83%b3%e3%82%b9%ef%bc%89%e3%81%ae%e3%83%ac%e3%83%97%e3%83%aa%e3%82%b1%e3%83%bc","status":"publish","type":"post","link":"https:\/\/www.climb.co.jp\/blog_dbmoto\/archives\/1163","title":{"rendered":"[DBMoto]Oracle\u9806\u5e8f\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\uff08\u30b7\u30fc\u30b1\u30f3\u30b9\uff09\u306e\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u3092\u30b9\u30af\u30ea\u30d7\u30c8\u3067\u5b9f\u73fe"},"content":{"rendered":"

DBMoto\u3092\u4f7f\u7528\u3057\u3066Oracle\u306e\u30c7\u30fc\u30bf\u3092\u5dee\u5206\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u3059\u308b\u5834\u5408\u3001Redo\u30ed\u30b0\u3092\u53c2\u7167\u3057\u3066\u76f4\u63a5\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u3092\u78ba\u8a8d\u3057\u307e\u3059\u3002
\n\u3057Oracle\u306e\u9806\u5e8f\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\uff08\u30b7\u30fc\u30b1\u30f3\u30b9\uff09\u306f\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u51e6\u7406\u3067\u306f\u306a\u304fRedo\u30ed\u30b0\u306b\u60c5\u5831\u304c\u8a18\u9332\u3055\u308c\u306a\u3044\u305f\u3081\u3001DBMoto\u3067\u666e\u901a\u306b\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u3092\u884c\u3046\u3053\u3068\u304c\u3067\u304d\u307e\u305b\u3093\u3002<\/p>\n

DBMoto\u3092\u30c7\u30a3\u30b6\u30b9\u30bf\u30ea\u30ab\u30d0\u30ea\u7b49\u306eBCP\u76ee\u7684\u3067\u4f7f\u7528\u3055\u308c\u308b\u5834\u5408\u306b\u306f\u3001\u30c6\u30fc\u30d6\u30eb\u30c7\u30fc\u30bf\u306f\u540c\u671f\u304c\u53d6\u308c\u3066\u3082\u9806\u5e8f\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u306e\u540c\u671f\u304c\u53d6\u308c\u306a\u3044\u3068\u3044\u3046\u3053\u3068\u306b\u306a\u308a\u3001\u3053\u306e\u5834\u5408\u306f\u9806\u5e8f\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u624b\u52d5\u3067\u79fb\u52d5\u3057\u305f\u308a\u30b7\u30fc\u30b1\u30f3\u30b9\u306e\u6700\u5f8c\u306e\u5024\u3092\u624b\u52d5\u3067\u8a2d\u5b9a\u3059\u308b\u7b49\u304c\u5fc5\u8981\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n

\u3057\u304b\u3057DBMoto\u306e\u6a19\u6e96\u6a5f\u80fd\u3067\u3042\u308b\u30b9\u30af\u30ea\u30d7\u30c8\u3092\u8a18\u8ff0\u3059\u308b\u3053\u3068\u3067\u3001\u7591\u4f3c\u7684\u3067\u306f\u3042\u308a\u307e\u3059\u304c\u9806\u5e8f\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u306e\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u304c\u53ef\u80fd\u3068\u306a\u308a\u307e\u3059\u3002<\/p>\n

\u25cf\u9806\u5e8f\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u306e\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u3092\u5b9f\u73fe\u3055\u305b\u308b\u305f\u3081\u306e\u6e96\u5099\u3068\u6d41\u308c<\/strong><\/span><\/p>\n

1. \u30bd\u30fc\u30b9\u3068\u30bf\u30fc\u30b2\u30c3\u30c8\u306e\u30b7\u30fc\u30b1\u30f3\u30b9\u3092\u4e88\u3081[NOCACHE]\u306b\u3057\u307e\u3059\u3002
\n\u21d2ALTER SEQUENCE \u30b7\u30fc\u30b1\u30f3\u30b9\u540d NOCACHE
\n2. \u30bd\u30fc\u30b9\u3068\u30bf\u30fc\u30b2\u30c3\u30c8DB\u306b\u30c0\u30df\u30fc\u30c6\u30fc\u30d6\u30eb\u3092\u6e96\u5099\u3057\u307e\u3059\u3002\uff08\u30ec\u30b3\u30fc\u30c9\u6570\u306f0\u3067\u554f\u984c\u3042\u308a\u307e\u305b\u3093\uff09
\n3. \u30c0\u30df\u30fc\u30c6\u30fc\u30d6\u30eb\u3092\u4f7f\u7528\u3057\u305f\u30ea\u30d5\u30ec\u30c3\u30b7\u30e5\u306e\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u5b9a\u7fa9\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002
\n4. \u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u5b9a\u7fa9\u306b\u5bfe\u3057\u3066\u30ea\u30d5\u30ec\u30c3\u30b7\u30e5\u3092\u5b9a\u671f\u9593\u9694\uff081\u5206\u304a\u304d\u7b49\uff09\u3067\u5b9f\u884c\u3059\u308b\u3088\u3046\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u8a2d\u5b9a\u3057\u307e\u3059\u3002
\n5. \u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u5b9a\u7fa9\u306b\u5bfe\u3057\u3066\u3001\u4e0b\u8a18\u304b\u3089\u30c0\u30a6\u30f3\u30ed\u30fc\u30c9\u53ef\u80fd\u306a\u30b9\u30af\u30ea\u30d7\u30c8\u3092\u9069\u7528\u3057\u307e\u3059\u3002<\/p>\n

\u30c0\u30df\u30fc\u30c6\u30fc\u30d6\u30eb\u3092\u4f7f\u7528\u3057\u3066\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u30ea\u30d5\u30ec\u30c3\u30b7\u30e5\u6bce\u306b\u30b9\u30af\u30ea\u30d7\u30c8\u3092\u5b9f\u884c\u3057\u3001\u30b9\u30af\u30ea\u30d7\u30c8\u304b\u3089\u9806\u5e8f\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u3059\u308b\u3001\u3068\u3044\u3046\u6d41\u308c\u306b\u306a\u308a\u307e\u3059\u3002
\n\u30b9\u30af\u30ea\u30d7\u30c8\u306f\u4e0b\u8a18\u3088\u308a\u30c0\u30a6\u30f3\u30ed\u30fc\u30c9\u3044\u305f\u3060\u3051\u307e\u3059\u3002<\/p>\n

\r\nImports System\r\nImports System.Data\r\nImports System.Collections\r\nImports Microsoft.VisualBasic\r\nImports DBMotoPublic\r\nImports DBMotoScript\r\nImports DBRS.GlobalScript\r\n\r\nNamespace DBRS\r\n\r\n      \r\n   Public Class SequenceName\r\n      Public owner       As String\r\n      Public name        As String\r\n      Public incrementBy As Integer\r\n      Public lastNumber  As Integer\r\n   End Class\r\n      \r\n\r\n   Public Class ReplicationScript : Inherits IReplicationScript\r\n\r\n      Dim sequenceArrayList As New ArrayList()      \r\n    \r\n      Public Overrides Sub Record_onBeforeMapping(recSource As IRecord, ByRef AbortRecord As Boolean)\r\n         ' Do this so that no record is written to the target dummy table\r\n         AbortRecord = True\r\n      End Sub      \r\n                 \r\n\r\n      Public Overrides Sub Refresh_onPrepareRefresh(ByRef CancelRefresh As Boolean, ByRef Filter As String)\r\n\r\n         ' Here, we are identifying and initializing the sequences we want to replicate\r\n  \r\n         AddLog("debug> Refresh_onPrepareRefresh()", 0) \r\n         sequenceArrayList.Clear()\r\n          \r\n         ' \u3053\u3053\u306b\u30b9\u30ad\u30fc\u30de\u540d\u3068\u30b7\u30fc\u30b1\u30f3\u30b9\u540d\u3092\u8a2d\u5b9a\u3057\u307e\u3059\u3002\r\n         Dim selectCondition = "SEQUENCE_OWNER = 'schemaname' AND SEQUENCE_NAME LIKE '%SEQ%'" \r\n\r\n         Dim conn as IDbConnection = Nothing\r\n         Dim cmd as IDbCommand = Nothing \r\n         Dim reader as IDataReader = Nothing \r\n         Dim opened As Boolean = False\r\n                   \r\n         Try \r\n            conn = InternalSourceConnection\r\n            If conn.State <> ConnectionState.Open Then\r\n               AddLog("debug>    open source connection because it hasn't been", 0) \r\n               conn.Open\r\n               opened = True\r\n            End If\r\n            cmd = conn.CreateCommand\r\n            cmd.CommandText = "SELECT SEQUENCE_OWNER, SEQUENCE_NAME, INCREMENT_BY, LAST_NUMBER FROM ALL_SEQUENCES WHERE " & selectCondition & " ORDER BY 1,2"\r\n            AddLog("debug> cmd.CommandText = " & cmd.CommandText, 0) \r\n            reader = cmd.ExecuteReader      \r\n            While reader.Read\r\n               Dim seq = new SequenceName\r\n               seq.owner = reader.GetString(0)\r\n               seq.name = reader.GetString(1)\r\n               seq.incrementBy = reader.GetDecimal(2)\r\n               seq.lastNumber = reader.GetDecimal(3)\r\n               sequenceArrayList.add(seq)\r\n               AddLog("debug> " & seq.owner & "." & seq.name & " incr:" & seq.incrementBy.ToString & " lastnum: " & seq.lastNumber , 0) \r\n            End While\r\n            If sequenceArrayList.Count = 0 Then\r\n               AddLog("Warning, no sequences will be replicated.",0) \r\n               AddLog("This statement returns no rows: [" + cmd.CommandText + "]",0) \r\n            End If\r\n         Catch ex As Exception\r\n            AddLog("Exception in Internal Target Connection: " & ex.ToString,0) \r\n         Finally \r\n            If (not reader is Nothing) Then \r\n                reader.Close \r\n            End If \r\n            If (not cmd is Nothing) Then \r\n                cmd.Dispose \r\n            End If \r\n            If opened = True Then\r\n               conn.Close\r\n            End If\r\n         End Try \r\n         \r\n         AddLog("debug> sequenceArrayList.Count = " & sequenceArrayList.Count.ToString, 0) \r\n         \r\n      End Sub\r\n\r\n       \r\n      Public Overrides Sub Refresh_onAfterRefresh()\r\n      \r\n         AddLog("debug> Refresh_onAfterRefresh()...", 0) \r\n        \r\n         Dim conn as IDbConnection = InternalTargetConnection  \r\n         Dim cmd as IDbCommand = conn.CreateCommand \r\n         Dim cmd2 as IDbCommand = conn.CreateCommand\r\n         Dim reader as IDataReader = Nothing \r\n         Dim opened As Boolean = False\r\n\r\n         Try \r\n\r\n            If conn.State <> ConnectionState.Open Then\r\n               AddLog("debug>    open target connection because it hasn't been", 0) \r\n               conn.Open\r\n               opened = True\r\n            End If\r\n\r\n            Dim seq As SequenceName\r\n            \r\n            For Each seq In sequenceArrayList\r\n               cmd.CommandText = "select LAST_NUMBER from ALL_SEQUENCES where SEQUENCE_OWNER='" & seq.owner & "' AND SEQUENCE_NAME='" & seq.name & "'" \r\n               AddLog("debug>    lastNumber = " & seq.lastNumber.ToString & ", target sql = " & cmd.CommandText, 0) \r\n               reader = cmd.ExecuteReader      \r\n               Dim nextval As Integer = -1\r\n               If reader.Read Then \r\n                  nextval = reader.GetDecimal(0) \r\n                  AddLog("debug>    target nextval = " & nextval.ToString, 0) \r\n                  reader.Close\r\n                  While (seq.incrementBy > 0 AND seq.lastNumber > nextval) OR (seq.incrementBy < 0 AND seq.lastNumber < nextval)\r\n                     AddLog("debug>       1) lastNumber " & seq.lastNumber.ToString & " > nextval " & nextval.ToString, 0) \r\n                     cmd2.CommandText = "select " & seq.owner & "." & seq.name & ".NEXTVAL from ALL_OBJECTS where ROWNUM <= ABS(" & ((seq.lastNumber-nextval)\/seq.incrementBy).ToString & ")"\r\n                     AddLog("debug>          cmd2.CommandText = " & cmd2.CommandText, 0) \r\n                     reader = cmd2.ExecuteReader      \r\n                     While reader.Read  \r\n                        'AddLog("debug>          2) lastNumber " & seq.lastNumber.ToString & " > nextval " & nextval.ToString, 0) \r\n                        nextval = reader.GetDecimal(0)+seq.incrementBy\r\n                     End While \r\n                     reader.Close\r\n                  End While                  \r\n               End If\r\n            Next\r\n                                    \r\n         Catch ex As Exception\r\n            AddLog("Exception in Internal Target Connection: " & ex.ToString,0) \r\n         Finally \r\n            if (not reader is Nothing) Then \r\n                reader.Close \r\n            End If \r\n            if (not cmd is Nothing) Then \r\n                cmd.Dispose \r\n            End If \r\n            If opened = True Then\r\n               conn.Close\r\n            End If\r\n         End Try \r\n            \r\n      End Sub\r\n\r\n   End Class\r\nEnd Namespace\r\n<\/pre>\n
\u25cf\u30b9\u30af\u30ea\u30d7\u30c8\u306e\u5185\u5bb9<\/strong><\/span><\/p>\n
1. \u30bd\u30fc\u30b9\u3068\u30bf\u30fc\u30b2\u30c3\u30c8\u5074\u306e\u30b7\u30fc\u30b1\u30f3\u30b9\u306e\u6700\u5f8c\u306e\u5024\u3092\u62bd\u51fa\u3057\u307e\u3059\u3002
\n2. \u4e21\u8005\u306e\u5024\u3092\u6bd4\u8f03\u3057\u3001\u3010\u30bd\u30fc\u30b9\uff1e\u30bf\u30fc\u30b2\u30c3\u30c8\u3011\u306e\u5834\u5408\u306b\u306f\u3010\u30bd\u30fc\u30b9\uff1d\u30bf\u30fc\u30b2\u30c3\u30c8\u3011\u306b\u306a\u308b\u307e\u3067\u3001\u30bf\u30fc\u30b2\u30c3\u30c8\u5074\u306e\u30b7\u30fc\u30b1\u30f3\u30b9\u306b\u5bfe\u3057\u3066NEXTVAL\u3092\u5b9f\u884c\u3057\u307e\u3059\u3002<\/p>\n
\u25cf\u30b9\u30af\u30ea\u30d7\u30c8\u9069\u7528\u624b\u9806<\/strong><\/span><\/p>\n
\u30e1\u30bf\u30c7\u30fc\u30bf\u306f\u4e0b\u8a18\u306e\u69cb\u6210\u3068\u306a\u3063\u3066\u3044\u307e\u3059\u3002
\n\u30fb\u30c6\u30fc\u30d6\u30eb\u300cEMPLOYEE\u300d\u304c\u9806\u5e8f\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u306e\u7591\u4f3c\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u7528\u30c0\u30df\u30fc\u30c6\u30fc\u30d6\u30eb\uff08\u30ec\u30b3\u30fc\u30c9\u306f\u306a\u3057\uff09
\n\u30fb\u30c6\u30fc\u30d6\u30eb\u300cTABLE1\u300d\u304c\u9806\u5e8f\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u4f7f\u7528\u3057\u3066\u3044\u308b\u5b9f\u30c6\u30fc\u30d6\u30eb
\n\u30fb\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u300cTABLE1\u300d\u304c\u3001\u5b9f\u30c6\u30fc\u30d6\u30ebTABLE1\u306e\u30df\u30e9\u30fc\u30ea\u30f3\u30b0\u69cb\u6210
\n\u30fb\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u300c\u9806\u5e8f\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u300d\u306f\u3001\u30c0\u30df\u30fc\u30c6\u30fc\u30d6\u30ebEMPLOYEE\u306e\u30ea\u30d5\u30ec\u30c3\u30b7\u30e5\u69cb\u6210
\n\u21d2\u3053\u308c\u306b\u5bfe\u3057\u3066\u30b9\u30af\u30ea\u30d7\u30c8\u3092\u9069\u7528\u3057\u307e\u3059\u3002
\n\"\"<\/a><\/p>\n
\u30ea\u30d5\u30ec\u30c3\u30b7\u30e5\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u30921\u5206\u9593\u9694\u3067\u5b9f\u884c\u3059\u308b\u3088\u3046\u8a2d\u5b9a\u3057\u307e\u3059\u3002
\n\"\"<\/a><\/p>\n
\u300c\u30b9\u30af\u30ea\u30d7\u30c8\u3092\u4f7f\u7528\u300d\u306b\u30c1\u30a7\u30c3\u30af\u3092\u5165\u308c\u3001\u30b9\u30af\u30ea\u30d7\u30c8\u753b\u9762\u3092\u958b\u304d\u307e\u3059\u3002
\n\"\"<\/a><\/p>\n
\u30b9\u30af\u30ea\u30d7\u30c8\u3092\u30b3\u30d4\u30fc\u3057\u3066\u9069\u7528\u3057\u307e\u3059\u3002
\n\"\"<\/p>\n","protected":false},"excerpt":{"rendered":"
DBMoto\u3092\u4f7f\u7528\u3057\u3066Oracle\u306e\u30c7\u30fc\u30bf\u3092\u5dee\u5206\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u3059\u308b\u5834\u5408\u3001Redo\u30ed\u30b0\u3092\u53c2\u7167\u3057\u3066\u76f4\u63a5\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u3092\u78ba\u8a8d\u3057\u307e\u3059\u3002 \u3057Oracle\u306e\u9806\u5e8f\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\uff08\u30b7\u30fc\u30b1\u30f3\u30b9\uff09\u306f\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u51e6\u7406\u3067\u306f\u306a\u304fRedo\u30ed\u30b0 … \u7d9a\u304d\u3092\u8aad\u3080 →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[34,178,179],"class_list":["post-1163","post","type-post","status-publish","format-standard","hentry","category-syniti","tag-oracle","tag-178","tag-179"],"_links":{"self":[{"href":"https:\/\/www.climb.co.jp\/blog_dbmoto\/wp-json\/wp\/v2\/posts\/1163","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.climb.co.jp\/blog_dbmoto\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.climb.co.jp\/blog_dbmoto\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.climb.co.jp\/blog_dbmoto\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.climb.co.jp\/blog_dbmoto\/wp-json\/wp\/v2\/comments?post=1163"}],"version-history":[{"count":11,"href":"https:\/\/www.climb.co.jp\/blog_dbmoto\/wp-json\/wp\/v2\/posts\/1163\/revisions"}],"predecessor-version":[{"id":2810,"href":"https:\/\/www.climb.co.jp\/blog_dbmoto\/wp-json\/wp\/v2\/posts\/1163\/revisions\/2810"}],"wp:attachment":[{"href":"https:\/\/www.climb.co.jp\/blog_dbmoto\/wp-json\/wp\/v2\/media?parent=1163"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.climb.co.jp\/blog_dbmoto\/wp-json\/wp\/v2\/categories?post=1163"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.climb.co.jp\/blog_dbmoto\/wp-json\/wp\/v2\/tags?post=1163"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}