Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to transplant SQLServer2005 to Oracle10g". In daily operation, I believe many people have doubts about how to transplant SQLServer2005 to Oracle10g. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to transplant SQLServer2005 to Oracle10g". Next, please follow the editor to study!

As OMWB only supports SQLServer2000, according to the official website, the next version of OMWB will launch support for SQLServer 2005, so in the current situation, we can only migrate the library from SQLServer 2005 to SQLServer2000, which is the first step in our migration process.

How to migrate SQLServer2005 to Oracle10g

1. SQLServer 2005-> SQLServer 2000

It has always been very difficult for the version to be downgraded, because there are bound to be some new features in the new version, and if you happen to use these features, you will encounter some problems when downgrading to a lower version. After several attempts, to sum up, this process is relatively easy to do, after all, it is the same database, no matter how big problems will not be encountered. But it's not as simple as upgrading the library from SQLServer 2000 to SQLServer 2005, and the whole migration process goes like this:

1. Data export based on SQLServer 2005 imports table structure and data into SQLServer 2000

What should be noted in this step is that SQLServer imports tables and views together by default. Do not select views here, otherwise some views will become tables after import into SQLServer 2000. Basically, there will be no problems in this step after selecting the tables to be imported, and you can complete the migration of table structure and data.

2. Generate scripts based on SQLServer 2005 to migrate views / functions / stored procedures to SQLServer 2000

This step needs to be taken slowly, because in the view / function / stored procedure you may have used some new features of SQLServer 2005, if you encounter such a situation, it can only be manually modified to make it fully in line with the requirements of SQLServer 2000, although you can choose to generate the target version of the script for SQLServer 2000, there will still be some script execution will go wrong.

After the migration of SQLServer 2005 to SQLServer 2000 is completed, the library can be migrated from SQLServer 2000 to Oracle based on OMWB. Although there are tools, this step will be troublesome, as summarized as follows:

2. SQLServer 2000-> Oracle 10g

The procedure for migrating libraries from SQLServer 2000 to Oracle 10g based on OMWB can be found in this document:

If you get off oracle now, you may not be able to find the plug-in package for sqlserver 2000. If you can't find it, you can download it here:

What I want to summarize here is some things that need to be done manually after OMWB migrates the library from SQLServer 2000 to Oracle 10g. Don't expect OMWB to help you migrate the library from SQLServer to Oracle seamlessly. Silver bullet does not exist, so we need to do some manual work to complete the migration of the library:

How to migrate SQLServer2005 to Oracle10g

1. Problems that may arise in transplanting table structure and data

The default values / primary keys / foreign keys / indexes of the fields in the table cannot be migrated, which need to be supplemented manually.

2. Problems that may arise in migrating views

Transplanting past views may lead to a variety of syntax errors, which require manual correction, which are generally relatively simple errors

Another problem is that some views may not be portable in the past, so these views can only be found and migrated manually after comparing the migration report of OMWB.

3. Problems that may occur in porting functions / stored procedures

There may still be a lot of syntax problems in transplanting past functions / stored procedures, such as functions such as SCOPE_IDENTITY (), REPLICATE and newid () that OMWB does not know how to handle, and functions such as returning Table type, which can only be corrected manually after transplantation. About syntax errors caused by different functions, you can refer to this document to compare SQLServer and Oracle functions:

There may be no problem with the compilation of functions / stored procedures transplanted in the past, that is, Oracle thinks there is no syntax problem, but errors will be reported in execution, such as string addition. After OMWB migration, some string additions will be replaced with |, but some will be omitted. At this time, we can only correct these errors manually.

In the process of transplanting past functions / stored procedures, the primary key value of some tables may not be empty. Most of the reasons for this phenomenon are that the default value of this field is defined as IDENTITY in SQLServer, but it cannot be given such a default value in Oracle. You can only add the assignment to the primary key field in the inserted sql statement, and you can use sequence to generate the sequence number.

If the query statement in the past function / stored procedure is in the form of a string and then executed dynamically, the query statement at this time will have to be manually modified to conform to the syntax of oracle, because OMWB will not handle the query statement in the form of string when transplanting.

Some functions / stored procedures can not be migrated to oracle because OMWB really cannot be handled. At this time, we must refer to the migration report of OMWB to find out these functions / stored procedures and transplant them manually.

The whole migration process may encounter more problems than those listed above, which shows that the whole migration process does require a lot of work, but overall, it is not difficult to complete.

Is that really the case? Of course not, even if you have completed the above migration, it can only be said that on the surface, the migration is completed, it is very likely that there will be no problem with the stored procedure syntax, etc., but the effect of execution is different from that of SQLServer. Why? It may be because Oracle and SQLServer are different in concurrency control and transaction mechanism, which will affect the writing of sql and stored procedures when the program is called. That is to say, after the above migration process is completed, it is necessary to carefully check whether the current sql statements / functions / stored procedures have achieved the desired results in SQLServer according to the mechanism of Oracle. Before we can say that the transplant process is complete.

At this point, the study on "how to transplant SQLServer2005 to Oracle10g" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.