Re-organizing tables containing LONG datatypes?

From: Glenn Stauffer <stauffer_at_cc.swarthmore.edu>
Date: Thu, 01 Dec 1994 09:55:31 -0500
Message-ID: <stauffer-0112940955310001_at_stauffer.swarthmore.edu>


I am re-organizing tables in an Oracle database to compress table extents, move them to new tablespaces, and update storage parameters to properly reflect the nature of the table data and usage.

For most tables, I do this using a statement such as:

CREATE TABLE NAME_TEMP
   TABLESPACE NEW_TABLESPACE
   PCTFREE 5
   PCTUSED 90
   STORAGE (INITIAL NEW_VAL NEXT NEW_VAL PCTINCREASE NEW_VAL)    AS SELECT * FROM NAME; The concepts manual tells me that a LONG field cannot be used in a CREATE AS SELECT statement or an INSERT INTO SELECT statement.

I expect that I will need to do the following to re-organize this table:

  • unload the table data to a flat-file
  • create the new table using the proper storage parameters and new tablespace
  • load the data into the new table using sqlloader
  • drop the old table
  • rename the new table

Is there an easier way to accomplish this task?

Thanks for any suggestions.
Glenn Stauffer
DBA
Swarthmore College Received on Thu Dec 01 1994 - 15:55:31 CET

Original text of this message