3 Replies Latest reply: May 20, 2009 2:03 AM by Marko Sutic RSS

    Export table with LOB column

    Marko Sutic

      I have to export table with lob column (3 GB is the size of lob segment) and then drop that lob column from table. Table has about 350k rows.

      (I was thinking) - I have to:
      1. create new tablespace
      2. create copy of my table with CTAS in new tablespace
      3. alter new table to be NOLOGGING
      4. insert all rows from original table with APPEND hint
      5. export copy of table using transport tablespace feature
      6. drop newly created tablespace
      7. drop lob column and rebuild original table

      DB is Oracle
      UNDO tablespace limited on 2GB with retention 10800 secs.

      When I tried to insert rows to new table with /*+append*/ hint operation was very very slow so I canceled it.

      How much time should I expect for this operation to complete?

      Is my UNDO sufficient enough to avoid snapshot too old?

      What do you think?

      Thanks for your answers!

      Marko Sutic