LONG to CLOB conversion

[julian]

It took me 1h30' to convert a LONG column to CLOB in a table with 42000 record. Is this normal?

[TimHall]

Hi.

Couple of questions:

1) What method did you use (extract & load or TO_LOB)?
2) What's the average size of the LONG?

See: http://download-west.oracle.com/docs...9a.htm#1189967

Cheers

[julian]

Originally posted by TimHall
Hi.

Couple of questions:

1) What method did you use (extract & load or TO_LOB)?
2) What's the average size of the LONG?

See: http://download-west.oracle.com/docs...9a.htm#1189967

Cheers

I ran:

ALTER TABLE emp MODIFY (text_info CLOB);

I do not know the average size.

[TimHall]

OK.

It really depends on the amount of data being processed. If you only have a few rows but each one contains a large amount of text it may take longer than a small amount text in many rows etc.

I would normally use the method shown in that link. That way you can parallelize the creation and you're not having to restructure existing blocks. Once the process is complete you'll have a clean table and you can drop the old one. If availability is an issue you should think about using an online table redefinition.

Cheers dude!

[julian]

Originally posted by TimHall
OK.

It really depends on the amount of data being processed. If you only have a few rows but each one contains a large amount of text it may take longer than a small amount text in many rows etc.

I would normally use the method shown in that link. That way you can parallelize the creation and you're not having to restructure existing blocks. Once the process is complete you'll have a clean table and you can drop the old one. If availability is an issue you should think about using an online table redefinition.

Cheers dude!

But as far as I know online table redefinition does not work with LONGs:

Oracle9i Database Administrator's Guide, Chapter 15, Managing Tables, section Redefining Tables Online => Restrictions for a detailed list.