转换 Oracle 架构 (OracleToSQL)Converting Oracle Schemas (OracleToSQL)
01/19/2017
本文内容
连接到 Oracle、连接到 SQL ServerSQL Server 并设置项目和数据映射选项后，可以将 Oracle 数据库对象转换为 SQL ServerSQL Server 数据库对象。After you have connected to Oracle, connected to SQL ServerSQL Server, and set project and data mapping options, you can convert Oracle database objects to SQL ServerSQL Server database objects.
转换过程The Conversion Process
转换数据库对象将获取 Oracle 中的对象定义，将它们转换为类似 SQL ServerSQL Server 对象，然后将此信息加载到 SSMA 元数据。Converting database objects takes the object definitions from Oracle, converts them to similar SQL ServerSQL Server objects, and then loads this information into the SSMA metadata. 它不会将信息加载到的实例中 SQL ServerSQL Server 。It does not load the information into the instance of SQL ServerSQL Server. 然后，您可以使用 " SQL ServerSQL Server 元数据资源管理器" 查看对象及其属性。You can then view the objects and their properties by using the SQL ServerSQL Server Metadata Explorer.
在转换过程中，SSMA 会将输出消息打印到 "输出" 窗格，并将错误消息打印到 "错误列表" 窗格。During the conversion, SSMA prints output messages to the Output pane and error messages to the Error List pane. 使用输出和错误信息来确定是否必须修改 Oracle 数据库或转换过程以获取所需的转换结果。Use the output and error information to determine whether you have to modify your Oracle databases or your conversion process to obtain the desired conversion results.
设置转换选项Setting Conversion Options
转换对象之前，请在 " 项目设置 " 对话框中查看项目转换选项。Before converting objects, review the project conversion options in the Project Settings dialog box. 使用此对话框，可以设置 SSMA 如何转换函数和全局变量。By using this dialog box, you can set how SSMA converts functions and global variables.
转换结果Conversion Results
下表显示了转换哪些 Oracle 对象以及生成的 SQL ServerSQL Server 对象：The following table shows which Oracle objects are converted, and the resulting SQL ServerSQL Server objects:
Oracle 对象Oracle Objects
生成的 SQL Server 对象Resulting SQL Server Objects
函数Functions
如果函数可以直接转换为，则 Transact-SQLTransact-SQL SSMA 将创建一个函数。If the function can be directly converted to Transact-SQLTransact-SQL, SSMA creates a function.
在某些情况下，该函数必须转换为存储过程。In some cases, the function must be converted to a stored procedure. 在这种情况下，SSMA 创建存储过程和调用存储过程的函数。In this case, SSMA creates a stored procedure and a function that calls the stored procedure.
过程Procedures
如果可以将该过程直接转换为 Transact-SQLTransact-SQL ，则 SSMA 将创建一个存储过程。If the procedure can be directly converted to Transact-SQLTransact-SQL, SSMA creates a stored procedure.
在某些情况下，必须在自治事务中调用存储过程。In some cases a stored procedure must be called in an autonomous transaction. 在这种情况下，SSMA 创建两个存储过程：一个用于实现过程，另一个用于调用实现存储过程。In this case, SSMA creates two stored procedures: one that implements the procedure, and another that is used for calling the implementing stored procedure.
包Packages
SSMA 创建一组由类似对象名称统一的存储过程和函数。SSMA creates a set of stored procedures and functions that are unified by similar object names.
序列Sequences
SSMA SQL Server 2012 或 SQL Server 2014) 或模拟 Oracle 序列 (创建序列对象。SSMA creates sequence objects (SQL Server 2012 or SQL Server 2014) or emulates Oracle sequences.
具有依赖对象(如索引和触发器)的表Tables with dependent objects such as indexes and triggers
SSMA 创建具有依赖对象的表。SSMA creates tables with dependent objects.
具有依赖对象(如触发器)的视图View with dependent objects, such as triggers
SSMA 创建具有依赖对象的视图。SSMA creates views with dependent objects.
具体化视图Materialized Views
SSMA 在 SQL server 上创建索引视图，但有一些例外情况。如果具体化视图包含一个或多个以下构造，转换将失败：SSMA creates indexed views on SQL server with some exceptions. Conversion will fail if the materialized view includes one or more of the following constructs:
用户定义函数User-defined function
SELECT、WHERE 或 GROUP BY 子句中的非确定性字段/函数/表达式Non deterministic field / function / expression in SELECT, WHERE or GROUP BY clauses
在 SELECT *、WHERE 或 GROUP BY 子句中使用 Float 列 (上一问题的特殊情况)Usage of Float column in SELECT*, WHERE or GROUP BY clauses (special case of previous issue)
自定义数据类型 (包括嵌套表)Custom data type (incl. nested tables)
计数 (非重复 < 字段 >)COUNT(distinct )
FETCHFETCH
OUTER 联接(LEFT、RIGHT 或 FULL)OUTER joins (LEFT, RIGHT, or FULL)
子查询，其他视图Subquery, other view
过度、排名、潜在顾客、日志OVER, RANK, LEAD, LOG
MIN、MAXMIN, MAX
UNION、减法、INTERSECTUNION, MINUS, INTERSECT
HAVINGHAVING
触发器Trigger
SSMA 基于以下规则创建触发器：SSMA creates triggers based on the following rules:
在触发器转换为 INSTEAD of 触发器之前。BEFORE triggers are converted to INSTEAD OF triggers.
AFTER 触发器转换为 AFTER 触发器。AFTER triggers are converted to AFTER triggers.
INSTEAD of 触发器会转换为 INSTEAD of 触发器。INSTEAD OF triggers are converted to INSTEAD OF triggers. 在同一操作中定义的多个 INSTEAD of 触发器合并为一个触发器。Multiple INSTEAD OF triggers defined on the same operation are combined into one trigger.
使用游标模拟行级触发器。Row-level triggers are emulated using cursors.
级联触发器将转换为多个单独的触发器。Cascading triggers are converted into multiple individual triggers.
同义词Synonyms
为以下对象类型创建同义词：Synonyms are created for the following object types:
表和对象表Tables and object tables
视图和对象视图Views and object views
存储过程Stored procedures
函数Functions
以下对象的同义词由直接对象引用解析和替换：Synonyms for the following objects are resolved and replaced by direct object references:
序列Sequences
包Packages
Java 类架构对象Java class schema objects
用户定义的对象类型User-defined object types
不能迁移其他同义词的同义词，并将其标记为错误。Synonyms for another synonym cannot be migrated and will be marked as errors.
不会为具体化视图创建同义词。Synonyms are not created for Materialized views.
用户定义的类型User Defined Types
SSMA 不支持转换用户定义的类型。用户定义的类型(包括其在 PL/SQL 程序中的用法)标记有以下规则所述的特殊转换错误：SSMA does not provide support for conversion of user defined types. User Defined Types, including its usage in PL/SQL programs are marked with special conversion errors guided by the following rules:
用户定义类型的表列转换为 VARCHAR (8000) 。Table column of a user defined type is converted to VARCHAR(8000).
存储过程或函数的用户定义类型的参数转换为 VARCHAR (8000) 。Argument of user defined type to a stored procedure or function is converted to VARCHAR(8000).
PL/SQL 块中用户定义类型的变量转换为 VARCHAR (8000) 。Variable of user defined type in PL/SQL block is converted to VARCHAR(8000).
对象表转换为标准表。Object Table is converted to a Standard table.
对象视图转换为标准视图。Object view is converted to a Standard view.
转换 Oracle Database 对象Converting Oracle Database Objects
若要转换 Oracle 数据库对象，请首先选择要转换的对象，然后让 SSMA 执行转换。To convert Oracle database objects, you first select the objects that you want to convert, and then have SSMA perform the conversion. 若要在转换过程中查看输出消息，请在 " 视图 " 菜单上选择 " 输出"。To view output messages during the conversion, on the View menu, select Output.
将 Oracle 对象转换为 SQL Server 语法To convert Oracle objects to SQL Server syntax
在 Oracle 元数据资源管理器中，展开 Oracle 服务器，然后展开 " 架构"。In Oracle Metadata Explorer, expand the Oracle server, and then expand Schemas.
选择要转换的对象：Select objects to convert:
若要转换所有架构，请选中 " 架构" 旁边的复选框。To convert all schemas, select the check box next to Schemas.
若要转换或省略数据库，请选中该架构名称旁边的复选框。To convert or omit a database, select the check box next to the schema name.
若要转换或省略对象的类别，请展开一个架构，然后选中或清除该类别旁边的复选框。To convert or omit a category of objects, expand a schema, and then select or clear the check box next to the category.
若要转换或省略单个对象，请展开 category 文件夹，然后选中或清除该对象旁边的复选框。To convert or omit individual objects, expand the category folder, and then select or clear the check box next to the object.
若要转换所有选定的对象，请右键单击 " 架构 "，然后选择 " 转换架构"。To convert all selected objects, right-click Schemas and select Convert Schema.
您还可以通过右键单击对象或其父文件夹，然后选择 " 转换架构"，来转换各个对象或对象类别。You can also convert individual objects or categories of objects by right-clicking the object or its parent folder, and then selecting Convert Schema.
查看转换问题Viewing Conversion Problems
某些 Oracle 对象可能不会转换。Some Oracle objects might not be converted. 您可以通过查看摘要转换报告来确定转换成功率。You can determine the conversion success rates by viewing the summary conversion report.
查看摘要报表To view a summary report
在 Oracle 元数据资源管理器中，选择 " 架构"。In Oracle Metadata Explorer, select Schemas.
在右侧窗格中，选择 " 报表 " 选项卡。In the right pane, select the Report tab.
此报表显示已评估或转换的所有数据库对象的摘要评估报告。This report shows the summary assessment report for all database objects that have been assessed or converted. 您还可以查看单个对象的摘要报表：You can also view a summary report for individual objects:
若要查看单个架构的报表，请在 Oracle 元数据资源管理器中选择该架构。To view the report for an individual schema, select the schema in Oracle Metadata Explorer.
若要查看单个对象的报表，请在 "Oracle 元数据资源管理器" 中选择该对象。To view the report for an individual object, select the object in Oracle Metadata Explorer. 具有转换问题的对象具有红色错误图标。Objects that have conversion problems have a red error icon.
对于失败转换的对象，可以查看导致转换失败的语法。For objects that failed conversion, you can view the syntax that resulted in the conversion failure.
查看各个转换问题To view individual conversion problems
在 Oracle 元数据资源管理器中，展开 " 架构"。In Oracle Metadata Explorer, expand Schemas.
展开显示红色错误图标的架构。Expand the schema that shows a red error icon.
在该架构下，展开一个包含红色错误图标的文件夹。Under the schema, expand a folder that has a red error icon.
选择包含红色错误图标的对象。Select the object that has a red error icon.
在右侧窗格中，单击 " 报表 " 选项卡。In the right pane, click the Report tab.
在 " 报表 " 选项卡的顶部是一个下拉列表。At the top of the Report tab is a drop-down list. 如果列表显示 " 统计信息"，请将所选内容更改为 " 源"。If the list shows Statistics, change the selection to Source.
SSMA 将显示源代码，并将多个按钮直接显示在代码上方。SSMA will display the source code and several buttons immediately above the code.
单击 " 下一问题 " 按钮。Click the Next Problem button. 这是一个红色的错误图标，其中有一个指向右侧的箭头。This is a red error icon with an arrow that points to the right.
SSMA 将突出显示在当前对象中找到的第一个有问题的源代码。SSMA will highlight the first problematic source code it finds in the current object.
对于无法转换的每个项，必须确定要对该对象执行哪些操作：For each item that could not be converted, you have to determine what you want to do with that object:
您可以在 " SQL " 选项卡上修改过程的源代码。You can modify the source code for procedures on the SQL tab.
您可以修改 Oracle 数据库中的对象，以删除或修改有问题的代码。You can modify the object in the Oracle database to remove or revise problematic code. 若要将更新的代码加载到 SSMA 中，必须更新元数据。To load the updated code into SSMA, you will have to update the metadata.
可以从迁移中排除对象。You can exclude the object from migration. 在 SQL ServerSQL Server "元数据资源管理器" 和 "Oracle 元数据资源管理器" 中，清除项旁的复选框，然后将对象加载到 SQL ServerSQL Server Oracle 并从 Oracle 迁移数据。In SQL ServerSQL Server Metadata Explorer and Oracle Metadata Explorer, clear the check box next to the item before loading the objects into SQL ServerSQL Server and migrating data from Oracle.
下一步Next Step
另请参阅See Also