Changing Tablespaces
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Changing Tablespaces

  1. #1
    Join Date
    Apr 2001
    Posts
    55

    Lightbulb Changing Tablespaces

    Hi....

    Does anyone know how i can change the tablespace of a table that uses blobs. I want to change the tablespace for the LOB Storage but have so far been unsuccesful. I am using the following SQL statement:-

    ALTER TABLE IMAGE MOVE TABLESPACE XSU001

    But when i go and check the table the tablespace being used for the LOB_STAORAGE is the old one and the tablespace being used for the Table is XSU001. But ideally i would like the whole database in 1 tablespace.

    Appreciate your help!!!! and thanks in advance

  2. #2
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    That is because you move TABLE segment, but not LOB segment.
    Please check documentation:

    http://tahiti.oracle.com/pls/db901/d...25&preference=
    Best wishes!
    Dmitri

  3. #3
    Join Date
    Dec 2002
    Location
    USA
    Posts
    53
    exp the table and recreate table structure in new tablespace and imp the table

  4. #4
    Join Date
    Apr 2001
    Posts
    55
    I tried the export and import method mentioned but that did solve the problem as it still looked for the old tablespace name because the LOB segment was stored in the old tablespace.

    I'll have a look at the link mentioned above and try that.

  5. #5
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    195
    I have a table using RAW datatype. There is no problem to move it between different table spaces. Do you have enough space in the new tablespace?

  6. #6
    Join Date
    Apr 2001
    Posts
    55
    well according to the lin k i should be using the following syntax:-

    alter table image_data move lob(image) store as tablespace xsu001;

    But when i run this command it gives me the following error message
    ERROR at line 1:
    ORA-14133: ALTER TABLE MOVE cannot be combined with other operations

    Anyone any ideas?

  7. #7
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    Originally posted by hummy
    I tried the export and import method mentioned but that did solve the problem as it still looked for the old tablespace name because the LOB segment was stored in the old tablespace.

    I'll have a look at the link mentioned above and try that.
    It is impossible, if you create object and import data with option ignore=Y, you will not get any errors at all.
    Best wishes!
    Dmitri

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by hummy
    well according to the lin k i should be using the following syntax:-

    alter table image_data move lob(image) store as tablespace xsu001;

    But when i run this command it gives me the following error message
    ERROR at line 1:
    ORA-14133: ALTER TABLE MOVE cannot be combined with other operations

    Anyone any ideas?
    Hi, try this:

    ALTER TABLE image_data MOVE TABLESPACE xsu001 LOB (image) STORE AS lobsegment (TABLESPACE xsu001);
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  9. #9
    Join Date
    Apr 2001
    Posts
    55
    Ok thanks managed to do it with the import export method. Forgot the ignore=y parameter the first time round.

    Thanks everyone

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