Re: Converting tables from CHAR to VARCHAR2
Date: 1996/01/26
Message-ID: <Pine.SUN.3.91.960126075912.1030D-100000_at_seatimes>#1/1
On Tue, 23 Jan 1996, Brian M. Biggs wrote:
> you if you export, drop the tables, redefine them as VARCHAR2, and
> re-import (I doubt it, but was hoping...)?
Sounds reasonable to me. Especially after you look at an export file. It's simply a set of INSERT instructions. Keep the columns named the same.
> 1) Create a second set of the tables using VARCHAR2. INSERT INTO the
> new tables from the old tables. Would probably work, but could cause
> rollback problems on larger tables. We would also have to right trim
> all CHAR columns in the INSERT statement. Yech!
On 7.2 if you use the CTAS (create table as select) with the (memmory just failed me as to the actual syntax) option to not create redo entries (can't remember the word), then no redo logs will be built. However, if the system dies in process then you need to drop and start over again. An yes, I think you will need to RTRIM the fields being changed.
>
> 3) Write Pro* language programs to extract data into flat files, right
> trim appropriate columns, and re-load using SQL*Loader. Might be the
> easiest option, since we already have SQL*Loader control files created.
> But, the extract programs might be cumbersome to write because of the
> large number of tables and columns.
Somebody here had a script that would read the USER_TABLES table and build the flat files with the unloaded data. Perhaps they will respond to this post also.
+----------------------------------------------------+ | Steve Butler Voice: 206-464-2998 | | The Seattle Times Fax: 206-382-8898 | | PO Box 70 Internet: sbut-is_at_seatimes.com | | Seattle, WA 98111 Packet: KG7JE_at_N6EQZ.WA | +----------------------------------------------------+All standard and non-standard disclaimers apply. All other sources are annonymous. Received on Fri Jan 26 1996 - 00:00:00 CET