Article-ID:         <Note:148740.1>
Circulation:        PUBLISHED (EXTERNAL)
Folder:             server.Utilities.ExportImport
Topic:              Alerts
Title:              ALERT: Export of table with dropped functional index may 
                    cause IMP-20 on import


Import may fail with IMP-20 if direct-path export taken after functional index was dropped
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Versions Affected 
~~~~~~~~~~~~~~~~~ 
  This problem:
        Does not affect Oracle 8.0 releases 
        Affects Oracle release 8.1.5, 8.1.6, 8.1.7, 9.0.1
        The issue is fixed in the 8.1.7.3 Patch Set.
 
Platforms Affected 
~~~~~~~~~~~~~~~~~~ 
  GENERIC - affects all platforms
 
Description 
~~~~~~~~~~~ 
  Direct export may create a corrupt export file if a table is exported which
  had it's functional index dropped.
  Subsequent attempts to import the affected table result in errors:
    IMP-00020: long column too large for column buffer size
  Increasing the import buffer size has no effect.  
 
Likelihood of Occurrence 
~~~~~~~~~~~~~~~~~~~~~~~~ 
  In order to hit this problem an export file must have been created under 
  the following circumstances:

    a. The database was started with compatible set to 8.1.x or higher

    b. A table had a functional index created on it

    c. The functional index was dropped

    d. The table did NOT have unused columns dropped after the index was dropped

    e. The table was exported in direct-path mode

What are the chances of hitting this problem ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  Connect as a SYSDBA user (or SYS) and run this statement:

      set echo off
      select 'ALTER TABLE '||u.name||'.'||o.name||' DROP UNUSED COLUMNS;'
        from user$ u, obj$ o, col$ c
      where o.type#=2
        and c.col#=0
        and bitand(32768,c.property)=32768
        and o.obj#=c.obj#
        and u.user#=o.owner#
        and u.user#!=0;

  If any rows are returned, drop the unused columns from the affected
  tables using the 'ALTER TABLE....' SQL statements returned from the
  above dictionary query. 

Example Sequence of events leading to a Corrupt Export file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  The steps below illustrate a common sequence of events which lead to 
  a corrupt export file:

    a. Create and populate a table    
       CREATE TABLE W21
         ( W21PROGRAMM CHAR(8) DEFAULT (' ')  NOT null,
           W21BEREICH  CHAR(2) DEFAULT (' ')  NOT null,
           OGRAMID     CHAR(8) DEFAULT (' ')  NOT null);
       INSERT INTO W21 VALUES ('SIBG43',0,'STAU');
       COMMIT;

    b. Create the functional index, then drop it
       CREATE INDEX W2102 ON W21 (W21PROGRAMM DESC) ;
       DROP INDEX W2102;

       Note: The DESC keyword causes a functional index to be created in 
       Oracle8i onwards unless the init.ora parameter _IGNORE_DESC_IN_INDEX 
       is set to TRUE. The same problem occurs for a normal functional index. 
       For example:

          CREATE INDEX W2102 ON W21 (UPPER(W21PROGRAMM)); 

    c. Export the table (direct-path)
       exp user/pw buffer=100000 direct=yes tables=w21

    d. Drop the table 
       DROP TABLE W21;

    e. Attempt to import the table
       imp user/pw fromuser=username
       IMP-00020: long column too large for column buffer size (2)
       
Workaround 
~~~~~~~~~~

    a. If a functional index is dropped, immediately drop any unused
       columns from the table on which the index was created, using the
       syntax:

         SQL> ALTER TABLE <TABNAME> DROP UNUSED COLUMNS;

       Note that in some cases this command may fail with an ORA-600 [711]
       error. In such cases it should be possible to work around this
       ORA-600 by adding a dummy column to the table then dropping it.
       eg: 
         SQL> ALTER TABLE <TABNAME> ADD ( dummy_col number );
         SQL> ALTER TABLE <TABNAME> DROP COLUMN dummy_col;
         SQL> ALTER TABLE <TABNAME> DROP UNUSED COLUMNS;
 
    b. Use export in conventional mode, rather than direct-path

    Note, it is possible to import other objects from the same export dump
    file, regardless of their ordering within the file (i.e. whether they
    were created before or after the affected table).

References
~~~~~~~~~~
  Base bug for this alert                             <Bug:1805146>
  IMP-20 with DIRECT=Y issue caused by this bug       <Bug:1754922>

Modification History
~~~~~~~~~~~~~~~~~~~~
  23-Oct-2001 This alert was modified to add steps to take if the
              DROP UNUSED COLUMNS command errors with ORA-600 [711].
  21-Jan-2002 Added fixed in 8.1.7.3 information