Is it possible to merge two tablespaces into one?
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?
Use "ALTER TABLE ..MOVE TABLESPACE ..." command to move tables from one tablespace to another tablespace, and rebuild the indexes.
Then, drop the unwanted tablespace.
Thanks for the info. I'll RTFM the exact commands in the Oracle documentation and see if I can come up with something good
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?
Great, thanks again Tamil.
Click Here to Expand Forum to Full Width