Oracle Import issues: Resumable and buffer

Posted by

0

Yesterday and today I noticed two things with Oracle import (in Oracle 10.2.0.4) I did not know:

1. An issue with the buffer parameter in Import
2. An issue with Resumable=Y and FROMUSER/TOUSER where the FROMUSER and TOUSER have different default tablespace and quotas

Buffer

  • I did a DIRECT export yesterday of a table that contains a LONG column
  • I did an import (which is conventional) of that table with BUFFER=<some value>
  • I got embarrassed because my laptop started beeping around and my screen started screaming weird characters

I went through that embarrassment a few times, because I tried twice with that same import file (only the foolish try the same thing twice and expect a different result, yes I am guilty ;-)) and once with another import file of which I knew it contained that same table and I imported successfully in an 11g database earlier. (I almost thought I had a corrupt importfile and lost the contents of that table). But when that same import also initiated the terrible beeping (I have to turn of system beeps) and everybody in the large room was staring at me, I knew it had to be something with my import parameters. It turned out to be the buffer parameter. Even though that still surprises me. Oracle documentation says about buffer in IMPORT:

Default: operating system-dependent

The integer specified for BUFFER is the size, in bytes, of the buffer through which data rows are transferred.BUFFER determines the number of rows in the array inserted by Import.

The following formula gives an approximation of the buffer size that inserts a given array of rows: buffer_size = rows_in_array * maximum_row_size

For tables containing LOBs or LONG, BFILE, REF, ROWID, UROWID, or DATE columns, rows are inserted individually. The size of the buffer must be large enough to contain the entire row, except for LOB and LONG columns. If the buffer cannot hold the longest row in a table, Import attempts to allocate a larger buffer.

So I would have expected that Oracle would adjust the size of the buffer automatically when it turns out not to be big enough…

Resumable

I did an import for an entire schema into another schema (VIND into VINS). User VIND’s tables are in tablespace VIN_D. User VINS’s tables need to be created in tablespace VINS_D. USER VINS has no quota on the tablespaces of user VIND. So the meaning is that IMPORT will create the tables (which are defined to be in tablespace VIN_D) into tablespace VINS_D. I have seen that working all the time in the past. Until this day when it did not work as expected. My import hung…it was due to the RESUMABLE=Y parameter I added to be able to handle out of space issues if they would occur.  But apparently RESUMABLE=Y and the need to import into a different tablespace without the hassle of pre-creating the tables first does not seem to work. It was not after I removed my RESUMABLE=Y that Import went ahead and created the tables in the default tablespace of user VINS.

Leave a comment