Database migration, from PostgreSQL to Oracle9i

The projects at hand need to be migrated to Oracle9i. PostgreSQL was used in the past and the migration was successful in a few days. Record the migration process:

1. First, you must know the differences between Oracle and PostgreSQL:

PostgreSQL --> ORACLE:

----------------------------

Text --> clob

Bigint --> numvber (38)

Boolean --> char (1), represented by T, F, or number (1), expressed by 0 and 1. Here I use char (1)

2. The following are precautions for table creation:

2.1 In Oracle, number, commnet, and so on are keywords. Note that double quotation marks must be added to the SQL statement of the table to be created.

2.2 There is no only option for alter table.
2.3. No cascade update: On update cascade.

2.4 The Name Of The constraint cannot be the same.

2.5 if you need an alias, you can use as after the field, but note that the table name is not followed by!

3. One requirement for this database migration is that, without modifying the original program, you can only change the configuration file and database structure to complete the migration. The main architecture of this system is J2EE + SSH + Tomcat + PostgreSQL. Many of the entity classes mapped by the prototype through hibernte are Java's Boolean types, while Oracle does not have the boolean type. Therefore, Hibernate needs to be used to map the boolean type.

First, export the table creation statement from PostgreSQL, and then modify the statements according to the Notes mentioned above to create a table in Oracle.

4. Use hibernate to map the Boolean Type:

4.1 In the ing file of the object class, change the Boolean or Java. Lang. boolean type to true_false, for example:

    <property        name="isDefault"        type="true_false"        column="is_default"        not-null="true"         length="1"    >        <meta attribute="use-in-tostring">true</meta>        <meta attribute="use-in-equals">true</meta>        <meta attribute="field-description">           @hibernate.property            column="is_default"            length="1"            type="true_false"        </meta>        </property>

4.2 configure the hibernate. query. Substitutions option in the property configuration file of hibernate: for example:

<Property name = "hibernateproperties"> <props> <! -- <Prop key = "hibernate. dialect "> Org. hibernate. dialect. postgresqldialect </prop> --> <prop key = "hibernate. dialect ">$ {hibernate. connection. dialect} </prop> <prop key = "hibernate. show_ SQL ">$ {hibernate. connection. show_ SQL} </prop> <prop key = "hibernate. format_ SQL "> true </prop> <! -- For Oracle compatibility, Hibernate automatically converts t/F and true/false. The database field is of the char (1) type, use Type = "true_false" --> <prop key = "hibernate. query. substitutions "> true 'T', false 'F' </prop> <! -- If the database uses MS sqlserver, this property is set to false, otherwise an exception is thrown during system startup: Unsupported method: resultset. if absolute supports scrollable result, use the absolute method of resultset to directly move to the query start point without additional performance overhead. If not, use the loop statement, Rs. next. --> <Prop key = "hibernate. JDBC. use_scrollable_resultset"> true </prop> </props> </property>

In this way, we almost migrated the table structure. As for the imported data, we will not talk about it here. The main note is the order in which data tables with a master-slave relationship are imported.