LONG to CLOB conversion
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: LONG to CLOB conversion

  1. #1
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    LONG to CLOB conversion

    It took me 1h30' to convert a LONG column to CLOB in a table with 42000 record. Is this normal?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Last edited by TimHall; 09-17-2003 at 05:06 AM.
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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!
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width