1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to find all CLOBs in the database in order to convert them ?

Discussion in 'General' started by liranco, Jul 24, 2013.

  1. liranco

    liranco Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,

    Can anybody tell me how can I search my database for all the CLOBs types in order to covert them to VARCHAR(4000).

    Thanks

    LN.C
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    One, why do you want to do that? In some cases converting a CLOB to a VARCHAR2(4000) will truncate the data and make it useless.

    Two, if you are familiar at all with the data dictionary you know that DBA_TAB_COLUMNS will provide that information.

    Without a solid purpose for this action I won't go any further in assisting you as I wnn't be part of any data loss or corruption in your database. Please give a GOOD reason for what you're asking to do.
     
  3. liranco

    liranco Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi David and thank you for your answer.

    There is a need to convert all CLOB because some issue we have with distribute the data on multisite environment.
    Second, unfortunately this database, I got from one of my customers so I'm not familiar with all the data structure.
    Is it possible to query the DB and get all the table that contain CLOB type and the column name ?

    Thanks in advance.
     
  4. liranco

    liranco Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Well, I think I found the solution:
    SELECT owner, table_name,column_name
    FROM all_tab_cols where data_type = 'CLOB';

    10X
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That may not give you all of the CLOB columns in the database -- the ALL_ prefixed views report tables/views the connected user has access to, which may not be all of the tables/views in the database. You should be using dba_tab_columns to get this information.

    Also, you do NOT want to alter any of the tables owned by SYS or SYSTEM as those are necessary for the database to function.