moving objects from one tablespace to another
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: moving objects from one tablespace to another

  1. #1
    Join Date
    Nov 2000
    Posts
    198
    Hi,
    what is the easiest way to move a user with its objects
    from one tablespace to another

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    In 8.1.x:
    alter table move tablespace new_ts;
    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
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Hard way, if you are not using the latest version oracle 8i which has trasportable tablespaces feature.

    Listing out all the objects and inlude all the objects in export and import.

  4. #4
    Join Date
    Nov 2000
    Posts
    198
    How about if I did and EXP FOR ALL the USERS I need
    to move then drop all the users and create them again
    with a new default tablespace !!!!!!! wouldn't that be
    easier than doing on the table level ????

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    The problem here is if you export full=y or specific users, it will also export the definitions of objects ( I mean definition tablespaces, the tables belongs to) and when you import, it will try to create that tablespace as part of the object definitions and imports into that tablespace if it cant find one thats already defined/precreated.

    so your import fails unless otherwise it finds precreated tablespace (specifying ignore=y) or its allowed to create tablespace (ignore=n).

    Its just like reorganizing your databases. The best bet is to use Enterprize Manager Reorg utility if you do have...

  6. #6
    Join Date
    Dec 2000
    Posts
    46
    There is no straight forward way that I know of. But this is what I have
    done in the past

    Assume you wanted to move table emp from tablespace user_data
    to tablespace emp_data within the same schema.


    rename emp to emp_old; -- so that nobody does any dml while you are moving

    create table emp_new
    tablespace emp_data
    as select * from emp
    unrecoverable --- this line will skip generating redo's
    /

    validate the number of rows in both table; -- Always perform this check
    count(*) from emp_old;
    count(*) from emp_new;

    rename emp_new to emp;
    create the indexes, constraints;
    I don't what happens to tables that reference emp; (you have to check this)
    drop and recreate any synonyms that you have to;
    grant insert, update, delete to those who had before;
    compile any invalid objects;

    You should retain emp_old for a few weeks before you get rid of it (just in case).

    This should do it I think.

  7. #7
    Join Date
    Aug 2000
    Posts
    194
    you can export the table/user, import them with "indexfile=<filename>" option which will create the object scripts. Edit the file <filename>, change whatever is needed. import the data without indexfile option and with rows=y,...

    Your drop user option may also work. Create the user with the default tablespace and make sure to alter the user to have 0K quota on the previous default tablespace.
    (use this option if u have to move most of the objects for the user)

  8. #8
    Join Date
    Nov 2000
    Posts
    198
    Sreddy,
    How come when IMP users it also import the tablespace found
    in the dump file!!!! there is a DESTROY option DEFAULTED=N (which means don't execute CREATE TABLESPACE that exist
    in the dump file).

    I thought IGNORE option is used for (not)displaying errors only. (does it actually do overwrite the existing objects if it was set to Y???)

    thanks,

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    ignore=y will insert rows and ignore the object creation error, if the table doesnt have any constraint then you will get duplicate rows if ignore is set to N then insert rows part will be skipped if object exists

  10. #10
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    [QUOTE][i]Originally posted by ocpdude [/i]
    [B]Sreddy,
    How come when IMP users it also import the tablespace found
    in the dump file!!!! there is a DESTROY option DEFAULTED=N (which means don't execute CREATE TABLESPACE that exist
    in the dump file).

    I thought IGNORE option is used for (not)displaying errors only. (does it actually do overwrite the existing objects if it was set to Y???)

    thanks, [/B][/QUOTE]

    yes, You are right. IGNORE=y is just a flag to indicate how object creation errors should be handled.

    DESTROY=N is option to reuse the existing datafile, if the datafile exists already, not to stop executing CREATE TABLESPACE command in the dump file. CREATE TABLESPACE command will be successful if its using different datafile other than one that exists, existing datafile can be used specifying DESTROY=y.

    For tables, IGNORE=Y causes rows to be imported into existing tables. No message is given. If a table already exists, IGNORE=N causes an error to be reported, and
    the table is skipped with no rows inserted.

    Also, objects dependent on tables, such as indexes, grants, and constraints, will not be created if a table already exists and IGNORE=N.

    So, you have to make sure to get objects depends on tables also be moved to the new tablespace.

    [Edited by sreddy on 01-03-2001 at 01:55 PM]

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