DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Re-organisation of table using CLOB datatype column

  1. #1
    Join Date
    Sep 2006
    Location
    London
    Posts
    58

    Re-organisation of table using CLOB datatype column

    I need to move some tables from the current tablespace(schema) to another tablespace. I also need to poupalte these data back to original tablespace as a part of table re-oragnisation using

    ALTER TABLE tablename MOVE TABLESPACE

    It may be re-oranised in the same tablepsace or another.

    But one of my table contains CLOB datatype. I wanted to know this type of data type would be handled using Oracle 8.1.7.

    I wish to follow steps, if CLOB datatype handelded

    A. Create a new tablespace TEMP_DATA with autoextend option of datafile

    B. Move all the tables of schema (tablespace) in the new tablespace, moving the data we want to save over temporarily

    C. Truncate all the tables from schema

    D. Resize the current tablespace, if required.

    E. Move the data from TEMP_DATA back to the current
    tablespace

    F. Re-build all the concern unusable or invalidated indexes

    G. Drop the TEMP_DATA tablespace

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    If the whole idea is to reorg these tables...

    May I suggest:
    1-- Export whole schema
    2-- Truncate all schema tables
    3-- Coalesce tablespaces
    4-- Import fromuser/touser skip=y

  3. #3
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Quote Originally Posted by dbajay
    I need to move some tables from the current tablespace(schema) to another tablespace. I also need to poupalte these data back to original tablespace as a part of table re-oragnisation using

    ALTER TABLE tablename MOVE TABLESPACE

    It may be re-oranised in the same tablepsace or another.

    But one of my table contains CLOB datatype. I wanted to know this type of data type would be handled using Oracle 8.1.7.

    I wish to follow steps, if CLOB datatype handelded

    A. Create a new tablespace TEMP_DATA with autoextend option of datafile

    B. Move all the tables of schema (tablespace) in the new tablespace, moving the data we want to save over temporarily

    C. Truncate all the tables from schema

    D. Resize the current tablespace, if required.

    E. Move the data from TEMP_DATA back to the current
    tablespace

    F. Re-build all the concern unusable or invalidated indexes

    G. Drop the TEMP_DATA tablespace

    I'm not sure where the question is here, you seem to have it all in hand...

    Are you asking if this is possible in 8.1.7?:

    Code:
    ALTER TABLE tab_with_lob
        MOVE LOB(c/blob_column) STORE AS (
            TABLESPACE new_tablespace
        )
    /
    Assistance is Futile...

  4. #4
    Join Date
    Sep 2006
    Location
    London
    Posts
    58
    I wanted to know that I should provide the follwing statement or suggested by you.

    I mean it is possible to handel and to move data for CLOB datatype using

    ALTER TABLE tablename MOVE TABLESPACE

    I need to deal the follwing statement only for CLOB.

    I am not cleared about this one.

    I will appreciate if you could explain me,

    ALTER TABLE tab_with_lob
    MOVE LOB(c/blob_column) STORE AS (
    TABLESPACE new_tablespace
    )

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Statement below will move table to new tablespace BUT will not move LOB segments from current tablespace...

    ALTER TABLE table_name MOVE TABLESPACE target_tablespace
    /


    Statement below will move segments that make up specific LOB column to new tablespace...

    ALTER TABLE table_name
    MOVE LOB(clob_column) STORE AS (
    TABLESPACE target_tablespace
    )
    /

    Hope this helps.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Sep 2006
    Location
    London
    Posts
    58
    Thanks for your reply. But, I wanted to know any impact or missingd data by just my moving table to another tablepspace which contatns LOB type of column.

    ALTER TABLE tablename MOVE TABLEPSCAE tablespace name

    Your clarification would be greatly appreciated.

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    LOB data is stored outside the table.

    Your alter-table-move statement will move your "table" to the new tablespace while keeping your LOB data in the same place where it was before alter-table-move.

    You can always conduct a test and see how nice it behaves.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Sep 2006
    Location
    London
    Posts
    58
    If I use the tabespace approch for moving table from one tablespace to another then is it required to truncate all schema tables.

    Please make your comment on it.


    Quote Originally Posted by PAVB
    If the whole idea is to reorg these tables...

    May I suggest:
    1-- Export whole schema
    2-- Truncate all schema tables
    3-- Coalesce tablespaces
    4-- Import fromuser/touser skip=y

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Could you define "tablespace approach"?

    Are you planing to "exp/imp"?
    or
    are you planing to "alter table move"?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #10
    Join Date
    Sep 2006
    Location
    London
    Posts
    58
    I'm planning 'alter table move' approch as one of my table is having CLOB datatype so that I can not go for exp/imp approch.

    Could you please expalin me an exact steps to move table J55 having incuding CLOB datatype including index

    1) alter table j35 move tablepsace target tablepscae

    2) alter table j35 move tablespace source tablespace

    3) alter index indexname re-build

    Please let me know alos I need to take any extra step to be invloved for CLOB daat type.

    Your help would be highly appreciated.

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