Shulou(Shulou.com)06/01 Report--

Original: https://www.enmotech.com/web/detail/1/788/1.html

Introduction: this article comes from the contribution of the reader "Little Panther refueling (screen name)". It mainly talks about the process of dealing with IMP-00009 problems when using imp to import files.

I. Overview

Recently, on an Oracle database in the test environment, there was no problem using exp to export the table, but the following error occurred when importing the exported file using imp.

IMP-00009: abnormal end of export file

Import terminated successfully with warnings.

After repeated experiments, we finally found out the cause of the problem, which is caused by the following points:

a. The parameter deferred_segment_creation in the database is set to the default value true, that is, segments are not allocated immediately when the table is created, and segments are not allocated until there are rows.

b. There is a partition table in the exported table, and it just so happens that the partition table has no rows, that is, some partitions have no assigned segments.

c. Direct=true is used when exporting.

Look at the solution directly (III. Solution)

Second, the recurrence of problems

1. Preparatory work

Prepare two users in the test library, tom (the exported user) and jerry (the imported user), and give them the maximum permissions.

SQL > create user tom identified by tom

SQL > grant dba to tom

SQL > create user jerry identified by jerry

SQL > grant dba to jerry

two。 Check the parameter deferred_segment_creation in the database

SQL > show parameter deferred

NAME TYPE VALUE

-

Deferred_segment_creation boolean TRUE

You can see that this parameter is true, the default value.

3. Create a test table

SQL > create table tom.t_normal as select * from scott.emp; / / create a regular table with rows

SQL > create table tom.t_norows as select * from scott.emp where 1: 0; / / create an empty table

SQL > create table tom.t_par (id number, name varchar2 (10))

Partition by list (id)

(partition P01 values (1)

Partition P02 values (default); / / create a partition table with two partitions

SQL > insert into tom.t_par values (1, 'aa'); / / insert a piece of data into partition p01

SQL > commit

At this point, there are three tables under tom users. T_normal is a regular table, t_norows is an ordinary empty table, and t_par is a partition table.

Through the sql below, it is found that there are only two segment under the tom user, and neither the empty table nor the partition with no data in the partition table creates a segment.

SQL > select owner,segment_name,partition_name,segment_type from dba_segments where owner='TOM'

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE

-

TOM T_NORMAL TABLE

TOM T_PAR P01 TABLE PARTITION

4. Export tables using tom

Exp tom/tom file=tom.dmp log=tom_exp.log direct=true

Export the log as follows (omit some irrelevant contents):

. . Exporting table T_NORMAL 14 rows exported

. . Exporting table T_NOROWS 0 rows exported

. . Exporting table T_PAR

. . Exporting partition P01 1 rows exported

. . Exporting partition P02 0 rows exported

Export terminated successfully without warnings.

5. Import files using jerry

Mp jerry/jerry file=tom.dmp log=jerry_imp.log fromuser=tom touser=jerry

Import the log as follows (omit some irrelevant contents):

. . Importing table "T_NORMAL" 14 rows imported

. . Importing table "T_NOROWS" 0 rows imported

. . Importing partition "T_PAR": "P01" 1 rows imported

. . Importing partition "T_PAR": "P02"

IMP-00009: abnormal end of export file

Import terminated successfully with warnings.

The mistakes made in production are repeated here. And it is the error of importing "T_PAR": "P02", which confirms the previous point of view.

III. Solutions

There are two solutions (either):

a. Do not add direct=true when using exp export

b. Set the database parameter deferred_segment_creation to false (note: this parameter only affects the newly created partition table, and the old partition table will still report an error when exporting and then importing!)

Readers who have time and interest can continue to do the experiment.

You may ask, how do you know it's the partition table problem, how do you know it's the direct=true problem, and how do you know it's the parameter deferred_segment_creation? Next, I'll verify them one by one.

1. Clear the jerry table, export the following table for tom users, import it into jerry users

SQL > drop user jerry cascade; / / clear the jerry table by rebuilding the jerry user

SQL > create user jerry identified by jerry

SQL > grant dba to jerry

Exp tom/tom file=tom.dmp log=tom_exp.log direct=true tables=t_normal,t_norows

Export logs:

. . Exporting table T_NORMAL 14 rows exported

. . Exporting table T_NOROWS 0 rows exported

Export terminated successfully without warnings.

Imp jerry/jerry file=tom.dmp log=jerry_imp.log fromuser=tom touser=jerry

Import Log:

. . Importing table "T_NORMAL" 14 rows imported

. . Importing table "T_NOROWS" 0 rows imported

Import terminated successfully without warnings.

You can see that there is no problem with importing these two tables.

two。 Clear the jerry table, export the following table t_par for tom users, and then import it into jerry users

For the operation of emptying the jerry table, please see the steps above.

Exp tom/tom file=tom.dmp log=tom_exp.log direct=true tables=t_par

Export logs:

. . Exporting table T_PAR

. . Exporting partition P01 1 rows exported

. . Exporting partition P02 0 rows exported

Export terminated successfully without warnings.

Imp jerry/jerry file=tom.dmp log=jerry_imp.log fromuser=tom touser=jerry

Import Log:

. Importing TOM's objects into JERRY

. . Importing partition "T_PAR": "P01" 1 rows imported

. . Importing partition "T_PAR": "P02"

IMP-00009: abnormal end of export file

Import terminated successfully with warnings.

You can see that the problem lies in the import of this partition table.

3. Clear the jerry table, re-export the tom user table t_par, and then import it into the jerry user (this export does not add the parameter direct=true)

For the operation of emptying the jerry table, please see the steps above.

Exp tom/tom file=tom.dmp log=tom_exp.log tables=t_par

Export logs:

. . Exporting table T_PAR

. . Exporting partition P01 1 rows exported

. . Exporting partition P02 0 rows exported

Export terminated successfully without warnings.

Imp jerry/jerry file=tom.dmp log=jerry_imp.log fromuser=tom touser=jerry

Import Log:

. Importing TOM's objects into JERRY

. . Importing partition "T_PAR": "P01" 1 rows imported

. . Importing partition "T_PAR": "P02" 0 rows imported

Import terminated successfully without warnings.

You can see that there is no problem with this import, that is to say, the problem can be solved directly without adding direct=true, but what if I have to add this parameter, or if this command is written to the program, there is no way to change it? See article 6 below for treatment.

4. Empty the jerry table, insert a piece of data in the p02 partition of tom.t_par, re-export the tom user table t_par, and then import it into the jerry user (this export still adds the parameter direct=true)

For the operation of emptying the jerry table, please see the steps above.

SQL > insert into tom.t_par values (2, 'bb'); / / insert a piece of data into partition p02

SQL > commit

Check the following sql to find that both t_par partitions have segments.

SQL > select owner,segment_name,partition_name,segment_type from dba_segments where owner='TOM'

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE

-

TOM T_PAR P01 TABLE PARTITION

TOM T_PAR P02 TABLE PARTITION

TOM T_NORMAL TABLE

Exp tom/tom file=tom.dmp log=tom_exp.log direct=true tables=t_par

Export logs:

. . Exporting table T_PAR

. . Exporting partition P01 1 rows exported

. . Exporting partition P02 1 rows exported

Export terminated successfully without warnings.

Imp jerry/jerry file=tom.dmp log=jerry_imp.log fromuser=tom touser=jerry

Import Log:

. Importing TOM's objects into JERRY

. . Importing partition "T_PAR": "P01" 1 rows imported

. . Importing partition "T_PAR": "P02" 1 rows imported

Import terminated successfully without warnings.

You can see that if all partitions in the partition table have data, there will be no problem with import.

5. Verify the influence of deferred_segment_creation parameters on it

For the operation of emptying the jerry table, please see the steps above.

Modify the parameter deferred_segment_creation in the database to false

SQL > alter system set deferred_segment_creation=false

Rebuild the tom user's t _ par table so that one partition has data and the other has no data.

SQL > drop table tom.t_par

SQL > create table tom.t_par (id number, name varchar2 (10))

Partition by list (id)

(partition P01 values (1)

Partition P02 values (default); / / create a partition table with two partitions

SQL > insert into tom.t_par values (1, 'aa'); / / insert a piece of data into partition p01

SQL > commit

Check the following sql to find that both t_par partitions have segments, even though there is no data in P02 partition

SQL > select owner,segment_name,partition_name,segment_type from dba_segments where owner='TOM'

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE

-

TOM T_PAR P01 TABLE PARTITION

TOM T_PAR P02 TABLE PARTITION

TOM T_NORMAL TABLE

Exp tom/tom file=tom.dmp log=tom_exp.log direct=true tables=t_par

Export log:. . Exporting table T_PAR

. . Exporting partition P01 1 rows exported

. . Exporting partition P02 0 rows exported

Export terminated successfully without warnings.

Imp jerry/jerry file=tom.dmp log=jerry_imp.log fromuser=tom touser=jerry

Import Log:

. Importing TOM's objects into JERRY

. . Importing partition "T_PAR": "P01" 1 rows imported

. . Importing partition "T_PAR": "P02" 0 rows imported

Import terminated successfully without warnings.

You can see that it is normal to change the parameter deferred_segment_creation to false import, but this only applies to the newly created partition table, and the import will still fail for existing partition tables. See article 6 below for treatment.

6. Next, answer the questions in steps 3 and 5 above.

If you want to use exp,imp for export and import, you must add direct=true to the export, and the exported table contains a partition table, and there is a situation in which the partition does not have segments. What can we do?

Simply changing the parameter deferred_segment_creation to false is not enough, because this only affects the new table, and if you want to take effect on the old table, you can take the following approach.

6.1. Change the parameter deferred_segment_creation to false

SQL > alter system set deferred_segment_creation=false

6.2. Use exp to export partition tables (only those with partitions without assigned segments in the partition table need to be exported), and be careful not to add direct=true

Exp tom/tom file=tom.dmp log=tom_exp.log tables=t_par

6.3. Delete the partition table

SQL > drop table tom.t_par

6.4. Import it using imp

Imp tom/tom file=tom.dmp log=tom_imp.log full=y

Import Lo

. Importing TOM's objects into TOM

. . Importing partition "T_PAR": "P01" 1 rows imported

. . Importing partition "T_PAR": "P02" 0 rows imported

Import terminated successfully without warnings.

Check the status of the segment after import

SQL > select owner,segment_name,partition_name,segment_type from dba_segments where owner='TOM'

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE

-

TOM T_PAR P02 TABLE PARTITION

TOM T_PAR P01 TABLE PARTITION

You can see that segments are now assigned even if there are no rows in the P02 partition. This is because I have previously set the parameter deferred_segment_creation to false and deleted the table. During execution, imp will create the table first and then insert the data, and each partition will be assigned a segment when the table is created. In other words, you only need to solve the situation where there is no allocation in the middle of the old partition table, and you will not encounter IMP-00009 later.

V. Summary

It is only when there is a partition in the partition table that there is no segment assigned and the direct=true parameter is used in the export that these two situations together will cause the error of my IMP-00009. For other ordinary tables, using direct=true will not cause this error, regardless of whether there are assigned segments or not.

When I analyze the problem of IMP-00009, first of all, I write "IMP-00009: abnormal end of export file" with only one line of log record, and check the exported log, "Export terminated successfully without warnings." as soon as possible, and the exported log does not show any exceptions. This is confusing me. Then I began to turn to the omnipotent Internet. I looked around and didn't find any solution. in fact, it was not that Daniel did not answer, but that the person who asked the question provided too little information, so you provided an error log, such as the problem I encountered this time. Suppose you only give an error log and Daniel can't reproduce the problem, then you can't solve the problem. And often when we describe the whole problem clearly, the problem will be easily solved.

Remarks on the relevant information involved in the article:

Deferred_segment_creation: delay segment creation, which has been clearly described through the experiment.

Direct=true: export data without going through buffer cache. This parameter is a sinkhole, and there are a lot of bug. Try not to use it when exporting.

Source: https://www.cnblogs.com/ddzj01/

Want to know more about databases and cloud technologies?

Come and follow the official account of "data and Cloud" and the official website of "Yunhe Enmo". We look forward to learning and making progress with you!

(scan the QR code above and follow the official account of "data and Cloud" for more science and technology 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.