How to migrate a large amount of data from SQL Server to Oracle

The following means that if txt text data is imported into Oracle.

Use SQl*Loader command to load in Dos environment.

Use data transmission tools of other databases.

Data loading function in Oracle enterprise manager

Specific technology implementation

I. loading in Dos environment

1. First, the server-side listening service must be started.

Test method: input under Dos.

c:/& gt； Sqlplus username/password @ service name

2. Then use Oracle's sqlldr command to import data.

prerequisite

1) The Oracle database side must have established the structure of the data table to be imported.

2) Data source file In the following example, it is a tab-delimited text file model.txt, which is exported from an Excel table.

3) Manually edit the control file of XXX. cytotoxic lymphocyte,ctl

4) Load data on the command line

Examples are as follows:

By default, the following files are located under C:/. If not, you need to specify the full path.

1. command controls the contents of the file input.ctl

order

explain

Load data

1, control file identification

infile 'model.txt '

2. The file name of the data to be input is test.txt

Append to the table system. Tower attribute table

3. Add records to the table test

Fields ending with X'09'

4. Specify the delimiter. The field ends with X'09' and is a tab.

(Quantity, name, size)

5. Define the order of columns in the corresponding table.

Keyword that specifies how to insert data in the control file.

The default mode Insert requires that the table be empty at the beginning of data loading.

Add, add a new record to the table.

Replace, delete the old record and replace it with the newly loaded record.

Truncation, same as above

Use SQl*Loader command to import data in Dos window.

c:/& gt； sqlldr userid = system/manager @ service name control = input . CTL

The default log file name is: input.log

The default error log file is: input.bad

Second, use other database transmission tools.

Take the SQL Server Import and Export Wizard as an example.

1. Set the data source server in the data import and export wizard, and select SQL Server as the data source in the instance.

2. Then specify the Oracle data source to import.

3. You need to configure the attribute information of Oracle.

It should be noted that the user information logged into the database is the schema name after data import, that is, the table in SQL Server is marked as username after import. Table name in Oracle.

Follow the tips below. You can import data tables and views completely from SQl Server, or you can use query statements to return the columns or rows you want to select.

Thirdly, the data loading function in Oracle Enterprise Manager.

You need to specify a control file in the middle, which is consistent with Dos loading and will not be repeated.

Control file format used when importing text data into Oracle.

No matter which way you used in the last article, you need to have a control file. The following is the basic format for writing control files (ctl files):

order

explain

Load data

1, control file identification

infile 'testl.txt '

2. At this point, the file name of the data to be input is test.txt, and the data file to be imported is located in the same path as the control file. If it is not in the same path, you need to write the full path name.

Table name (full name or synonym) appended to the table

3. Add records to the table test

Fields ending with X'09'

4. Specify the delimiter. The field ends with X'09' and is a tab. If separated by commas, replace X'09' with','.

(Quantity, name, size)

5. Define the order of columns in the corresponding table.

Keyword that specifies how to insert data in the control file.

The default mode Insert requires that the table be empty at the beginning of data loading.

Add, add a new record to the table.

Replace, delete the old record and replace it with the newly loaded record.

Truncation, same as above

Examples of control files:

Load data

infile 'test.txt '

Append to table test. Test

Fields ending with X'09'

(Test, test 1, test 2)

Sample control file for data import with time type:

Load data

infile ' h:/TB _ FACT _ PHS _ TICKET _ day . txt '

Append to table TB_FACT_PHS_TICKET_DAY.

Fields ending with X'09'

(Time stamp

, regional identification

, customer type identification

, product type identification

, Account _ Item _ Type _ ID

, account _ project _ type _ CD

，Exchange97_Cd

, Latin _Cd

, call duration

, access duration

, income

, date date "year-month-day"

)

File import command c:/> Sqlldruuserid = test/test @ testcontrol = test. CTL (at this time, the control file test.ctl exists in the C:/ path).

Enter Oracle C:/>sqlplus username/password @ service name.

Oracle imports txt data file Wednesday, July 30th, 2008 17:2 1 The way to import txt data file into Oracle is to use sqlloader tool.

Step 1: put the data file in text format into drive C.

Step 2: Create the control file append.ctl. (You can take any name and put it under drive C)

The contents of append.ctl are as follows:

Load data-1, control file identification

Infile' test.txt'-2。 The file name of the data to be input is test.txt

Append to table CTXSYS.test-3. Append records to table test in the ctxsys tablespace.

Fields ending in X'09'-4 4. The field ends at X'09' and is a tab.

(ID, username, password, SJ)- Defines the corresponding column order.

Where append is the data loading method, and there are other options:

The default mode insert requires that the table be empty at the beginning of data loading.

Append, add a new record to the table.

C. Replace: delete the old record and replace it with the newly loaded record.

D, truncation, same as above

Step 3: Enter the command at the command prompt.

c:/& gt； Sqlldr userid = username/password

User name and password in Control=c:/append.ctl database.

Or c:/> sqlldr userid = system/manager @ service name control = input . CTL

The user name of the system database in the second command.

Manager password

@serviceName is the service name of the local configuration file in Oracle.

Some frequently asked questions:

1。 Questions about the date format:

The basic writing of ctl is as follows:

Load data

infile ' C:/TP _ loancontractsum . txt '

Inserted into the table TP_LOANCONTRACTSUM.

With "|!" End field

(

Column 0 1,

Column 02,

Column 03,

column04 "to_date(:column04，' ' ' yyyy-mm-dd hh24:mi:ss ' ')"，

column05 "to_date(:column05，' ' ' yyyy-mm-dd hh24:mi:ss ' ')"，

Column 06,

Column 07,

Column 08,

Column 09,

Column 10,

Column 1 1,

column 12 " to _ date(:column 12，' ' ' yyyy-mm-dd hh24:mi:ss ' ')"，

Column 13

)

2。 Regarding the problem of long strings, ctl defaults to 256 bits (or about 256 bits), so when it is a long string, an error will be reported in the log, prompting that the value entered exceeds the maximum length. The solution is to specify the size in the CTL file and pay attention to the situation. You can't write VARCHAR, you can only write CHAR, otherwise you will report an error, such as:

Load data

infile ' C:/TP _ pledge contract info . txt '

Insert into table TP _ pledge contract information

With "|!" End field

(

Column 0 1,

Column 02,

Column 03,

Column 04,

Column 05,

Column 06,

column07 "to_date(:column07，' ' ' yyyy-mm-dd hh24:mi:ss ' ')"，