Is it possible to merge two tablespaces into one?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Is it possible to merge two tablespaces into one?

  1. #1
    Join Date
    Jan 2007
    Posts
    4

    Is it possible to merge two tablespaces into one?

    Hello everyone,

    We have an instance which was migrated from Windows to RedHat by export/import. The result generated two tablespace names which are quite similar, but not identical. I would like to merge the data in them in order to keep only one tablespace instead of two.

    Here is how it looks. We have the tablespace NUM_DATA01 which is in the /u05/oradata/desref/num_data01.dbf datafile.

    Next we have the NUM_DATA tablespace which lives in the /u01/app/oracle/product/920/dbs/D:ORADATADESREFNUM_DATA01.DBF datafile (which you can see it's Windows origin in the file name).

    The goal is to keep the NUM_DATA tablespace and destroy the NUM_DATA01 tablespace.

    Question is: Can we merge the data that is in NUM_DATA01 into NUM_DATA? (I don't even know if it contains data, but I don't want to take chances).

    I would then rename /u01/app/oracle/product/920/dbs/D:ORADATADESREFNUM_DATA01.DBF into /u05/oradata/desref/num_data.dbf. Finally, drop the /u05/oradata/desref/num_data01.dbf datafile.

    I know how to proceed in order to rename datafiles, but it's the data merge of NUM_DATA01 into NUM_DATA that puzzles me?!??

    Is this possible? Am I dreaming here? Are there better ways of doing this?

    Many thanks!

    David

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Use "ALTER TABLE ..MOVE TABLESPACE ..." command to move tables from one tablespace to another tablespace, and rebuild the indexes.

    Then, drop the unwanted tablespace.

    Tamil

  3. #3
    Join Date
    Jan 2007
    Posts
    4
    Hi Tamil,

    Thanks for the info. I'll RTFM the exact commands in the Oracle documentation and see if I can come up with something good

    Cheers,

    David

  4. #4
    Join Date
    Jan 2007
    Posts
    4
    Hello again,

    I need to drop a tablespace. But before I do so, I'm trying to figure out if a tablespace has any data in it with this:

    SQL> select * from dba_extents where tablespace_name = 'NUM_DATA01';

    no rows selected

    Does that mean that the tablespace NUM_DATA01 has no data in it and therefore it is safe to destroy?

    Many thanks,

    David

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    YES

    Tamil

  6. #6
    Join Date
    Jan 2007
    Posts
    4
    Great, thanks again Tamil.

    David

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