Move tables/objects to different tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Move tables/objects to different tablespace

  1. #1
    Join Date
    Dec 2000
    Posts
    75

    Unhappy

    Hi,

    Anyone have scripts or instructions on how to easily move tables/objects from say SYSTEM tablespace to a different tablespace?

    We have some objects in SYSTEM tablespace that belong to non-system user. I have read some notes from Metalink but it is sort of complex to do.

    Thanks
    goodhealth

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Since you didn't specify your version and platform, I'm assuming 8.1.7.2 and Solaris.

    Use:

    ALTER TABLE abc.xyz MOVE TABLESPACE new_ts

    and

    ALTER INDEX abc.xyz_pk REBUILD TABLESPACE new_ts

    To make this easier, you could write some SQL that generates a SQL script for you. Something like:
    SELECT 'ALTER TABLE ' || owner || '.' || table_name || ' MOVE TABLESPACE New_Ts;' FROM DBA_TABLES
    WHERE OWNER = 'THE_OWNER_OF_THE_OBJECT'

    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Aug 2000
    Location
    Peru
    Posts
    7
    Hi Friend:
    I agree with the option ALTER TABLE MOVE, but with it we can not work if the table has one field type LONG.

    In this case you must make an export, then create the table in the tablespace ( without data) and then import the data

    Javier Cullas
    Master Oracle
    Enterprise DBA - Networked &
    Distributed DBA Specialist
    http://education.oracle.com/latinamerica/masters_cert/mt.names.html

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    the problem is not we cant work if we move a table with long column, the problem is if you try to move a table with long column you get a ORA- error

    The other option (for LONG column) would be create an empty table in the tablespace you want and use sql*plus copy command and copy over the data from old table to new table since this command does support LONG

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